Display message when no matches in database 1

[dom24]

Hi,

Cn somebody please tell me how I write in ASP:

when there are no matches in the database then display "Sorry there are no matches."

Thanks.

 

[dom24]

Within a form and loop that is:

<form name="details">
<input type="text" value="<%=objRS("StaffNo")%>" name="StaffNo"><p>
<input type="text" value="<%=objRS("Location")%>" name="Location">
<input type="text" value="<%=objRS("ManagerNo")%>" name="ManagerNo"><p>
<select name="Course">
<%do while not objRS.eof %>
<option value="<%=objRS("CourseKey")%>"><%=objRS("CourseKey")%>
</option>
<%
objRS.MoveNext
loop
%>
</select>
<input type="text" value="<%=objRS("CourseDate")%>" name="CourseDate">
<input type="text" value="<%=objRS("TrainerNo")%>" name="TrainerNo">
</form>

I know I use:
If objRs.eof then
Response.write "<br><br><center><H2>Sorry this number does not exist. Please try again."

But I keep getting an error message so I must have it in the wrong place.

 

[Julian4145]

Whats the error you are getting?

Regards
Julian

 

[Tarwn]

Unless all the top code is inside the else off that if statement, your errors are going to be because your printing out stuff from a recordset with no records:

[highlight]<input type="text" value="<%=objRS("StaffNo")%>" name="StaffNo"><p>
<input type="text" value="<%=objRS("Location")%>" name="Location">
<input type="text" value="<%=objRS("ManagerNo")%>" name="ManagerNo"><p>[/highlight]
<select name="Course">
        <%do while not objRS.eof %>
            <option value="<%=objRS("CourseKey")%>"><%=objRS("CourseKey")%>
            </option>
        <%
        objRS.MoveNext
        loop
        %>
</select>
[highlight]<input type="text" value="<%=objRS("CourseDate")%>" name="CourseDate">
<input type="text" value="<%=objRS("TrainerNo")%>" name="TrainerNo">[/highlight]

Of course if you do have records returned your going to get an error on the first line after your loop"

[highlight]<input type="text" value="<%=objRS("CourseDate")%>" name="CourseDate">[/highlight]

the reason you will get an error on this line is because you have already looped through the recordset, it is now sitting at EOF, you can't pull data out of an EOF. If your cursor allows you to, do a MovePrev or MoveFirst after your loop so you can output more data. You should have a MoveFirst before your loop as well, never assume the recordset pointer is setup pointing at the first record, if you order your data then many times you will get a recordset with the pointer somewhere in the middle.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[dom24]

I'm confused! This is the code i've got at the mo and it works unitl I take the comments out for when the staff number doesn't exist.

Dim strSQL, objRS

'If Request.QueryString("staffNo").Count = 0 Then
'Response.Write "Sorry this staff number does not exist. Please try again."

'Else

If Request.QueryString("StaffNo")="" and Request.QueryString("LName")=""then
Response.Redirect "name1.asp"

ElseIf Request.QueryString("LName2")="" and Request.QueryString("StaffNo")<>"" then
strSQL = "SELECT * from Staff, Validation where staff.staffno = validation.staffno and staff.staffno = '" & Request.QueryString("StaffNo") & "'"

GetConnection
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open strSQL, Conn%>
<input type="text" value="<%=objRS("LName")%>" name="LName">
<input type="text" value="<%=objRS("FName")%>" name="FName">

<%ElseIf Request.QueryString("LName")<>"" then
strSQL = "SELECT * from Staff, Validation where staff.staffno = validation.staffno and staff.Lname = '" & Request.Querystring("LName") & "' and staff.FName = '" & Request.Querystring("FName") & "'"%>
<input type="text" value="<%=Request.querystring("LName")%>" name="LName">
<input type="text" value="<%=Request.querystring("FName")%>" name="FName">
<%GetConnection
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open strSQL, Conn
End If
%>

<form name="details">
<input type="text" value="<%=objRS("StaffNo")%>" name="StaffNo"><p>
<input type="text" value="<%=objRS("Location")%>" name="Location">
<input type="text" value="<%=objRS("ManagerNo")%>" name="ManagerNo"><p>
<select name="Course" onChange="submit()">
<%do while not objRS.eof %>
<option value="<%=objRS("CourseKey")%>"><%=objRS("CourseKey")%>
</option>
<%
objRS.MoveNext
loop
%>
</select>
<input type="text" value="<%=objRS("CourseDate")%>" name="CourseDate">
<input type="text" value="<%=objRS("TrainerNo")%>" name="TrainerNo">
</form>


The error message I get when I take the comments out is:

Microsoft VBScript runtime error '800a000d'

Type mismatch

/Evaluation/Name3.asp, line 46

 

[Tarwn]

Line 46 being...?

Your still going to get an error on:
<input type="text" value="<%=objRS("CourseDate")%>" name="CourseDate">

