Link to home
Start Free TrialLog in
Avatar of ram0135
ram0135

asked on

Don't Show Blank Records...

Hello,
I have some recordsets on various pages which pull of data of some tables. Since I have a primary key for deletion purposes, this creates some blank fields under some tables with no data, only a key for the one with the data. Is there a way to skip any blank fields in a recordset and simply not display them? I know there is EOF but thats only if there are no records totally.

Thanks,
Ram0135
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image
Try apply your condition in your SQL statement, like:

Select  from mytable Where mycheckfield <> "" and mycheckfield is Not Null
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image
missing a * for above example:

Select * from mytable Where mycheckfield <> "" and mycheckfield is Not Null
Avatar of ram0135
ram0135

ASKER

Hello,
Thanks for responding. Sorry for not being specific. The above cant work. I have 6 recordsets on one page, each one loading one table. At the beginning I do this: Select * from TheTable

Then in the other parts of the document I simply call up the recorset in this manner: <%=(rsMINE.Fields.Item("Table1").Value)%>

Is there a way to check the if null  at this level (fields.item)?

Ram0135
Avatar of ajitanand
ajitanand
You may either filter out the nulls as hinted above through an SQL Query (Recommended) like:

select * from mytable where mycheckfield <> "" and mycheckfield is Not Null


OR

you may have a filter in your ASP Code so that when you display items, you can skip the null rows processing as :
----------------
while not rs.eof
   If Not IsNull(rs("mycheckfield")) Then
      Response.write rs("mycheckfield")
   End If
   rs.movenext
Wend
rs.close
----------------


The above one is recommended, since it will put less network traffic, and null rows will not even be returned from your database to you ASP Engine.

rgds,
Ajit Anand
Avatar of ram0135
ram0135

ASKER

hello,
OK I tried the code above and ASP says that the field is either EOF or BOF and/or the record has been deleted. The code itself does supress EOF erros but with the addition of that code it doesnt. I then again tried the
Select From Test Where Test_5 <> "" and Test_5 is Not Null
BUT I get an HTTP 500 error that says that there was an Internal Error and I get no error messages from ASP.

Thanks,
Ram0135
Avatar of ajitanand
ajitanand
please paste your entire script here.
Avatar of ram0135
ram0135

ASKER

Here is the code:
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../../Connections/default.asp" -->
<%
Dim rsHW
Dim rsHW_numRows

Set rsHW = Server.CreateObject("ADODB.Recordset")
rsHW.ActiveConnection = MM_CAAHWK_STRING
rsHW.Source = "SELECT *  FROM Test Where P2 <> "" and P2 is Not Null"
rsHW.CursorType = 0
rsHW.CursorLocation = 2
rsHW.LockType = 3
rsHW.Open()

rsHW_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsHW_numRows = rsHW_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Delete Records</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--
function MM_findObj(n, d) { //v3.0
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document); return x;
}

function MM_validateForm() { //v3.0
  var i,p,q,nm,test,num,min,max,errors='',args=MM_validateForm.arguments;
  for (i=0; i<(args.length-2); i+=3) { test=args[i+2]; val=MM_findObj(args[i]);
    if (val) { nm=val.name; if ((val=val.value)!="") {
      if (test.indexOf('isEmail')!=-1) { p=val.indexOf('@');
        if (p<1 || p==(val.length-1)) errors+='- '+nm+' must contain an e-mail address.\n';
      } else if (test!='R') { num = parseFloat(val);
        if (val!=''+num) errors+='- '+nm+' must contain a number.\n';
        if (test.indexOf('inRange') != -1) { p=test.indexOf(':');
          min=test.substring(8,p); max=test.substring(p+1);
          if (num<min || max<num) errors+='- '+nm+' must contain a number between '+min+' and '+max+'.\n';
    } } } else if (test.charAt(0) == 'R') errors += '- '+nm+' is required.\n'; }
  } if (errors) alert('The following error(s) occurred:\n'+errors);
  document.MM_returnValue = (errors == '');
}
//-->
</script>
<LINK REL=stylesheet TYPE="text/css" HREF="../../style.css">
</head>
<body bgcolor="#FFFFFF">
<p align="center">Delete Records </p>
<form name="p2" method="get" action="del2.asp?checkbox=<%=(rsHW.Fields.Item("ID").Value)%>">
  <p align="center">Welcome to the delete records page. This page is designed to allow the deletion of records that are no longer needed or have become obsolete. Please keep in mind some guidelines:</p>
  <div align="center">
    <ul>
      <li>
        <div align="center">Select the records that you wish to delete and click on the button below the form to delete the record.</div>
      </li>
        <li>
          <div align="center">By clicking on the delete button the records are deleted with no prior confirmation. </div>
        </li>
        <li>
          <div align="center">Please <strong>DO NOT </strong>delete any records which don&rsquo;t have a Record Value, they are placeholders for another record.</div>
        </li>
    </ul>
  </div>
  <div align="center">
    <p><font size="5">P2</font></p>
  </div>
  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0">
    <tr>
      <td width="20">&nbsp;</td>
      <td width="63" valign="top">
      <div align="center"><b><font size="2" face="Arial, Helvetica, sans-serif">Record ID</font></b></div></td>
      <td width="677">        <p align="center"><font face="Arial, Helvetica, sans-serif"><b><font size="2">Record Value</font></b></font></p></td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT rsHW.EOF))
