Ошибка синтаксиса в инструкции insert into access

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.

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

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.

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.

Sorry i forgot to mention that OrdersID Column is a Combobox

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.

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

ok Here’s the access file
Dropbox.com
user oussama.j.zeidan@gmail.com
pass 123456

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

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.

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

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

there is 9 check it

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

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.

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

my code works!
this was the problem

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