Help with SQL

[chris6user]

Hi,

I have to insert a new record in two tables

Table1(IDTable1, IDTable2,Value1, Value2)
Table2(IDTable2,Value1)

What the statment will look like?

Thanks.

 

[Ladyhawk]

For starters you will need two insert statements...

INSERT INTO Table1(IDTable1, IDTable2,Value1, Value2)
VALUES (blah, blah, blah, blah)

INSERT INTO Table2(IDTable2,Value1)
VALUES (blah, blah)



Ladyhawk.
** ASP/VB/Java Programmer **

 

[NickISD]

Following on from Ladyhawk you may also need to test the value set for IDTable1 if you have a unique key on this field, if you do and the value for IDTable1 already exists within the table you will get an error. So within the insert module / function make sure you have adequate error trapping.

You could get round this by simply doing a :-
DELETE FROM Table1 WHERE IDTable1 = value1 AND IDTable2 = value2
and for the second table :-
DELETE FROM Table2 WHERE IDTable2 = value2

Or look at doing an UPDATE sql statement.

Regards, Nick

 

[tjessejeff]

Hi gang, I would like to piggy back on this one. I am having a problem with the INSERT function of SQL. I have an ACCESS database that uses auto number for the record key. When I first used the INSERT, my error was that the record key was not updatable. Then I took the record key out and now the error message is "Run-time error '-2147217900(80040e14)': Syntax error (missing operator) in query expression". All I did was take out the record key. Here is my SQL statement below.


' Construct SQL statement
SQL_Statement = "INSERT INTO [tblProblemID] " _
& "([Issue Date],[Program Area],[Functional Area],[Problem Description],[Impact],[Goal],[Measurement])" _
& "VALUES (" _
& Issue_Date & "','" _
& Program_Area & "','" _
& Functional_Area & "','" _
& Problem_Description & "','" _
& Impact & "','" _
& Goals & "','" _
& Measurement & "'," _
& 0 & &quot"

Maybe in answering my question you all can help both of us out.

Thank you all very much for your support.

 

[petermeachem]

As far as I can see, I think you also took the ' from before Issue_Date. Difficult to read all those " and ' isn't it. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[chris6user]

Thanks, for the replys. But is it posible to insert values in two or more tables in one statment?

 

[petermeachem]

I don't think so, just do two executes. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[chris6user]

Okey, so if I create a new record and this record has an instance in 3 other tables, if for some reason one table fails to save then I have to go back and delite what was saved in other two?