|
 |
asp_databases thread: Logic - not able to retrieve records
Message #1 by Leo Clayton <claytonl@z...> on Wed, 29 Nov 2000 18:16:02 -0500
|
|
--=====================_1070876929==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
I have a table that I know contains the record I am looking for, but for
some reason I am unable to access it! When I check for EOF it doesn't find
the record and response.writes "There were no records found". Can someone
tell me what is going on (what I'm doing wrong)?
This involves three pages:
(1) one has a form with a first and last name in it that I submit to
another ASP page that
(2) SELECTS a record based upon a first and last name match, if there is no
match it response writes that message and then "meta=refresh" back to the
first (original) page. If there is a match, it goes to another page that,
(3) has a form with two radio buttons, one of which is already checked,
that determines what table will be updated and then on submit goes to
another page for further processing.
Page 2 code:
<%
Dim dsn
dsn="EmployeeMaster"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode=3
conn.open dsn
Set Session("emp_conn") = conn
Set rs = Server.CreateObject("ADODB.RecordSet")
sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName =
'"&Request.Form("Firstname")&"') AND (LastName = '" &
Request.Form("LastName")&"'))"
rs.Open sql, conn, 3, 3
If rs.EOF Then
Response.Write ("<STRONG>The was <U>NO MATCHING RECORD</U> FOR A
PERSON NAME</STRONG> ")
Response.Write (Request.Form("FirstName"))
Response.Write " "
Response.Write (Request.Form("LastName"))
Else
'Response.Write("The Record IDNumber is ")
'Response.write(rs("IDNumber"))
Session("IDNumber") = rs("IDNumber")
'Response.Redirect "SelectFileToUpdate3.asp"
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Response.Redirect "SelectFileToUpdate2.asp"
%>
Page 3 code:
<%
Dim dsn
dsn="EmployeeMaster"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode=3
conn.open dsn
Set Session("emp_conn") = conn
Set rs = Server.CreateObject("ADODB.RecordSet")
sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName =
'"&Request.Form("Firstname")&"') AND (LastName = '" &
Request.Form("LastName")&"'))"
rs.Open sql, conn, 3, 3
If not rs.EOF Then
Response.Write("The Record IDNumber is ")
Response.write(rs("IDNumber"))
Session("IDNumber") = rs("IDNumber")
Response.write(Session("IDNumber"))
Else
Response.Write "There were no records found"
End If
%>
<FORM ACTION="UpdateEmployeeMaster.asp" METHOD="POST" >
<CENTER>
<TABLE BORDER=0>
<TR>
<TD><INPUT TYPE="RADIO" NAME="TableToBeUpdated" VALUE="Employee"
CHECKED>EMPLOYEE PERSONAL INFO</TD>
</TR>
<TR>
<TD><INPUT TYPE="RADIO" NAME="TableToBeUpdated"
VALUE="EmployeeSkills" >EMPLOYEE SKILLS INFO</TD>
</TR>
<TR>
<TD></TD>
<TD ALIGN=CENTER COLSPAN=2><BR>
<INPUT TYPE="Submit" VALUE="Select File To Update">
<INPUT TYPE="RESET"></TD>
</TR>
</TABLE>
</CENTER>
</FORM>
Message #2 by Stephane_Dattenny@D... on Thu, 30 Nov 2000 02:17:01 -0600
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C05AA5.EA99DE30
Content-Type: text/plain;
charset="iso-8859-1"
Hi
Simple:
Your page 2 works well, getting the Form information (Firstname and
lastname) and the recordset is not empty.
But you put a redirect to "SelectFileToUpdate2.asp" (I think it's page 3)
WITHOUT FORM INFORMATION.
So your page3 COULD NOT GET Request.Form("Firstname") and
Request.Form("Lastname") because they haven't been sent !!
To do so, do like this:
On page 2:
Response.Redirect "SelectFileToUpdate2.asp?Firstname=" &
Server.URLEncode(Request.Form("Firstname")) & "&Lastname=" &
Server.URLEncode(Request.Form("Lastname"))
instead of Response.Redirect "SelectFileToUpdate2.asp"
This sends information using a GET method.
On page 3:
sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName
'"&Request.QueryString("Firstname")&"') AND (LastName = '" &
Request.QueryString("LastName")&"'))"
This replace Request.Form by Request.QueryString to get "GET" sent
information.
Best regards / Cordialement
Stephane Dattenny
Dell Computers - EMEA IT - VB and Web developer
Phone: +33 (0)4 99 75 49 88
-----Original Message-----
From: Leo Clayton [ mailto:claytonl@z...
<mailto:claytonl@z...> ]
Sent: 30 November 2000 00:16
To: ASP Databases
Subject: [asp_databases] Logic - not able to retrieve records
I have a table that I know contains the record I am looking for, but for
some reason I am unable to access it! When I check for EOF it doesn't find
the record and response.writes "There were no records found". Can someone
tell me what is going on (what I'm doing wrong)?
This involves three pages:
(1) one has a form with a first and last name in it that I submit to another
ASP page that
(2) SELECTS a record based upon a first and last name match, if there is no
match it response writes that message and then "meta=refresh" back to the
first (original) page. If there is a match, it goes to another page that,
(3) has a form with two radio buttons, one of which is already checked, that
determines what table will be updated and then on submit goes to another
page for further processing.
Page 2 code:
<%
Dim dsn
dsn="EmployeeMaster"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode=3
conn.open dsn
Set Session("emp_conn") = conn
Set rs = Server.CreateObject("ADODB.RecordSet")
sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName
'"&Request.Form("Firstname")&"') AND (LastName = '" &
Request.Form("LastName")&"'))"
rs.Open sql, conn, 3, 3
If rs.EOF Then
Response.Write ("<STRONG>The was <U>NO MATCHING RECORD</U> FOR A
PERSON NAME</STRONG> ")
Response.Write (Request.Form("FirstName"))
Response.Write " "
Response.Write (Request.Form("LastName"))
Else
'Response.Write("The Record IDNumber is ")
'Response.write(rs("IDNumber"))
Session("IDNumber") = rs("IDNumber")
'Response.Redirect "SelectFileToUpdate3.asp"
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Response.Redirect "SelectFileToUpdate2.asp"
%>
Page 3 code:
<%
Dim dsn
dsn="EmployeeMaster"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode=3
conn.open dsn
Set Session("emp_conn") = conn
Set rs = Server.CreateObject("ADODB.RecordSet")
sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName
'"&Request.Form("Firstname")&"') AND (LastName = '" &
Request.Form("LastName")&"'))"
rs.Open sql, conn, 3, 3
If not rs.EOF Then
Response.Write("The Record IDNumber is ")
Response.write(rs("IDNumber"))
Session("IDNumber") = rs("IDNumber")
Response.write(Session("IDNumber"))
Else
Response.Write "There were no records found"
End If
%>
<FORM ACTION="UpdateEmployeeMaster.asp" METHOD="POST" >
<CENTER>
<TABLE BORDER=0>
<TR>
<TD><INPUT TYPE="RADIO" NAME="TableToBeUpdated" VALUE="Employee"
CHECKED>EMPLOYEE PERSONAL INFO</TD>
</TR>
<TR>
<TD><INPUT TYPE="RADIO" NAME="TableToBeUpdated" VALUE="EmployeeSkills"
>EMPLOYEE SKILLS INFO</TD>
</TR>
<TR>
<TD></TD>
<TD ALIGN=CENTER COLSPAN=2><BR>
<INPUT TYPE="Submit" VALUE="Select File To Update">
<INPUT TYPE="RESET"></TD>
</TR>
</TABLE>
</CENTER>
</FORM>
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips,
tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com <http://www.earthweb.com>
today!
Message #3 by Imar Spaanjaars <Imar@S...> on Thu, 30 Nov 2000 08:33:10 +0100
|
|
Hi Leo,
Could it be that in the third page Request.Form("Firstname") and
Request.Form("Lastname") are empty? I don't see you write them into hidden
fields or anything.
What you need to do is pass these values from page 2 to page 3 through
hidden fields, a querystring or session variables.
I think that if you'd Response.Write(sql) then you'd seen the problem
yourself. Always do a response.write(sql) before you open the recordset.
This will show you if you are submitting the right sql to the
database!!!!!!!!!!!!!
So change this:
sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName =
'"&Request.Form("Firstname")&"') _
AND (LastName = '" & Request.Form("LastName")&"'))"
rs.Open sql, conn, 3, 3
into
sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName =
'"&Request.Form("Firstname")&"') _
AND (LastName = '" & Request.Form("LastName")&"'))"
Response.write(sql)
rs.Open sql, conn, 3, 3
Also, why are you storing your connection object into a session variable?
First of all, this is very bad programming since by doing this, you prevent
the connections from being pooled. It's faster and it scales better when
you open and close a connection on every page you need one.
But second, you are just storing it and not even using it!! In page 3 you
create a new connection without getting the old one from the session
object. I'd say you better remove the lines: Set Session("emp_conn") = conn
from page 2 and 3.
HtH
Imar
At 06:16 PM 11/29/2000 -0500, you wrote:
>I have a table that I know contains the record I am looking for, but for
>some reason I am unable to access it! When I check for EOF it doesn't
>find the record and response.writes "There were no records found". Can
>someone tell me what is going on (what I'm doing wrong)?
>
>This involves three pages:
>(1) one has a form with a first and last name in it that I submit to
>another ASP page that
>(2) SELECTS a record based upon a first and last name match, if there is
>no match it response writes that message and then "meta=refresh" back to
>the first (original) page. If there is a match, it goes to another page that,
>(3) has a form with two radio buttons, one of which is already checked,
>that determines what table will be updated and then on submit goes to
>another page for further processing.
>
>
>
>Page 2 code:
><%
> Dim dsn
> dsn="EmployeeMaster"
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.Mode=3
> conn.open dsn
> Set Session("emp_conn") = conn
>
> Set rs = Server.CreateObject("ADODB.RecordSet")
>
> sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName =
> '"&Request.Form("Firstname")&"') AND (LastName = '" &
> Request.Form("LastName")&"'))"
> rs.Open sql, conn, 3, 3
>If rs.EOF Then
> Response.Write ("<STRONG>The was <U>NO MATCHING RECORD</U> FOR A
> PERSON NAME</STRONG> ")
> Response.Write (Request.Form("FirstName"))
> Response.Write " "
> Response.Write (Request.Form("LastName"))
>Else
> 'Response.Write("The Record IDNumber is ")
> 'Response.write(rs("IDNumber"))
> Session("IDNumber") = rs("IDNumber")
> 'Response.Redirect "SelectFileToUpdate3.asp"
>End If
>
>rs.Close
>Set rs = Nothing
>
>conn.Close
>Set conn = Nothing
>
>Response.Redirect "SelectFileToUpdate2.asp"
>%>
>
>Page 3 code:
><%
>
> Dim dsn
> dsn="EmployeeMaster"
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.Mode=3
> conn.open dsn
> Set Session("emp_conn") = conn
>
> Set rs = Server.CreateObject("ADODB.RecordSet")
>
> sql="SELECT IDNumber FROM EmployeeTable WHERE ((FirstName =
> '"&Request.Form("Firstname")&"') AND (LastName = '" &
> Request.Form("LastName")&"'))"
> rs.Open sql, conn, 3, 3
>
>If not rs.EOF Then
> Response.Write("The Record IDNumber is ")
> Response.write(rs("IDNumber"))
> Session("IDNumber") = rs("IDNumber")
> Response.write(Session("IDNumber"))
>Else
> Response.Write "There were no records found"
>End If
>%>
>
><FORM ACTION="UpdateEmployeeMaster.asp" METHOD="POST" >
><CENTER>
>
><TABLE BORDER=0>
>
><TR>
><TD><INPUT TYPE="RADIO" NAME="TableToBeUpdated" VALUE="Employee"
>CHECKED>EMPLOYEE PERSONAL INFO</TD>
></TR>
>
><TR>
><TD><INPUT TYPE="RADIO" NAME="TableToBeUpdated"
>VALUE="EmployeeSkills" >EMPLOYEE SKILLS INFO</TD>
></TR>
>
><TR>
><TD></TD>
><TD ALIGN=CENTER COLSPAN=2><BR>
><INPUT TYPE="Submit" VALUE="Select File To Update">
><INPUT TYPE="RESET"></TD>
></TR>
></TABLE>
>
></CENTER>
></FORM>
Message #4 by "Dallas Martin" <dmartin@z...> on Thu, 30 Nov 2000 08:47:53 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_006C_01C05AAA.3A49F740
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Let me share with you an interesting experience.
I develop my code on one network and upload to
the production network.
On my development network, IIS and SQL are running
on the same machine. On the production network, IIS
is on one machine and SQL on another.
I had a simple logon page (username/password). On submit,
another ASP page would check the database for matching
username/logon using code like "SELECT user_id FROM logon
WHERE username =3D'" & username & "' AND password =3D'" & password &
"'".
This code work perfectly well on the development network, but failed
on the production network. I was puzzled. I checked all my permissions
on the production network and SQL db. All was OK. Still, the code
failed.
I finally took a look at the logon table. I had created the username and
password
fields as char(10) fields. I changed them from char(10) to varchar(10).
Now the code
worked both on the development and production servers.
I haven't researched the reasons for this behavior. But the caveat I
learned is to
make all my text fields varchar() datatypes instead of char datatypes.
Remember,
this behaviour is evident when the SQL server is on a separate machine
from the
webserver.
Hope this helps.
Dallas Martin
----- Original Message -----
From: Leo Clayton
To: ASP Databases
Sent: Wednesday, November 29, 2000 6:16 PM
Subject: [asp_databases] Logic - not able to retrieve records
I have a table that I know contains the record I am looking for, but
for some reason I am unable to access it! When I check for EOF it
doesn't find the record and response.writes "There were no records
found". Can someone tell me what is going on (what I'm doing wrong)?
This involves three pages:
(1) one has a form with a first and last name in it that I submit to
another ASP page that
(2) SELECTS a record based upon a first and last name match, if there
is no match it response writes that message and then "meta=3Drefresh"
back to the first (original) page. If there is a match, it goes to
another page that,
(3) has a form with two radio buttons, one of which is already
checked, that determines what table will be updated and then on submit
goes to another page for further processing.
Page 2 code:
<%
Dim dsn
dsn=3D"EmployeeMaster"
Set conn =3D Server.CreateObject("ADODB.Connection")
conn.Mode=3D3
conn.open dsn
Set Session("emp_conn") =3D conn
Set rs =3D Server.CreateObject("ADODB.RecordSet")
sql=3D"SELECT IDNumber FROM EmployeeTable WHERE ((FirstName =3D
'"&Request.Form("Firstname")&"') AND (LastName =3D '" &
Request.Form("LastName")&"'))"
rs.Open sql, conn, 3, 3
If rs.EOF Then
Response.Write ("<STRONG>The was <U>NO MATCHING RECORD</U> FOR
A PERSON NAME</STRONG> ")
Response.Write (Request.Form("FirstName"))
Response.Write " "
Response.Write (Request.Form("LastName"))
Else
'Response.Write("The Record IDNumber is ")
'Response.write(rs("IDNumber"))
Session("IDNumber") =3D rs("IDNumber")
'Response.Redirect "SelectFileToUpdate3.asp"
End If
rs.Close
Set rs =3D Nothing
conn.Close
Set conn =3D Nothing
Response.Redirect "SelectFileToUpdate2.asp"
%>
Page 3 code:
<%
Dim dsn
dsn=3D"EmployeeMaster"
Set conn =3D Server.CreateObject("ADODB.Connection")
conn.Mode=3D3
conn.open dsn
Set Session("emp_conn") =3D conn
Set rs =3D Server.CreateObject("ADODB.RecordSet")
sql=3D"SELECT IDNumber FROM EmployeeTable WHERE ((FirstName =3D
'"&Request.Form("Firstname")&"') AND (LastName =3D '" &
Request.Form("LastName")&"'))"
rs.Open sql, conn, 3, 3
If not rs.EOF Then
Response.Write("The Record IDNumber is ")
Response.write(rs("IDNumber"))
Session("IDNumber") =3D rs("IDNumber")
Response.write(Session("IDNumber"))
Else
Response.Write "There were no records found"
End If
%>
<FORM ACTION=3D"UpdateEmployeeMaster.asp" METHOD=3D"POST" >
<CENTER>
<TABLE BORDER=3D0>
<TR>
<TD><INPUT TYPE=3D"RADIO" NAME=3D"TableToBeUpdated" VALUE=3D"Employee"
CHECKED>EMPLOYEE PERSONAL INFO</TD>
</TR>
<TR>
<TD><INPUT TYPE=3D"RADIO" NAME=3D"TableToBeUpdated"
VALUE=3D"EmployeeSkills" >EMPLOYEE SKILLS INFO</TD>
</TR>
<TR>
<TD></TD>
<TD ALIGN=3DCENTER COLSPAN=3D2><BR>
<INPUT TYPE=3D"Submit" VALUE=3D"Select File To Update">
<INPUT TYPE=3D"RESET"></TD>
</TR>
</TABLE>
</CENTER>
</FORM>
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML
tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
$subst('Email.Unsub')
|
|
|
View Cart
