10 Replies - 282 Views - Last Post: 17 October 2018 - 07:46 AM Rate Topic: -----

#1 hiran1800   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 06-November 10

Error to save listview data into mysql database in VB.net

Posted 16 October 2018 - 08:10 PM

I import data from another source ( pervasive sql ) into listview and i want to save data in list view into database mysql but it's show many error ( in attachfile )
i don't know what happen, I think all code below correct.

Sub ImportIC()

        Dim cnSave As New ClsCommand
       
        Dim sqlinsert As String = ""

      
            Dim i As Integer = 0
            For i = 0 To ListView1.Items.Count - 1


                With ListView1

                    sqlinsert = "insert into tb_icitem(iccode,icdes,icunit,icgroup1,icgroup2)"
                    sqlinsert = sqlinsert & " value ('" & .Items(i).SubItems(1).Text & "','" & .Items(i).SubItems(2).Text & "','" & .Items(i).SubItems(4).Text & "','" & .Items(i).SubItems(5).Text & "','" & .Items(i).SubItems(6).Text & "')"


                    cnSave.ExcData(sqlinsert)


                End With

            Next

                      
             cnSave.closedb()

       
    End Sub


Attached File(s)

  • Attached File  ERROR.pdf (242.23K)
    Number of downloads: 9


Is This A Good Question/Topic? 0
  • +

Replies To: Error to save listview data into mysql database in VB.net

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14776
  • View blog
  • Posts: 59,069
  • Joined: 12-June 08

Re: Error to save listview data into mysql database in VB.net

Posted 16 October 2018 - 09:02 PM

Please copy/paste your error messages here.

What is 'ClsCommand'?
I would suggest using parameters.
Was This Post Helpful? 0
  • +
  • -

#3 hiran1800   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 06-November 10

Re: Error to save listview data into mysql database in VB.net

Posted 16 October 2018 - 09:32 PM

Error show :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'S CLIP ';'PCS ';RM01 ';'RACCES'' at line 1
Was This Post Helpful? 0
  • +
  • -

#4 hiran1800   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 06-November 10

Re: Error to save listview data into mysql database in VB.net

Posted 16 October 2018 - 09:40 PM

I think that the problem come from data in list view , It's may be have specific word ??
I try to export data from listview to excel , I saw empty space and '
looklike press space bar , please you see picture in attachment.
Was This Post Helpful? 0
  • +
  • -

#5 hiran1800   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 06-November 10

Re: Error to save listview data into mysql database in VB.net

Posted 17 October 2018 - 03:15 AM

Now, It's ok
I correct reserve word " ' "
Was This Post Helpful? 0
  • +
  • -

#6 hiran1800   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 06-November 10

Re: Error to save listview data into mysql database in VB.net

Posted 17 October 2018 - 03:29 AM

But another problem :
data in the field ,it's not fit
Example : Code ID = AC102
the cursor should be after AC102| but in my database show curror very far from this code like this AC102 | , it's look like have null value also.
What the problem this.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Error to save listview data into mysql database in VB.net

Posted 17 October 2018 - 05:14 AM

Perhaps the data coming from pervasive sql includes some additional hidden or foreign characters. Try putting the data into another environment, such as Notepad++.
Was This Post Helpful? 0
  • +
  • -

#8 hiran1800   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 06-November 10

Re: Error to save listview data into mysql database in VB.net

Posted 17 October 2018 - 07:01 AM

I already check these data but no additional hidden.

Is it possible that me method incorrect ?
I start to pull data from pervasive sql and show it on the list view and after I transfer from list view into mysql.
Can i transfer direct from pervasive to mysql without pass list view ? if yes please sample.
If still same , I think source data in pervasive sql may problem ??
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14776
  • View blog
  • Posts: 59,069
  • Joined: 12-June 08

Re: Error to save listview data into mysql database in VB.net

Posted 17 October 2018 - 07:08 AM

Again - can you use parameters? That query string concatenation is always a big target for errors. As in what happens when there's a tick mark in your text? Yeah, it throws off everything. Parameters - less so.
Was This Post Helpful? 0
  • +
  • -

#10 hiran1800   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 06-November 10

Re: Error to save listview data into mysql database in VB.net

Posted 17 October 2018 - 07:25 AM

i don't know what parameter , please sample. I never use it.
here is code to connect database and show on listview.


Dim cn As New PsqlConnection(Strcon2)
        Dim cm As PsqlCommand = cn.CreateCommand
        Dim dr As PsqlDataReader

        Dim sql As String
        If cn.State = ConnectionState.Open Then cn.Close()
        cn.Open()

        sql = "Select ITEMNO ,""DESC"", INACTIVE , STOCKUNIT  , ITEMBRKID ,CNTLACCT from ICITEM"
        sql = sql & " Where INACTIVE = '0'and ITEMBRKID <> 'FG01'  "

        cm = New PsqlCommand(sql, cn)
        dr = cm.ExecuteReader

        Dim i As Integer = 0


        ListView1.Items.Clear()
        If dr.HasRows <> 0 Then
          
                Do While dr.Read()
                    i = i + 1

                    Dim newitem As New ListViewItem(New String() {i, dr.Item("ITEMNO").ToString(), dr.Item("DESC").ToString(), dr.Item("INACTIVE").ToString(), dr.Item("STOCKUNIT").ToString(), dr.Item("ITEMBRKID").ToString(), dr.Item("CNTLACCT").ToString()})
                    Me.ListView1.Items.Add(newitem)

                Loop
cn.Close()
           



Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14776
  • View blog
  • Posts: 59,069
  • Joined: 12-June 08

Re: Error to save listview data into mysql database in VB.net

Posted 17 October 2018 - 07:46 AM

I do not know what 'PsqlCommand' or 'ClsCommand' are.

In general - parameters are ways to use place holders instead of appending text.. they handle weird formatting issues and are better for security.
Example:
https://msdn.microso...y/80x06z3b.aspx
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1