| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-23-08, 08:32
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 51
|
|
|
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
|
|
01-23-08, 08:57
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
You have to perform the inserts separately...
You can use the saame connection and variables; but you have to run 2 separate execute commands.
|
|
01-23-08, 09:16
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 51
|
|
|
|
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
|
|
01-23-08, 09:20
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
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 & "')"
|
|
01-23-08, 09:34
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 51
|
|
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
|
|
01-23-08, 09:47
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Post the full code 
|
|
01-23-08, 09:54
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 51
|
|
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
%>
|
|
01-23-08, 09:57
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
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!
|
|
01-23-08, 10:33
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 51
|
|
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
|
|
01-23-08, 10:53
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
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
|
|
01-23-08, 11:12
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 51
|
|
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
|
|
01-24-08, 08:26
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 51
|
|
I think what I might do, George, use two different asp files. One for each INSERT.
Steve
|
|
01-31-08, 03:45
|
|
Registered User
|
|
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
%>
|
|
01-31-08, 08:14
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 51
|
|
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
|
|
01-31-08, 20:26
|
|
Registered User
|
|
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...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Two SQL statements?
