*pls help (3 day bug)* If statement not returning TRUE when it should

I've had this bug for days now, please take a look to see if you can help. Thx

I made an html table to store values that VBscript pulls from an Microsoft Access data base. I modified code from w3school to do this. Here's the link: http://www.w3schools.com/ado/ado_display.asp.
All I change are the path, table, and field names. It works fine; It prints out the DB info in a table.

However, when I add a simple if statement (poined out in the code below) to ensure that the only data that gets printed is that which has the same zip code as the zip code that the user entered into the html field, nothing gets printed. The if statement always evaluates FALSE, even though I've checked the 2 sides of the condition statement, and they match up. Here's the code:

<!DOCTYPE asp>
<% Response.Buffer = True %>

<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\wwwroot\RH25.mdb"
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT zip, email, skills FROM members"
rs.Open sql, conn
zip = rs("zip")
zw = request.querystring("zipWanted")
response.write "zip= " & zip & " zw= " & zw
%>
<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td>
<%if zip = zw Then <--here's the if statement
Response.Write (x.value)
end if%>
</td>
<%next
rs.MoveNext%>
</tr>
<%loop
rs.close
conn.close
%>
</table></body>
</html>

Thx again.You are setting zip = rs("zip") only for the first record. In your loop to process all the records, you do not reset zip so it still has the same value as it was originally set to. Should be something like this:

<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields
zip = rs("zip")
%>
<td>
<%if zip = zw Then <--here's the if statement
Response.Write (x.value)
end if%>
</td>
<%next
rs.MoveNext%>
</tr>
<%loop

Actually, this isn't a good way to do what you want anyway. If you are only interested in records where zip = zw then you should put that condition in the WHERE clause of your SELECT.|||I try to do what you suggest, but i get this error:

Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/RH25/listings_where.asp, line 20

Where line 20 is: rs.open sql, conn

Here's lines 1-20 of the code i'm using:

<% Option Explicit %>

<% Response.Buffer = True %>

<!-- #include file="adovbs.inc" -->

<html>
<body>
<%
dim zw
dim conn
dim sql
dim rs
zw = CStr(Request.Querystring("zipwanted"))
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\wwwroot\RH25.mdb"
sql="SELECT * FROM members WHERE zip='" & zw & "'"
set rs = Server.CreateObject("ADODB.recordset")
rs.open sql, conn

Thx again|||What is the data type of column members.zip? If it is a number then you don't want the single quotes around the zw value.

(Revised)Database Design question, Header with two detail.. pls help

Hi All,
There is some additional info I forget on this same topic I just posted.

I have a database design question, pls give me some help..

I want to define tables for salesman's sales target commission . The
commission could be given per EITHER sales amount of : Group of Products OR
Group of Brand. e.g : the data example :
For one salesman_A :
product_1, product_2, product_3 etc.. => sales = $100 - $200 =>
commission = 5%
product_1, product_2, product_3 etc.. => sales = $201 - $400 =>
commission = 10%
Brand_A, Brand_B, Brand_C .. etc => sales = $100 - $200 =>
commission = 2.5%
Brand_A, Brand_B, Brand_C .. etc => sales = $201 - $400 =>
commission = 5%

Below is my table design, is this a good design or something is wrong here ?
Thank you for your help.

CREATE TABLE Sales_Commission_Header (
Sales_ID Char(4) ,
Sales_Commission_Group Char(4),
Note Varchar(30),
Constraint Sales_Commission_Header_PK Primary Key(Sales_ID,
Sales_Commission_Group)
)
Alter Table Sales_Commission_Header Add Constraint
FK_Sales_Commission_Header Foreign Key (Sales_Commission_Group)
References Commission_Group_Header(Sales_Commission_Group)

