Access from ASP 2

[gav12345]

Hi all,

I'm a bit of a newbie to ASP, so hope this isn't too obvious...essentially I'm querying a very simple Access database from some ASP. The SQL I'm using is very simple, but unfortunately when I add a 'WHERE' clause to it, no data is returned. If I run the same query directly within the database, the query returns a number of rows.

The code that returns data OK is:

Set con = CreateObject("ADODB.Connection")
con.Open "CentralReporting"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = con
   	cmd.CommandText = "SELECT Report.ReportName, System.SystemName FROM System INNER JOIN Report ON System.SystemID = Report.SystemID" 
Set rs = cmd.Execute()

cmd.CommandText = "Select ReportURL from Report"
Set rs2 = cmd.Execute()

cmd.CommandText = "Select Description from Report" 
Set rs3 = cmd.Execute()
 
dim link(7)
dim iCount
dim iCount2
iCount = 0
iCount2 = 0

for iCount = 1 to 7 
	link(iCount) = "<a href=" & rs2("ReportURL")& "><h5>" & rs("ReportName") & "</h5></a>" & " <h6> "  & rs3("Description") & " </h6>"
	rs.movenext
	rs2.movenext
	rs3.movenext
next

for iCount2 = 1 to 7
	response.write (link(iCount2) & "<br>")
next

con.close

The code that returns no data at all is:

Set con = CreateObject("ADODB.Connection")
con.Open "CentralReporting"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = con
   	cmd.CommandText = "SELECT Report.ReportName, System.SystemName FROM System INNER JOIN Report ON System.SystemID = Report.SystemID WHERE Report.ReportName like " & chr(34) & "*" & chr(34)
Set rs = cmd.Execute()

cmd.CommandText = "Select ReportURL from Report"
Set rs2 = cmd.Execute()

cmd.CommandText = "Select Description from Report" 
Set rs3 = cmd.Execute()
 
dim link(7)
dim iCount
dim iCount2
iCount = 0
iCount2 = 0

for iCount = 1 to 7 
	link(iCount) = "<a href=" & rs2("ReportURL")& "><h5>" & rs("ReportName") & "</h5></a>" & " <h6> "  & rs3("Description") & " </h6>"
	rs.movenext
	rs2.movenext
	rs3.movenext
next

for iCount2 = 1 to 7
	response.write (link(iCount2) & "<br>")
next

con.close

Sorry if the code is a bit long-winded, as I say I'm quite new to this game. Basically I've tried various connotations of the where clause - selecting specific fields, messing around with the Chr(34) character etc., but essentially as soon as I stick in the 'WHERE', I get no data on my page!

Any suggestions? Many thanks.

 

[ChrisHirst]

LIKE would be used with wildcards which is "%" in SQL NOT "*"

SQL also uses single quotes "'" not double quotes """

so your query should be ... report.reportname LIKE '%' ;"

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[Chopstik]

Actually, I think the OP said they were using Access so I think he's on the right path. Have you tried to response.write your SQL and see what it looks like and test from there? Chances are it's not returning what you think it should be returning.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[gav12345]

Thanks - actually yes, I did try the response.write and copied that and created a new query from it in the Access database and it ran no problem! - I was kind of hoping there was a well known problem with ASP and the 'WHERE' clause! Any ideas?

Thanks again.

 

[Chopstik]

Out of curiosity, what does your code look like when you use the response.write? I know that I've had a problem once (a long time ago) in Access where the ASP side didn't match up the way I expected when it translated back over to Access. If you post back what your SQL looks like from the response.write, maybe something'll jog my memory a little...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[gav12345]

Thanks chopstik, I'll post tomorrow (in UK - late here!).

 

[Tarwn]

Chopstik: Chris may be right about needing to use single quotes and a percent instead of double quotes and a *. I think it depends on whetehr your using OLE or ODBC to do your connection as to which you use, though I never really was paying attention when I stopped doing one and the other.

gav12345: For the heck of it, try using those in your sql string like so:
"SELECT Report.ReportName, System.SystemName FROM System INNER JOIN Report ON System.SystemID = Report.SystemID WHERE Report.ReportName like '%'"

Also, could I ask why your pulling back the ReportURL and Description in two additional recordsets instead of just pulling them back in the original one?

-T

 

[gav12345]

Chris, Chopstik, Tarwn, thanks for your help. Yes, although Access was happy enough with double quotes, I needed to use single quotes from the code (and the '%' sign as a wildcard).

Tarwn, I was pulling back the other fields in additional recordsets because a.)New-ish to ASP b.)I've been sort of experimenting / adding bits on and that was easiest for me.

Thanks again, Gavin