asp/ultradev Insert 2 tables access db

[puterdude]

Hi,
I'm trying to insert a record into a table from a form. That works fine. Problem is I would like to simultaneously insert a record into another table using hidden fields and only 1 submit button for both. I dont even want the user to know he is creating the second record. Not being sneaky, honest.

Table 1 creates a record with an autoincrement field as key. It would be great to be able to pass this key to the other table while doing the insert on the second table. I didn't think that was possible because the key is not created until the insert is submitted.

I am wanting to create new records in two tables because I ran out of room in the one table > 255 fields. I have to keep them in sync somehow and that is the problem.

Is this possible? Suggestions for doing this?

Thank you.

Puterdude

 

[link9]

True the field won't be created until the insert is committed, and then it's gone...

However, you could then run a query to retrieve the record you just inserted in the table based on what you know you just inserted, and then extract the value and do your insert on the other table...

I don't **think** there's going to be a much slicker way to take care of it...


Paul Prewett

 

[puterdude]

Drat!
Well, I thought so. Ok, so I commit the record and I know what I committed. Is there a way to simply insert a record into another table at the same time and passing some of the same fields in the first table to the second table, on the same form?

 

[link9]

Certainly... just build yourself another handy-dandy INSERT statement using the same values, only a different table name, and pass it to your connectionObject.execute method.

 

[puterdude]

Hate to be dense, but can you give me an example? I was going to use ultradev but it wont let you put to inserts on the same page.

 

[link9]

No problem --

Let's say I have two tables, three fields each, 'empID', 'empName', and 'empAddress' --

Table names are table1 and table2

Ok, then, I have the same three form element names on some previous page that has just posted to this page, and I'm gonna pick up the values, and stick them into both tables...

dim empName, empID, empAddress
empName = request.form("empName")
empID = request.form("empID")
empAddress = request.form("empAddress")

dim sql1, sql2

sql1 = "INSERT INTO table1 empName, empID, empAddress VALUES ("
sql1 = sql1 & empName & ","
sql1 = sql1 & empID & ","
sql1 = sql1 & empAddress & ")"

sql2 = "INSERT INTO table2 empName, empID, empAddress VALUES ("
sql2 = sql1 & empName & ","
sql2 = sql1 & empID & ","
sql2 = sql1 & empAddress & ")"

dim con
set con = server.createobject ("ADODB.Connection")
con.open ("DSN=myDSN;UID=uid;PWD=pwd")

con.execute sql1
con.execute sql2

con.close
set con = nothing

Yes?

 

[puterdude]

Well, it looks simple enough...when YOU do it I'll have to rummage through the code on the that page and see if I can make it work. Many thanks...you guru you!

puterdude