Help with command object parameters query?

[What-a-Tool]

Have no problem getting my select queries to work using this method:

strSQL = "SELECT tblUI.IPAdd FROM tblUI WHERE (tblUI.IPAdd =?)"

arSPrm = Array(strRemHst)

Set rst = cmd.Execute(,arSPrm)

Can anyone point me to some examples of using this method for UPDATE and
INSERT queries? Can't seem to get it to work no matter what I can think of
to try. (If it is possible?)

Thanks,
Sean

 

[Bob Barrows [MVP]]

Have no problem getting my select queries to work using this method:

strSQL = "SELECT tblUI.IPAdd FROM tblUI WHERE (tblUI.IPAdd =?)"

arSPrm = Array(strRemHst)

Set rst = cmd.Execute(,arSPrm)

Can anyone point me to some examples of using this method for UPDATE
and INSERT queries? Can't seem to get it to work no matter what I can
think of to try. (If it is possible?)

Thanks,
Sean

You should have shown us what you tried. however:

strSQL = "INSERT INTO tblUI (IPAdd) VALUES (?)"
arSPrm = Array(strRemHst)
cmd.Execute ,arSPrm,129

Of course, this query will fail if you have required fields that aren't
referenced in this query.

Bob Barrows

 

[What-a-Tool]

This I believe was the last thing I tried, without the 129 after the
parameter array in the execute statement

strSQL = "INSERT INTO tblUI (fldA, fldB, fldC, fldD) VALUES (?, ?, ? , ?)"
arSPrm = Array(strA, strB, strC, strD)
cmd.Execute ,arSPrm,129

--
Thank You

/ Sean Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[Bob Barrows [MVP]]

It looks all right. What happens when you run it? Have you verified that the
variables contain what you think they contain?

Suggestion: open your database in Access, create a new query and switch to
sql view. Enter the insert statement with actual hard-coded values in the
VALUES clause and make sure it works. Then, replace the hard-coded values
with parameter markers (?) for execution in ASP.

Bob Barrows

 

[What-a-Tool]

Yes - when I enter the data direcrly thru a query within the Access program,
or thru asp with the standard sql string with values concatenated in,
everything is entered as it should be.

I have several different data types that I am lumping together within this
parameters array - String, Boolean, single, short integer, and date.
The error message I am getting is that my data "is of the wrong or
conflicting types, or outside acceptable bounds". I notice that entering
with the parameter method, there doesn't seem to be any way to enclose the
data type with '...' or #...#. Could this have something to do with it?

--

/ Sean Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[Bob Barrows [MVP]]

Yes - when I enter the data direcrly thru a query within the Access
program, or thru asp with the standard sql string with values
concatenated in, everything is entered as it should be.

I have several different data types that I am lumping together within
this parameters array - String, Boolean, single, short integer, and
date. The error message I am getting is that my data "is of the wrong or
conflicting types, or outside acceptable bounds". I notice that
entering with the parameter method, there doesn't seem to be any way
to enclose the data type with '...' or #...#. Could this have
something to do with it?

No. Delimiters are not needed when using parameters. Here is a repro showing
the passing of all of these parameter types (this is tested code which ran
without error for me):

<%
dim cn, cmd, arParms, sSQL, rs, sHTML
sSQL="CREATE TABLE InsertTest (" & _
"IntCol smallint," & _
"BoolCol bit," & _
"TextCol CHAR(15)," & _
"DateCol datetime," & _
"SngCol real)"

set cn = CreateObject("ADODB.Connection")

'substitute your database in the following string
cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("db7.mdb")

cn.Execute sSQL,,129

sSQL= "INSERT INTO InsertTest(IntCol,BoolCol,TextCol," & _
"DateCol,SngCol) VALUES (?,?,?,?,?)"

arParms=array(25,true,"test",#2005-03-22#,1.25)


set cmd=createobject("adodb.command")
cmd.CommandText=sSQL
set cmd.ActiveConnection=cn
cmd.Execute ,arParms,129
set cmd=nothing

sSQL="select IntCol,BoolCol,TextCol," & _
"DateCol,SngCol from InsertTest"

set rs=cn.Execute(sSQL,,1)
sHTML=rs.getstring(2,,"</td><td>","</td></tr><tr><td>")
rs.close:set rs=nothing
cn.Close:set cn=nothing
Response.Write "<table border=""1""><tr><td>"
Response.Write left(sHTML,len(sHTML)-8)
Response.Write "</table>"

%>

BTW, you may be interested in going to the next level: saved parameter
queries. The code is even easier to write than the Command object code. See:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

Bob Barrows

 

[What-a-Tool]

No. Delimiters are not needed when using parameters. Here is a repro

showing the passing of all of these parameter types (this is tested code
which ran without error for me):

<%
dim cn, cmd, arParms, sSQL, rs, sHTML
sSQL="CREATE TABLE InsertTest (" & _
"IntCol smallint," & _
"BoolCol bit," & _
"TextCol CHAR(15)," & _
"DateCol datetime," & _
"SngCol real)"

set cn = CreateObject("ADODB.Connection")

'substitute your database in the following string
cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("db7.mdb")

cn.Execute sSQL,,129

sSQL= "INSERT INTO InsertTest(IntCol,BoolCol,TextCol," & _
"DateCol,SngCol) VALUES (?,?,?,?,?)"

arParms=array(25,true,"test",#2005-03-22#,1.25)


set cmd=createobject("adodb.command")
cmd.CommandText=sSQL
set cmd.ActiveConnection=cn
cmd.Execute ,arParms,129
set cmd=nothing

sSQL="select IntCol,BoolCol,TextCol," & _
"DateCol,SngCol from InsertTest"

set rs=cn.Execute(sSQL,,1)
sHTML=rs.getstring(2,,"</td><td>","</td></tr><tr><td>")
rs.close:set rs=nothing
cn.Close:set cn=nothing
Response.Write "<table border=""1""><tr><td>"
Response.Write left(sHTML,len(sHTML)-8)
Response.Write "</table>"

%>

BTW, you may be interested in going to the next level: saved parameter
queries. The code is even easier to write than the Command object code.
See:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

AhHa - I see my error - Delimeters are needed, but in the array itself. (and
I thought I tried everything - DOWH!)

Saved parameter query - Very interesting - Thanks for the help and the tips

Sean

 

[Bob Barrows [MVP]]

AhHa - I see my error - Delimeters are needed, but in the array
itself.

Not really. I used delimiters in the array creation statement in my demo
because I was hard-coding the literal values. In a real application, I would
use the vbscript conversion functions to make sure my arguments were of the
proper type:

on error resume next
dim startdate
startdate=cdate(Request.form("StartDate"))
if err<> 0 then
response.write "Invalid Start Date"
response.end
end if
....
arParms = array( ..., startdate, ...)

Bob Barrows