%>
    <tr>
      <td width="20" valign="top">
        <input type="checkbox" name="checkbox" value="<%=(rsHW.Fields.Item("ID").Value)%>">
      </td>
      <td width="63" valign="top"><div align="center"><font face="Arial, Helvetica, sans-serif" size="2"><%=(rsHW.Fields.Item("ID").Value)%></font></div></td>
      <td width="677">
        <p align="center"><font face="Arial, Helvetica, sans-serif" size="2"><%=(rsHW.Fields.Item("P2").Value)%></font></p>
      </td>
    </tr>
    <%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsHW.MoveNext()
Wend
%>
    <tr>
      <td width="20">&nbsp;
      </td>
      <td width="63">
        <div align="left">        </div></td>
      <td width="677"><div align="center">
      </div></td>
    </tr>
  </table>
</form>
<p align="center">
  <input type="submit" name="Delete" value="Delete Records">
</p>
<p align="center">-----------------------------</p>
<p align="center">P4</p>
<form name="period4" method="get" action="del2.asp?checkbox=<%=(rsHW.Fields.Item("ID").Value)%>">
  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0">
    <tr>
      <td width="20">&nbsp;</td>
      <td width="63" valign="top"><div align="center"><b><font size="2" face="Arial, Helvetica, sans-serif">Record ID</font></b></div></td>
      <td width="677"><p align="center"><font face="Arial, Helvetica, sans-serif"><b><font size="2">Record Value</font></b></font></p></td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT rsHW.EOF))
%>
    <tr>
      <td width="20" valign="top"><input type="checkbox" name="checkbox" value="<%=(rsHW.Fields.Item("ID").Value)%>">
      </td>
      <td width="63" valign="top"><div align="center"><font face="Arial, Helvetica, sans-serif" size="2"><%=(rsHW.Fields.Item("ID").Value)%></font></div></td>
      <td width="677"><p align="center"><font face="Arial, Helvetica, sans-serif" size="2"><%=(rsHW.Fields.Item("P4").Value)%></font></p></td>
    </tr>
    <%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsHW.MoveNext()
Wend
%>
    <tr>
      <td width="20">&nbsp;</td>
      <td width="63"><div align="left"> </div></td>
      <td width="677"><div align="center"> </div></td>
    </tr>
  </table>
</form>
<p align="center">
  <input type="submit" name="Delete" value="Delete Records">
</p>
<p align="center">&nbsp;</p>
<p align="center">--------------------------</p>
<p align="center">P6</p>
<form name="p6" method="get" action="del2.asp?checkbox=<%=(rsHW.Fields.Item("ID").Value)%>">
  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0">
    <tr>
      <td width="20">&nbsp;</td>
      <td width="63" valign="top"><div align="center"><b><font size="2" face="Arial, Helvetica, sans-serif">Record ID</font></b></div></td>
      <td width="677"><p align="center"><font face="Arial, Helvetica, sans-serif"><b><font size="2">Record Value</font></b></font></p></td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT rsHW.EOF))
%>
    <tr>
      <td width="20" valign="top"><input type="checkbox" name="checkbox" value="<%=(rsHW.Fields.Item("ID").Value)%>">
      </td>
      <td width="63" valign="top"><div align="center"><font face="Arial, Helvetica, sans-serif" size="2"><%=(rsHW.Fields.Item("ID").Value)%></font></div></td>
      <td width="677"><p align="center"><font face="Arial, Helvetica, sans-serif" size="2"><%=(rsHW.Fields.Item("P6").Value)%></font></p></td>
    </tr>
    <%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsHW.MoveNext()
