ASP/SQL Empty Recordset producing blank page.

[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.

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>"
%>
 

[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.

 
<%   
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>"
%>
 
 

@rfiscus

[Rabbit]

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

[rfiscus]

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.

 
<%   
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]

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?

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

@rfiscus

[Rabbit]

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:

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

[jhardman]

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:

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

Jared

[rfiscus]

Would I add that before or inside the:

 
Do while Not recordset.eof
 
 

[Rabbit]

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

[rfiscus]

@Rabbit
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:

 
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]

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

[rfiscus]

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]

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

[rfiscus]

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]

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.

[rfiscus]

If I do it twice, it just posts both responses even if just one of the fields is blank. It seems like there would have to be a way to see the difference between the two fields in the select statement, otherwise, doesn't the recordset.eof just check to see if any records exist, not which part of the sql is causing it to produce no records? I thought you were saying to just write the recordset.eof statement twice?

 
if recordset.eof then
    Response.Write "<Table>"
    Response.Write "<tr><td><span class=""style8"">No records returned, please correct the purchase order #.</span><br><br></td></tr>"
    Response.Write "<tr><td><span class=""style8"">Please press the back button and correct.&nbsp;&nbsp;&nbsp;</span>"
    Response.Write "<INPUT TYPE=""button"" VALUE=""Back"" onClick=""history.go(-1);return true;"" class=""form_button_clear""></td></tr>"
    Response.Write "</Table>"
End if
 
if recordset.eof then
    Response.Write "<Table>"
    Response.Write "<tr><td><span class=""style8"">No records returned, please correct your employee number.</span><br><br></td></tr>"
    Response.Write "<tr><td><span class=""style8"">Please press the back button and correct.&nbsp;&nbsp;&nbsp;</span>"
    Response.Write "<INPUT TYPE=""button"" VALUE=""Back"" onClick=""history.go(-1);return true;"" class=""form_button_clear""></td></tr>"
    Response.Write "</Table>"
End if
 
 

[Rabbit]

Just to be clear, the .eof does not check to see if there are no records returned. It only checks to see if the cursor is at the end of the record set. It's just a happenstance that if there are no records, then it will be at the end of the record set.

You don't have to write the result twice, that makes no sense. You just have to do the check twice, once for one field, and then again for the other field. Each time with a different query.

There's no way to know which WHERE clause "causes no records" from a single query. You must first establish that there are no matching records, then check each and every combination of WHERE clauses to find out which combination of clauses cause no records.

For example:

sql = "select * from someTable where field1 = 1 and field2 = 2 and field3 = 3"
recordset = run sql
if recordset count = 0 then
   print "field1 = 1 and field2 = 2 and field3 = 3 causes no records"
 
   sql = "select * from someTable where field1 = 1"
   recordset2 = run sql
   if recordset2 count = 0 then
      print "field1 = 1 does not exist"
   end if
 
   sql = "select * from someTable where field2 = 2"
   recordset2 = run sql
   if recordset2 count = 0 then
      print "field2 = 2 does not exist"
   end if
 
   sql = "select * from someTable where field3 = 3"
   recordset2 = run sql
   if recordset2 count = 0 then
      print "field3 = 3 does not exist"
   end if
 
   sql = "select * from someTable where field1 = 1 and field2 = 2"
   recordset2 = run sql
   if recordset2 count = 0 then
      print "field1 = 1 and field2 = 2 does not exist"
   end if
 
   sql = "select * from someTable where field1 = 1 and field3 = 3"
   recordset2 = run sql
   if recordset2 count = 0 then
      print "field1 = 1 and field3 = 3 does not exist"
   end if
 
   sql = "select * from someTable where field3 = 3 and field2 = 2"
   recordset2 = run sql
   if recordset2 count = 0 then
      print "field3 = 3 and field2 = 2 does not exist"
   end if
end if

In this example, the original 3 clauses have 6 possible combinations, not counting the original.

In your question, you have 2 clauses, so you have 2 possible combinations not counting the original.

[rfiscus]

That explains it very clearly, thank you!