 |
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
 Posted: Tue Nov 25, 2003 3:24 pm
Problems using ADO
|
I'm trying to use ADO to connect to SQL Server. For some reason, the code below isn't working properly. I don't think I'm even getting a result set returned from the query I'm running. Any ideas?
#VAR ConnStr = "my connection string"
#VAR rsquery %comcreate( "ADODB.Recordset")
#VAR csql %concat( "select * from mytable where password = '", @password,
"'")
#CALL @rsquery.Open(@csql, @ConnStr)
#IF (@rsquery.eof) {
#ECHO nothing
}{
#IF (@rsquery.bof) {
#ECHO nothing
}{
#ECHO something
}
}
#CALL @rsquery.movefirst
#CALL @rsquery.close |
|
|
|
|
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
Posted: Tue Nov 25, 2003 3:25 pm
|
One more note. I tried the same code in VB and it works fine. Thanks.
-JennyA |
|
|
|
|
hatespyware
Apprentice
Joined: 16 Dec 2002
Posts: 103
|
Posted: Tue Nov 25, 2003 4:23 pm
|
I think the problem is probably related to your if/else chain. The following works for me.
| Code: |
#VAR ConnStr "DSN=eq"
#VAR rsquery %comcreate( "ADODB.Recordset")
#VAR csql "select id,name from items where name like '%spiked%'"
#CALL @rsquery.Open(@csql, @ConnStr)
#IF (@rsquery.eof) {
#ECHO at eof
#abort
}
#IF (@rsquery.bof) {
#ECHO at bof
#abort
}
#echo @rsquery("name")
#Com @rsquery.movefirst
#COM @rsquery.close
|
p.s. Why not post the vb code,too, next time? Would eliminate any need to guess at your goal.
p.s.s. I despise the way zmud requires the opening block to begin on the same line as the conditional. Makes for very ugly, hard to read code (as evidenced, above). |
|
|
|
|
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
Posted: Tue Nov 25, 2003 4:51 pm
|
Agreed on the formatting of zMud code. To be able to indent things and keep them that way would be very helpful. Maybe we should suggest that in the beta forum.
Thanks for the snippet of code. I can't try it right now, but will definitely give it a shot when I get home from work. Interesting, I was trying to embed my if/else statements, but maybe that was what was causing the problem. Yours is probably easier to maintain too since having long conditionals becomes hard to read. |
|
|
|
|
LightBulb
MASTER
Joined: 28 Nov 2000
Posts: 4817
Location: USA
|
Posted: Tue Nov 25, 2003 5:19 pm
|
Haven't tested, but you have a common mistake in what you posted here -- no space between } and {.
#VAR ConnStr = "my connection string"
#VAR rsquery %comcreate( "ADODB.Recordset")
#VAR csql %concat( "select * from mytable where password = '", @password,
"'")
#CALL @rsquery.Open(@csql, @ConnStr)
#IF (@rsquery.eof) {
#ECHO nothing
}{
#IF (@rsquery.bof) {
#ECHO nothing
}{
#ECHO something
}
}
#CALL @rsquery.movefirst
#CALL @rsquery.close |
|
|
|
|
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
Posted: Tue Nov 25, 2003 5:28 pm
|
Ah ha, the lack of spaces. That sounds like it might be the problem. Thanks for the help guys, I'll try this all out as soon as I get home.
|
|
|
|
|
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
Posted: Wed Nov 26, 2003 1:27 am
|
Ok, I've changed a few things around. Lightbulb's observation of the lack of spaces did clear up my IF/ELSE problem, but I still seem to be having trouble determine the EOF or BOF of a recordset. Anyone see why this wouldn't work? Backend is SQL Server 2k. Quite frustrated at this point, not sure what I'm doing wrong...
#VAR ncount 0
#VAR rsquery %comcreate( "ADODB.recordset")
#VAR csql %concat( "select * from mytable where password = '", @password, "'")
#CALL @rsquery.open( @csql, @ConnStr, 2)
#CALL @rsquery.movefirst
#WHILE (not @rsquery.eof) {
#ADD ncount 1
#CALL @rsquery.MoveNext
}
#ECHO @ncount
#CALL @rsquery.close
Just wanted to add one more thing, #IF ( @rsquery.eof) is not working either. It doesn't seem to recognize the end of the recordset. Another thing to mention is the query should be returning a NULL result set. Anyhow, any help would be GREATLY appreciated.
-JennyA |
|
|
|
|
hatespyware
Apprentice
Joined: 16 Dec 2002
Posts: 103
|
Posted: Wed Nov 26, 2003 2:12 am
|
It works fine for me. Prints count(*).
|
|
|
|
|
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
Posted: Wed Nov 26, 2003 3:03 am
|
Could it be that I'm using SQL Server? I'll try porting this to VB as well and see if it works there. No idea what the hell I could be doing wrong. When I run the above code the WHILE loop never ends - infinite loop.
|
|
|
|
|
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
Posted: Wed Nov 26, 2003 3:18 am
|
Here's the VB version and this works
| Code: |
ncount = 0
Dim rsquery As New ADODB.Recordset
csql = "select * from mytable"
Call rsquery.Open(csql, connstr, 2)
rsquery.MoveFirst
Do While Not rsquery.EOF
ncount = ncount + 1
rsquery.MoveNext
Loop
MsgBox (ncount)
rsquery.Close
|
|
|
|
|
|
hatespyware
Apprentice
Joined: 16 Dec 2002
Posts: 103
|
Posted: Wed Nov 26, 2003 3:51 am
|
Please stop censoring your scripts. Why would you execute a different query in the vbs and consider it to be doing the same thing? Also, mask your password/username if you need to do so, but we need to see your connection string. I have a SQL server, but I might connect to the same db with as many as three or four different connect strings. How can I duplicate your problem without knowing the details? The following works for me. Since it is virtually identical to your code (by intent), the problem must be related to either your connection string or a bogus query (including the value of "@password").
| Code: |
#VAR ncount 0
#VAR ConnStr "DSN=eq"
#VAR rsquery %comcreate( "ADODB.Recordset")
#VAR csql "select id,name from items where name like '%spiked%'"
#CALL @rsquery.open( @csql, @ConnStr, 2)
#CALL @rsquery.movefirst
#WHILE (not @rsquery.eof) {
#ADD ncount 1
#CALL @rsquery.MoveNext
}
#ECHO @ncount
#CALL @rsquery.close
|
|
|
|
|
|
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
Posted: Wed Nov 26, 2003 5:23 am
|
Alright, let's try this again. Wasn't trying to censor my script, but you are right I should have included the connection string in there. I wasn't actually changing queries, I tried both versions in zMud and VB, but I posted this VB version here instead of the other one. Stupid on my part. Thanks for being patient hatespyware, I appreciate it. This query should return one record. Here's what I'm doing in zMud (doesn't work) and VB (works):
zMUD
| Code: |
#VAR ConnStr "Provider=SQLOLEDB;Data
Source=hostname;database=dbname;uid=userid;pwd=password;"
#VAR ncount 0
#VAR rsquery %comcreate( "ADODB.recordset")
#VAR csql "select * from mytable"
#CALL @rsquery.open( @csql, @ConnStr, 2)
#CALL @rsquery.movefirst
#WHILE (not @rsquery.eof) {
#ADD ncount 1
#CALL @rsquery.MoveNext
}
#ECHO @ncount
#CALL @rsquery.close |
VB
| Code: |
Private Sub Command1_Click()
connstr = "Provider=SQLOLEDB;Data Source=hostname;"+ _
database=dbname;uid=userid;pwd=password;"
ncount = 0
Dim rsquery As New ADODB.Recordset
csql = "select * from mytable"
Call rsquery.Open(csql, connstr, 2)
rsquery.MoveFirst
Do While Not rsquery.EOF
ncount = ncount + 1
rsquery.MoveNext
Loop
MsgBox (ncount)
rsquery.Close
End Sub |
|
|
|
|
|
hatespyware
Apprentice
Joined: 16 Dec 2002
Posts: 103
|
Posted: Wed Nov 26, 2003 7:00 am
|
Thank you for being more clear. I'm afraid, however, that I am unable to duplicate your problem.
|
|
|
|
|
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
Posted: Wed Nov 26, 2003 8:57 pm
|
Thanks again for the help HateSpyWare. Damn, this stinks that I can't figure out the problem. I've got a complex script I've been writing and if I can't use the EOF or BOF functions of ADO with SQL Server, it won't work. Not sure what to do next...
HateSpyWare, do you happen to be using the beta version of zMud? I haven't given that a shot yet. |
|
|
|
|
|
|
|
