Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: 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?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    5
    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
    %>

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    thanks!

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    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
    %>

  5. #5
    Join Date
    Feb 2004
    Posts
    5
    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)"?

  6. #6
    Join Date
    Feb 2004
    Posts
    5
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •