Displaying records in a table

[rosebud289]

I have developed a web page that retrieves a list of topics and speakers from an Access database and displays them in a table. Some topics have multiple speakers, therefore I do not want the topics to be displayed again - just additional speakers. The code I have developed works fine for one or two speakers, but if there's more than two then the speaker's name is displayed above the table. Could you please let me know how to fix this problem? Thanks!

Example: TOPIC SPEAKER(S)
ABC's of Scuba Diving Joan Dickson
Mike Reynolds
George Stevens

Here's the code:
<% Dim Bool
header = Request.Form("header")
speaker_name = Request.Form("speaker_name")
lecture_time = Request.Form("lecture_time")
header = replace(header, "'", "")
q = "SELECT s_id, topic, description, min_time, first_name, last_name, headers, av_requirements FROM webupdates WHERE headers LIKE '%" & header & "%' AND S_ID LIKE '%" & speaker_name & "%' AND min_time LIKE '%" & lecture_time & "%' order BY topic, last_name, first_name"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open q, "DSN=access;"
%>
<%If NOT rs.EOF then%>
<% Bool = True %>
<% num = 0 %>
<%while not rs.EOF%>
<% num = num + 1 %>
<% if num mod 2 = 0 then %>
<% strColor = "#CCDDDD" %>
<% else %>
<% strColor = "#d1e3f8" %>
<% end if %>
<%noav=rs("av_requirements").value%>
<%if nodup<>rs("topic").value then%>
<tr>
<td bgcolor="<%=strColor%>" height="127" valign="middle"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b><font face="Arial, Helvetica, sans-serif"><%=rs("topic")%></font></b><font face="Arial, Helvetica, sans-serif"><br>
<%=rs("description")%></font></font></td>
<td valign="middle" bgcolor="<%=strColor%>">
<div align="center"><font face="Arial, Helvetica, sans-serif" size="2"><%=rs("min_time")%>
min.</font></div>
</td>
<td valign="middle" bgcolor="<%=strColor%>" align="center"><font face="Arial, Helvetica, sans-serif" size="2">

<%if noav=" " then%>
<%Response.Write("see ")%>
<%else%><%=rs("av_requirements")%><% end if%>

</font> </td>
<td bgcolor="<%=strColor%>" valign="middle"><a href="bios_revised.asp?s_id=<%=rs("s_id")%>"><font face="Arial, Helvetica, sans-serif" size="2"><%=rs("first_name")%>
<%Response.Write(" ")%>
<%=rs("last_name")%></font></a><br>
<%else%>
<a href="bios_revised.asp?s_id=<%=rs("s_id")%>"><font face="Arial, Helvetica, sans-serif" size="2"><%=rs("first_name")%>
<%Response.Write(" ")%>
<%=rs("last_name")%></font></a> </td>

<%end if%>
<% nodup=rs("topic").value%>
<%rs.MoveNext%>
<br>
<%wend%>
<%else%>
<%Bool=False%>
<% Response.Write("No topics found.") %><br><br>
<% end if%>
</tr>

 

[ethorn10]

remove the ',' before the word 'FROM'

 

[Sheco]

Whoops, my bad. Sorry about that!

I can see your code and even suggest changes to it but I can't actually run the changes to test them because I don't have your database. This is a syntax error that slipped through.

 

[PaulBricker]

If this is an Access database, then Like '%American Culture%' should be
Like '*American Culture*'

I don't have a clue what Like '%%%' is supposed to return but when I use it in a query, it doesn't return anything.


Paul

 

[Sheco]

yeah, thats a different problem... the input variable should probably be scanned to remove characters that will give you a bad SQL string... especially the single quote [red]'[/red] and the percent sign [red]%[/red]

 

[rosebud289]

I'm using the "%" as the wildcard to search for ALL headers, speakers and lecture times. I removed the comma in front of FROM and removed the percent signs surrounding the the variable and I'm receiving the following error message:

SELECT s_id, first_name, last_name FROM webupdates WHERE header LIKE '%' AND S_ID LIKE '%' AND min_time LIKE '%' AND topic = '19th-Century German Art Songs' ORDER BY topic, last_name, first_name
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

 

[Sheco]

You can keep the percent signs surrounding the variable.

All I was saying is that you should scan the variable to make sure that it doesnt contain any percent signs or single quotes as part of the data value.

As a simple example, suppose your SQL looked like this:
SELECT * FROM myTable WHERE myField = 'dog'

But you wanted to search by a variable and not the hardcoded string 'dog'

So you did this:
mySQL = "SELECT * FROM myTable WHERE myField = '" & myVar & "'"

Ok, well that is all fine and dandy as long as the value inside of myVar doesn't contain any single quotes.

If you had, for example:
myVar = "Tom's dog"

Well now your SQL string will look like this:
SELECT * FROM myTable WHERE myField = 'Tom'[red]s dog'[/red]

This will cause an error because, what you really have is:
SELECT * FROM myTable WHERE myField = 'Tom'

With an extra s dog' tacked onto the end.

The solution? Either reject input with those character or replace them with something else. I like to replace the single quote with the apostrophe character that is next to the 1 on my keyboard... it looks like this: [red]`[/red]

 

[steven290]

you could also do this couldn't you:

myVar = replace("Tom's dog","'","''")

 

[Sheco]

You mean using a pair of single quotes as an escape sequence for the single quote?

Yeah but then you don't get to use the word apostrophe in a sentence. Apostrophe, apostrophe, apostrophe!!!!

 

[steven290]

? explain i'm not to sure what you mean

 

[Sheco]

By "escape sequence" I mean typing one thing when you mean another.

Im not sure where the term originates but the most popular escape sequences that I can think of is the C-style tab and newline. They are also used in JavaScript.

\t = tab
\n = newline

You couldn't actually type a tab as part of your character array because your editor uses the tab key to, well, to tab over, so instead you use \t.

But a "problem" arises if you actually want a backslash character so your have to escape your escape with a double backslash \\

In C you use \' and \" to escape quotes.

 

[steven290]

i understand that part i don't understand this

Yeah but then you don't get to use the word apostrophe in a sentence. Apostrophe, apostrophe, apostrophe!!!!

 

[Sheco]

Oh, I just have a certain fondness for the apostrophe.

I like the way it is spelled... also the way the word flows off the tongue.

There could have been a Greek Goddess named Apostrophe.

 

[Sheco]

If I ever have a daughter, perhaps I will name her Apostrophe.

But I think my wife would kill me.

 

[steven290]

lololol

 

[PaulBricker]

Can someone explain to me what we are looking for with
Like '%'
If % is a wildcard, it doesn't work in Access. Access uses the # sign to delimit wildcards. SQL Server uses the % sign. I'm just a tad confused so if someone might clear that up I'd appreciate it.

Paul

 

[ethorn10]

Paul. Maybe I'm wrong, but I'm pretty sure Access uses '*' as the wildcard delimiter. Not '#'. Just some food for thought...hope it clears that up.

 

[PaulBricker]

Sorry about that. You are right, the wildcard is the * sign and I just wasn't thinking when I typed that sign (getting ready to leave the office after a very long day). My confusion comes in because Rosebud is trying to pass a string to a .mdb file using '%' in a Like statement. I'm wondering what is expected for return data when using that statement because I don't think a .mdb database will return anything with it. So I thought I'd completely confuse the hell out of everyone else too so I didn't look too stupid.

Thanks

Paul