sql statement

[cindynicole]

Hi all, I need a fresh set of eyes :eek: . Can anyone see what is wrong w/the sql statement?

Dim sql As String = "INSERT INTO order " & "VALUES ('" & itemId & "', '" & description & "', " & oNum & ")"

Danka, Cindy

[Yomet]

Hi cindynicole,

At first glance I cannot see anything flagrantly wrong, however, I don't know the structure of the table.

If you are not using all the fields of the table you have to specify which fields will hold the inserted data, i.e.
"INSERT INTO order (ID, Desc, oNum) " & _
"VALUES ('" & itemId & "', '" & description & "', " & oNum & ")"

Next, verify that your ID is unique, if necessary, and that it is of String type. Also verify that oNum is actually of numeric type.

Another thing is that if Description contains single quotes you will get an error since you are using single quotes as string delimiters, i.e. [, '" & description & "',]. The way around this is to use double quotes as delimiters, this makes the code look ugly but it works. Like so:
"VALUES ('" & itemId & "', " & Chr(34) & description & Chr(34) & ", " & oNum & ")"

That's all I can give you for now, if you still have problems I will need to know the table structure and anything else about the DB.

Hope this helps

Yomet

[cindynicole]

Hi cindynicole,

At first glance I cannot see anything flagrantly wrong, however, I don't know the structure of the table.

If you are not using all the fields of the table you have to specify which fields will hold the inserted data, i.e.
"INSERT INTO order (ID, Desc, oNum) " & _
"VALUES ('" & itemId & "', '" & description & "', " & oNum & ")"

Next, verify that your ID is unique, if necessary, and that it is of String type. Also verify that oNum is actually of numeric type.

Another thing is that if Description contains single quotes you will get an error since you are using single quotes as string delimiters, i.e. [, '" & description & "',]. The way around this is to use double quotes as delimiters, this makes the code look ugly but it works. Like so:
"VALUES ('" & itemId & "', " & Chr(34) & description & Chr(34) & ", " & oNum & ")"

That's all I can give you for now, if you still have problems I will need to know the table structure and anything else about the DB.

Hope this helps

Yomet

Thank you Yomet,
Here is my function, trying out the code you suggested, I get a syntax error in my INSERT INTO

Public Shared Function AddToOrderTbl(ByRef menuItems As ArrayList)
        'received receipt array from order
        Dim adpOrder As New OleDbDataAdapter
        conn.Open()
        MsgBox("number in array is  " & menuItems.Count & " oNum value should be count + 1")

        'loop thru array to get ind product ordered to add to order table
        For i As Integer = 0 To menuItems.Count - 1
            Dim newItem As Product
            newItem = menuItems.Item(i)
            Dim itemId As String = newItem.mId
            Dim description As String = newItem.mSize
            Dim oNum As Integer = menuItems.Count + 1



            'Dim sql As String = "INSERT INTO order " & "VALUES ('" & itemId & "', '" & description & "', " & oNum & ")"

            Dim sql As String = "INSERT INTO order (productId, productDescription, orderNumber) " & "VALUES ('" & itemId & "', " & Chr(34) & description & Chr(34) & ", " & oNum & ")"


            'view sql 
            MsgBox(sql)
            Try

                adpOrder.InsertCommand = New OleDbCommand(sql, conn)

                adpOrder.InsertCommand.ExecuteNonQuery()

                ''ListRecords()  ' Invoke ListRecords method
            Catch ee As Exception
                MsgBox(ee.ToString)
            End Try
        Next



        conn.Close()


    End Function

my table is order
fields are: productId as text
productDescription as text
orderNumber as number

and there is no PK

I print out the sql in a msg box and it's receiving the values intended and the sql looks right. but it won't INSERT

Again, Thank you for your time, if i can give you more info i will

Cindy

[Yomet]

Cindy,

The only things that I can see now are:
- Is your order.productId defined as Unique? If so you need to revise your table structure.
- Does your itemID contain single quotes (apostrophies)? If so use the Chr(34) for it as well.

From what I can see there is nothing else wrong with your SQL statement, however I am not used to working in .NET nor with ADODB so it might be something specific to these environments.

If it is specific to .NET you might wat to ask this question in the .NET forum.

Hope you succeed.

Yomet

[cindynicole]

Yomet,
I will try the Chr(34) on my id as well.... there are no unuique fields, took them out thinking they would cause a problem.. thank you for your help, i'll let you know how it goes.
cindy

[cindynicole]

Well, the Chr(34) didn't work. I'll keep looking and asking around. Thanks, Cindy