> 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
>

> 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 Patrick
> Start-to-Finish Visual Basic 2005
>
> > 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 wrote:
>
>> 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 Patrick
>> Start-to-Finish Visual Basic 2005
>>> 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,
> 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!
>

> 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 Patrick
> Start-to-Finish Visual Basic 2005
>
> > Tim Patrick wrote:
> >
> >> 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 Patrick
> >> Start-to-Finish Visual Basic 2005
> >>> 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,
> > 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!
> >