I need to execute TWO SQL statements...how?

I basically have this:

<%
Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\isclassof2003\db\ForSale.mdb"))

sSQL = "SELECT NetID FROM Student"
Set oRS = oConn.Execute(sSQL)

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

But right after I execute the first SQL statement, I wanna run this too:

sSQL = "INSERT INTO Student (NetID,Password) VALUES ('" & netid & "','" & pass1 & "')"

Do i need to close the first connecttion and open a new one? if so, do i need to assign new variables to it or just re-use the ones i have now?

Or can I just insert the second SQL statement straight in wherever i need it?

ThanksHi Delphi,

You should be able to execute your INSERT statement after opening the recordset, using the same data connection.

You might need to open the recordset into a dedicated object, to separate the connection object and the recordset.

Try this:

<%
Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\isclassof2003\db\ForSale.mdb"))

Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.ActiveConnection = oConn
oRS.Source = "SELECT NetID FROM Student"
oRS.Open()

oConn.Execute "INSERT INTO Student (NetID,Password) VALUES ('" & netid & "','" & pass1 & "')"

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>|||thanks!|||You do not need to close the connection if you execute the queries in the same database. You just close the record set and re-open it for another query execution. In your case, you do not need a record set for your second query.

<%
Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\isclassof2003\db\ForSale.mdb"))

sSQL = "SELECT NetID FROM Student"
Set oRS = oConn.Execute(sSQL)
.....
oRS.Close

sSQL = "INSERT INTO Student (NetID,Password) VALUES ('" & netid & "','" & pass1 & "')"

oConn.Execute(sSQL)

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>|||Originally posted by gyuan
You do not need to close the connection if you execute the queries in the same database. You just close the record set and re-open it for another query execution. In your case, you do not need a record set for your second query.

<%
Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\isclassof2003\db\ForSale.mdb"))

sSQL = "SELECT NetID FROM Student"
Set oRS = oConn.Execute(sSQL)
.....
oRS.Close

sSQL = "INSERT INTO Student (NetID,Password) VALUES ('" & netid & "','" & pass1 & "')"

oConn.Execute(sSQL)

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

Should the second "oConn.Execute(sSQL)" be "Set oRS = oConn.Execute(sSQL)"?|||Delphi,

Only queries which return a set of records need the 'Set oRS = ' statement. (Generally SELECT queries).

'Action' commands, such as INSERT, DELETE, UPDATE don't return a recordset, so they can just be executed like so:

oConn.Execute(sSQL)

Hope this helps!