asp_databases thread: ADO RS versus Command
Message #1 by "Drew, Ron" <RDrew@B...> on Fri, 22 Jun 2001 08:22:27 -0400
|
|
Sent out for help the other day and received one or two
responses...thanks...the solution is closer but still not working.
I have an Access DB that I want to insert a record with some 7-8
request.form fields. Found out that ADO does have allow Recordsets for any
modifications. It works great for selects. So I have set up the insert to
use what the ASP Bible indicates to use the COMMAND with Parameters. Still
can not get it to work. Please take a quick look at the code and see if you
can find the problem...thanks a bunch!!
Dim objConn, strSQL, dtInput, objCommand, objParam
set objConn = Server.CreateObject("ADODB.Connection")
set objCommand = Server.CreateObject("ADODB.Command")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\InetPub\wwwroot\vette\guestbook2\guestbook.mdb;" & _
"User Id=admin;" & _
"Password=;"
dtInput = date
'set command text with placeholders......
strSQL = "INSERT INTO Guestbook (name, email, city, country, URL,
howfind, message, dateinput)" &_
" VALUES ('?','?','?','?','?','?','?',?)"
objCommand.CommandText = strSQL
'now setup the parameter collections........
objCommand.Parameters.Append objCommand.CreateParameter("name",200, ,255)
objCommand("name") = "'" & sName & "'"
objCommand.Parameters.Append objCommand.CreateParameter("email",200, ,60)
objCommand("email") = "'" & sEmail & "'"
objCommand.Parameters.Append objCommand.CreateParameter("city",200, ,40)
objCommand("city") = "'" & sCity & "'"
objCommand.Parameters.Append objCommand.CreateParameter("country",200,
,30)
objCommand("country") = "'" & sUSA & "'"
objCommand.Parameters.Append objCommand.CreateParameter("URL",200, ,50)
objCommand("URL") = "'" & sURL & "'"
objCommand.Parameters.Append objCommand.CreateParameter("howfind",200,
,40)
objCommand("howfind") = "'" & sHow& "'"
objCommand.Parameters.Append objCommand.CreateParameter("message",200,
,255)
objCommand("message") = "'" & sMessage & "'"
objCommand.Parameters.Append objCommand.CreateParameter("dateinput",134,
,40)
objCommand("dateinput") = dtInput
'show me the parameters for debugging only......
Response.Write "<br><br>" & objCommand.Parameters("name").Value
Response.Write "<br>" & objCommand.Parameters("email").Value
Response.Write "<br>" & objCommand.Parameters("city").Value
Response.Write "<br>" & objCommand.Parameters("country").Value
Response.Write "<br>" & objCommand.Parameters("URL").Value
Response.Write "<br>" & objCommand.Parameters("howfind").Value
Response.Write "<br>" & objCommand.Parameters("message").Value
Response.Write "<br>" & objCommand.Parameters("dateinput").Value
objCommand.ActiveConnection = objConn
objCommand.Execute
objConn.Close
set objCommand = Nothing
set objConn = Nothing
Message #2 by "Tomm Matthis" <matthis@b...> on Fri, 22 Jun 2001 09:07:36 -0400
|
|
You've almost got it dude.... try this:
cmd.ActiveConnection =3D objConn
objCommand.CommandType =3D adCmdText
'set command text with placeholders......
strSQL =3D "INSERT INTO Guestbook (name, email, city, country, URL,
howfind, message, dateinput)" &_
" VALUES ('?','?','?','?','?','?','?',?)"
objCommand.CommandText =3D strSQL
You have to create the paramter before appending it... and you can do
both in one step. Also, go ahead and assign the value here:
objCommand.Parameters.Append
objCommand.CreateParameter("name",200,sName ,255)
^^^^
.. the rest of them go here.
objCommand.Execute
Message #3 by "Drew, Ron" <RDrew@B...> on Fri, 22 Jun 2001 10:57:29 -0400
|
|
Thanks Tomm...tried it and got...
Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/Vette/guestbook2/mergeguest.asp, line 110
.......................new code follows..............
Dim objConn, objRS, strSQL, dtInput, objCommand, objParam
set objConn =3D Server.CreateObject("ADODB.Connection")
set objCommand =3D Server.CreateObject("ADODB.Command")
objConn.Open "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & _
"Data
Source=3Dc:\InetPub\wwwroot\vette\guestbook2\guestbook.mdb;" & _
"User Id=3Dadmin;" & _
"Password=3D;"
dtInput =3D date
objCommand.ActiveConnection =3D objConn
objCommand.CommandType =3D adCmdText
strSQL =3D "INSERT INTO Guestbook (name, email, city, country, URL,
howfind, message, dateinput)" &_
" VALUES ('?','?','?','?','?','?','?',?)"
objCommand.CommandText =3D strSQL
objCommand.Parameters.Append objCommand.CreateParameter("name",200,
,255)
objCommand("name") =3D "'" & sName & "'"
objCommand.Parameters.Append objCommand.CreateParameter("email",200,
,60)
objCommand("email") =3D "'" & sEmail & "'"
objCommand.Parameters.Append objCommand.CreateParameter("city",200,
,40)
objCommand("city") =3D "'" & sCity & "'"
objCommand.Parameters.Append
objCommand.CreateParameter("country",200,
,30)
objCommand("country") =3D "'" & sUSA & "'"
objCommand.Parameters.Append objCommand.CreateParameter("URL",200,
,50)
objCommand("URL") =3D "'" & sURL & "'"
objCommand.Parameters.Append
objCommand.CreateParameter("howfind",200,
,40)
objCommand("howfind") =3D "'" & sHow& "'"
objCommand.Parameters.Append
objCommand.CreateParameter("message",200,
,255)
objCommand("message") =3D "'" & sMessage & "'"
objCommand.Parameters.Append
objCommand.CreateParameter("dateinput",134,
,40)
objCommand("dateinput") =3D dtInput
'Line 110 follows.....
objCommand.Execute
objConn.Close
set objCommand =3D Nothing
set objConn =3D Nothing
-----Original Message-----
From: Tomm Matthis [mailto:matthis@b...]
Sent: Friday, June 22, 2001 9:08 AM
To: ASP Databases
Subject: [asp_databases] RE: ADO RS versus Command
You've almost got it dude.... try this:
cmd.ActiveConnection =3D objConn
objCommand.CommandType =3D adCmdText
'set command text with placeholders......
strSQL =3D "INSERT INTO Guestbook (name, email, city, country, URL,
howfind, message, dateinput)" &_
" VALUES ('?','?','?','?','?','?','?',?)"
objCommand.CommandText =3D strSQL
You have to create the paramter before appending it... and you can do
both
in one step. Also, go ahead and assign the value here:
objCommand.Parameters.Append
objCommand.CreateParameter("name",200,sName
,255)
=09
^^^^
.. the rest of them go here.
objCommand.Execute
Message #4 by "Tomm Matthis" <matthis@b...> on Fri, 22 Jun 2001 13:51:30 -0400
|
|
Well, to be honest with you, since you're sending a SQL string, the
command object is overkill.
Why not create a query with named parameters? that way Access can
optimize the query a bit, and the use of the command object will make
more sense...??
-- Tomm
|
View Cart
