Using asp code to update a record and add a new record at same time

[fchan]

Hi,

Is it possible to write asp code or actually an SQL statement for my recordset object, that will update a specific field in a specific record and also add a new record to a db table all at the same time?

Thanks.

 

[vasah20]

that's a perfect time to use a stored proc.

if you can't use a stored proc, it gets a little bit harder. Are you going to be adding and updating the same table? If not, then you can't do it with just one query, you'll need to issue at least two queries...

Although you may be able to combine the two queries that you form and separate them with a semicolon. Technically it should work, but don't be surprised if it doesn't.

hth leo

------------
Leo Mendoza
lmendoza@students.depaul.edu

 

[fchan]

Yes, I am trying to add and update a record in the same table. I'm using an Access database. I know how to add a record and update a record using a query in Access, but I'm not sure how to do that using asp code.

Any suggestions?

 

[ToddWW]

You might achieve your goal using transactions. Here's how we do it.

<%
dim Conn
dim strSQL1
dim strSQL2

Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;DSN=dsnname&quot;
Conn.BeginTrans

strSQL1 = &quot;INSERT INTO user (username,password) &quot; _
& &quot;VALUES ('fchan','mypassword')&quot;

strSQL2 = &quot;UPDATE access SET count = 1 &quot; _
& &quot;WHERE username = 'fchan'&quot;

Conn.Execute strSQL1
Conn.Execute strSQL2

Conn.CommitTrans
set Conn = Nothing
%>

The Conn.CommitTrans will commit all Conn.Execute statements between Conn.BeginTrans and Conn.CommitTrans

Hope this helps.

TW

 

[fchan]

Thanks I'll give it a try.