because you already looped through the recordset to the end and there is no data to pull out anymore.

To correct your code a little:

<%
Dim strSQL, objRS

If [highlight]Len(Request.QueryString("staffNo")) = 0[/highlight] Then
	Response.Write "Sorry this staff number does not exist. Please try again."
Else

	If Request.QueryString("StaffNo")="" and Request.QueryString("LName")=""[highlight] [/highlight]then
		Response.Redirect "name1.asp"

	ElseIf Request.QueryString("LName2")="" and Request.QueryString("StaffNo")<>"" then
		strSQL = "SELECT * from Staff, Validation where staff.staffno = validation.staffno and staff.staffno = '" & Request.QueryString("StaffNo") & "'"

		GetConnection
		Set objRS = CreateObject("ADODB.Recordset")
		objRS.Open strSQL, Conn

		[highlight]'If no records were returned then give them an error message
		If objRS.EOF Then
			Response.Write "Sorry, the staff number you providd does not appear to exist. Please try again."
		Else[/highlight]
			%>
			<input type="text" value="<%=objRS("LName")%>" name="LName">
			<input type="text" value="<%=objRS("FName")%>" name="FName">
			<%
		[highlight]End If[/highlight]

	ElseIf Request.QueryString("LName")<>"" then
		strSQL = "SELECT * from Staff, Validation where staff.staffno = validation.staffno and staff.Lname = '" & Request.Querystring("LName") & "' and staff.FName = '" & Request.Querystring("FName") & "'"
		%>
		<input type="text" value="<%=Request.querystring("LName")%>" name="LName">
		<input type="text" value="<%=Request.querystring("FName")%>" name="FName">
		<%
		GetConnection
		Set objRS = CreateObject("ADODB.Recordset")
		objRS.Open strSQL, Conn
	End If

	[highlight]'If there are no records, display an error
	If objRS.EOF Then
		Response.Write "No records match your criteria, please try again."
	Else
		objRS.MoveFirst
	%>
	[/highlight]
	<form name="details">
	<input type="text" value="<%=objRS("StaffNo")%>" name="StaffNo"><p>
	<input type="text" value="<%=objRS("Location")%>" name="Location">
	<input type="text" value="<%=objRS("ManagerNo")%>" name="ManagerNo"><p>
	<select name="Course" onChange="submit()">
        <%
		do while not objRS.eof %>
            <option value="<%=objRS("CourseKey")%>"><%=objRS("CourseKey")%>
            </option>
	        <%
		    objRS.MoveNext
        loop

		[highlight]'after the loop move back into the recordset so we have data to output
		objRS.MoveFirst[/highlight]
        %>
	</select>
	<input type="text" value="<%=objRS("CourseDate")%>" name="CourseDate">
	<input type="text" value="<%=objRS("TrainerNo")%>" name="TrainerNo">
	</form>
	<%
	[highlight]End If[/highlight]
[highlight]End If[/highlight]
%>

Now there is a lot of that code that could be trimmed down and made a little smoother. For instance, right now your doing severl things in a redundant fashion, such as opening the recordset. If I were writing this I think I would do something like:

<%
Dim strSQL, objRS

'--- Verify that data has been entered
If Request.QueryString("StaffNo") = "" And Request.QueryString("LName") = "" Then
	ShowError "You have not entered any criteria."
End If

'--- Build the SQL based on whichever data they input
strSQL = "SELECT LName, FName, Staff.StaffNo, Location, ManagerNo, CourseKey, CourseDate, TrainerNo " & _
		 "FROM Staff INNER JOIN Validation WHERE Staff.StaffNo = Validation.StaffNo "

If Request.QueryString("StaffNo") <> "" Then
	strSQL = strSQL & "AND Staff.StaffNo = '" & Replace(Request.QueryString("StaffNo","'","''")) & "' "
End If

If Request.QueryString("LName") <> "" Then
	strSQL = strSQL & "AND Staff.LName LIKE '%" & Replace(Request.QueryString("LName","'","''")) & "'% " & _
					  "AND Staff.FName LIKE '%" & Replace(Request.QueryString("FName","'","''")) & "'% "
End If

'--- Get the recordset for this data
GetConnection
Set objRS = Conn.Execute(strSQL)

'--- Check for data in the recordset
If objRS.EOF Then
	ShowError "Sorry, there are no records that macthed your criteria. Please Try Again."
End If

