ASP/SQL Empty Recordset producing blank page.

**rfiscus** · Apr 23 '12, 04:58 PM

Additional if then attempt

I have also tried this but same thing, if the record is valid, it will show the correct data, if the form field is not like a row from the sql table the page comes up blank.

Code:

<%   
If InStr(Recordset.Fields("GF3_EKKO_EBELN"), purchaseorder)<0 Then
Response.Write "<td align=""left""><span class=""style8"">YOUR RESPONSE IS INVALID</span></td>"
Else
Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
%>

Originally posted by rfiscus

In essence, I have a asp form page that posts itself to another asp page for validation, as long as the second page finds an equal PO number in the sql query, everything works great, if there is not a result in the SQL table the page is blank, I have been trying to say if PO is in recorset then response write the field else write response invalid or something similar. I have obviously written the code wrong, any help would be appreciated. As I said if the resultant set is good, it all works.

Code:

sSQL="SELECT * FROM [database].[dbo].[table1],[database].[dbo].[table2] where GF3_EKKO_EBELN like '%" & purchaseorder & "' and DE_Employee = '" & employee & "'"

<%   
If (Recordset.Fields("GF3_EKKO_EBELN") not like purchaseorder Then
Response.Write "<td align=""left""><span class=""style8"">YOUR RESPONSE IS INVALID</span></td>"
Else
Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
%>

**Rabbit** · Apr 23 '12, 05:48 PM

Use the RecordCount property to see how many records were returned. If it's 0, then you didn't get a match.

**rfiscus** · Apr 23 '12, 05:52 PM

I know that I am getting a record count of 0 when I type in a wrong purchaseorder number, that is the issue, I am trying to make sure that it does not display the blank recordset, I want it to display a generic error in its place. I tried this but same results, displays if valid but blank page if invalid.

Code:

<%   
If InStr(Recordset.Fields("GF3_EKKO_EBELN"), purchaseorder)>0 Then
Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
Else
Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="""">"
Response.Write "<td align=""left""><span class=""style8"">YOUR RESPONSE IS INVALID</span>"
Response.Write "<span class=""style8"">Company - </span></td>"
End If
%>

**rfiscus** · Apr 23 '12, 06:09 PM

Error in the query statement in itself.

I believe the real problem is that since the query is not pulling up a good record then it stops processing the page. Is there a way to say if the result set in the sql query produces a good result then process rest of page, if not go to a different page?

Code:

sSQL="SELECT * FROM [database].[dbo].[table1],[database].[dbo].[table2] where GF3_EKKO_EBELN like '%" & purchaseorder & "' and DE_Employee = '" & employee & "'"

Originally posted by rfiscus

I have also tried this but same thing, if the record is valid, it will show the correct data, if the form field is not like a row from the sql table the page comes up blank.

Code:

<%   
If InStr(Recordset.Fields("GF3_EKKO_EBELN"), purchaseorder)<0 Then
Response.Write "<td align=""left""><span class=""style8"">YOUR RESPONSE IS INVALID</span></td>"
Else
Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
%>

**Rabbit** · Apr 23 '12, 07:35 PM

You said you know you're getting a record count of 0. So use that. Check the record count in code. That's what I was saying in my post.

In pseudo code, it would be:

Code:

if record count = 0 then
   show message saying no record
else
   show record
end if

**jhardman** · Apr 24 '12, 05:25 AM

Rabbit is right. Your query is coming back blank so your if statement is meaningless, it would be better to base it off of record count. I like to use the recordset.eof property for that type of thing. If when you open the recordset you get "end of file" (eof) that means no records were returned:

Code:

if rs.eof then
   'No records returned
Else
   'You have at least one good hit
End if

Jared

**rfiscus** · Apr 24 '12, 11:11 AM

Would I add that before or inside the:

Code:

Do while Not recordset.eof

**Rabbit** · Apr 24 '12, 03:33 PM

By necessity it has to be before. If there are no records, the loop isn't going to run.

**rfiscus** · Apr 24 '12, 05:51 PM

Originally posted by Rabbit

By necessity it has to be before. If there are no records, the loop isn't going to run.

So now it errors out with:
Microsoft OLE DB Provider for SQL Server

error '80040e14'

An expression of non-boolean type specified in a context where a condition is expected, near 'else'.

/data_entry/safety/safety_t.asp, line 63

I have looked this up but this should not be doing an update or insert at this point.

The code is as follows:

Code:

sSQL="If (SELECT * FROM [Database].[dbo].[TABLE1],[Database].[dbo].[TABLE2] where GF3_PO_to_NUMBER = '" & purchaseorder & "' and DE_Employee = '" & employee & "') else DE_Employee = '" & employee & "'"

'create an ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.connection") 
Set recordset = Server.CreateObject("ADODB.Recordset")

'define the connection string, specify database 
'driver and the location of database 
sConnString = "Provider=SQLOLEDB; Data Source = server; Initial Catalog = Database; User Id=username;Password=password;" 

'Open the connection to the database
Connection.Open sConnString

'Open the recordset object, executing the SQL
Recordset.Open sSQL, Connection

if recordset.eof then
    Response.Write "No records returned"
Else
    Response.Write "You have at least one good hit"
End if 

'Looping through the records until the end of the records
Do while Not recordset.eof
%>
<form id="form1" name="form1" method="post" action="add_safety.asp">

<table border="0" class="formset">

<tr>
	<td><span class="style8">Employee #:</span></td>

<%
If employee = "" or (IsNull(employee)) Then
Response.Write "<INPUT TYPE=""hidden"" id=""employee"" NAME=""employee"" class=""inputtext"" VALUE="""">"
Else
Response.Write "<INPUT TYPE=""hidden"" id=""employee"" NAME=""employee"" class=""inputtext"" VALUE="& employee &">"
End If
Response.Write "<td align=""left""><span class=""style8"">"& employee &" &nbsp;&nbsp;&nbsp;&nbsp;</span>"
Response.Write "<span class=""style8"">&nbsp;&nbsp;" & recordset("DE_Name_Formatted(Last,_First)") & "</span></td>"
%>
</tr>

<tr>
	<td><span class="style8">Purchase Order #:</span></td>
<%   
If Recordset.Fields("GF3_PO_to_NUMBER").value = "invalid" Then
Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="""">"
Response.Write "<td align=""left""><span class=""error"">INVALID!&nbsp;&nbsp;</span>"
Response.Write "<span class=""style8"">Company - </span></td>"
Else
Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
End If
%>

......

**Rabbit** · Apr 24 '12, 06:00 PM

Why did you change your SQL? No one said to change it.

**rfiscus** · Apr 24 '12, 06:09 PM

Good question, stupid mistake, I pasted from what I was trying on a test site, sorry. It does look like it works with my original sql statement which makes it much easier.

The only real issue is that if any of the records fails it responds with the Else response, is there a way to make it so you know which field failed? For example, whether or not it was the employee number or the purchaseorder number that is invalid? That is why I was trying to construct the SQL with the if statement.

Thanks for all your time.

**Rabbit** · Apr 24 '12, 06:21 PM

If you need to know which one is missing, you need to run two separate queries and check each.

**rfiscus** · Apr 24 '12, 06:24 PM

Sorry, I am not usually this needy but could you give me an example for one of the separate queries, this is literally the last step and I will be done with this site. Thanks.

**Rabbit** · Apr 24 '12, 08:08 PM

Don't really need an example. Do what you're doing now. Except do it twice. Once to check just the PO number. And do the same thing again to check the employee.

ASP/SQL Empty Recordset producing blank page.

ASP/SQL Empty Recordset producing blank page.

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment