Need multiple SQL statements to run...

Hello all. I am not too familiar with using multiple recordsets to insert multiple records at one time, so my code may be a little 'laughable', but here it goes.

What I am wanting to do is to create a record in one table, and then get the ID (Autonumber) of that record and use it to populate a field in another table called LinkId. Here is what I have so far:

<%
Dim oConn, oRS, sQry
Dim sConn

set oConn = Server.CreateObject("ADODB.Connection")
'Relpace Xxxxx with the name of your dsn
sConn = MM_timesheet_STRING
oConn.Open sConn
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oRS.ActiveConnection = oConn

'SQL RecordSet
sQry = "INSERT INTO tblEquipment (EqNum, EqDesc, Active) VALUES ('" & EqNum & "', '" & EqDesc & "', -1)"
oRS.Open sQry

'oRS.Close()
'Set oRS = Nothing
%>

<%
Dim oConn2, oRS2, sQry2
Dim sConn2

set oConn2 = Server.CreateObject("ADODB.Connection")
'Relpace Xxxxx with the name of your dsn
sConn2 = MM_timesheet_STRING
oConn2.Open sConn2
Set oRS2 = Server.CreateObject("ADODB.Recordset")
Set oRS2.ActiveConnection = oConn2

'SQL RecordSet
sQry2 = "SELECT ID FROM tblEquipment WHERE EqNum = '" & EqNum & "'"
oRS2.Open sQry2
' ------------------------------------------------

LinkId = oRS2("ID")
Response.Write("LinkId = " & LinkId & "<BR>") ' For testing purposes
%>

Well, what is happening is that I am getting the following error:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/Equipment/Eq_AddEdit2.asp, line 74

... but if I refresh the page, it works, although now I have 2 identical records in the first table (except for the Autonumber of course). It seems like the second SQL statement is trying to read the table before the first one actually finished writing to the DB.

Any suggestions would be greatly appreciated.

Thanks,

Parallon

Try this one:


<%
Dim oConn, oRS, sQry
Dim sConn

set oConn = Server.CreateObject("ADODB.Connection")
'Relpace Xxxxx with the name of your dsn
sConn = MM_timesheet_STRING
oConn.Open sConn

'SQL RecordSet
sQry = "INSERT INTO tblEquipment (EqNum, EqDesc, Active) VALUES ('" & EqNum & "', '" & EqDesc & "', -1)"
oConn.Execute(sQry)
%>

<%
Dim oRS2, sQry2

'Relpace Xxxxx with the name of your dsn
Set oRS2 = Server.CreateObject("ADODB.Recordset")

'SQL RecordSet
sQry2 = "SELECT ID FROM tblEquipment WHERE EqNum = '" & EqNum & "'"
oRS2.Open sQry2,oConn,3
LinkId = oRS2("ID")
Response.Write("LinkId = " & LinkId & "<BR>") ' For testing purposes
%>

Thank you so much. That worked perfectly. Although, I noticed that you added the following:

oRS2.Open sQry2,oConn,3

... what does the 3 do?

Also, how come you don't have to run the oConn.Execute() for sQry2 like you did with sQry?

Sorry if some of these questions are a little elementary, I am just now trying to get away from DreamWeaver writing my recordsets and other code for me.

Thanks in advance,

Parallon

the 3 SSJ used is an ADO constant for the type of database cursor used. In this case it stands for adOpenStatic

The 1st query was done using the Execute method of the Connection object.

The second query uses the Recordset object's open method