String error when adding items from a listbox to an access database

Question

I am trying to add the items in a list box to an access database. However at first, I was getting an error message saying the syntax was missing but now, I seem to get Conversion from string "" to type 'Double' is not valid ERROR. I've researched into this and it says it's because there may be a textbox empty but the listbox has many items in it which is what I'm not sure on.

Help will be appreciated, thanks.

 Dim vari1 As String
        MyConn = New OleDbConnection
        MyConn.ConnectionString = connString
        MyConn.Open()
        Dim cmd1 As OleDbCommand = New OleDbCommand(str1, MyConn)
        Try
            For i As Integer = 0 To LstOrderItems.Items(i) - 1
                vari1 = LstOrderItems.Items.Item(i).ToString
                cmd1.CommandText = ("insert into RestaurantData ([Food Order]) VALUES(" + vari1 + ")")
                cmd1.ExecuteNonQuery()

            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        MyConn.Close()
    End If

Visual Vincent · Accepted Answer · 2017-07-06T12:59:27.117

1

It appears this line is your problem:

For i As Integer = 0 To LstOrderItems.Items(i) - 1

You're trying to read the item at index i (which is currently 0) as a number to loop to.

I think what you meant to write was this:

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

As for your database error you are missing two single quotes. When inserting data you must surround every value inside VALUES() with single quotes.

For instance:

INSERT INTO table_name (column1, column2, column3) VALUES('column1 value', 'column2 value', 'column3 value')

So to fix your error you must add those single quotes as well:

cmd1.CommandText = ("insert into RestaurantData ([Food Order]) VALUES('" + vari1 + "')")

Though you should really look into Parameterized queries (aka Prepared statements) since your code is vulnerable to SQL Injection.

See How do I create a parameterized SQL query? Why Should I? for more info.

edited Jul 06 '17 at 12:59

answered Jul 05 '17 at 13:01

Visual Vincent

18,045
5
28
75

That is what I had before, however now it's gone back to say `Syntax error (missing operator) in query expression` – Matthew Jul 06 '17 at 10:03
1

@Matthew : That's related to your database query and not the loop. What I've shown above is one of the correct ways to iterate every item of the `ListBox`. – Visual Vincent Jul 06 '17 at 12:28
@Matthew : I have updated my answer with a fix to your database query as well. – Visual Vincent Jul 06 '17 at 12:50
@Matthew : Glad I could help! Good luck! – Visual Vincent Jul 08 '17 at 09:59

String error when adding items from a listbox to an access database

1 Answers1