Check for entries in database

[gmagerr]

Hi guys, i can't figure this one out... I have a form that displays jobs in progress. on this form you can enter materials for whichever job you select from the list. I'm trying to check the database to see if a job number already exists, if it does i want to go to the page that displays what's been ordered for that job, if the job number doesn't exist, i want to go to the page that lets you enter materials. So from my job listings page, when you click on materials, i'm running a little script to check the database for the job number then redirecting to the correct page based on the results. What's happening is even if the job number does exist, it's still taking me to the page to order materials, when it should be taking me to the page that lists what's already been ordered. anyway any help on this is greatly appreciated. here's my code to check the database (this is right after you click on materials from the main job listing page)

<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<%
Option Explicit
Response.Buffer = True
Response.ContentType = &quot;text/html; charset=iso-8859-1&quot;
Session(&quot;JobCode&quot = Request.QueryString(&quot;JobCode&quot
'--------------------------------------------------------------
'--- Module: .asp
'---
'--- Version: 1.0
'---
'--- Purpose:
'---
'--- Dependencies:
'---
'--- Notes:
'---
'--- Author: Gene Magerr genemagerr@hotmail.com
'--- Copyright 2001 Magerr Media all rights reserved
'--------------------------------------------------------------
%>
<!--#include file=&quot;../adovbs.inc&quot;-->
<%
'--------------------------------------------------------------
Dim rsCheck, cnCheck, JobCode, strSQL
'--------------------------------------------------------------
'--- Declarations
'--------------------------------------------------------------

Set cnCheck = Server.CreateObject(&quot;ADODB.Connection&quot
cnCheck.Open &quot;DSN=PSS&quot;

strSQL = &quot;SELECT * FROM Orders&quot;

Set rsCheck = Server.CreateObject(&quot;ADODB.Recordset&quot
rsCheck.Open strSQL, cnCheck, adOpenDynamic, adLockPessimistic

'--------------------------------------------------------------
'--- Initialization
'--------------------------------------------------------------
Do While Not rsCheck.EOF
If Session(&quot;JobCode&quot = rsCheck.Fields.Item(&quot;JobCode&quot.Value Then
Response.Redirect &quot;DisplayQty.asp?JobCode=&quot;& Session(&quot;JobCode&quot
Else
Response.Redirect &quot;MatOrderMaster.asp?JobCode=&quot;& Session(&quot;JobCode&quot
End If
rsCheck.MoveNext
Loop

rsCheck.Close
Set rsCheck = Nothing
cnCheck.Close
Set cnCheck = Nothing
'--------------------------------------------------------------
'--- Body
'--------------------------------------------------------------

'--------------------------------------------------------------
'--- Begin HTML output
'--------------------------------------------------------------
%>

<%
'----------------------------------------------------------------------
'--- End HTML Output
'----------------------------------------------------------------------

'----------------------------------------------------------------------
' All ASP post processing code goes here, as well as
' sub routines and functions
'----------------------------------------------------------------------
%>


Thanks in advance.

 

[williamu]

Try outputing the values of Session(&quot;JobCode&quot and rsCheck.Fields.Item(&quot;JobCode&quot.Value to see what they are. They may not be what you expect!

You may also need to do some conversion on tha data to ensure that you are comparing both ints or chars, in your equiv check at :If Session(&quot;JobCode&quot = rsCheck.Fields.Item(&quot;JobCode&quot.Value

HTH
William
Software Engineer

 

[JuanitaC]

I can think of three ways to handle this:

1) Trim your values before you compare in case there are any trailing spaces:
If Trim(Session(&quot;JobCode&quot) = Trim(rsCheck.Fields.Item(&quot;JobCode&quot.Value) Then

2) If your JobCode is alphanumeric, make sure it is all in the same case before you compare:
If Trim(UCase(Session(&quot;JobCode&quot)) = Trim(UCase(rsCheck.Fields.Item(&quot;JobCode&quot.Value)) Then

3) If you have the job code already, why loop through all the records in your database? Try just getting the one you are comparing:

strSQL = &quot;SELECT * FROM Orders WHERE JobCode = &quot; & Session(&quot;JobCode&quot

Then, check to see if it exists:
If rsCheck.EOF then 'doesn't exist
...
else 'does exist
...
end if

 

[gmagerr]

Thank you thank you thank you.. you guys are awesome. very quick response. JuanitaC your suggestion works flawlessly thanks a million

 

[derren]

The reason you are always going to the order materials page is you are doing this:

Do while not rsCheck.EOF
If my session variable matches the first record
go to the what's already ordered page
but if the FIRST record doesn't match
go to the order materials page

You never get past record one. If you were going to persist in doing it this way you should redirect to the materails page after the loop has finished.

However, the big mistake is not following JuanitaC's advice (and something which immediately struck me about it as well). If you are checking to see if a record exists the most efficient way is to do a &quot;select where that value=your check value&quot; and then seeing if it is at the EOF
Derren
[Mediocre talent - spread really thin]