Wend
%>
    <tr>
      <td width="20">&nbsp;</td>
      <td width="63"><div align="left"> </div></td>
      <td width="677"><div align="center"> </div></td>
    </tr>
  </table>
</form>
<p align="center">
  <input type="submit" name="Delete" value="Delete Records">
</p>
<p align="center">&nbsp; </p>
</body>
</html>
<%
rsHW.Close()
Set rsHW = Nothing
%>

Using this code I get the HTTP 500 error, removing the Null part gives me a functioning page BUT it also gives me a field error. Any help is appreciated!

Ram0135
Avatar of ajitanand
ajitanand

First thing, The query should be like:
rsHW.Source = "SELECT *  FROM Test Where P2 <> '' and P2 is Not Null"
Please note that you need to pass on 2 single quotes  (apostrophes) to sql, instead of quotes!
(this will correct the 500 Error)

Another logical problem in your script:

The following loop is given multiple times in your code. Why? This is wrong as once you have looped through your recordset in a [while not rs.eof], it will never come into the second loop, as the rs.eof has already been reached.

While ((Repeat1__numRows <> 0) AND (NOT rsHW.EOF))

There is some definite logical problem in your code. Let me know in detail what you are doing here so that i can help you correct it.

rgds,
Ajit Anand
Avatar of ram0135
ram0135

ASKER

Thanks for responding. Here is what I am trying to do with this page. This page is a page to delete records from a page via checkboxes. There are 3 columns in each table P2, P4, & P6 in the table Test. I have a table which gets the ID and the value of the the field and then displays it (hence I did not want blank records to show). I also want P4 and P6 on the same page so that is why it is repeated.

Thanks,
Ram0135
ASKER CERTIFIED SOLUTION
Avatar of ajitanand
ajitanand
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ram0135
ram0135

ASKER

Hello,
Thanks for responding once again. At first the delete button didnt do anything. This was because the delete page did not have the original URL values that need to be passed: checkbox=... anyway so I added those in then I now get this error:
ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

del.asp, line 127

line 127: <form name="period_4" method="get" action="del2.asp?checkbox=<%=(rsHW.Fields.Item("ID").Value)%>">

Thanks,
Ram0135
Avatar of ajitanand
ajitanand
hello

Checkbox values are not suppoed to be passed like this - "del2.asp?checkbox=<%=(rsHW.Fields.Item("ID").Value)%>"

Only the URL needs to be the action of the form like - action="del2.asp"

This is because, when someone checks in any of the checkboxes, the "value" parameter of the checkbox thus selected is sent as the form data upon submission.

So if you have checkboxes like:

<form action="del2.asp" method=GET>
<input type=checkbox name="checkbox" value="1"> [this is checked by the user]
<input type=checkbox name="checkbox" value="2">
<input type=checkbox name="checkbox" value="3"> [this is checked by the user]
<input type=checkbox name="checkbox" value="4"> [this is checked by the user]
<input type=checkbox name="checkbox" value="5">
<input type=submit>
</form>

then on del2.asp the following form data is submitted as : checkbox=1,3,4

If you specified form method=GET, then this form data will be automatically sent by the browser to the url del2.asp as: del2.asp?checkbox=1,3,4
[This data can be collected at del2.asp script as 'SelectedBoxes = request.querystring("checkbox")' which will have the value as 1,3,4 . You need
to split these comma seperated values to an array in your del2.asp, to get individual selected checkbox values.]


If you specified form method=POST, then this form data will be automatically sent by the browser to the url del2.asp as: checkbox=1,3,4 as submitted headers.
[This data can be collected at del2.asp script as 'SelectedBoxes = request.Form("checkbox")' which will have the value as 1,3,4 . You need
to split these comma seperated values to an array in your del2.asp, to get individual selected checkbox values.]

So you SHOULD not set the <del2.asp?checkbox=....> manually. Just specify the action of the form, and the browser automatically appends the selected checkboxes values with the URL.

---------------------------------------------------
rgds,
Ajit Anand
Avatar of ram0135
ram0135

ASKER

Hello,
Thanks Ajit for all of your help. I found out that the page was not working because the Delete button was outside of the form so it was not sending anything! ;-P.

Thanks,
Ram0135