Hello: <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

I am desperate I posted this on the Macromedia Ultra Dev board about 6 hours ago and no answer.

I have built an update page and in the database is 3 category listings

Main Category
Sub Category 1
Sub Category 2

The member doesn't have to have a sub category 1 or 2. so therefore
sometimes there value is null or blank.

on the update page I have 4 record sets.
1. rsMembers
2. rsMainCategory
3. rsSubCategory1
4. rsSubCategory2

in the update for the field Main Cagetory the user selects from a drop down
list of the rsMainCategory with the default being the category in the record
already in the Database (for example if the record already has Hotel in the
record that is what shows up however the list select give the option of
changing the main category) This works so far.

However, I have the same thing going for the SubCategory 1 and 2 only in
the database many of the member records do not have a Sub Category 1 or 2
therefore their values are null. When I try to do the default value being
from rsMembers SubCategory and there is no value I get the following error:

Microsoft VBScript runtime error '800a005e' Invalid use of Null: 'Cstr'

Is there a way in the default value to not show error if there is no record?
Or do I have to go back to the database and place some sort of value for
those that have no record.

I will be using this database for showing searches on the website and will
want to be able to search based on sub categories.

Sorry for such a long explanation but felt this way someone would be able to
figure it out.

Thanks


Ms. Barbara S. ONeal

I'm a bit confused as to exactly what you're are trying to achieve.

Why do you have 4 recordsets on the update page? Why not one recordset based on a query?
Are the Main Category, Sub1 and Sub2 all in the same table or separate tables?

If Main, Sub1, Sub2 are in one table and asuming that the categories are being held as text, you should be able to set up a recordset based on an SQL statement something like:

Collect the input from the search form:

varMainCategory = Replace(Request("selectMaincategory","'","''"
varSubCategory1 = Replace(Request("selectMaincategory","'","''"
varSubcategory2 = Replace(Request("selectMaincategory","'","''"

Then construct your SELECT statement:

RecordsetName.Source = "SELECT * FROM YourTableName WHERE MainCategory LIKE '" & varMainCategory & "' AND SubCategory1 LIKE "' & varSubCategory1 & "' AND SubCategory2 LIKE '" & varSubcategory2 &"'"

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Owen:

Thanks for responding. Let me try to explain further. First off there is one Final Members Table with relations to the other tables MainCat, SubCat1 and SubCat2. In the Final Members Table these are drop down menus to select the catagories.

All of the members have a Main Category, however only some of the members have a SubCat1 and even fewer have a SubCat2. Which leaves some of the fields blank.
here is the code that is giving me the problem.


&lt;select name="Category1"&gt;
&lt;%
While (NOT rsSub1.EOF)
%&gt;
&lt;option value="&lt;%=(rsSub1.Fields.Item("sub_cat_1".Value)%&gt;"
&lt;%if (CStr(rsSub1.Fields.Item("sub_cat_1".Value) =
CStr(rsMembers.Fields.Item("sub_cat_1".Value)) then
Response.Write("SELECTED" :
Response.Write(""%&gt;&gt;&lt;%=(rsSub1.Fields.Item("sub_cat_1".Value)%&gt;&lt;/option&gt;
&lt;%
rsSub1.MoveNext()
Wend
If (rsSub1.CursorType &gt; 0) Then
rsSub1.MoveFirst
Else
rsSub1.Requery
End If
%&gt;
&lt;/select&gt;




Ms. Barbara S. ONeal

Let me see if I've got this right.

You have a dynamic list menu on a page that is poulated from a recordset "rsSub1" and you want to have the value that exists in the table for a particular member pre selected. However sometimes this field is empty.

I think the set up of your database may be a bit unusual.


Dynamic List/Menus should be populated from full database tables e.g.

MianCategory Table: CategoryID(Numeric), MainCategory(TextDescription)

SubCategory1 Table: SubCategory1ID(Numeric), SubCategory1(TextDescription)

SubCategory2 Table: SubCategory2ID(Numeric), SubCategory2(TextDescription)


Then the Members Table:

MemberID(numeric), MemberName(text), MemeberInfo(text), MoreMemberInfo(text) etc..... MemberMainCategoryID(numeric), MemberSubCategory1ID(numeric), MemberSubCategory2ID(numeric)


So in the Member table you only store the Main, Sub1 and Sub2 CategoryID's, the category tables contain all the possible categories all the time. Then when you want to display information about the member categories you do this by means of a query usng the relationship between the Members table and the three category tables where by Member.MainCategoryID = MainCategory.CategoryID etc.


Pre selecting your List/Menus then becomes a breeze, something like:

&lt;select name="selectMainCategory"&gt;
&lt;%
While (NOT rsMainCategory.EOF)
%&gt;
&lt;option value="&lt;%=(rsMainCategory.Fields.Item("MainCategory".Value)%&gt;"
&lt;%if (CStr(rsMainCategory.Fields.Item("MainCategoryID".Value) =
CStr(rsMembers.Fields.Item("MainCategoryID".Value)) then
Response.Write("SELECTED" :
Response.Write(""%&gt;&gt;&lt;%=(rsMainCategory.Fields.Item("rsMainCategoryID".Value)%&gt;&lt;/option&gt;
&lt;%
rsMainCategory.MoveNext()
Wend
If (rsMainCategory.CursorType &gt; 0) Then
rsMainCategory.MoveFirst
Else
rsMainCategory.Requery
End If
%&gt;
&lt;/select&gt;

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Hi,
I used this one time I had this problem to.
Just put it somewhere under your RS ASP code.

&lt;%
' //// EXTRA CODE Correct if dbase values are blank
YourVariableName = YourRecordSetName.Fields.Item("YourVariableName ".Value
if len(YourVariableName )=0 then YourVariableName = "YourAlternativeVariableValue"
%&gt;

Change values as you need <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> E.g. you could use a space as AlterniveValue.

Hope this helps...
e_site