• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Check value of model number then do something based on that

I have to list of model numbers with about 10-15 different models each and if they are in list A then i need Label #1 printed and if in list B then I need Lable #2 printed.
Model Numbers for ETL
6100-LP
6100-MC
6100-PL
6100-PR
6100-SG
7100-LP
7100-MC
7100-PL
7100-PR
7100-SG
7102-LP
7102-MC
7102-PL
7102-PR
7102-SG
8100-LR
8100-MC
8100-PL
8100-PR
8100-SG
8102-LP
8102-MC
8102-PL
8102-PR
8102-SG
10100-LP
10100-MC
10100-PL
10100-PR
10100-SG
10102-LP
10102-MC
10102-PL
10102-PR
10102-SG

Model Numbers for CE
6200-LP
6200-MC
6200-PL
6200-PR
6200-SG
7200-LP
7200-MC
7200-PL
7200-PR
7200-SG
7202-LP
7202-MC
7202-PL
7202-PR
7202-SG
8200-LR
8200-MC
8200-PL
8200-PR
8200-SG
8202-LP
8202-MC
8202-PL
8202-PR
8202-SG
10200-LP
10200-MC
10200-PL
10200-PR
10200-SG
10202-LP
10202-MC
10202-PL
10202-PR
10202-SG

Plus I need it to still check to see if record exist in the label table, and if the value is > 0.
This is what I have right now but it does not look at the model numbers:
<%
     strSQL2 = "SELECT * FROM labels WHERE sf_ord = " & rs("ord_no")
     set rs2 = Conn.Execute(strSQL2)
if rs2.BOF AND rs2.EOF then%><b><font size="4">
<a target="_blank" href="labelprint.asp?ordno=<%= rs("ord_no") %>">Print First</a>

<%Elseif  rs2("zn_4") = 0 then %></font></b> <b>
<font size="4">
<a target="_blank" href="labelprint.asp?ordno=<%= rs("ord_no") %>">Print First</a>
</font><font size="2">

<%Elseif  rs2("zn_4") > 0 then %></font></b> <b>
<a target="_blank" href="labelprint.asp?ordno=<%= rs("ord_no") %>"><font size="1">Completed</font></a><font size="1">

<%End If%>
0
kwitcom
Asked:
kwitcom
  • 16
  • 14
5 Solutions
 
WMIFCommented:
where are these list a & b stored?  in a seperate table?
0
 
kwitcomAuthor Commented:
No, But i can create one... Right now i Have them in excel.
0
 
