Need help with adding function to record display

[foundsheep]

I'm working on this app that displays parts that need to be reordered. I need it not to show parts that have their recommended stock at zero, and I have yet to figure out the best way to do it. Any help is greatly appreciated. Here's the code:

%

Set rsVen = Server.CreateObject("ADODB.Recordset")
Set rsPrt = Server.CreateObject("ADODB.Recordset")
rsVen.Open "SELECT DISTINCT Company FROM Vendor", db, 3, 3, 1
x = 0
if not rsVen.EOF then
Do Until rsVen.EOF
if Fix(x/2) = x/2 then
dColor = "white"
else
dColor = "#cccccc"
end if
%>
<tr bgColor="<%=dColor%>">
<td colspan="3"><b><%=rsVen("Company")%></b></td>

<%
rsPrt.Open "SELECT * FROM Parts WHERE parVendor = '" & rsVen("Company") & "'", db, 3, 3, 1
if not rsPrt.EOF then
Dim lm
lm = 0
Do Until rsPrt.EOF
If rsPrt("qtyonHand") <= rsPrt("reorderLevel") then
lm = lm + 1

'response.write(lm)
end if
rsPrt.MoveNext
Loop

if lm >= 1 then%>

 

[Sheco]

Have you considered adding additional conditions to the WHERE clause in your SQL statement?
[TT]
SELECT *
FROM Parts
WHERE parVendor = 'blahblahblah'
AND qtyonHand <= reorderLevel
AND reorderLevel > 0
[/TT]

If your Recordset was only populated by a query like the one above, you wouldnt need to check the quantity in your ASP logic because the recordset would only contain rows for items that should be reordered.

 

[foundsheep]

I thought about it, but I haven't actually tried. Let me give it a whirl and see what happens. Thanks.

 

[foundsheep]

I'm having issues connecting with the server but does this look right?

<%
rsPrt.Open "SELECT * FROM Parts WHERE parVendor = ' AND qtyonHand <= reorderLevel AND recStock > 0" & rsVen("Company") & "'", db, 3, 3, 1
then%>

 

[foundsheep]

I'm not sure what is essential and what is not in the asp code. Right it displays that no parts need reordering which is incorrect. So, its backwards.

This is what I currently have:

<table width="100%" cellspacing="0" cellpadding="3" border="0" style="FONT-SIZE: 9pt">
<%

Set rsVen = Server.CreateObject("ADODB.Recordset")
Set rsPrt = Server.CreateObject("ADODB.Recordset")
rsVen.Open "SELECT DISTINCT Company FROM Vendor", db, 3, 3, 1
x = 0
if not rsVen.EOF then
Do Until rsVen.EOF
if Fix(x/2) = x/2 then
dColor = "white"
else
dColor = "#cccccc"
end if
%>
<tr bgColor="<%=dColor%>">
<td colspan="3"><b><%=rsVen("Company")%></b></td>

<%
rsPrt.Open "SELECT * FROM Parts WHERE parVendor = ' AND qtyonHand <= reorderLevel AND recStock >= 1" & rsVen("Company") & "'", db, 3, 3, 1
if not rsPrt.EOF then
Dim lm
lm = 0
Do Until rsPrt.EOF
'If rsPrt("qtyonHand") <= rsPrt("reorderLevel") then
'lm = lm + 1
'response.write(lm)
'end if
rsPrt.MoveNext
Loop

if lm >= 1 then%>
<td colspan="2" align="right"><input TYPE="button" style="border: 1px solid #000000" name="butOrder" value="View Order" onClick="location.href='parts.asp?action=ordprts&comp=<%=rsVen("Company")%>&s=1'"></td>
<%else%>
<td colspan="2" align="right">No Orders Needed.</td>
<%end if

else%>
<td colspan="2" align="right">No Orders Needed.</td>
<%end if
x = x + 1
rsPrt.Close
rsVen.MoveNext
loop
end if
rsVen.Close
%>
</table>

 

