Help with command object parameters query?

Discussion in 'ASP General' started by What-a-Tool, Mar 24, 2005.

  1. What-a-Tool

    What-a-Tool Guest

    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
     
    What-a-Tool, Mar 24, 2005
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Mar 24, 2005
    #2
    1. Advertising

  3. What-a-Tool

    What-a-Tool Guest

    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
    >
    >
    >
    >
     
    What-a-Tool, Mar 24, 2005
    #3
  4. 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.
     
    Bob Barrows [MVP], Mar 24, 2005
    #4
  5. What-a-Tool

    What-a-Tool Guest

    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]" <> wrote in message
    news:...
    > 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.
    >
    >
     
    What-a-Tool, Mar 25, 2005
    #5
  6. 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/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"
     
    Bob Barrows [MVP], Mar 25, 2005
    #6
  7. What-a-Tool

    What-a-Tool Guest

    > 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
     
    What-a-Tool, Mar 27, 2005
    #7
  8. 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"
     
    Bob Barrows [MVP], Mar 27, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jonck van der Kogel
    Replies:
    2
    Views:
    1,008
    Jonck van der Kogel
    May 27, 2004
  2. magda muskala
    Replies:
    0
    Views:
    620
    magda muskala
    Feb 15, 2005
  3. Bruno Alexandre
    Replies:
    5
    Views:
    187
    Bob Barrows
    Mar 3, 2004
  4. Arpan

    Parameters to Command Object!

    Arpan, Jun 26, 2005, in forum: ASP General
    Replies:
    6
    Views:
    178
  5. Florian Loitsch
    Replies:
    11
    Views:
    262
    Michael Winter
    Mar 15, 2005
Loading...

Share This Page