Epsylon
asked on
MS-SQL & ADODB.Command parameters
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 .connectio n")
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
And yes, adovbs.inc is included.
set conntemp = server.createobject("adodb
conntemp.open MYDSN
Set cmn = Server.CreateObject("ADODB
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
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 & "'"
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 & "'"
have you tried adVarChar instead?
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.
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.
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.
Yes, you do need the 200 as lenght, you can also try 200 in place of adVarCharadBSTR, I know that works dandy in Oracle.
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.
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.
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
John
ASKER
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(
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(
epsylon,
have you tried a small test query? just to see if it works?
have you tried a small test query? just to see if it works?
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
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
try
cmn.CommandText = "SELECT COUNT(IPaddress) AS vTotal FROM ECards WHERE IPaddress=?"
withe the 's gone.
cmn.CommandText = "SELECT COUNT(IPaddress) AS vTotal FROM ECards WHERE IPaddress=?"
withe the 's gone.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
John
ASKER
Without the quotes AND with specifying the size, it works! adBSTR and adVarChar both work ok.
Thank you all.
Thank you all.
cmn.Parameters.Append cmn.CreateParameter("@IpPa
the 200 is the length of the varchar field in defined in your sp