By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

Please help with cmd.execute select SQL?

P: n/a
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)


Jul 22 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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"
<Di*************************@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)


Jul 22 '05 #2

P: n/a
www.adopenstatic.com/faq/recordcounterror.asp
http://www.adopenstatic.com/faq/reco...ternatives.asp

Cheers
Ken
"What-a-Tool" <Di*************************@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)
:
:
:
:
Jul 22 '05 #3

P: n/a
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"
Jul 22 '05 #4

P: n/a
Thank you for the advice and the links to the articles. Very helpfull

Sean

Jul 22 '05 #5

P: n/a
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.
Jul 22 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.