By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,868 Members | 1,991 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,868 IT Pros & Developers. It's quick & easy.

ASP/SQL Empty Recordset producing blank page.

P: 14
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.

Expand|Select|Wrap|Line Numbers
  1. sSQL="SELECT * FROM [database].[dbo].[table1],[database].[dbo].[table2] where GF3_EKKO_EBELN like '%" & purchaseorder & "' and DE_Employee = '" & employee & "'"
  2.  
  3. <%   
  4. If (Recordset.Fields("GF3_EKKO_EBELN") not like purchaseorder Then
  5. Response.Write "<td align=""left""><span class=""style8"">YOUR RESPONSE IS INVALID</span></td>"
  6. Else
  7. Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
  8. Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
  9. Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
  10. %>
  11.  
Apr 23 '12 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. sql = "select * from someTable where field1 = 1 and field2 = 2 and field3 = 3"
  2. recordset = run sql
  3. if recordset count = 0 then
  4.    print "field1 = 1 and field2 = 2 and field3 = 3 causes no records"
  5.  
  6.    sql = "select * from someTable where field1 = 1"
  7.    recordset2 = run sql
  8.    if recordset2 count = 0 then
  9.       print "field1 = 1 does not exist"
  10.    end if
  11.  
  12.    sql = "select * from someTable where field2 = 2"
  13.    recordset2 = run sql
  14.    if recordset2 count = 0 then
  15.       print "field2 = 2 does not exist"
  16.    end if
  17.  
  18.    sql = "select * from someTable where field3 = 3"
  19.    recordset2 = run sql
  20.    if recordset2 count = 0 then
  21.       print "field3 = 3 does not exist"
  22.    end if
  23.  
  24.    sql = "select * from someTable where field1 = 1 and field2 = 2"
  25.    recordset2 = run sql
  26.    if recordset2 count = 0 then
  27.       print "field1 = 1 and field2 = 2 does not exist"
  28.    end if
  29.  
  30.    sql = "select * from someTable where field1 = 1 and field3 = 3"
  31.    recordset2 = run sql
  32.    if recordset2 count = 0 then
  33.       print "field1 = 1 and field3 = 3 does not exist"
  34.    end if
  35.  
  36.    sql = "select * from someTable where field3 = 3 and field2 = 2"
  37.    recordset2 = run sql
  38.    if recordset2 count = 0 then
  39.       print "field3 = 3 and field2 = 2 does not exist"
  40.    end if
  41. 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.

Share this Question
Share on Google+
17 Replies


P: 14
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. <%   
  3. If InStr(Recordset.Fields("GF3_EKKO_EBELN"), purchaseorder)<0 Then
  4. Response.Write "<td align=""left""><span class=""style8"">YOUR RESPONSE IS INVALID</span></td>"
  5. Else
  6. Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
  7. Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
  8. Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
  9. %>
  10.  
  11.  
@rfiscus
Apr 23 '12 #2

Rabbit
Expert Mod 10K+
P: 12,370
Use the RecordCount property to see how many records were returned. If it's 0, then you didn't get a match.
Apr 23 '12 #3

P: 14
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. <%   
  3. If InStr(Recordset.Fields("GF3_EKKO_EBELN"), purchaseorder)>0 Then
  4. Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
  5. Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
  6. Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
  7. Else
  8. Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="""">"
  9. Response.Write "<td align=""left""><span class=""style8"">YOUR RESPONSE IS INVALID</span>"
  10. Response.Write "<span class=""style8"">Company - </span></td>"
  11. End If
  12. %>
  13.  
  14.  
Apr 23 '12 #4

P: 14
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?

Expand|Select|Wrap|Line Numbers
  1. sSQL="SELECT * FROM [database].[dbo].[table1],[database].[dbo].[table2] where GF3_EKKO_EBELN like '%" & purchaseorder & "' and DE_Employee = '" & employee & "'"
  2.  


@rfiscus
Apr 23 '12 #5

Rabbit
Expert Mod 10K+
P: 12,370
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:
Expand|Select|Wrap|Line Numbers
  1. if record count = 0 then
  2.    show message saying no record
  3. else
  4.    show record
  5. end if
Apr 23 '12 #6

jhardman
Expert 2.5K+
P: 3,405
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:
Expand|Select|Wrap|Line Numbers
  1. if rs.eof then
  2.    'No records returned
  3. Else
  4.    'You have at least one good hit
  5. End if 
Jared
Apr 24 '12 #7

P: 14
Would I add that before or inside the:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Do while Not recordset.eof
  3.  
  4.  
Apr 24 '12 #8

Rabbit
Expert Mod 10K+
P: 12,370
By necessity it has to be before. If there are no records, the loop isn't going to run.
Apr 24 '12 #9

P: 14
@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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. sSQL="If (SELECT * FROM [Database].[dbo].[TABLE1],[Database].[dbo].[TABLE2] where GF3_PO_to_NUMBER = '" & purchaseorder & "' and DE_Employee = '" & employee & "') else DE_Employee = '" & employee & "'"
  3.  
  4. 'create an ADO connection and recordset object
  5. Set connection = Server.CreateObject("ADODB.connection") 
  6. Set recordset = Server.CreateObject("ADODB.Recordset")
  7.  
  8. 'define the connection string, specify database 
  9. 'driver and the location of database 
  10. sConnString = "Provider=SQLOLEDB; Data Source = server; Initial Catalog = Database; User Id=username;Password=password;" 
  11.  
  12. 'Open the connection to the database
  13. Connection.Open sConnString
  14.  
  15. 'Open the recordset object, executing the SQL
  16. Recordset.Open sSQL, Connection
  17.  
  18. if recordset.eof then
  19.     Response.Write "No records returned"
  20. Else
  21.     Response.Write "You have at least one good hit"
  22. End if 
  23.  
  24. 'Looping through the records until the end of the records
  25. Do while Not recordset.eof
  26. %>
  27. <form id="form1" name="form1" method="post" action="add_safety.asp">
  28.  
  29. <table border="0" class="formset">
  30.  
  31. <tr>
  32.     <td><span class="style8">Employee #:</span></td>
  33.  
  34. <%
  35. If employee = "" or (IsNull(employee)) Then
  36. Response.Write "<INPUT TYPE=""hidden"" id=""employee"" NAME=""employee"" class=""inputtext"" VALUE="""">"
  37. Else
  38. Response.Write "<INPUT TYPE=""hidden"" id=""employee"" NAME=""employee"" class=""inputtext"" VALUE="& employee &">"
  39. End If
  40. Response.Write "<td align=""left""><span class=""style8"">"& employee &" &nbsp;&nbsp;&nbsp;&nbsp;</span>"
  41. Response.Write "<span class=""style8"">&nbsp;&nbsp;" & recordset("DE_Name_Formatted(Last,_First)") & "</span></td>"
  42. %>
  43. </tr>
  44.  
  45. <tr>
  46.     <td><span class="style8">Purchase Order #:</span></td>
  47. <%   
  48. If Recordset.Fields("GF3_PO_to_NUMBER").value = "invalid" Then
  49. Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="""">"
  50. Response.Write "<td align=""left""><span class=""error"">INVALID!&nbsp;&nbsp;</span>"
  51. Response.Write "<span class=""style8"">Company - </span></td>"
  52. Else
  53. Response.Write "<INPUT TYPE=""hidden"" id=""purchaseorder"" NAME=""purchaseorder"" class=""inputtext"" VALUE="& purchaseorder &">"
  54. Response.Write "<td align=""left""><span class=""style8"">"& purchaseorder &" &nbsp;</span>"
  55. Response.Write "<span class=""style8"">Company - " & recordset("GF3_LFA1_NAME1")& "</span></td>"
  56. End If
  57. %>
  58.  
  59. ......
  60.  
  61.  
Apr 24 '12 #10

Rabbit
Expert Mod 10K+
P: 12,370
Why did you change your SQL? No one said to change it.
Apr 24 '12 #11

P: 14
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.
Apr 24 '12 #12

Rabbit
Expert Mod 10K+
P: 12,370
If you need to know which one is missing, you need to run two separate queries and check each.
Apr 24 '12 #13

P: 14
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.
Apr 24 '12 #14

Rabbit
Expert Mod 10K+
P: 12,370
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.
Apr 24 '12 #15

P: 14
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2. if recordset.eof then
  3.     Response.Write "<Table>"
  4.     Response.Write "<tr><td><span class=""style8"">No records returned, please correct the purchase order #.</span><br><br></td></tr>"
  5.     Response.Write "<tr><td><span class=""style8"">Please press the back button and correct.&nbsp;&nbsp;&nbsp;</span>"
  6.     Response.Write "<INPUT TYPE=""button"" VALUE=""Back"" onClick=""history.go(-1);return true;"" class=""form_button_clear""></td></tr>"
  7.     Response.Write "</Table>"
  8. End if
  9.  
  10. if recordset.eof then
  11.     Response.Write "<Table>"
  12.     Response.Write "<tr><td><span class=""style8"">No records returned, please correct your employee number.</span><br><br></td></tr>"
  13.     Response.Write "<tr><td><span class=""style8"">Please press the back button and correct.&nbsp;&nbsp;&nbsp;</span>"
  14.     Response.Write "<INPUT TYPE=""button"" VALUE=""Back"" onClick=""history.go(-1);return true;"" class=""form_button_clear""></td></tr>"
  15.     Response.Write "</Table>"
  16. End if
  17.  
  18.  
Apr 25 '12 #16

Rabbit
Expert Mod 10K+
P: 12,370
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:
Expand|Select|Wrap|Line Numbers
  1. sql = "select * from someTable where field1 = 1 and field2 = 2 and field3 = 3"
  2. recordset = run sql
  3. if recordset count = 0 then
  4.    print "field1 = 1 and field2 = 2 and field3 = 3 causes no records"
  5.  
  6.    sql = "select * from someTable where field1 = 1"
  7.    recordset2 = run sql
  8.    if recordset2 count = 0 then
  9.       print "field1 = 1 does not exist"
  10.    end if
  11.  
  12.    sql = "select * from someTable where field2 = 2"
  13.    recordset2 = run sql
  14.    if recordset2 count = 0 then
  15.       print "field2 = 2 does not exist"
  16.    end if
  17.  
  18.    sql = "select * from someTable where field3 = 3"
  19.    recordset2 = run sql
  20.    if recordset2 count = 0 then
  21.       print "field3 = 3 does not exist"
  22.    end if
  23.  
  24.    sql = "select * from someTable where field1 = 1 and field2 = 2"
  25.    recordset2 = run sql
  26.    if recordset2 count = 0 then
  27.       print "field1 = 1 and field2 = 2 does not exist"
  28.    end if
  29.  
  30.    sql = "select * from someTable where field1 = 1 and field3 = 3"
  31.    recordset2 = run sql
  32.    if recordset2 count = 0 then
  33.       print "field1 = 1 and field3 = 3 does not exist"
  34.    end if
  35.  
  36.    sql = "select * from someTable where field3 = 3 and field2 = 2"
  37.    recordset2 = run sql
  38.    if recordset2 count = 0 then
  39.       print "field3 = 3 and field2 = 2 does not exist"
  40.    end if
  41. 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.
Apr 25 '12 #17

P: 14
That explains it very clearly, thank you!
Apr 25 '12 #18

Post your reply

Sign in to post your reply or Sign up for a free account.