'--- Output the received data
%>
<html>
<head>
<body>
<form method="POST" action="Somewhere.asp">
	<!-- Display Data in Form -->
	Staff #: <input type="text" value="<%=objRS("StaffNo")%>" name="StaffNo"><br />
	Name: <input type="text" value="<%=objRS("LName")%>" name="LName">, 
	<input type="text" value="<%=objRS("FName")%>" name="FName"><br />
	Location: <input type="text" value="<%=objRS("Location")%>" name="Location"><br />
	Manager #: <input type="text" value="<%=objRS("ManagerNo")%>" name="ManagerNo"><br />

	Courses: <select name="Course" onChange="submit()">
		<%
		Do Until objRS.EOF
			Response.Write "<option>" & objRS("CourseKey") & "</option>"
			objRS.MoveNext
		Loop
		objRS.MoveFirst
		%>
		</select><br />

	Course Date: <input type="text" value="<%=objRS("CourseDate")%>" name="CourseDate"><br />
	Trainer #: <input type="text" value="<%=objRS("TrainerNo")%>" name="TrainerNo"><br />
</form>
</body>
</html>

<%
'This function clears any previous output, displays the error message and link to name1
'	then forces the output to end, basically exiting early
Function ShowError(message)
	response.Clear
	%>
	<html>
	<head><META HTTP-EQUIV=Refresh CONTENT="3; URL=name1.asp"></head>
	<body>
	<center>
	<%=message%><br />
	If your browser does not redirect you to the previous page, please click <a href="name1.asp">Here</a>.
	</center></body></html>
	<%
	Response.End
End Function
%>

Of course I am a little confused about this whole thing, i mean it looks liek this is some kind of course selection page, but your selectiong from among courses the person has already taken, plus your making all of their data editable, and the CourseNo and Trainer # are always going to display the same thing, which may not agree with what is in the Course Select box. I would almost think that you would want to output that data in a table with checkboxes so you could show the course, course no, and trainer no on each row for each course. Plus I don't think you want to make the other data editable and you may want to put an address in the form tag so it submits somewhere...though I'm not sure the submit() in the onClick in the select box is going to submit correctly since your not referencing a form first.

Anyways, just my thoughts,
-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[dom24]

Thanks very much for that it's a big help.

Just to clear it up, I'm creating an online feedback system. Users select their name or enter their staff number and I then want it to pull back and display all the courses they have taken and the dates (already stored in a SQL database). Once their details are displayed i'm going to have check boxes where they rate certain aspects of the course and then write this to the database in order to create reports.

It's the first time I've used ASP really so that's why everything is all over the place!

At the moment i've got the page submitting to another page when an option is selected from a drop down. Only thing is I have to use a hidden input type so that I can display the selection again on the next page. Think there must be another way round this but I can't find how so that seems to be the easiest way.

Couple of things I don't understand in your code (if you get chance to explain, no worries if not you've helped enough) the "replace" part and "LEN". Not sure what they do?

Thansk again for this.

 

[Tarwn]

Len(string) gives you the length of a string
Replace(originalString,matchString,replaceString) replace every copy of matchString in originalString with replaceString. The reason I added that replace was because if someone inserted some single-quotes into one of the inputs thy could break your SQl statement or even force it to report information you hadn't planned on it reporting (it's called SQL injection). So we replace single quotes with two single quotes and the database understands we don't want it treated like a single quote, just treated like any other character inside a string.

Suggestions:
1) I would suggest not placing all the non-course data in textboxes, it makes it seem like it is editablewhen in reality your just displaying it to the user for their own confirmation. So i would put the StaffNo in a hidden field and just print it, and then just print all of the other fields (name, etc).
2) I wouldn't display the CourseNo and TrainerNo simply because if thy change the selection in the dropdown box it won't automatically update them, making those entries meaningless when they select a new course. Maybe wait until the next page after they have selected a course and then display those details.

I'm not sure about the hiden input, i don't think i'm quite comprehending what your doing with it from your explanation.

Congrats on a good job for your first time, I never would have guessed it was your first piece of ASP. Remember we're always here if you run into something odd, unexpected, or just downright painful

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[dom24]

Sorry to bother you again but i've got an error now!

The message is:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near 'Staff'.

/Evaluation/name3.asp, line 40

But line 40 in my code is Set objRS = Conn.Execute(strSQL)
????

Can you also tell me when should I be using:
objRS.Open strSQL, Conn

I was using it straight after getting the connection, like this:

GetConnection
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open strSQL, Conn

but i've noticed you didn't do that in the one you sent me.

 

[Tarwn]

Sorry about that. It's two differant methods to get the same results, I just have seen better performance/efficiency using the Connection.Execute method then I have with the Recordset.Open. Plus I have seen to many peopl eget sloppy with Recordset.Open and open a whole table just to search for a single record. Connection.Execute requires a little more work so even though you still might see people doing Select *'s from tables, it is less frequent.

The error you received is due to an error in the SQL string. Anytime you get an error on the line ith a Recordset.Open or COnnection.Execute you can usually bet that is the case. If you add something like:
Response.Write sqlString
Response.Flush

right before the Execute line then it will print it to the screen and we'll be able to see where it is having issues.

-T


_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Help, the rampaging, spear-waving, rabid network gnomes are after me!