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 OleDbTranscatio n
Dim id as string
con = new OleDbConnection (connectionstri ng)
con.open
tran = con.BeginTransa ction
cmd = new OleDbCommand
cmd.Connection = con
cmd.Transaction = tran
cmd.CommandText = "insert into table1 (id,name) values (1,'test name')"
cmd.ExecuteNonQ uery()
cmd.CommandText = "select Max(id) from table1"
id = cmd.ExecuteScal ar()
cmd.CommandText = "insert into table2 (id,name) values " & id &
",'another test name')"
cmd.ExecuteNonQ uery()
Do you have any suggestions?
Thanks again!
Note: I am using an MS Access database 4 1469
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 OleDbTranscatio n
Dim id as string
con = new OleDbConnection (connectionstri ng)
con.open
tran = con.BeginTransa ction
cmd = new OleDbCommand
cmd.Connection = con
cmd.Transaction = tran
cmd.CommandText = "insert into table1 (id,name) values (1,'test
name')"
cmd.ExecuteNonQ uery()
cmd.CommandText = "select Max(id) from table1"
id = cmd.ExecuteScal ar()
cmd.CommandText = "insert into table2 (id,name) values " & id &
",'another test name')"
cmd.ExecuteNonQ uery()
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 OleDbTranscatio n
Dim id as string
con = new OleDbConnection (connectionstri ng)
con.open
tran = con.BeginTransa ction
cmd = new OleDbCommand
cmd.Connection = con
cmd.Transaction = tran
cmd.CommandText = "insert into table1 (id,name) values (1,'test
name')"
cmd.ExecuteNonQ uery()
cmd.CommandText = "select Max(id) from table1"
id = cmd.ExecuteScal ar()
cmd.CommandText = "insert into table2 (id,name) values " & id &
",'another test name')"
cmd.ExecuteNonQ uery()
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 OleDbTranscatio n Dim id as string con = new OleDbConnection (connectionstri ng) con.open tran = con.BeginTransa ction cmd = new OleDbCommand cmd.Connectio n = con cmd.Transacti on = tran cmd.CommandTe xt = "insert into table1 (id,name) values (1,'test name')" cmd.ExecuteNo nQuery() cmd.CommandTe xt = "select Max(id) from table1" id = cmd.ExecuteScal ar() cmd.CommandTe xt = "insert into table2 (id,name) values " & id & ",'another test name')" cmd.ExecuteNo nQuery() 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!
Tim Patrick wrote:
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 OleDbTranscatio n Dim id as string con = new OleDbConnection (connectionstri ng) con.open tran = con.BeginTransa ction cmd = new OleDbCommand cmd.Connecti on = con cmd.Transactio n = tran cmd.CommandTex t = "insert into table1 (id,name) values (1,'test name')" cmd.ExecuteNon Query() cmd.CommandTex t = "select Max(id) from table1" id = cmd.ExecuteScal ar() cmd.CommandTex t = "insert into table2 (id,name) values " & id & ",'another test name')" cmd.ExecuteNon Query() 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!
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) : ) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Howard Hinnant |
last post by:
I recently asked for a survey of multimap insert with hint behavior, in
support of a paper I'm writing concerning lwg issue 233. My sincere
thanks to Beman Dawes, Raoul Gough, Russell Hind, Bronek Kozicki, Nicola
Musatti, John Potter and Maxim Yegorushkin for helping with that survey.
Since I started work on this paper at least two people I respect very
much have expressed interest in nailing down the "insert without hint"
function a...
|
by: Mark P |
last post by:
Some time ago I posted here about inserting into a set with a hint:
http://groups-beta.google.com/group/alt.comp.lang.learn.c-c++/browse_thread/thread/fb75b00f73e979db/018b8d0eadb38dbf?q=%22STL+insert+with+hint%22+%22Mark+P%22&rnum=1&hl=en#018b8d0eadb38dbf
I quoted the SGI STL docs describing a.insert(p, t), where p is the hint
iterator and t is the inserted object:
"Insert with hint is logarithmic in general, but it is amortized...
|
by: serge |
last post by:
I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.
I need to salvage the records from the many table and without going
into detail, one of the reasons I can't do the opposite as
there are records in the ONE table that I need to keep even if they
don't have any child records in the MANY table.
Below I created...
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
by: Carl |
last post by:
Hi,
I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with one-to-one relationship but on this
occasion
I must keep username/password details within a seperate table.
Here's the basic specs and database schema:
-------------------------------------------
| |
by: Chris Kratz |
last post by:
Hello all,
We have run into what appears to be a problem with rules and subselects in
postgres 7.4.1. We have boiled it down to the following test case. If
anyone has any thoughts as to why this would be happening, we would
appreciate feedback.
We have tested on 7.3.4, 7.3.6 and 7.4.1 and all exhibit the same behavior.
Test case one tries to populate table2 from table1 with records that are not
in table2 already. Table2 gets...
|
by: Geoffrey KRETZ |
last post by:
Hello,
I'm wondering if the following behaviour is the correct one for
PostGreSQL (7.4 on UNIX).
I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching
the following request :
INSERT INTO temp_tab VALUES (1,2,3)
|
by: MP |
last post by:
Hi
Posted this several hours ago to another ng but it never showed up thought
i'd try here.
using vb6, ado, .mdb, jet4.0, no access
given table tblJob with field JobNumber text(10)
'The example I had to go by
'INSERT INTO tblCustomers (CustomerID, , )
|
by: lenygold via DBMonster.com |
last post by:
Hi everybody:
What is the best way to
I have 10 tables with similar INSERT requiremnts.
INSERT INTO ACSB.VAATAFAE
WITH AA(AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP) AS
( SELECT AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP
FROM VAATAFAA
WHERE AB_TP_ACNT_STAT_CD <0),
|
by: EJO |
last post by:
with sql 2000 enterprise
Trying to build a stored procedure that will take the rows of a parent
table, insert them into another table as well as the rows from a child
table to insert into another table and be able to maintain the
relationships between the parent/child rows of the new records.
Something like
old_id
new_id
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |