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?

Thanks

Hi 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
%>

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!