ADODB.RecordSet in VBS

[alonex]

Hi,

I'm having problems retrieving data from large SQL table (100,000+ users), some users can be found using "select" command in SQL analyzer but NOT in the VB script.
Any ideas?

Thanks,
Alonex

Excerpt of the Code:
==========================

SET rsUnAcknowledged = CreateObject("ADODB.RecordSet")
cnHeat.Open("Provider=SQLOLEDB; DATA SOURCE=127.0.0.1;UID=sa;PWD=123456; DATABASE=testDB")
SQLText = "Select * from users where userid=N'"&user_id.value&"'"

rsUnAcknowledged.Open SQLText, cnHeat, 0,1 'Cursor Type=0 (Forward Only)

uidSQL = 0

On Error Resume Next
uidSQL = cInt(rsUnAcknowledged("uid"))
===========================================

 

[SQLSister]

Ok several things here. First absolutely under no circumstances should you ever allow an application to use the sa user to access your database. You have created a major security flaw here. Also never use select * unless you truly need every single column in the table (which is rare) as it puts an extra burden onthe server.

NOw as far as your problem. I would try one of the users that you can;t find from the vb app and print the sql it creates and then run that sql from QA rather than trying to run it manually from QA. Perhaps you will see that the SQL being created is different from what you are expecting to see. I wouldn;t be surprised if there are some extra spaces or other characters in your variable which would lead to there not being a match.

If that doesn't fix the problem, I suggest you encapsulize your code in a stored proc and run that instead. Hopefully that will have more consistent results for you.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

Try this...

SQLText = "Select * from users where userid=N'" & [!]Replace([/!]user_id.value[!], "'", "''")[/!] & "'"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[alonex]

Thanks, I double-checked the SQL command text and it's fine, using "replace" function didn't help.
So my last resort is store procedure, but I'm getting an error when running the store procedure withing the VBS: (in line --> cmd.ActiveConnection = cnHeat)

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

What is wrong?

Thanks,
Alonex

Code:
==========
set cmd = createobject("ADODB.Command")
set cnHeat = CreateObject("ADODB.Connection")
SET rsUnAcknowledged = CreateObject("ADODB.RecordSet")
SET rs = CreateObject("ADODB.RecordSet")
cnHeat.Open("Provider=SQLOLEDB; DATA SOURCE=127.0.0.1;UID=user;PWD=123456; DATABASE=test")

' got the error in this line
cmd.ActiveConnection = cnHeat


cmd.CommandType = adCmdStoredProc
cmd.CommandText = "getUserType"


cmd.Parameters.Append cmd.CreateParameter("user_id", adVarChar, adParamInput, 6, user_id.value)

Set rs = cmd.Execute

If Not rs.EOF Then
MsgBox rs.Fields(0)
MsgBox rs.Fields(1)
MsgBox rs.Fields(2)
MsgBox rs.Fields(3)
End If
============

 

[SQLSister]

YOu might want to ask this in the vb forum.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

Since the ActiveConnection property of the Command Object is ALSO an object, you need to SET it.

[!]Set [/!]cmd.ActiveConnection = cnHeat

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom