Insert

[Guest]

Hi All,
Thank you in advance. I am trying to insert into two tables but I am
getting the following error: "You cannot add or change a record because
a related record is required in table..." I am not sure why this is
happening since I am using transactions. Below is the code I am using.


Dim con as OleDbConnection
Dim cmd as OleDbCommand
Dim tran as OleDbTranscation
Dim id as string

con = new OleDbConnection(connectionstring)
con.open
tran = con.BeginTransaction
cmd = new OleDbCommand
cmd.Connection = con
cmd.Transaction = tran
cmd.CommandText = "insert into table1 (id,name) values (1,'test name')"
cmd.ExecuteNonQuery()

cmd.CommandText = "select Max(id) from table1"
id = cmd.ExecuteScalar()

cmd.CommandText = "insert into table2 (id,name) values " & id &
",'another test name')"
cmd.ExecuteNonQuery()


Do you have any suggestions?

Thanks again!

Note: I am using an MS Access database

 

[Tim Patrick]

There is a syntax error in your second INSERT statement. You are missing
the opening parenthesis before the ID.

Beyond that, you might want to run the statements directly in Microsoft Access,
as it will give you a more complete error message.

 

[Guest]

There is a syntax error in your second INSERT statement. You are missing
the opening parenthesis before the ID.

Beyond that, you might want to run the statements directly in Microsoft Access,
as it will give you a more complete error message.

Tim,
Thank you for your quick reply. I have fixed the syntax error but I am
still getting the following error:

You cannot add or change a record because a related record is required
in table 'Table1'

Does MS Access support this type of transaction? Can you think of any
reason why the transaction wouldn't be working?

Thanks again!

 

[Tim Patrick]

Have you established any referrential integrity between the two tables, or
between any one of those two tables and other tables in your database? Have
you set up any field-specific rules? Do you have other fields in Table1 and
Table2 besides ID and Name that have referential integrity enabled, perhaps
to themselves or to each other? Did you get a chance to run the same statements
within the Access environment and check the error messages there?

I didn't see the Commit of the transaction in your code, but I assume you
are doing it just after the code block you pasted. Does the code work if
you move the commit to just after the first insert? What value do you get
back from the selection of MAX(ID)? I would think that the uncommitted transaction
would still return the right identity value for your statement within your
connection, but perhaps not.

 

[Guest]

Have you established any referrential integrity between the two tables, or
between any one of those two tables and other tables in your database? Have
you set up any field-specific rules? Do you have other fields in Table1 and
Table2 besides ID and Name that have referential integrity enabled, perhaps
to themselves or to each other? Did you get a chance to run the same statements
within the Access environment and check the error messages there?

I didn't see the Commit of the transaction in your code, but I assume you
are doing it just after the code block you pasted. Does the code work if
you move the commit to just after the first insert? What value do you get
back from the selection of MAX(ID)? I would think that the uncommitted transaction
would still return the right identity value for your statement within your
connection, but perhaps not.

Tim,
Thanks again for you help. I greatly appreciate it. It appears there is
a logic issue on my end. I removed all of the referrential integrity
and I am getting some very interesting results.

Thanks again for your assistance (and mentioning referrential
integrity) : )