sql statement through vb code 1

[butchkmd]

I am using this sql statement called from a class module

RST.Open "select DISTINCT REPNAME, E_MAILADDR, ILEC, LEVEL] from escalateemail where ilec = " & "'" & strIlec & "'" & " AND " & strState & " = 1 order by [level]", CN, adOpenForwardOnly, adLockReadOnly, adCmdText

strState is a column name in the table.

When I run the query from sql analyzer I get my record set, when I run it form my code I get the field count right but no records...any suggestions?

PS I have tried assigning the sql statement to a variable then runing it with the same result.

 

[luisfelipe007]

Hi, try...

RST.Open "select DISTINCT REPNAME, E_MAILADDR, ILEC, LEVEL] from escalateemail where ilec = '" & strIlec & "' AND " & strState & " = '1' order by [level]", CN, adOpenForwardOnly, adLockReadOnly, adCmdText

sending 1 as '1', also debug strState

regards

--
Luis MX

 

[nicsin]

hi butchkmd,

if strState is a column in the table then:

RST.Open "select DISTINCT REPNAME, E_MAILADDR, ILEC, LEVEL] from escalateemail where ilec = " & "'" & strIlec & "'" & " AND strState = 1 order by [level]", CN, adOpenForwardOnly, adLockReadOnly, adCmdText

a second thing to check is the value of strIlec. Does it have the correct one?

regards,
nicsin

 

[CassandraR]

Please excuse my ignorance, but what is the purpose of the right square bracket after the first LEVEL?

Cassie

 

[butchkmd]

hhmm,
to reply to Luis, I tried the ''s around the 1 and no dice.
when I step through the code, the variables are correct...I'm not sure what additional debugging you are refering to??

nicsin,
If I included the strState inside the quotes it would pass the variable name instead of the value right?

Cassie,
the bracket on the left side is missing in the entry but there in the code, it is due to the fact that "level" is a "key" word used and recognized by sql. If you don't enclose it in brackets, you will have a syntax error

 

[zemp]

If you are not getting any errors check to see that you have data that matches your where clause.

What I like to do to debug a built SQL is to use debug.print to see exactly what the SQL looks like. Then Cut and paste it to the database (query analyzer for SQL server) and run the SQL. You can usually get more detailed error messages and you can see exactly what your SQL looks like.

One other note, just like you need to surround field names that are also key words with square barackets, it is a good idea to surround field names with an underscore with square brackets as well. The underscore can sometimes cause a problem.


Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[luisfelipe007]

then DISTINCT its not working as you want :O



--
Luis MX

 

[butchkmd]

I tried to cut and paste it into analyzer and fill in the values for the variables, it returns the right data, when I check the values of the variables steopping through, it matches the data in the query. It has to be something with the select, something silly, there are no errors but it returns an empty recordset.

 

[luisfelipe007]

after cn.open try..

CN.CursorLocation = adUseClient

and then your rst.open, are you using 'on Error' ??? remove it, if you are.

try changing adForward/adLock, for other options.

--
Luis MX

 

[butchkmd]

that was it, my connection optionswere messing me up.
thanks for the help...

 

[luisfelipe007]

wow wow that was my first star thanks!!!

--
Luis MX