[Sheco]

I'm not familiar with your database structure but my first guess would be:[tt]
rsPrt.Open "SELECT * FROM Parts WHERE parVendor = '" & rsVen("Company") & "' AND qtyonHand <= reorderLevel AND reorderLevel > 0", db, 3, 3, 1
[/tt]

Actually, to make it easier to read, I might split it into multip lines like this:[tt]

sSQL = ""
sSQL = sSQL & "SELECT * "
sSQL = sSQL & "FROM Parts " _
sSQL = sSQL & "WHERE parVendor = '" & rsVen("Company") & "' "
sSQL = sSQL & "AND qtyonHand <= reorderLevel "
sSQL = sSQL & "AND reorderLevel > 0 "
rsPrt.Open sSQL, db, 3, 3, 1
[/tt]

Yes it uses more lines of code but, in my opinion, making code easier to read reduces errors and makes debugging easier.

 

[foundsheep]

I'm now getting this error:

Microsoft VBScript compilation error '800a0400'

Expected statement

/modules/parts/prtFunctions.asp, line 1482

END SELECT
^

with this code:

<%
rsPrt.Open "SELECT * FROM Parts WHERE parVendor = ' AND qtyonHand <= reorderLevel AND recStock >= 1" & rsVen("Company") & "'", db, 3, 3, 1
%>
<table border="1" width="100%">
<%do until rs.EOF%>
<tr>
<%for each x in rsVen.Fields%>
<td><%Response.Write(x.value)%><input TYPE="button" style="border: 1px solid #000000" name="butOrder" value="View Order" onClick="location.href='parts.asp?action=ordprts&comp=<%=rsVen("Company")%>&s=1'"></td>
<%next
rsVen.MoveNext%>
</tr>
<%loop
rsVen.Close
%>
</table>

</table>
<%
END SELECT

call closeConn()

End Function

Function updQty()



End Function

Function PrtsRec()



End Function



%>

 

[foundsheep]

I've kind of bare bonesed it and started over. I have no idea if what I've got now is close to working but I'm getting this error:

Microsoft VBScript compilation error '800a03fb'

Expected 'Loop'

/modules/parts/OrderParts.asp, line 49

And here is the code as it stands now:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/_private/ServiceSolution.mdb"))

Set rsVen = Server.CreateObject("ADODB.Recordset")
Set rsPrt = Server.CreateObject("ADODB.Recordset")
rsVen.Open "SELECT DISTINCT Company FROM Vendor", conn
%>
<%
rsPrt.Open "SELECT parVendor FROM Parts"& rsVen("Company"), conn
Do Until rsPrt.EOF
rsPrt.MoveNext
%>
<table width="100%" border="0" cellpadding="2" cellspacing="0">
<tr>
<%for each x in rsVen.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%
x=0
do until rsVen.EOF
if Fix(x/2) = x/2 then
dColor = "white"
else
dColor = "#cccccc"
end if%>
<tr bgcolor="<%=dColor%>">
<td colspan="3"><b><%=rsVen("Company")%></b></td>
<%for each x in rsVen.Fields%>
<td colspan="2" align="right"><input type="button" style="border: 1px solid #000000" name="butOrder" value="View Order" onclick="location.href='parts.asp?action=ordprts&comp=<%=rsVen("Company")%>&s=1'" /></td>
<%next
rsVen.MoveNext
rsPrt.Close%>
</tr>
<%loop
rsVen.close
conn.close
%>
</table>

 

[MoLaker]

You have two "Do until" statements, but only one "loop" statement. You need another "loop", but I can't tell exactly where you intend for it to be.

"Retired Programmer". So, please be patient.

 

[foundsheep]

Ok, I've got the info displaying as needed. Now, I've come to another bump in the road. I have the page displaying the results with a checkbox. The idea is to have the user select the needed parts and then submit them which in turn displays on a new page the selected parts to be printed. How do I gather the info to be submitted? Thanks.

 

