No data return from ASP but Access returns correctly.

[Jon Turlington]

When in access the SQL Statement works as expected; in ADO it does not.
I have replaced the * with the % so I am sure that's not the problem.

Anyone have any idea?

<%

Dim objConn, objRS, sCity,CONNECTIONSTRING,ReponseString

CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and
Settings\jturlington\Desktop\Demographics\App_Data\DemoGraphics.mdb;User
Id=admin;Password=;"

'Capture the username that we need to lookup, making sure its prepared
for
'our forthcoming SQL query
sCity = Replace(Trim(Request.QueryString("city")),"'","")

'Fire up ADO - ask the database whether or not the user idexists
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open CONNECTIONSTRING
sSQL = "select ID,GeographicArea from Data where GeographicArea like
'%uni%'"
response.Write(sSQL & "<br />")
Set objRS = objConn.Execute(sSQL)

WHILE NOT objRS.EOF

ReponseString = objRS("ID") & "=" & objRS("GeographicArea") & ","

objRS.MoveNext

WEND

Response.Write(sCity & " ")

If Len(ResponseString) > 1 Then

Response.Write(Left(ResponseString, Len(ResponseString) - 1))

Else

Response.Write("No Records")

End If

'Clean up
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing

%>

 

[Bob Barrows [MVP]]

When in access the SQL Statement works as expected; in ADO it does
not. I have replaced the * with the % so I am sure that's not the
problem.

Anyone have any idea?

<%

Dim objConn, objRS, sCity,CONNECTIONSTRING,ReponseString

CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and
Settings\jturlington\Desktop\Demographics\App_Data\DemoGraphics.mdb;User
Id=admin;Password=;"

'Capture the username that we need to lookup, making sure its prepared
for
'our forthcoming SQL query
sCity = Replace(Trim(Request.QueryString("city")),"'","")

'Fire up ADO - ask the database whether or not the user idexists
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open CONNECTIONSTRING
sSQL = "select ID,GeographicArea from Data where GeographicArea like
'%uni%'"
response.Write(sSQL & "<br />")
Set objRS = objConn.Execute(sSQL)

WHILE NOT objRS.EOF

ReponseString = objRS("ID") & "=" & objRS("GeographicArea") & ","

objRS.MoveNext

WEND

Response.Write(sCity & " ")

If Len(ResponseString) > 1 Then

Response.Write(Left(ResponseString, Len(ResponseString) - 1))

Else

Response.Write("No Records")

End If

In the future, when experiencing a problem with a query, you need to show us
the _query_, not just the vbscript code to generate the query. Show us the
result of the response.Write(sSQL & "<br />") statement!

The better way to determine if your recordset contains records is to test
its EOF property:

if objRS.EOF then
Response.Write("No Records")
Else
ReponseString = objRS("ID") & "=" & objRS("GeographicArea") & ","
Response.Write(Left(ResponseString, Len(ResponseString) - 1))
' or do the loop
End if

I'm a little confused: Can your recordset contain more than one record? The
way you've coded your loop, it looks as if you are only interested in the
contents of the last record. Why is that? I think the query could be better
constructed to return only the single record you are interested in.

And finally:
our use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]