Solved

MS-SQL & ADODB.Command parameters

Posted on 2001-08-22
14
2,123 Views
Last Modified: 2010-05-18
I have a MS-SQL table with some records. In the 'IPaddress' field some records have '127.0.0.1' as value. The type of this field is 'varchar'. When I do a query, using ADODB.Command (see code below), string parameters won't work. The output is always '[0]'. Why? I have no problems with passing date or numeric values, only strings (adBSTR) fail.

And yes, adovbs.inc is included.



set conntemp = server.createobject("adodb.connection")
conntemp.open MYDSN

Set cmn = Server.CreateObject("ADODB.Command")
Set cmn.ActiveConnection = conntemp

cmn.CommandText = "SELECT COUNT(IPaddress) AS vTotal FROM ECards WHERE IPaddress='?'"
cmn.Parameters.Append cmn.CreateParameter(, adBSTR, , , "127.0.0.1")
Set rst = cmn.Execute

Set cmn = nothing

response.write "[" & rst("vTotal") & "]<br>"

rst.close
Set rst = nothing

conntemp.close
Set conntemp=nothing
0
Comment
Question by:Epsylon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
14 Comments
 
LVL 7

Expert Comment

by:John844
ID: 6413416
try something like:

cmn.Parameters.Append cmn.CreateParameter("@IpParamName", adVarCharadBSTR, adParamInput, 200, "127.0.0.1")

the 200 is the length of the varchar field in defined in your sp

0
 
LVL 7

Expert Comment

by:John844
ID: 6413425
scratch last comment, did not se that you were creating your parameter in SQL statement

Why don't you just use a sql statement like
dim strIpAddress
strIpAddress = "127.0.0.1"

cmn.CommandText = "SELECT COUNT(IPaddress) AS vTotal FROM ECards WHERE IPaddress='" & strIpAddress & "'"
0
 
LVL 2

Expert Comment

by:MCM
ID: 6413426
have you tried adVarChar instead?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 9

Expert Comment

by:msdixon
ID: 6413520
why are you adding parameters that way?

my thought is to create the sql script the same way john844 indicated. there's just less to go wrong, and you can always write out your sql statement and see what is going wrong if you start to have problems.
0
 
LVL 2

Expert Comment

by:turbosig
ID: 6413554
The addparam is alot more efficient.  it allows SQL to save the query in memory as a reusable query.  I would not recommend writing out your SQL in your code, unless you are not concerned with efficiency.

Yes, you do need the 200 as lenght, you can also try 200 in place of adVarCharadBSTR, I know that works dandy in Oracle.
0
 
LVL 9

Expert Comment

by:msdixon
ID: 6413581
turbosiq,

i did not know that it was more efficient that way. i only thought you got a performance boost when stored procedures were used. thanks for the info.
0
 
LVL 7

Expert Comment

by:John844
ID: 6413780
Is there some special way that you have to perform this operation to recieve the performance benefits.  I just tried a simple test to open 10 recordsets both ways, the command with parameters was twice as slow.
John
0
 
LVL 13

Author Comment

by:Epsylon
ID: 6413829
Thanks for the replies everyone.

The above code is just a simplified version of a large query. And I have to use ADODB.Command with parameters.

adVarChar doesn't work eighter   :o(
0
 
LVL 9

Expert Comment

by:msdixon
ID: 6413863
epsylon,

have you tried a small test query? just to see if it works?
0
 
LVL 2

Expert Comment

by:turbosig
ID: 6414081
John844, your correct, but we have over 6500 concurrent users on our system.  This infact has shown an increase in performance and ESPECIALLY a decrease in memory usage, which may be the largest point of this iteration.

I do agree that you gain performance increases by not
using the Command object in smaller local pages. the overhead of building the command parameters objects does take its toll on the smaller scale.

encapsulated and standardized code though is still an issue we have to deal withg.

Also, most of the performance issues (about 80%) have dealy with Oracle..

Thanks,
Turbosig
0
 
LVL 2

Expert Comment

by:MCM
ID: 6414339
try

cmn.CommandText = "SELECT COUNT(IPaddress) AS vTotal FROM ECards WHERE IPaddress=?"

withe the 's gone.
0
 
LVL 7

Accepted Solution

by:
John844 earned 50 total points
ID: 6414485
in conjunction with MGM's comment, this worked on my system

cmn.CommandText = "SELECT COUNT(IPaddress) AS vTotal FROM ECards WHERE IPaddress=?"
cmn.Parameters.Append cmn.CreateParameter(, adVarChar, ,16 , "127.0.0.1")
Set rst = cmn.Execute
0
 
LVL 7

Expert Comment

by:John844
ID: 6414601
BTW, on my system,  I could use adBSTR without the size specified.  using adVarChar needed the size specified, and performed much faster than adBSTR.

John
0
 
LVL 13

Author Comment

by:Epsylon
ID: 6416767
Without the quotes AND with specifying the size, it works! adBSTR and adVarChar both work ok.

Thank you all.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JS does not refresh 6 40
alert(innerHTML); 8 39
Send form to asp server side 6 28
Diff of the day 2 11
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question