Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2006
    Posts
    72

    Unanswered: Two SQL statements?

    Hello

    I have an odd problem inserting data into an Access 2000 database and wonder if I could be assisted with it.

    I have the following in one asp file which works:

    Dim conn,rs,SQL,myMail,name,email,business,country,mes sage

    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"


    And I have the following in another asp file which also works:


    Dim sIPAddress

    sIPAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
    If sIPAddress="" Then sIPAddress = Request.ServerVariables("REMOTE_ADDR")

    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"

    When I say they work, I mean that the data is inserted into the databases.

    But when I put them together, as follows, only the INSERT IP address works (and not the INSERT name,email, etc). That is, IP address is inserted, but not names, email messages, etc).

    This is the script which combines both INSERTS that I am using:

    Dim conn,rs,SQL,myMail,name,email,business,country,mes sage,sIPAddress

    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"

    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"

    I only have one database which has two tables. One is called users and the other sIPAddresses.

    Many thanks for any suggestions.

    Steve

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You have to perform the inserts separately...
    You can use the saame connection and variables; but you have to run 2 separate execute commands.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2006
    Posts
    72
    Isn't that what I have done, George:

    Same variables, same connection ,etc and two separate SQL INSERTS:

    Dim conn,rs,SQL,myMail,name,email,business,country,mes sage,sIPAddress

    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"

    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"

    Or do you mean I need to separate asp files with one INSERT in each?

    Cheers

    Steve

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Where's your execute statement(s)?
    Code:
    Dim conn,rs,SQL,myMail,name,email,business,country,mes sage,sIPAddress
    
    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"
    
    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2006
    Posts
    72
    I have this:

    rs.Open SQL, conn

    As I say, it inserts one lot of data into one table, but not both sets of data into both tables.

    Cheers and thanks for your help (again!)

    Steve

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post the full code
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2006
    Posts
    72
    This is it:

    <%@LANGUAGE="VBSCRIPT"%>
    <%
    Option Explicit
    Response.Buffer = True
    %>
    <%
    Dim conn,rs,SQL,myMail,name,email,business,country,mes sage,sIPAddress

    sIPAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
    If sIPAddress="" Then sIPAddress = Request.ServerVariables("REMOTE_ADDR")

    name = Request.Form("name")
    email = Request.Form("email")
    business = Request.Form("business")
    country = Request.Form("country")
    message = Request.Form("message")

    'Open MS Access database, store form field values, and close

    set conn=Server.CreateObject("ADODB.Connection")

    conn.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\form.mdb;"
    set rs = Server.CreateObject("ADODB.recordset")

    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"

    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"

    rs.Open SQL, conn

    Set rs=Nothing

    conn.Close
    Set conn=Nothing
    %>

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oki doke, the problem seems fairly obvious now looking at the whole thing...

    You're assigning a value to your variable SQL; and then overwriting the value with a new one!

    What you need to do is
    Code:
    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"
    rs.Open SQL, conn
    rs.Close
    
    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"
    rs.Open SQL, conn
    rs.Close
    See the difference?
    2 different recordsets being opened; one for each statement.
    I'm fairly sure you have to close the connection inbetween; but maybe give it a try without.

    Good luck!
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2006
    Posts
    72
    Hello George

    I'm sure I have done it right (just copied and pasted):

    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"
    rs.Open SQL, conn
    rs.Close

    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"
    rs.Open SQL, conn
    rs.Close

    'SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    'name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"

    'SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"

    'rs.Open SQL, conn

    Set rs=Nothing

    conn.Close
    Set conn=Nothing


    But now nothing is inserted in either table.

    Strange, isn't it!?

    Steve

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't have anything available to me right now to play with to check what's going on... Give this a try though
    Code:
    set rs1 = Server.CreateObject("ADODB.recordset")
    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"
    rs1.Open SQL, conn
    rs1.Close
    Set rs1 = Nothing
    
    set rs2 = Server.CreateObject("ADODB.recordset")
    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"
    rs2.Open SQL, conn
    rs2.Close
    Set rs2 = Nothing
    
    conn.Close
    Set conn=Nothing
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2006
    Posts
    72
    Pasted this in:

    set rs1 = Server.CreateObject("ADODB.recordset")
    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"
    rs1.Open SQL, conn
    rs1.Close
    Set rs1 = Nothing

    set rs2 = Server.CreateObject("ADODB.recordset")
    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"
    rs2.Open SQL, conn
    rs2.Close
    Set rs2 = Nothing

    conn.Close
    Set conn=Nothing


    Still nothing, in either table, being added.

    Steve

  12. #12
    Join Date
    Feb 2006
    Posts
    72
    I think what I might do, George, use two different asp files. One for each INSERT.

    Steve

  13. #13
    Join Date
    Jan 2008
    Posts
    6
    Code:
    <%
      Dim conn, rs1, rs2
    
      Set conn = Server.CreateObject("ADODB.Connection")
      conn.Provider = "Microsoft.Jet.OLEDB.4.0"
      conn.Open(Server.Mappath("yourDatabase.mdb"))
      Set rs1 = Server.CreateObject("ADODB.recordset")
      Set rs2 = Server.CreateObject("ADODB.recordset")
    
      Set rs1 = conn.Execute("INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')
      Set rs2 = conn.Execute("INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')
    %>
    <%
    	rs1.Close
    	set rs1=nothing
    	rs2.Close
    	set rs2=nothing
    	conn.Close
    	set conn=nothing
    %>

  14. #14
    Join Date
    Feb 2006
    Posts
    72
    Hello Monie

    Thank you for your message.

    I have actually got it working without using rs (can you only use rs in particular cases?) using this:

    <%
    Dim sIPAddress

    sIPAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
    If sIPAddress="" Then sIPAddress = Request.ServerVariables("REMOTE_ADDR")

    'Open MS Access database, store form field values

    set conn=Server.CreateObject("ADODB.Connection")

    conn.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\form.mdb;"

    SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & _
    sIPAddress & "')"

    conn.Execute(SQL)

    'now you have executed the first query, here you start to build the query again but for the second table;

    Dim conn,SQL,myMail,name,email,business,country,messag e

    name = Request.Form("name")
    email = Request.Form("email")
    business = Request.Form("business")
    country = Request.Form("country")
    message = Request.Form("message")

    SQL="INSERT INTO users (name, email, business, country, message) VALUES ('" & _
    name & "', '" & email & "','" & business & "', '" & country & "', '" & message & "')"

    conn.Execute(SQL)

    conn.Close
    Set conn=Nothing
    %>

    The sequence seems to be:

    build connection
    build query1
    execute query1
    build query2
    execute query2
    close connection

    Cheers for your post!

    Steve

  15. #15
    Join Date
    Jan 2008
    Posts
    6
    Good, that's a lot simpler and organized!
    Lucky you didn't open the connection two times LOL
    Cheers...

Posting Permissions

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