WMIFCommented:
it would be best to place those lists in a table in your db.  otherwise you would need to build an array or access the flat file every time.  that would be very slow.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kwitcomAuthor Commented:
Ok I found that in the description some say Export and some say export w/******.  How do i do a search for wildcards?
SELECT     *, item_desc_2 AS Expr1
FROM         imitmidx_sql
WHERE     (item_desc_2 = 'Export')      <----- Need to have a Wildcard after the word export.
ORDER BY item_desc_2
0
 
WMIFCommented:
SELECT     *, item_desc_2 AS Expr1
FROM         imitmidx_sql
WHERE     (item_desc_2 like 'Export%')      <----- Need to have a Wildcard after the word export.
ORDER BY item_desc_2

that should work.
0
 
kwitcomAuthor Commented:
Now that I have the filer, How should I write this I want it is to do this:
1. check to see if record is in Labels
2. If exist see if zn_4 is >=0
3. then want it to see if it is export or not.
   - IF in list do one thing and if not do another.
In short i have 2 different labels I need to print based on the value and the zn_4 is a warning if the label has printed before.

Now I have this in my ASP:
<%
'DECLARATIONS
dim dia, strID, strSQL, Conn, rs, strSQL2, strSQL3, strSQL4, strSQL5, rs2, rs3, rs4, rs5, ordno
dia = Date()
      strSQL = "SELECT * FROM sfordfil_sql WHERE compl_dt='"& dia &"' ORDER BY ord_no DESC"
      set rs = Conn.Execute(strSQL)
    strSQL2 = "SELECT * FROM labels WHERE sf_ord = " & rs("ord_no")
    set rs2 = Conn.Execute(strSQL2)
      strSQL3 = "SELECT * FROM item_no WHERE item_desc_2 item_desc_2 LIKE 'export%'"
      set rs3 = Conn.Execute(strSQL)
%>
<% While Not rs.EOF %>
     <tr>
          <td width="109" align="center"><%= rs("ord_no") %></td>
          <td width="130" align="center"><%= rs("item_no") %></td>
          <td width="208" align="center"><%= rs("item_desc_1") %></td>
          <td width="128" align="center"><%= rs("compl_dt") %></td>
            <td width="136" align="center">
<%if rs2.BOF AND rs2.EOF then%>
<b><font size="4">
<a target="_blank" href="labelprint.asp?ordno=<%= rs("ord_no") %>">Print First</a>

<%Elseif  rs2("zn_4") = 0 then %></font></b> <b>
<font size="4">
<a target="_blank" href="labelprint.asp?ordno=<%= rs("ord_no") %>">Print First</a>
</font><font size="2">

<%Elseif  rs2("zn_4") > 0 then %></font></b> <b>
<a target="_blank" href="labelprint.asp?ordno=<%= rs("ord_no") %>"><font size="1">Completed</font></a><font size="1">

<%End If%>
0
 
WMIFCommented:
i would do it all in one recordset and use subqueries.

query = "SELECT *, " & _
            "(SELECT 'true' FROM labels WHERE sf_ord = " & rs("ord_no") & ") as inlabels, " & _
            "(SELECT 'true' FROM item_no WHERE item_desc_2 LIKE 'export%') as inexport " & _
            "FROM sfordfil_sql " & _
            "WHERE compl_dt='"& dia &"' " & _
            "ORDER BY ord_no DESC"


then use your if statements to check for the true value in those columns.
0
 
kwitcomAuthor Commented:
So my code would look like this?

query = "SELECT *, " & _
        "(SELECT 'true' FROM labels WHERE sf_ord = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT 'true' FROM item_no WHERE item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql " & _
        "WHERE compl_dt='"& dia &"' " & _
        "ORDER BY ord_no DESC"

if query.BOF AND query.EOF and export = trure then %>
Test 1 Export
<%Else if query.BOF AND query.EOF then %>
Test 1 USA
<%Elseif  rs1("zn_4") = 0 and export = trure then %>
Test 2 Export
<%Elseif  rs1("zn_4") > 0 and export = trure then %>
Test 3 Export
<%Elseif  rs1("zn_4") = 0 then %>
Test 2 USA
<%Elseif  rs1("zn_4") > 0 then %>
Test 3 USA
<%end if%>
0
 
kwitcomAuthor Commented:
Ok don't look at the last comment... .I might have found it... but i get page can't be displayed:  (Any Ideas)

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Session("permission")="YES"
Session("username")="operator1"
%>
<html>
<head>
<meta http-equiv="refresh" content="120">
</head>
<body>
<%
'CONNECTION
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=xxxxx; Password=xxxxx"
Set ObjRs = Server.CreateObject("ADODB.Recordset")

'DECLARATIONS
dim dia, strID, strSQL, Conn, rs, strSQL2, strSQL3, strSQL4, strSQL5, rs2, rs3, rs4, rs5, ordno
dia = Date()
strSQL = "SELECT * FROM sfordfil_sql WHERE compl_dt is NULL ORDER BY ord_no DESC"
set rs = Conn.Execute(strSQL)

strSQL3 = "SELECT *, " & _
        "(SELECT 'true' FROM labels WHERE sf_ord = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT 'true' FROM item_no WHERE item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql " & _
        "ORDER BY ord_no DESC"
        set rs3 = Conn.Execute(strSQL3)
Response.Write(query)
%>
<div align="center">
<table border="1" width="745" id="table1" height="63">
     <tr>
          <td height="31" width="109" align="center">Shop Order #</td>
          <td height="31" width="130" align="center">Short Model #</td>
          <td height="31" width="208" align="center">Item Description</td>
          <td height="31" width="128" align="center">Start Date</td>
          <td height="31" width="136" align="center">Print</td>
     </tr>
<% While Not rs3.EOF %>
     <tr>
          <td width="109" align="center"><%= rs("ord_no") %></td>
          <td width="130" align="center"><%= rs("item_no") %></td>
          <td width="208" align="center"><%= rs("item_desc_1") %></td>
          <td width="128" align="center"><%= rs("start_dt") %></td>
            <td width="136" align="center">
<%if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") > 0 then %>
Test 1 Export
<%Else if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") = 0 then %>
Test 2 Export
<%Else if rs3("inexport") = true then %>
Test 3 Export
<%Elseif rs3("inlabels") = true and rs1("zn_4") > 0 then %>
Test 1 USA
<%Elseif rs3("inlabels") = true and rs1("zn_4") = 0 then %>
Test 2 USA
<%Elseif then %>
Test 3 USA
<%End If%>
</font></b>
<font size="1">
</font></font></td>
</tr>
<% rs.MoveNext %>
<% Wend %>
</table>
</div>
<%
'CLOSE
conn.close
set conn = nothing
%>
</body>
</html>
0
 
WMIFCommented:
uncheck your friendly http errors so we can get details about the error.  go to tools -> internet options... -> advanced tab
0
 
kwitcomAuthor Commented:
Ok I turned off friendly errors and have been working with the code a little and this this the New error:
Microsoft OLE DB Provider for SQL Server error '80004005'
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
/finial/1.asp, line 28

Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%Session("permission")="YES"
Session("username")="operator1"%>
<html>
<head>
<meta http-equiv="refresh" content="120">
</head>
<body>
<%
'CONNECTION
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=xxxxxx; Password=xxxxxx"
Set ObjRs = Server.CreateObject("ADODB.Recordset")

'DECLARATIONS
dim dia, strID, strSQL, Conn, rs, strSQL2, strSQL3, strSQL4, strSQL5, rs2, rs3, rs4, rs5, ordno
dia = Date()
strSQL = "SELECT * FROM sfordfil_sql WHERE compl_dt is NULL ORDER BY ord_no DESC"
set rs = Conn.Execute(strSQL)

strSQL3 = "SELECT *, " & _
        "(SELECT 'true' FROM labels WHERE sf_ord = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT 'true' FROM sfordfil_sql WHERE item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql " & _
        "ORDER BY ord_no DESC"
        set rs3 = Conn.Execute(strSQL3)                        <--------- This is where the error is coming from
%>
<div align="center">
<table border="1" width="745" id="table1" height="63">
     <tr>
          <td height="31" width="109" align="center">Shop Order #</td>
          <td height="31" width="130" align="center">Short Model #</td>
          <td height="31" width="208" align="center">Item Description</td>
          <td height="31" width="128" align="center">Start Date</td>
          <td height="31" width="136" align="center">Print</td>
     </tr>
<% While Not rs3.EOF %>
     <tr>
          <td width="109" align="center"><%= rs("ord_no") %></td>
          <td width="130" align="center"><%= rs("item_no") %></td>
          <td width="208" align="center"><%= rs("item_desc_1") %></td>
          <td width="128" align="center"><%= rs("start_dt") %></td>
          <td width="136" align="center">
<%if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") > 0 then %>
Test 1 Export
<%Else if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") = 0 then %>
Test 2 Export
<%Else if rs3("inexport") = true then %>
Test 3 Export
<%Elseif rs3("inlabels") = true and rs1("zn_4") > 0 then %>
Test 1 USA
<%Elseif rs3("inlabels") = true and rs1("zn_4") = 0 then %>
Test 2 USA
<%Else%>
Test 3 USA
<%End If%>
</font></b>
<font size="1">
</font></font></td>
</tr>
<% rs.MoveNext %>
<%end if%>
<%end if%>
<% Wend %>
</table>
</div>
<%
'CLOSE
conn.close
set conn = nothing
%>
</body>
</html>
0
 
WMIFCommented:
gotcha.  lets do this then and restrict the subqueries to one row.

strSQL3 = "SELECT *, " & _
        "(SELECT top 1 'true' FROM labels WHERE sf_ord = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT top 1 'true' FROM sfordfil_sql WHERE item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql " & _
        "ORDER BY ord_no DESC"
        set rs3 = Conn.Execute(strSQL3)                        <--------- This is where the error is coming from
0
 
kwitcomAuthor Commented:
Moving on up.... looks like we got by that one now... here is the next one....
error '80020009'
/finial/1.asp, line 48

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Session("permission")="YES"
Session("username")="operator1"
%>
<html>
<head>
<meta http-equiv="refresh" content="120">
</head>
<body>
<%
'CONNECTION
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=xxxxxx; Password=xxxxxx"
Set ObjRs = Server.CreateObject("ADODB.Recordset")
'DECLARATIONS
dim dia, strID, strSQL, Conn, rs, strSQL2, strSQL3, strSQL4, strSQL5, rs2, rs3, rs4, rs5, ordno
dia = Date()
strSQL = "SELECT * FROM sfordfil_sql WHERE compl_dt is NULL ORDER BY ord_no DESC"
set rs = Conn.Execute(strSQL)
strSQL3 = "SELECT *, " & _
        "(SELECT top 1 'true' FROM labels WHERE sf_ord = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT top 1 'true' FROM sfordfil_sql WHERE item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql " & _
        "ORDER BY ord_no DESC"
        set rs3 = Conn.Execute(strSQL3)
strSQL1 = "SELECT * FROM labels WHERE sf_ord = " & rs3("ord_no")
set rs1 = Conn.Execute(strSQL1)
%>
<div align="center">
<table border="1" width="745" id="table1" height="63">
     <tr>
          <td height="31" width="109" align="center">Shop Order #</td>
          <td height="31" width="130" align="center">Short Model #</td>
          <td height="31" width="208" align="center">Item Description</td>
          <td height="31" width="128" align="center">Start Date</td>
          <td height="31" width="136" align="center">Print</td>
     </tr>
<% While Not rs3.EOF %>
     <tr>
          <td width="109" align="center"><%= rs("ord_no") %></td>
          <td width="130" align="center"><%= rs("item_no") %></td>
          <td width="208" align="center"><%= rs("item_desc_1") %></td>
          <td width="128" align="center"><%= rs("start_dt") %></td>
          <td width="136" align="center">
<%if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") > 0 then %>                                        <--------- This line
Test 1 Export
<%Else if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") = 0 then %>
Test 2 Export
<%Else if rs3("inexport") = true then %>
Test 3 Export
<%Elseif rs3("inlabels") = true and rs1("zn_4") > 0 then %>
Test 1 USA
<%Elseif rs3("inlabels") = true and rs1("zn_4") = 0 then %>
Test 2 USA
<%Else%>
Test 3 USA
<%End If%>
</font></b>
<font size="1">
</font></font></td>
</tr>
<% rs.MoveNext %>
<%end if%>
<%end if%>
<% Wend %>
</table>
</div>
<%
'CLOSE
conn.close
set conn = nothing
%>
</body>
</html>
0
 
WMIFCommented:
you might be getting eof on the rs1
0
 
kwitcomAuthor Commented:
Ok how do i get around that?
0
 
WMIFCommented:
why do you even need that recordset?  you are checking to see if its in the labels table already.
0
 
kwitcomAuthor Commented:
Here is what I am trying to do... I have a table called Labels and items will be added to this table normaly when an item is started in Zone 1.  Each time it goes through a zone it checks to see if it is in the table and updates when a label is printed for each zone.  So there may be a way around not needing to check for end of record for  zone 4 normaly because when this system is in place it will be created in zone 1,2,or 3.  What would you recommend that I do?  And thank you for your help.

(other information: the label table also will keep a copy of other data, i.e. model, sewr_lot, complete date.)
0
 
WMIFCommented:
now im even more confused.  i just saw that you have another recordset on this page to grab the order number list in descending order.  you are using the latest order number to query rs1, but not rs3.
0
 
kwitcomAuthor Commented:
Ok, sorry for the delay... Been in meetings all day, I have changed the code and it is kind of working now.... It is showing records and gets an error at the bottom of the page, and skips does not find the exports.

This is a sample of the output with the error:
Shop Order # Short Model # Item Description Start Date Print
3630 10202-LP  Spa, w/Lounge 8' - Lapis  4/28/2006 Test 3 USA  
3568 6100-SG  Spa, w/Lounge 6' - Sage  4/28/2006 Test 3 USA  
25 7100-PR  Spa, w/Lounge 7' - Pearl  4/28/2006 Test 3 USA  
24 7100-PR  Spa, w/Lounge 7' - Pearl  4/28/2006 Test 3 USA  
ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/finial/1.asp, line 0
 
Only runs the <%Else%> line. Do I need to change how my ElseIF lines are writen?
Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%Session("permission")="YES"
Session("username")="operator1"%>
<html><head><meta http-equiv="refresh" content="120">
</head><body>
<%
'CONNECTION
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=xxxxx; Password=xxxxxx"
Set ObjRs = Server.CreateObject("ADODB.Recordset")

'DECLARATIONS
dim dia, strID, strSQL, Conn, rs, strSQL2, strSQL3, strSQL4, strSQL5, rs2, rs3, rs4, rs5, ordno
dia = Date()
strSQL = "SELECT * FROM sfordfil_sql WHERE compl_dt is NULL ORDER BY ord_no DESC"
set rs = Conn.Execute(strSQL)

strSQL3 = "SELECT *, " & _
        "(SELECT top 1 'true' FROM labels WHERE sf_ord = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT top 1 'true' FROM sfordfil_sql WHERE item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql " & _
        "ORDER BY ord_no DESC"
        set rs3 = Conn.Execute(strSQL3)
strSQL1 = "SELECT * FROM labels WHERE sf_ord = " & rs("ord_no") & ""
set rs1 = Conn.Execute(strSQL1)
%>
<div align="center">
<table border="1" width="745" id="table1" height="63">
     <tr>
          <td height="31" width="109" align="center">Shop Order #</td>
          <td height="31" width="130" align="center">Short Model #</td>
          <td height="31" width="208" align="center">Item Description</td>
          <td height="31" width="128" align="center">Start Date</td>
          <td height="31" width="136" align="center">Print</td>
     </tr>
<% While Not rs3.EOF %>
     <tr>
          <td width="109" align="center"><%= rs("ord_no") %></td>
          <td width="130" align="center"><%= rs("item_no") %></td>
          <td width="208" align="center"><%= rs("item_desc_1") %></td>
          <td width="128" align="center"><%= rs("start_dt") %></td>
          <td width="136" align="center">
<%if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") > 0 then %>
Test 1 Export
<%Else if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") = 0 then %>
Test 2 Export
<%Else if rs3("inexport") = true then %>
Test 3 Export
<%Elseif rs3("inlabels") = true and rs1("zn_4") > 0 then %>
Test 1 USA
<%Elseif rs3("inlabels") = true and rs1("zn_4") = 0 then %>
Test 2 USA
<%Else%>                                                                                        <---------It is skipping the others and jumping to this.
Test 3 USA
<%End If%>
</font></b>
<font size="1">
</font></font></td>
</tr>
<% rs.MoveNext %>
<%end if%>
<%end if%>
<% Wend %>
</table>
</div>
<%
'CLOSE
conn.close
set conn = nothing
%>
</body>
</html>
0
 
kwitcomAuthor Commented:
Ok fixed the EOF error, but what do i need to do to make it do the ELSEIF lines?
dim dia, strID, strSQL, Conn, rs, strSQL2, strSQL3, strSQL4, strSQL5, rs2, rs3, rs4, rs5, ordno
dia = Date()
strSQL = "SELECT * FROM sfordfil_sql WHERE compl_dt is NULL ORDER BY ord_no DESC"
set rs = Conn.Execute(strSQL)
strSQL3 = "SELECT *, " & _
        "(SELECT top 1 'true' FROM labels WHERE sf_ord = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT top 1 'true' FROM sfordfil_sql WHERE item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql " & _
        "ORDER BY ord_no DESC"
        set rs3 = Conn.Execute(strSQL3)
strSQL1 = "SELECT * FROM labels WHERE sf_ord = " & rs("ord_no") & ""
set rs1 = Conn.Execute(strSQL1)%>
<%if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") > 0 then %>
Test 1 Export
<%Else if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") = 0 then %>
Test 2 Export
<%Else if rs3("inexport") = true then %>
Test 3 Export
<%Elseif rs3("inlabels") = true and rs1("zn_4") > 0 then %>
Test 1 USA
<%Elseif rs3("inlabels") = true and rs1("zn_4") = 0 then %>
Test 2 USA
<%Else%>
Test 3 USA
<%End If%>
</font></b><font size="1"></font></font></td></tr>
<% rs.MoveNext %>
<%end if%>
<%end if%>
<% Wend %>

EOF fix:
<% While Not rs.EOF %>
0
 
WMIFCommented:
try making that one long if statement.  to do that, you would make those "else if" into an "elseif".

if [cond] then
  ...
elseif [cond] then
  ...
elseif [cond] then
  ...
end if
0
 
kwitcomAuthor Commented:
Running but still not doing the correct action:
Another way to see if something is an export is the short Model # is a ***2** number.

Shop Order # Short Model # Item Description Start Date Print
3630 10202-LP  Spa, w/Lounge 8' - Lapis  4/28/2006 Test 3 USA                  <--------Should be an Export
3568 6100-SG  Spa, w/Lounge 6' - Sage  4/28/2006 Test 3 USA  
3382 6100-PR  Spa, w/Lounge 6' - Pearl  4/28/2006 Test 3 USA  

Code:
<%'DECLARATIONS
dim dia, strID, strSQL, Conn, rs, strSQL2, strSQL3, strSQL4, strSQL5, rs2, rs3, rs4, rs5, ordno
dia = Date()
strSQL = "SELECT * FROM sfordfil_sql WHERE compl_dt is NULL ORDER BY ord_no DESC"
set rs = Conn.Execute(strSQL)
strSQL3 = "SELECT *, " & _
        "(SELECT top 1 'true' FROM labels WHERE job_no = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT top 1 'true' FROM sfordfil_sql WHERE item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql " & _
        "ORDER BY ord_no DESC"
        set rs3 = Conn.Execute(strSQL3)
strSQL1 = "SELECT * FROM labels WHERE sf_ord = " & rs("ord_no") & ""
set rs1 = Conn.Execute(strSQL1)%>
<% While Not rs.EOF %>
     <tr>
          <td width="109" align="center"><%= rs("ord_no") %></td>
          <td width="130" align="center"><%= rs("item_no") %></td>
          <td width="208" align="center"><%= rs("item_desc_1") %></td>
          <td width="128" align="center"><%= rs("start_dt") %></td>
          <td width="136" align="center">
<%if rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") > 0 then %>
Test 1 Export
<%Elseif rs3("inlabels") = true and rs3("inexport") = true and rs1("zn_4") = 0 then %>
Test 2 Export
<%Elseif rs3("inexport") = true then %>
Test 3 Export
<%Elseif rs3("inlabels") = true and rs1("zn_4") > 0 then %>
Test 1 USA
<%Elseif rs3("inlabels") = true and rs1("zn_4") = 0 then %>
Test 2 USA
<%Else%>
Test 3 USA
<%End If%>
</font></b><font size="1"></font></font></td></tr>
<% rs.MoveNext %>
<% Wend %>
</table></div>
0
 
kwitcomAuthor Commented:
Looks like the export query is not working.
0
 
WMIFCommented:
can you try running this query in sql to see that we are getting the correct values at least there?  im wondering if we need to tweak that query a bit.
0
 
kwitcomAuthor Commented:
SELECT     *
FROM         sfordfil_sql
WHERE     (item_desc_2 LIKE 'export%')

Here is a Sample of the output.

1343      7200-PR                             4/28/2006      1343      Spa, w/Lounge 7' - Pearl            Export                        
3545      7202-PL                             4/28/2006      3545      Spa, w/Lounge 7' - Platinum         Export w/Stereo              
3626      6200-LP                             4/28/2006      3626      Spa, w/Lounge 6' - Lapis            Export                        
3628      10202-LP                            4/28/2006      3628      Spa, w/Lounge 8' - Lapis            Export w/Stereo              
3629      10202-LP                            4/28/2006      3629      Spa, w/Lounge 8' - Lapis            Export w/Stereo              
3630      10202-LP                            4/28/2006      3630      Spa, w/Lounge 8' - Lapis            Export w/Stereo              
44      10200-LP                            4/28/2006      44      Spa, w/Lounge 8' - Lapis            Export                        
45      10200-LP                            4/28/2006      45      Spa, w/Lounge 8' - Lapis            Export                        

This is what i can put into SQL dirrect and get a list.
0
 
WMIFCommented:
oh, i know why theres a problem. :)  the export query isnt specifying any models or anything.  we need to relate the model number in there.  so something like this (adjust the names if i am guessing incorrectly.

strSQL3 = "SELECT *, " & _
        "(SELECT top 1 'true' FROM labels WHERE job_no = " & rs("ord_no") & ") as inlabels, " & _
        "(SELECT top 1 'true' FROM sfordfil_sql sq2 WHERE q1.short_model = sq2.shortmodel and item_desc_2 LIKE 'export%') as inexport " & _
        "FROM sfordfil_sql q1" & _
        "ORDER BY ord_no DESC"
        set rs3 = Conn.Execute(strSQL3)
0
 
WMIFCommented:
do you need to do the same thing with the labels query?
0
 
kwitcomAuthor Commented:
what about this? When it returns the results it checks to see if export is in the "item_desc_2".  I get an error:
Microsoft VBScript runtime error '800a0023'
Sub or Function not defined
/finial/4.asp, line 40


dim dia, strID, strSQL, Conn, rs, strSQL2, strSQL3, strSQL4, strSQL5, rs2, rs3, rs4, rs5, ordno
dia = Date()
strSQL = "SELECT * FROM sfordfil_sql WHERE compl_dt is NULL ORDER BY ord_no DESC"
set rs = Conn.Execute(strSQL)
strSQL1 = "SELECT * FROM labels WHERE sf_ord = " & rs("ord_no") & ""
set rs1 = Conn.Execute(strSQL1)%>
<% While Not rs.EOF %>
     <tr>
          <td width="109" align="center"><%= rs("ord_no") %></td>
          <td width="130" align="center"><%= rs("item_no") %></td>
          <td width="208" align="center"><%= rs("item_desc_1") %></td>
          <td width="128" align="center"><%= rs("start_dt") %></td>
          <td width="136" align="center">
<%if rs("item_desc_2") like "'export%'" and rs1("zn_4") > 0 then %>                                      <----- Line with Error
Test 1 Export
<%Elseif rs("item_desc_2") like "'export%'" and rs1("zn_4") = 0 then %>
Test 2 Export
<%Elseif rs("item_desc_2") like "'export%'" then %>
Test 3 Export
<%Elseif rs1("zn_4") > 0 then %>
Test 1 USA
<%Elseif rs1("zn_4") = 0 then %>
Test 2 USA
<%Else%>
Test 3 USA
<%End If%>
</font></b><font size="1"></font></font></td></tr>
<% rs.MoveNext %>
<% Wend %>
</table></div>
0
 
WMIFCommented:
nope that doesnt work like that.  can you try that query i gave you?  i believe that is the problem we were facing.  what was happening was that for every model, it was retrieveing only the first record which wasnt in the export table.  by relating the model it pulls only the record for that model.
0
 
kwitcomAuthor Commented:
Ok I found it.... This works:

<%          
strSQL3 = "SELECT * FROM sfordfil_sql WHERE item_desc_2 LIKE 'export%' and ord_no = " & rs("ord_no") & ""
set rs3 = Conn.Execute(strSQL3) %>
<%if rs3.EOF and rs1("zn_4") > 0 then %>
Test 1 USA
<%Elseif rs3.EOF and rs1("zn_4") = 0 then %>
Test 2 USA
<%Elseif rs3.EOF then %>
Test 3 USA
<%Elseif rs1("zn_4") > 0 then %>
Test 1 Export
<%Elseif rs1("zn_4") = 0 then %>
Test 2 Export
<%Else%>
Test 3 Export
<%End If%>
</font></b><font size="1"></font></font></td></tr>
<% rs.MoveNext %>
<% Wend %>
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now