Syntax error in INSERT INTO statement. - Access Database

[oussama_1]

Hi
I have access database with two tables With a Relationship i can insert records into the first table with no problems but when im trying to insert a record in the second table i got error message
Error message : Syntax error in INSERT INTO statement. - Microsoft Office Access Database
Second Table Name : Orders
Column Name of the second table : OrdersID
OrdersID Datatype : Numbers
Code :

  Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & My.Settings.report & "';" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")
        Dim IDnum As String = "4"
        Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO Orders (OrdersID) VALUES ('" & IDnum & "')", Connection)
        Try
            Connection.Open()
            insertCommanddss.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
        Finally
            Connection.Close()
        End Try

The Same Code Works perfectly on the First table
So Whats Wrong ?!
thanks

[PerplexedB]

1. is this compiling at all because there seems to be an extra d and s in the insertcommands under try.

2. if idnum is autonumber, primary key, you cannot insert it, you have to let access generate it for you.

3. if idnum is a foreing key and the value does not exist in the related master table then you are violating integrity.

It would help if you could let us have a look at the structure of you tables.

Good luck.

[oussama_1]

1. sorry my bad (its not the problom though :D)
2. no its not autonumber or primary key its just a number
3. the number already exist in the first table according to the relation

these are not the problem.. im going crazy over this

thank you PerplexedB

[PerplexedB]

VALUES ('" & IDnum & "')" inserts this as a string, where you defined it as a number in your table. Did you not get an error message from VB?
Good luck.

[oussama_1]

i tried these statements :

"INSERT INTO Orders (OrdersID) VALUES ('" & IDnum & "')"



"INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")"




"INSERT INTO Orders ([OrdersID]) VALUES ('" & IDnum & "')"



"INSERT INTO Orders VALUES ('" & IDnum & "')"



"INSERT INTO Orders ([OrdersID]) VALUES (" & IDnum & ")"



Dim IDnum as Integer = 4
"INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")"

all with the same error message, Syntax error in INSERT INTO statement.

[oussama_1]

Sorry i forgot to mention that OrdersID Column is a Combobox

[PerplexedB]

Oussamah, you have been telling us that you had 2 tables that are related right. So does this relationship join orders.ordersid into customer.id? if that is the case, access would not let you insert a record where orders.orderid=9 if there is no customer with id=9. Don't think that would produce a syntax error though.

Have debug.print the sql string. Often that exposes syntax errors.

Alternatively, would you show is the insert statement that does work for you?

Good luck my friend.

[oussama_1]

yes you are right about the relation but none of the codes works for me.
say there's a customer with ID = 1 and i would try the code above or any of the codes to insert the number 1 into ordersID still wont work
i think it got something to do with the fact its a combobox am i right ?
thank for your help

[oussama_1]

ok Here's the access file
Dropbox.com
user [e-mail]
pass 123456

[PerplexedB]

Sorry.... I will have a look at this tomorrow ok?

[oussama_1]

sure and thanks a lot

[PerplexedB]

OK. OrdresID is definitely in a 1 to many relationship with Customers.id. In access I was able to add a record in Orders with OrdersId = 9, but it needs a customer record with id 9.

The fact that Orderid is a combobox should not be a problem. But look at it, it will not allow you to enter values under 9!

You should

1. open your customers table and look at what id's are available in it. If there are none, you should add a record and take a note of which id is generated.

2. execute

   Dim IDnum as Integer = <any value of customer id>
   "INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")"

Good luck.

[oussama_1]

wont work :(
you have the file so please try this code :

     Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & yourlocation\InvoiceProgram.accdb & "';" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")
     ' the record already exist in customers with the ID 9
    Dim IDnum As String = "9"
    Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")", Connection)
    Try
    Connection.Open()
    insertCommands.ExecuteNonQuery()
    Catch ex As Exception
    MessageBox.Show(ex.Message & " - " & ex.Source)
    Finally
    Connection.Close()
    End Try

and tell me why it wont work
thank you

[PerplexedB]

Do you have customer 9 now? In the file that you sent me there wasn't.

[oussama_1]

there is 9 check it

[oussama_1]

Anybody ? :(

[PerplexedB]

ossamah, have you succeeded in entering a record in access? It worked for me.

[PerplexedB]

This worked for me on your original .accdb. :

Module Module1

    Sub Main()
        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=t:\_recent\130922 oussama\InvoiceProgram.accdb;Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")
        ' the record already exist in customers with the ID 9
        Dim IDnum As String = "9"
        Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")", Connection)
        Try
            Connection.Open()
            insertCommands.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message & " - " & ex.Source)
        Finally
            Connection.Close()
        End Try

    End Sub

End Module

My previous answers were based on a state of the your .accdb after I had somewhat played around with it.

Maybe you should try your program on the .accdb as you sent it to me?

I dropped my version back. Hope that will help you find out your problem.

Good luck.

[oussama_1]

so we did the same thing but it only worked for you :(
ill check my access file in dropbox tomorrow
10x PerplexedB

[oussama_1]

my code works!
this was the problem

my access file needed some query fixes
thanks PerplexedB for your time