Save Listview items to database

[wilsonchama]

Hi guys,
I am trying to save the items in a listview to Access database. The listview has two columns;
"Description and Price".
For example, If a user adds three Items (products) in a listview and clicks a command button, I want these items to be saved in a database table calleed "tblSales", each item list in listview to be inserted in a new table row.

The code I have tried so far is

 Private Sub btnCpltSale_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCpltSale.Click

        myConnToAccess.Open()
        For Each Sale As ListViewItem In lsv1.Items
            Dim sqlQuery As String = "INSERT INTO tblSales (proDescription, SaleValue) Values ('" & _
            Sale.SubItems(0).Text & "' ,'" & _
            Sale.SubItems(1).Text & "')'"

            Dim cmd As New OleDbCommand
            With cmd
                .CommandText = sqlQuery
                .Connection = myConnToAccess
                .ExecuteNonQuery()

            End With
            MsgBox("Data Saved")
            lsv1.Items.Clear()

        Next



    End Sub

But When the programm is run, it is givving an error message "Missing semi colon in in the sql statement". This error is occuring at ".ExecuteNonQuery()" line.

Someone to help me please.
Thanks in advance.

[Reverend Jim]

Replace

Dim cmd As New OleDbCommand

with

Debug.WriteLine(sqlQuery)
Dim cmd As New OleDbCommand

and post the output here.

[wilsonchama]

Thanks for your response Reverend Jim

I tried to replace your suggested code, and now the code looks as shown below.

Private Sub btnCpltSale_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCpltSale.Click

        myConnToAccess.Open()
        For Each Sale As ListViewItem In lsv1.Items
            Dim sqlQuery As String = "INSERT INTO tblSales (proDescription, SaleValue) Values ('" & _
            "'" & Sale.SubItems(0).Text & "' ,'" & _
            "'" & Sale.SubItems(1).Text & "')'"

            Debug.WriteLine(sqlQuery)
            Dim cmd As New OleDbCommand
            With cmd
                .CommandText = sqlQuery
                .Connection = myConnToAccess
                .ExecuteNonQuery()

            End With
            MsgBox("Data Saved")
            lsv1.Items.Clear()

        Next


    End Sub

But then, I am getting another error message on the same line as the previous one, and the error message says "Syntax error (missing operator) in query expression '''Big Bucket' ,''100''.
The last line Big "bucket" and the "100" are the values of the two listview columns "Description" and "Price"

[Reverend Jim]

In order to determine what is wrong with your query we have to see the query and that depends on the values you are patching in from the listview. You didn't post the output of the Debug.WriteLine statement so we still don't know what the query looks like.

[wilsonchama]

Sorry for that, after adding the "Debug.WriteLine" statement the output was

"Syntax error (missing operator) in query expression '''Big Bucket' ,''100''

where Big bucket is the listvew item in the first Column called (product "Description" and 100 is also a listview item in the second column Called "Price".

The Query am using is

For Each Sale As ListViewItem In lsv1.Items
            Dim sqlQuery As String = "INSERT INTO tblSales (proDescription, SaleValue) Values ('" & _
            "'" & Sale.SubItems(0).Text & "' ,'" & _
            "'" & Sale.SubItems(1).Text & "')'"

And the query is being executed by the lines below

 Debug.WriteLine(sqlQuery)
            Dim cmd As New OleDbCommand
            With cmd
                .CommandText = sqlQuery
                .Connection = myConnToAccess
                .ExecuteNonQuery()

[Reverend Jim]

The output of the Debug.WriteLine statement should look like

INSERT INTO tblSales (proDescription, SaleValue) Values ...

If you don't see it then replace the line with

MsgBox(sqlQuery)

I need to see the value of sqlQuery.

[Rakhmat_1]

Dim sqlQuery As String = "INSERT INTO tblSales (proDescription, SaleValue) Values ('" & _
            Sale.SubItems(0).Text & "' ,'" & _
            Sale.SubItems(1).Text & "')"

[Reverend Jim]

You know, I had to stare at that for five minutes before I spotted the difference. Kind of makes the point for using parameterized queries.

[jwenting]

even if you're not facing potential SQL injection attacks (and who isn't facing those these days...).