sql statement
 

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

Re: sql statement
 

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

Re: sql statement
 

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

Re: sql statement
 

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

Re: sql statement
 

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

Re: sql statement
 

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