Inserting TWO SQL statements

[SteveH]

Hello

I am writing for advice on how to insert TWO sql statements in the one asp file:

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

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

I have one MS Access 2000 database with two tables in it: one is called 'users' and the other sIPAddresses.

Many thanks.

Steve

[Brandoe85]

Execute both statements. Do you need an example on how to execute the sql?

[SteveH]

Many thanks, Brandoe85, for your reply.

At the moment I have this:

'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:\private\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

conn.Close
Set conn=Nothing

%>

Doesn't rs.Open SQL, conn execute it? But if you can provide a sample of what I could use, that would be great!

Steve

[angst]

been a while since i used access, but try:

conn.execute(SQL)

[SteveH]

Hello angst

Thanks for your post.

Would that be in addition to what I have or instead of it? But you think 2 SQL INSERT statements in the one ASP file is fine?

Many thanks again.

Steve

[Brandoe85]

Having two queries in one asp file is perfectly fine. Good luck;

[SteveH]

Hello

Thank you for your replies. I have used conn.execute(SQL) and while it seems to work in order to insert the IP address, it does not store the online form data (name, email, etc).

OK, this is what I have: two pieces of ASP script. This works on its own:

ASP 1

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

conn.Close
Set conn=Nothing
%>

and I have this script, which also works on its own:

ASP 2

<%
Dim conn,rs,SQL,myMail,name,email,business,country,message

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

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

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

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

rs.Open SQL, conn

Set rs=Nothing

conn.Close
Set conn=Nothing
%>


but if I use a combination of the two with conn.Execute(SQL), only the IP address is inserted.

This is what both scripts have in common: both have variables, both share the same database, both use the INSERT statement, both use set conn, conn.Open, conn.Close, and Set conn=Nothing.

What they do not share are these: conn.Execute(SQL) which I have used in ASP 1 script only, and rs.Open SQL, conn and Set rs=Nothing, which I have used in my ASP 2 script only.

But they do not work together. I also have date columns (date/time values) in both tables, but the date is not inserted automatically in either, as I thought it would be.

Any further help would be appreciated.

Thanks.

Steve

[Roelf]

i think you are overwriting one query with the other.

basic sequence:

build connection
build query1
execute query1
build query2
execute query2
close connection
<%
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,rs,SQL,myMail,name,email,business,country,message

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

[SteveH]

Hello Roelf

Many thanks for your post - that seems to have done the trick!! :)

The IP address and the other data are inserted into their respective tables and no errors!!

Very many thanks again.

Cheers

Steve