Please help with cmd.execute select SQL?

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

  1. What-a-Tool

    What-a-Tool Guest

    What is the proper format for my SQL str using command.execute? Even though
    I know I have matching data in my table, I keep coming up with a
    ..RecordCount of -1. What am I doing wrong?

    Thanks in advance :

    'Create a connection object
    Set con = Server.CreateObject("ADODB.Connection")
    Set rst = Server.CreateObject("ADODB.Recordset")
    Set cmd = Server.CreateObject("ADODB.Command")

    strSQL = "SELECT tblUI.IPAdd FROM tblUI WHERE (tblUI.IPAdd ='?')"
    arSPrm = Array(strRemHst)

    con.Provider = "Microsoft.Jet.OLEDB.4.0"
    con.Open Server.MapPath("../dbs/vstr.mdb")

    cmd.CommandType = 1
    cmd.CommandText = strSQL
    Set cmd.ActiveConnection = con

    Set rst = cmd.Execute(,arSPrm)
    What-a-Tool, Mar 23, 2005
    #1
    1. Advertising

  2. Why do you need an ADODB.Command for this?

    Please see http://www.aspfaq.com/2126 for a more standard connection string,
    and see http://www.aspfaq.com/2193 for workarounds to your recordcount
    problem.


    On 3/22/05 9:06 PM, in article 3b40e.70832$SF.2693@lakeread08, "What-a-Tool"
    <Die!FrigginSpammers!DieDie!@IHateSpam.Com> wrote:

    > What is the proper format for my SQL str using command.execute? Even though
    > I know I have matching data in my table, I keep coming up with a
    > .RecordCount of -1. What am I doing wrong?
    >
    >
    > Thanks in advance :
    >
    > 'Create a connection object
    > Set con = Server.CreateObject("ADODB.Connection")
    > Set rst = Server.CreateObject("ADODB.Recordset")
    > Set cmd = Server.CreateObject("ADODB.Command")
    >
    > strSQL = "SELECT tblUI.IPAdd FROM tblUI WHERE (tblUI.IPAdd ='?')"
    > arSPrm = Array(strRemHst)
    >
    > con.Provider = "Microsoft.Jet.OLEDB.4.0"
    > con.Open Server.MapPath("../dbs/vstr.mdb")
    >
    > cmd.CommandType = 1
    > cmd.CommandText = strSQL
    > Set cmd.ActiveConnection = con
    >
    > Set rst = cmd.Execute(,arSPrm)
    >
    >
    >
    >
    Aaron [SQL Server MVP], Mar 23, 2005
    #2
    1. Advertising

  3. What-a-Tool

    Ken Schaefer Guest

    www.adopenstatic.com/faq/recordcounterror.asp
    www.adopenstatic.com/faq/recordcountalternatives.asp

    Cheers
    Ken


    "What-a-Tool" <Die!FrigginSpammers!DieDie!@IHateSpam.Com> wrote in message
    news:3b40e.70832$SF.2693@lakeread08...
    : What is the proper format for my SQL str using command.execute? Even
    though
    : I know I have matching data in my table, I keep coming up with a
    : .RecordCount of -1. What am I doing wrong?
    :
    : Thanks in advance :
    :
    : 'Create a connection object
    : Set con = Server.CreateObject("ADODB.Connection")
    : Set rst = Server.CreateObject("ADODB.Recordset")
    : Set cmd = Server.CreateObject("ADODB.Command")
    :
    : strSQL = "SELECT tblUI.IPAdd FROM tblUI WHERE (tblUI.IPAdd ='?')"
    : arSPrm = Array(strRemHst)
    :
    : con.Provider = "Microsoft.Jet.OLEDB.4.0"
    : con.Open Server.MapPath("../dbs/vstr.mdb")
    :
    : cmd.CommandType = 1
    : cmd.CommandText = strSQL
    : Set cmd.ActiveConnection = con
    :
    : Set rst = cmd.Execute(,arSPrm)
    :
    :
    :
    :
    Ken Schaefer, Mar 23, 2005
    #3
  4. What-a-Tool wrote:
    > What is the proper format for my SQL str using command.execute? Even
    > though I know I have matching data in my table, I keep coming up with
    > a .RecordCount of -1. What am I doing wrong?
    >
    > Thanks in advance :
    >
    > 'Create a connection object
    > Set con = Server.CreateObject("ADODB.Connection")
    > Set rst = Server.CreateObject("ADODB.Recordset")
    > Set cmd = Server.CreateObject("ADODB.Command")
    >
    > strSQL = "SELECT tblUI.IPAdd FROM tblUI WHERE (tblUI.IPAdd ='?')"
    > arSPrm = Array(strRemHst)
    >
    > con.Provider = "Microsoft.Jet.OLEDB.4.0"
    > con.Open Server.MapPath("../dbs/vstr.mdb")
    >
    > cmd.CommandType = 1
    > cmd.CommandText = strSQL
    > Set cmd.ActiveConnection = con
    >
    > Set rst = cmd.Execute(,arSPrm)


    It's got nothing to do with the use of a Command object. I think Aaron
    missed the fact that you are using the Command to pass parameters to your
    sql statement when he questioned your use of it.

    RecordCount requires the use of an expensive cursor. The default cursor
    (adOpenForwardOnly) which your code is usig, is a great, cheap cursor type
    which, however, does not support RecordCount. Now some may suggest
    specifying a more expensive cursor, either a client-side static cursor, or a
    server-side static, keyset, or dynamic cursor. However, there is no need to
    do this. Aaron's article makes the good suggestion of using GetRows, which
    has several advantages:

    1. By getting your data into an array, it allows you to close your recordset
    and connection immediately, releasing the connection back to the connection
    pool and allowing it to be re-used by the next user instead of requiring a
    new connection to be open. Fewer open connections = more scalable
    application.
    2. Processing the data is more efficient because it can be thousands of
    times faster to loop through an array than it is to loop through a recordset
    3. It allows you to use Ubound to determine the number of records that were
    returned

    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 23, 2005
    #4
  5. What-a-Tool

    What-a-Tool Guest

    Thank you for the advice and the links to the articles. Very helpfull

    Sean
    What-a-Tool, Mar 23, 2005
    #5
  6. What-a-Tool

    What-a-Tool Guest

    Wnt with this method after reading the post "Do Until Loop problems" replied
    to by Bob Barrows and decided to try it

    By the way, I ended up using the "GetRows" method suggested in the AspFaq
    #2193 - did just what I wanted.
    What-a-Tool, Mar 24, 2005
    #6
    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. Achim Domma (Procoders)

    read input for cmd.Cmd from file

    Achim Domma (Procoders), Jun 3, 2005, in forum: Python
    Replies:
    2
    Views:
    8,036
    Peter Otten
    Jun 3, 2005
  2. Sarir Khamsi

    Interpreter-like help in cmd.Cmd

    Sarir Khamsi, Jun 9, 2005, in forum: Python
    Replies:
    4
    Views:
    355
    Bengt Richter
    Jun 26, 2005
  3. =?ISO-8859-1?Q?Sch=FCle_Daniel?=

    [exec cmd for cmd in cmds]

    =?ISO-8859-1?Q?Sch=FCle_Daniel?=, Mar 8, 2006, in forum: Python
    Replies:
    3
    Views:
    370
    Scott David Daniels
    Mar 8, 2006
  4. Diez B. Roggisch

    pydb remote debugging/cmd.Cmd over socket?

    Diez B. Roggisch, May 28, 2008, in forum: Python
    Replies:
    2
    Views:
    537
    Diez B. Roggisch
    May 29, 2008
  5. Diez B. Roggisch

    cmd.Cmd bug or at least docu-bug

    Diez B. Roggisch, May 29, 2008, in forum: Python
    Replies:
    1
    Views:
    330
    Michele Simionato
    May 29, 2008
Loading...

Share This Page