[Sheco]

The checkboxes will need to be inside an HTML <form> element and you want a submit button.

The page specified as the "action" property of the form will pull the values of the form elements from the HTTP Request.

 

[foundsheep]

I have several elements of each record I want to send ie: part #, price, quantity, etc. How can they all be tied to the checkbox that way? Here is the code:

<table width="100%" border="0" cellpadding="3" cellspacing="0" style="FONT-SIZE: 9pt">
<%

Set rsPrt = Server.CreateObject("ADODB.Recordset")
rsPrt.Open "SELECT * FROM Parts WHERE parVendor = '" & Request("comp") & "'", db, 3, 3, 1
if not rsPrt.EOF then
Set rsVen = Server.CreateObject("ADODB.Recordset")
rsVen.Open "SELECT vID, Company FROM Vendor WHERE Company = '" & Request("comp") & "'", db, 3, 3, 1
vID = rsVen("vID")
rsVen.Close
%>
<tr class="table_Header" bgcolor="silver">
<td width="26%"><%=Request("comp")%> Parts
<input type="hidden" name="comp" value="<%=vID%>" /></td>
<td width="12%" align="center">Part Number </td>
<td width="14%" align="center">Recommended Stock</td>
<td width="12%" align="center">Reorder Level</td>
<td width="12%" align="center">In Stock</td>
<td width="12%" align="center">Needed</td>
<td width="12%" align="center"><div align="right">Price</div></td>
</tr>
<%
x = 0
Do Until rsPrt.EOF

If rsPrt("qtyonHand") <= rsPrt("reorderLevel") And rsPrt("recStock") > 0 And rsPrt("qtyonHand") < rsPrt("recStock") then
if Fix(x/2) = x/2 then
dColor = "white"
else
dColor = "#cccccc"
end if

%>
<tr bgcolor="<%=dColor%>">
<td><label for="chkOrder<%=x%>"><font title="Select&nbsp;<%=rsPrt("parDesc")%>">
<input name="chkOrder" type="checkbox" id="chkOrder<%=x%>" value="<%=rsPrt("parID")%>" checked="checked" />
</font>&nbsp;<%=rsPrt("parVendor")%>&nbsp;<%=rsPrt("parDesc")%></label></td>
<td align="right"><div align="center"><%=(rsPrt("parCode"))%></div></td>
<td align="right"><div align="center"><%=(rsPrt("recStock"))%></div></td>
<td align="right"><div align="center"><%=(rsPrt("reorderLevel"))%></div></td>
<td align="right"><div align="center"><b><%=(rsPrt("qtyonHand"))%></b></div></td>
<td align="right"><div align="center">
<font color="red"><b><%=(rsPrt("recStock") - rsPrt("qtyonHand"))%></b></font>
</div></td>
<td align="right"><%=FormatCurrency(rsPrt("parHRICost"))%></td>
</tr>
<%
x = x + 1
end if

rsPrt.MoveNext
Loop
end if
%>
<tr>
<td><input type="button" style="border: 1px solid #000000" name="butCancel2" value="Cancel" onclick="location.href='parts.asp?action=ordprts'" /></td>
<td align="right">&nbsp;</td>
<td align="right">&nbsp;</td>
<td align="right">&nbsp;</td>
<td align="right">&nbsp;</td>
<td align="right">&nbsp;</td>
<td align="right"><input type="submit" style="border: 1px solid #000000" name="submit2" value="Order Parts" /></td>
</tr>
</table>

 

[Sheco]

Have you considered submitting the record's key value on the HTML form?

The remaining columns could be looked up by key on the page that processes the form input.

 

[foundsheep]

Sounds like a splendid idea, only I'm not quite sure what you are talking about. I'm not familiar with that process. Do you have any details on how that would be accomplished?