CREATE TABLE Sales_Commission_Detail (
Sales_ID Char(4) ,
Sales_Commission_Group Char(4),
Sales_From Decimal(12,2) ,
Sales_To Decimal(12,2) ,
Commission Decimal(5,2),
Constraint Sales_Commission_Detail_PK Primary Key(Sales_ID,
Sales_Commission_Group, Sales_From, Sales_To)
)
Alter Table Sales_Commission_Detail Add Constraint FK_Sales_Commission
Foreign Key (Sales_ID, Sales_Commission_Group) References
Sales_Commission_Header(Sales_ID, Sales_Commission_Group)

--------------

** ALTERNATIVE _1 :

CREATE TABLE Commission_Group_Header (
Sales_Commission_Group Char(4) Primary Key,
Note Varchar(30)
)

CREATE TABLE Commission_Group_Detail_Product (
Sales_Commission_Group Char(4),
Product_ID VarChar(10), -- This product_ID will be FK
reference to master product
Constraint Commission_Group_Detail_Product_PK Primary
Key(Sales_Commission_Group, Product_ID)
)
Alter Table Commission_Group_Detail_Product Add Constraint
FK_Commission_Group_Detail_Product Foreign Key (Sales_Commission_Group)
References Commission_Group_Header(Sales_Commission_Group)

CREATE TABLE Commission_Group_Detail_Brand (
Sales_Commission_Group Char(4),
Brand_ID VarChar(10), -- This brand_ID will be FK
reference to master brand
Constraint Commission_Group_Detail_Brand_PK Primary
Key(Sales_Commission_Group, Brand_ID)
)
Alter Table Commission_Group_Detail_Brand Add Constraint
FK_Commission_Group_Detail_Brans Foreign Key (Sales_Commission_Group)
References Commission_Group_Header(Sales_Commission_Group)

** ALTERNATIVE _2 :

CREATE TABLE Commission_Group_Header (
Sales_Commission_Group Char(4),
Group_Type Char(1), -- 'B': Brand Group 'P': Product Group
Note Varchar(30),
Constraint Commission_Group_Header_PK Primary Key(Sales_Commission_Group,
Group_Type)
)

CREATE TABLE Commission_Group_Detail (
Sales_Commission_Group Char(4),
Group_Type Char(1), -- 'B': Brand Group 'P': Product Group
Product_Brand_ID VarChar(10),
Constraint Commission_Group_Detail_PK Primary Key(Sales_Commission_Group,
Group_Type, Product_Brand_ID)
)
Alter Table Commission_Group_Detail Add Constraint
FK_Commission_Group_Detail Foreign Key (Sales_Commission_Group)
References Commission_Group_Header(Sales_Commission_Group, Group_Type)

The PROBLEM here is : with Product_Brand_ID , I CAN NOT make foreign key
into both Master Product and Master Brand.

So which one is better design ?
split the Commission_Group_Detail into Two tables, product and brand , and
make the FOREIGN KEY
to master product and master brand (previous mail)
OR
combile Commission_Group_Detail for Product and Brand into one table like
above
and NOT make any FK to master Product or Brand ?

Thank you for your help,
TristantKrist (xtanto@.hotmail.com) writes:
> I have a database design question, pls give me some help..

For some reason Krist also mailed me about this - please do not both
post and mail! - and I gave him suggestions. I'm leaving this post
here as a bookmark, to mark this question as answered.

Briefly, I first suggested that in his first details table to replace
Sales_From and Sales_To with a nullable column Upperlimit. For the key
I suggested to have RowNo a running number within the Product_ID.
A trigger may be required to ensure that Upperlimit grows with RowNo.
For updates it's best to flush all rows for the product and reload.

For the other question about Product_Brand_ID, I suggested that one
alternative would be to create a supertable with both ids in them.
Another alternative would be two have two columns, and a check
constraint requiring that exactly one of them be non-NULL. This would
call for a surrogate key to be used in the table with the commission
levels.

Since I did not have full information of the meaning of brands and
products, none of these suggestions may make sense.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp