|
 |
asp_databases thread: Updating 2 tables from 1 ASP form
Message #1 by "Jonathan Mooney" <mooneyuuj@h...> on Tue, 04 Mar 2003 09:58:11 +0000
|
|
Can anyone help, please?
I have an Access 2000 DB two of the tables are listed below and there fields
are indented.
InternetUser
UserID (Auto number)
Fname
Lname
Address
City
TelephoneNo
email
Reports
UserID (to match above)
RegNo
Date
Time
Location
City
Status (must contain NULL)
The code I have used so far is to write on set in the normal way to the DB
table concerned and to try to insert the other by way of an insert SQL
statement (see below):
'Sets the DSN-less connection string to a local variable
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
Server.MapPath("MyDB.mdb")
'Opens a connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'update the Internet User table
set RS = Server.Createobject("adodb.recordset")
RS.open "InternetUser", conn, 3, 3
RS.addnew
RS("Fname") = request.form("FirstName")
RS("Lname") = request.form("LastName")
RS("Address") = request.form("FAddress")
RS("City") = request.form("FCity")
RS("TelephoneNo") = request.form("FTelephoneNo")
RS("email") = request.form("Femail")
RS.update
RS.movelast
'Get the ID that is created when the record was created
strID = RS("UserID")
RS.Close
set RS = nothing
'This part works and writes to DB
'Record Vehicle Location Info
strSQL = "INSERT INTO Reports(UserID, RegNo, Date, Time, Location, City,
Status)"
strSQL = strSQL & " SELECT "
strSQL = strSQL & "'" & strID & " as text1,"
strSQL = strSQL & request("RegNo") as text2,"
strSQL = strSQL & request("Date") & "' as text3,"
strSQL = strSQL & request("Time") & " as text4,"
strSQL = strSQL & request("Location") & " as text5,"
strSQL = strSQL & request("City") & " as text6,"
strSQL = strSQL & request("Status") & " as text7"
conn.execute(strsql)
' This part doesn't work it seems to stop the line after the select line.
All of the fields in the database are of text type.
Please help! Many Thanks.
Jon
conn.close
set conn = nothing
_________________________________________________________________
Chat online in real time with MSN Messenger http://messenger.msn.co.uk
Message #2 by "Paulo Fernandes" <paulofernandes@c...> on Tue, 4 Mar 2003 10:36:14 -0000
|
|
Usually, I response.write the sql statement to check for errors...
When you say " This part doesn't work it seems to stop the line after
the select line", what makes you say that? Do you get any errors? If so,
post them so we can help you...
PauloF
-----Original Message-----
From: Jonathan Mooney [mailto:mooneyuuj@h...]
Sent: ter=E7a-feira, 4 de Mar=E7o de 2003 9:58
To: ASP Databases
Subject: [asp_databases] Updating 2 tables from 1 ASP form
Can anyone help, please?
I have an Access 2000 DB two of the tables are listed below and there
fields
are indented.
InternetUser
UserID (Auto number)
Fname
Lname
Address
City
TelephoneNo
email
Reports
UserID (to match above)
RegNo
Date
Time
Location
City
Status (must contain NULL)
The code I have used so far is to write on set in the normal way to the
DB
table concerned and to try to insert the other by way of an insert SQL
statement (see below):
'Sets the DSN-less connection string to a local variable
strconn =3D "DRIVER=3DMicrosoft Access Driver (*.mdb);DBQ=3D" &
Server.MapPath("MyDB.mdb")
'Opens a connection object
set conn =3D server.createobject("adodb.connection")
conn.open strconn
'update the Internet User table
set RS =3D Server.Createobject("adodb.recordset")
RS.open "InternetUser", conn, 3, 3
RS.addnew
RS("Fname") =3D request.form("FirstName")
RS("Lname") =3D request.form("LastName")
RS("Address") =3D request.form("FAddress")
RS("City") =3D request.form("FCity")
RS("TelephoneNo") =3D request.form("FTelephoneNo")
RS("email") =3D request.form("Femail")
RS.update
RS.movelast
'Get the ID that is created when the record was created
strID =3D RS("UserID")
RS.Close
set RS =3D nothing
'This part works and writes to DB
'Record Vehicle Location Info
strSQL =3D "INSERT INTO Reports(UserID, RegNo, Date, Time, Location,
City,
Status)"
strSQL =3D strSQL & " SELECT "
strSQL =3D strSQL & "'" & strID & " as text1,"
strSQL =3D strSQL & request("RegNo") as text2,"
strSQL =3D strSQL & request("Date") & "' as text3,"
strSQL =3D strSQL & request("Time") & " as text4,"
strSQL =3D strSQL & request("Location") & " as text5,"
strSQL =3D strSQL & request("City") & " as text6,"
strSQL =3D strSQL & request("Status") & " as text7"
conn.execute(strsql)
' This part doesn't work it seems to stop the line after the select
line.
All of the fields in the database are of text type.
Please help! Many Thanks.
Jon
conn.close
set conn =3D nothing
_________________________________________________________________
Chat online in real time with MSN Messenger http://messenger.msn.co.uk
Message #3 by "Jonathan Mooney" <mooneyuuj@h...> on Tue, 04 Mar 2003 19:34:52 +0000
|
|
Hi !
The message I am getting in the browser when I run the code below is:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/labuser/AddReport.asp, line 40, column 35
strSQL = strSQL & request("RegNo") as text2,"
(labuser is just the name of my folder)
I be grateful for any Help!
Many Thanks
Jon
>From: "Paulo Fernandes" <paulofernandes@c...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] RE: Updating 2 tables from 1 ASP form
>Date: Tue, 4 Mar 2003 10:36:14 -0000
>
>Usually, I response.write the sql statement to check for errors...
>
>When you say " This part doesn't work it seems to stop the line after
>the select line", what makes you say that? Do you get any errors? If so,
>post them so we can help you...
>
>PauloF
>
>-----Original Message-----
>From: Jonathan Mooney [mailto:mooneyuuj@h...]
>Sent: terça-feira, 4 de Março de 2003 9:58
>To: ASP Databases
>Subject: [asp_databases] Updating 2 tables from 1 ASP form
>
>
>Can anyone help, please?
>
>I have an Access 2000 DB two of the tables are listed below and there
>fields
>are indented.
>
>InternetUser
> UserID (Auto number)
> Fname
> Lname
> Address
> City
> TelephoneNo
> email
>Reports
> UserID (to match above)
> RegNo
> Date
> Time
> Location
> City
> Status (must contain NULL)
>The code I have used so far is to write on set in the normal way to the
>DB
>table concerned and to try to insert the other by way of an insert SQL
>statement (see below):
>
>'Sets the DSN-less connection string to a local variable
>strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
>Server.MapPath("MyDB.mdb")
>
>'Opens a connection object
>set conn = server.createobject("adodb.connection")
>conn.open strconn
>
>'update the Internet User table
>set RS = Server.Createobject("adodb.recordset")
>RS.open "InternetUser", conn, 3, 3
>RS.addnew
>RS("Fname") = request.form("FirstName")
>RS("Lname") = request.form("LastName")
>RS("Address") = request.form("FAddress")
>RS("City") = request.form("FCity")
>RS("TelephoneNo") = request.form("FTelephoneNo")
>RS("email") = request.form("Femail")
>RS.update
>RS.movelast
>
>'Get the ID that is created when the record was created
>strID = RS("UserID")
>RS.Close
>set RS = nothing
>
>'This part works and writes to DB
>
>
>'Record Vehicle Location Info
>strSQL = "INSERT INTO Reports(UserID, RegNo, Date, Time, Location, City,
>
>Status)"
>strSQL = strSQL & " SELECT "
>strSQL = strSQL & "'" & strID & " as text1,"
>strSQL = strSQL & request("RegNo") as text2,"
>strSQL = strSQL & request("Date") & "' as text3,"
>strSQL = strSQL & request("Time") & " as text4,"
>strSQL = strSQL & request("Location") & " as text5,"
>strSQL = strSQL & request("City") & " as text6,"
>strSQL = strSQL & request("Status") & " as text7"
>conn.execute(strsql)
>' This part doesn't work it seems to stop the line after the select
>line.
>
>All of the fields in the database are of text type.
>
>Please help! Many Thanks.
>
>Jon
>
>conn.close
>set conn = nothing
>
>
>
>
>_________________________________________________________________
>Chat online in real time with MSN Messenger http://messenger.msn.co.uk
>
>
>
>
>
_________________________________________________________________
Express yourself with cool emoticons http://messenger.msn.co.uk
Message #4 by "Jonathan Mooney" <mooneyuuj@h...> on Tue, 04 Mar 2003 19:35:15 +0000
|
|
Thanks Paul!
The message I am getting in the browser when I run the code below is:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/labuser/AddReport.asp, line 40, column 35
strSQL = strSQL & request("RegNo") as text2,"
(labuser is just the name of my folder)
I be grateful for any Help!
Many Thanks
Jon
>From: "Paulo Fernandes" <paulofernandes@c...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] RE: Updating 2 tables from 1 ASP form
>Date: Tue, 4 Mar 2003 10:36:14 -0000
>
>Usually, I response.write the sql statement to check for errors...
>
>When you say " This part doesn't work it seems to stop the line after
>the select line", what makes you say that? Do you get any errors? If so,
>post them so we can help you...
>
>PauloF
>
>-----Original Message-----
>From: Jonathan Mooney [mailto:mooneyuuj@h...]
>Sent: terça-feira, 4 de Março de 2003 9:58
>To: ASP Databases
>Subject: [asp_databases] Updating 2 tables from 1 ASP form
>
>
>Can anyone help, please?
>
>I have an Access 2000 DB two of the tables are listed below and there
>fields
>are indented.
>
>InternetUser
> UserID (Auto number)
> Fname
> Lname
> Address
> City
> TelephoneNo
> email
>Reports
> UserID (to match above)
> RegNo
> Date
> Time
> Location
> City
> Status (must contain NULL)
>The code I have used so far is to write on set in the normal way to the
>DB
>table concerned and to try to insert the other by way of an insert SQL
>statement (see below):
>
>'Sets the DSN-less connection string to a local variable
>strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
>Server.MapPath("MyDB.mdb")
>
>'Opens a connection object
>set conn = server.createobject("adodb.connection")
>conn.open strconn
>
>'update the Internet User table
>set RS = Server.Createobject("adodb.recordset")
>RS.open "InternetUser", conn, 3, 3
>RS.addnew
>RS("Fname") = request.form("FirstName")
>RS("Lname") = request.form("LastName")
>RS("Address") = request.form("FAddress")
>RS("City") = request.form("FCity")
>RS("TelephoneNo") = request.form("FTelephoneNo")
>RS("email") = request.form("Femail")
>RS.update
>RS.movelast
>
>'Get the ID that is created when the record was created
>strID = RS("UserID")
>RS.Close
>set RS = nothing
>
>'This part works and writes to DB
>
>
>'Record Vehicle Location Info
>strSQL = "INSERT INTO Reports(UserID, RegNo, Date, Time, Location, City,
>
>Status)"
>strSQL = strSQL & " SELECT "
>strSQL = strSQL & "'" & strID & " as text1,"
>strSQL = strSQL & request("RegNo") as text2,"
>strSQL = strSQL & request("Date") & "' as text3,"
>strSQL = strSQL & request("Time") & " as text4,"
>strSQL = strSQL & request("Location") & " as text5,"
>strSQL = strSQL & request("City") & " as text6,"
>strSQL = strSQL & request("Status") & " as text7"
>conn.execute(strsql)
>' This part doesn't work it seems to stop the line after the select
>line.
>
>All of the fields in the database are of text type.
>
>Please help! Many Thanks.
>
>Jon
>
>conn.close
>set conn = nothing
>
>
>
>
>_________________________________________________________________
>Chat online in real time with MSN Messenger http://messenger.msn.co.uk
>
>
>
>
>
_________________________________________________________________
Worried what your kids see online? Protect them better with MSN 8
http://join.msn.com/?page=features/parental&pgmarket=en-gb&XAPID=186&DI=1059
Message #5 by "Greg Robillard" <grob1@o...> on Tue, 4 Mar 2003 13:55:51 -0600
|
|
strSQL = strSQL & "'" & strID & " as text1,"
This is supposed to be a database select string. strID is not a database
object. I see that you identify it previously, but this is not standard SQL
code. What exactly are you trying to retrieve fromt the table???
----------------------------------------------------------------------------
----
Greg L. Robillard
Department Chair - Computer Science
SiTanka University
333 9th St SW xxx-xxx-xxxx
Huron, SD 57350 support@o...
----------------------------------------------------------------------------
----
----- Original Message -----
From: "Jonathan Mooney" <mooneyuuj@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, March 04, 2003 1:35 PM
Subject: [asp_databases] RE: Updating 2 tables from 1 ASP form
> Thanks Paul!
>
> The message I am getting in the browser when I run the code below is:
>
> Error Type:
> Microsoft VBScript compilation (0x800A0401)
> Expected end of statement
> /labuser/AddReport.asp, line 40, column 35
> strSQL = strSQL & request("RegNo") as text2,"
>
> (labuser is just the name of my folder)
>
> I be grateful for any Help!
> Many Thanks
> Jon
>
>
>
>
>
>
> >From: "Paulo Fernandes" <paulofernandes@c...>
> >Reply-To: "ASP Databases" <asp_databases@p...>
> >To: "ASP Databases" <asp_databases@p...>
> >Subject: [asp_databases] RE: Updating 2 tables from 1 ASP form
> >Date: Tue, 4 Mar 2003 10:36:14 -0000
> >
> >Usually, I response.write the sql statement to check for errors...
> >
> >When you say " This part doesn't work it seems to stop the line after
> >the select line", what makes you say that? Do you get any errors? If so,
> >post them so we can help you...
> >
> >PauloF
> >
> >-----Original Message-----
> >From: Jonathan Mooney [mailto:mooneyuuj@h...]
> >Sent: terça-feira, 4 de Março de 2003 9:58
> >To: ASP Databases
> >Subject: [asp_databases] Updating 2 tables from 1 ASP form
> >
> >
> >Can anyone help, please?
> >
> >I have an Access 2000 DB two of the tables are listed below and there
> >fields
> >are indented.
> >
> >InternetUser
> > UserID (Auto number)
> > Fname
> > Lname
> > Address
> > City
> > TelephoneNo
> > email
> >Reports
> > UserID (to match above)
> > RegNo
> > Date
> > Time
> > Location
> > City
> > Status (must contain NULL)
> >The code I have used so far is to write on set in the normal way to the
> >DB
> >table concerned and to try to insert the other by way of an insert SQL
> >statement (see below):
> >
> >'Sets the DSN-less connection string to a local variable
> >strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
> >Server.MapPath("MyDB.mdb")
> >
> >'Opens a connection object
> >set conn = server.createobject("adodb.connection")
> >conn.open strconn
> >
> >'update the Internet User table
> >set RS = Server.Createobject("adodb.recordset")
> >RS.open "InternetUser", conn, 3, 3
> >RS.addnew
> >RS("Fname") = request.form("FirstName")
> >RS("Lname") = request.form("LastName")
> >RS("Address") = request.form("FAddress")
> >RS("City") = request.form("FCity")
> >RS("TelephoneNo") = request.form("FTelephoneNo")
> >RS("email") = request.form("Femail")
> >RS.update
> >RS.movelast
> >
> >'Get the ID that is created when the record was created
> >strID = RS("UserID")
> >RS.Close
> >set RS = nothing
> >
> >'This part works and writes to DB
> >
> >
> >'Record Vehicle Location Info
> >strSQL = "INSERT INTO Reports(UserID, RegNo, Date, Time, Location, City,
> >
> >Status)"
> >strSQL = strSQL & " SELECT "
> >strSQL = strSQL & "'" & strID & " as text1,"
> >strSQL = strSQL & request("RegNo") as text2,"
> >strSQL = strSQL & request("Date") & "' as text3,"
> >strSQL = strSQL & request("Time") & " as text4,"
> >strSQL = strSQL & request("Location") & " as text5,"
> >strSQL = strSQL & request("City") & " as text6,"
> >strSQL = strSQL & request("Status") & " as text7"
> >conn.execute(strsql)
> >' This part doesn't work it seems to stop the line after the select
> >line.
> >
> >All of the fields in the database are of text type.
> >
> >Please help! Many Thanks.
> >
> >Jon
> >
> >conn.close
> >set conn = nothing
> >
> >
> >
> >
> >_________________________________________________________________
> >Chat online in real time with MSN Messenger http://messenger.msn.co.uk
> >
> >
> >
> >
> >
>
>
> _________________________________________________________________
> Worried what your kids see online? Protect them better with MSN 8
>
http://join.msn.com/?page=features/parental&pgmarket=en-gb&XAPID=186&DI=1059
>
>
>
Message #6 by "Quita Ryder" <qryder1@m...> on Tue, 4 Mar 2003 21:23:24
|
|
I am not an expert in Access, however, it seems to me that you need the
server to interpret your " request("RegNo") " etc. items into their
appropriate text or numbers before the sql is executed. And in order for
the server to recognize that it needs to interpret these, I think those
items need to be enclosed within '" & request("RegNo") & "' in your select
statement.
Message #7 by "Quita Ryder" <qryder1@m...> on Tue, 4 Mar 2003 21:28:39
|
|
>
> I am not an expert in Access, however, it seems to me that you need the
s> erver to interpret your " request("RegNo") " etc. items into their
a> ppropriate text or numbers before the sql is executed. And in order
for
t> he server to recognize that it needs to interpret these, I think those
i> tems need to be enclosed within '" & request("RegNo") & "' in your
select
s> tatement.
Oops, didn't mean your select statement, but really meant your insert
statement. Quita
Message #8 by skip@f... on Wed, 5 Mar 2003 01:03:19
|
|
> Take a close look at the SQL statement. You're missing a quote on the
line, which is why you're getting the error. However, even after you
correct it, you will get others, so check your syntax very carefully.
Skip
Can anyone help, please?
I have an Access 2000 DB two of the tables are listed below and there
fields
are indented.
InternetUser
UserID (Auto number)
Fname
Lname
Address
City
TelephoneNo
email
Reports
UserID (to match above)
RegNo
Date
Time
Location
City
Status (must contain NULL)
The code I have used so far is to write on set in the normal way to the DB
table concerned and to try to insert the other by way of an insert SQL
statement (see below):
'Sets the DSN-less connection string to a local variable
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
Server.MapPath("MyDB.mdb")
'Opens a connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'update the Internet User table
set RS = Server.Createobject("adodb.recordset")
RS.open "InternetUser", conn, 3, 3
RS.addnew
RS("Fname") = request.form("FirstName")
RS("Lname") = request.form("LastName")
RS("Address") = request.form("FAddress")
RS("City") = request.form("FCity")
RS("TelephoneNo") = request.form("FTelephoneNo")
RS("email") = request.form("Femail")
RS.update
RS.movelast
'Get the ID that is created when the record was created
strID = RS("UserID")
RS.Close
set RS = nothing
'This part works and writes to DB
'Record Vehicle Location Info
strSQL = "INSERT INTO Reports(UserID, RegNo, Date, Time, Location, City,
Status)"
strSQL = strSQL & " SELECT "
strSQL = strSQL & "'" & strID & " as text1,"
strSQL = strSQL & request("RegNo") as text2,"
strSQL = strSQL & request("Date") & "' as text3,"
strSQL = strSQL & request("Time") & " as text4,"
strSQL = strSQL & request("Location") & " as text5,"
strSQL = strSQL & request("City") & " as text6,"
strSQL = strSQL & request("Status") & " as text7"
conn.execute(strsql)
' This part doesn't work it seems to stop the line after the select line.
All of the fields in the database are of text type.
Please help! Many Thanks.
Jon
conn.close
set conn = nothing
_________________________________________________________________
Chat online in real time with MSN Messenger http://messenger.msn.co.uk
Message #9 by "Peter Foti (PeterF)" <PeterF@S...> on Wed, 5 Mar 2003 10:42:33 -0500
|
|
You would be better off using an INSERT instead of creating a recordset for
the first part (INSERT is faster). Also, you should replace any single
quotes in the values with 2 single quotes, so that they are not mistaken as
the end of the value:
' This function will replace single quotes in the input with 2 single
' quotes, and then wrap the entire string with single quotes
Function sql_quote(str)
str = Replace(str,"'","''")
sql_quote = "'"& str & "'"
End Function
Dim SQLStr
SQLStr = " INSERT INTO InternetUser " & _
" (Fname, Lname, Address, City, TelephoneNo, email) " & _
" VALUES (" & _
sql_quote(Request.Form("FirstName")) & "," & _
sql_quote(Request.Form("LastName")) & "," & _
sql_quote(Request.Form("FAddress")) & "," & _
sql_quote(Request.Form("FCity")) & "," & _
sql_quote(Request.Form("FTelephoneNo")) & "," & _
sql_quote(Request.Form("Femail")) & _
")"
Also, you should use the @@Identity to get the ID of the inserted record.
You can read about how to do that here:
http://www.kamath.com/tutorials/tut007_identity.asp
Hope this helps.
Regards,
Peter Foti
> -----Original Message-----
> From: skip@f... [mailto:skip@f...]
> Sent: Wednesday, March 05, 2003 1:03 AM
> To: ASP Databases
> Subject: [asp_databases] Re: Updating 2 tables from 1 ASP form
>
>
> > Take a close look at the SQL statement. You're missing a
> quote on the
> line, which is why you're getting the error. However, even after you
> correct it, you will get others, so check your syntax very carefully.
>
> Skip
>
>
> Can anyone help, please?
>
> I have an Access 2000 DB two of the tables are listed below and there
> fields
> are indented.
>
> InternetUser
> UserID (Auto number)
> Fname
> Lname
> Address
> City
> TelephoneNo
> email
> Reports
> UserID (to match above)
> RegNo
> Date
> Time
> Location
> City
> Status (must contain NULL)
> The code I have used so far is to write on set in the normal
> way to the DB
> table concerned and to try to insert the other by way of an
> insert SQL
> statement (see below):
>
> 'Sets the DSN-less connection string to a local variable
> strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
> Server.MapPath("MyDB.mdb")
>
> 'Opens a connection object
> set conn = server.createobject("adodb.connection")
> conn.open strconn
>
> 'update the Internet User table
> set RS = Server.Createobject("adodb.recordset")
> RS.open "InternetUser", conn, 3, 3
> RS.addnew
> RS("Fname") = request.form("FirstName")
> RS("Lname") = request.form("LastName")
> RS("Address") = request.form("FAddress")
> RS("City") = request.form("FCity")
> RS("TelephoneNo") = request.form("FTelephoneNo")
> RS("email") = request.form("Femail")
> RS.update
> RS.movelast
>
> 'Get the ID that is created when the record was created
> strID = RS("UserID")
> RS.Close
> set RS = nothing
>
> 'This part works and writes to DB
>
>
> 'Record Vehicle Location Info
> strSQL = "INSERT INTO Reports(UserID, RegNo, Date, Time,
> Location, City,
> Status)"
> strSQL = strSQL & " SELECT "
> strSQL = strSQL & "'" & strID & " as text1,"
> strSQL = strSQL & request("RegNo") as text2,"
> strSQL = strSQL & request("Date") & "' as text3,"
> strSQL = strSQL & request("Time") & " as text4,"
> strSQL = strSQL & request("Location") & " as text5,"
> strSQL = strSQL & request("City") & " as text6,"
> strSQL = strSQL & request("Status") & " as text7"
> conn.execute(strsql)
> ' This part doesn't work it seems to stop the line after the
> select line.
>
> All of the fields in the database are of text type.
>
> Please help! Many Thanks.
>
> Jon
>
> conn.close
> set conn = nothing
>
>
>
>
> _________________________________________________________________
> Chat online in real time with MSN Messenger http://messenger.msn.co.uk
>
>
Message #10 by "Jonathan Mooney" <mooneyuuj@h...> on Wed, 12 Mar 2003 10:49:57 +0000
|
|
Hi Peter,
THANKS for all your help, will try the code now!
Thanks to everyone else who helped.
Jon
>From: "Peter Foti (PeterF)" <PeterF@S...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Re: Updating 2 tables from 1 ASP form
>Date: Wed, 5 Mar 2003 10:42:33 -0500
>
>You would be better off using an INSERT instead of creating a recordset for
>the first part (INSERT is faster). Also, you should replace any single
>quotes in the values with 2 single quotes, so that they are not mistaken as
>the end of the value:
>
>
>' This function will replace single quotes in the input with 2 single
>' quotes, and then wrap the entire string with single quotes
>Function sql_quote(str)
> str = Replace(str,"'","''")
> sql_quote = "'"& str & "'"
>End Function
>
>
>Dim SQLStr
>SQLStr = " INSERT INTO InternetUser " & _
> " (Fname, Lname, Address, City, TelephoneNo, email) " & _
> " VALUES (" & _
> sql_quote(Request.Form("FirstName")) & "," & _
> sql_quote(Request.Form("LastName")) & "," & _
> sql_quote(Request.Form("FAddress")) & "," & _
> sql_quote(Request.Form("FCity")) & "," & _
> sql_quote(Request.Form("FTelephoneNo")) & "," & _
> sql_quote(Request.Form("Femail")) & _
> ")"
>
>Also, you should use the @@Identity to get the ID of the inserted record.
>You can read about how to do that here:
>http://www.kamath.com/tutorials/tut007_identity.asp
>
>Hope this helps.
>
>Regards,
>Peter Foti
>
>
>
> > -----Original Message-----
> > From: skip@f... [mailto:skip@f...]
> > Sent: Wednesday, March 05, 2003 1:03 AM
> > To: ASP Databases
> > Subject: [asp_databases] Re: Updating 2 tables from 1 ASP form
> >
> >
> > > Take a close look at the SQL statement. You're missing a
> > quote on the
> > line, which is why you're getting the error. However, even after you
> > correct it, you will get others, so check your syntax very carefully.
> >
> > Skip
> >
> >
> > Can anyone help, please?
> >
> > I have an Access 2000 DB two of the tables are listed below and there
> > fields
> > are indented.
> >
> > InternetUser
> > UserID (Auto number)
> > Fname
> > Lname
> > Address
> > City
> > TelephoneNo
> > email
> > Reports
> > UserID (to match above)
> > RegNo
> > Date
> > Time
> > Location
> > City
> > Status (must contain NULL)
> > The code I have used so far is to write on set in the normal
> > way to the DB
> > table concerned and to try to insert the other by way of an
> > insert SQL
> > statement (see below):
> >
> > 'Sets the DSN-less connection string to a local variable
> > strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
> > Server.MapPath("MyDB.mdb")
> >
> > 'Opens a connection object
> > set conn = server.createobject("adodb.connection")
> > conn.open strconn
> >
> > 'update the Internet User table
> > set RS = Server.Createobject("adodb.recordset")
> > RS.open "InternetUser", conn, 3, 3
> > RS.addnew
> > RS("Fname") = request.form("FirstName")
> > RS("Lname") = request.form("LastName")
> > RS("Address") = request.form("FAddress")
> > RS("City") = request.form("FCity")
> > RS("TelephoneNo") = request.form("FTelephoneNo")
> > RS("email") = request.form("Femail")
> > RS.update
> > RS.movelast
> >
> > 'Get the ID that is created when the record was created
> > strID = RS("UserID")
> > RS.Close
> > set RS = nothing
> >
> > 'This part works and writes to DB
> >
> >
> > 'Record Vehicle Location Info
> > strSQL = "INSERT INTO Reports(UserID, RegNo, Date, Time,
> > Location, City,
> > Status)"
> > strSQL = strSQL & " SELECT "
> > strSQL = strSQL & "'" & strID & " as text1,"
> > strSQL = strSQL & request("RegNo") as text2,"
> > strSQL = strSQL & request("Date") & "' as text3,"
> > strSQL = strSQL & request("Time") & " as text4,"
> > strSQL = strSQL & request("Location") & " as text5,"
> > strSQL = strSQL & request("City") & " as text6,"
> > strSQL = strSQL & request("Status") & " as text7"
> > conn.execute(strsql)
> > ' This part doesn't work it seems to stop the line after the
> > select line.
> >
> > All of the fields in the database are of text type.
> >
> > Please help! Many Thanks.
> >
> > Jon
> >
> > conn.close
> > set conn = nothing
> >
> >
> >
> >
> > _________________________________________________________________
> > Chat online in real time with MSN Messenger http://messenger.msn.co.uk
> >
> >
>
_________________________________________________________________
Chat online in real time with MSN Messenger http://messenger.msn.co.uk
|
|
|
View Cart
