Help with command object parameters query?
 

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

Re: Help with command object parameters query?
 

What-a-Tool wrote:
> 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
--
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"

Re: Help with command object parameters query?
 

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)

"What-a-Tool" <Die!FrigginSpammers!DieDie!@IHateSpam.Com> wrote in message
news:H9x0e.72744$SF.19400@lakeread08...
> 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
>
>
>
>

Re: Help with command object parameters query?
 

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 wrote:
> 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)
>
> "What-a-Tool" <Die!FrigginSpammers!DieDie!@IHateSpam.Com> wrote in
> message news:H9x0e.72744$SF.19400@lakeread08...
>> 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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Help with command object parameters query?
 

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]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OmVen2LMFHA.1268@TK2MSFTNGP14.phx.gbl...
> 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 wrote:
>> 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)
>>
>> "What-a-Tool" <Die!FrigginSpammers!DieDie!@IHateSpam.Com> wrote in
>> message news:H9x0e.72744$SF.19400@lakeread08...
>>> 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
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Help with command object parameters query?
 

What-a-Tool wrote:
> 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/...d322b882a604bd

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"

Re: Help with command object parameters query?
 

> 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/...d322b882a604bd
>
> 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

Re: Help with command object parameters query?
 

What-a-Tool wrote:
>> No. Delimiters are not needed when using parameters. Here is a repro
<snip>
> 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
--
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"