| |
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
| |
Share:
| |
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" | | | |
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" <Di*************************@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
| | | |
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" <Di*************************@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. | | | |
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:Om**************@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" <Di*************************@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.
| | | |
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" | | | |
> 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 | | | |
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" | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
5 posts
views
Thread by Bruno Alexandre |
last post: by
|
1 post
views
Thread by Job Lot |
last post: by
|
3 posts
views
Thread by Shapper |
last post: by
|
6 posts
views
Thread by James Radke |
last post: by
|
reply
views
Thread by Elliot M. Rodriguez |
last post: by
|
7 posts
views
Thread by Siv |
last post: by
|
reply
views
Thread by gunimpi |
last post: by
|
3 posts
views
Thread by Darth Ferret |
last post: by
| | | | | | | | | | | | | | | | | | | | | | | |
