Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help..Search Page

Status
Not open for further replies.

senadaulay

Programmer
Feb 4, 2004
29
ID
Pleaseee help...thank's in advance.

I'm working on a simple search page that is supposed to search data, when user type a Employee ID in the
textBox (Form SearchUpdate.asp)
then the datas will show in the next form(ResultUpdate.asp).
my code are working Fine If the EmployeeID were correct and the datas are exists, but When I input EmployeeID which doesn't exists I got error message like this:

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.
/Nur/Project1_ASP/ResultUpdate.asp

I want to make a choices when the user input EmployeeID which doesn't exsits it will be show Message box say that datas aren't exsits and the user can chose if they want to insert the EmployeeID as a new Datas in (AddEmp.asp) or they can just will return to searchUpdate.asp. Is that make any sense?
where did I need to add the coding? any kind of help appreciated.

here's my code
-----SearchUpdate.asp
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<CENTER><H1>Search Employee Data </H1></CENTER>
</HEAD>
<BODY bgcolor=lightskyblue>
<FORM METHOD="post" ACTION="ResultUpdate.asp" name=form1>
<BR>
<CENTER>
<TABLE style="WIDTH: 426px; HEIGHT: 232px" cellSpacing=1 cellPadding=1 width=426 border=1>
<TBODY>
<BR>
<TR>
<TD bgcolor=snow><CENTER>
<P>&nbsp;</P>
<P>Input Employee ID: <INPUT name=EmpID></CENTER></P><center>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<INPUT type=submit value=Submit name=B1 style="WIDTH: 72px; HEIGHT: 42px" size=18>&nbsp;<INPUT style="WIDTH: 67px; HEIGHT: 42px" type=reset size=33 value=CLEAR>
<INPUT type="button" value=MainPage OnClick="location.href='MainPage.asp'" style="WIDTH: 76px; HEIGHT: 42px" size=19 >

</FORM></CENTER>
<CENTER></CENTER></TD></TR></TBODY></TABLE></CENTER>
</BODY>
</HTML>

-----ResultUpdate.asp
<%
Dim intID, varSName, varAddress
Dim intPhoneHome, intPostalCode, varSalary
Dim strsql,Conn,strConnect,rsData

intID = Request.Form ("EmpID")

Set Conn = Server.CreateObject ("ADODB.CONNECTION")
Set rsData = Server.CreateObject ("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

strsql = "Select * from Data Where EmployeeID ="&intID&""
rsData.Open strsql, Conn

%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<CENTER><H1>Update Employee Data</H1></CENTER>
<BODY bgcolor="lightblue">
<FORM ACTION= "ConfirmUpdate.asp" METHOD=post Name=frmConfirm >

<CENTER>
<TABLE style="WIDTH: 402px; HEIGHT: 172px" cellSpacing=1 cellPadding=1 width=402 border=1>
<TR><TD bgcolor=linen><P>EmployeeID</P></TD>
<TD bgcolor=linen><INPUT type="text" name="EmpID" value="<%=rsData("EmployeeID")%>" ></TD></TR>
<TR><TD bgcolor=linen><P>EmployeeName</P></TD>
<TD bgcolor=linen><INPUT type="text" name="EmpName" value="<%=rsData("EmployeeName")%>" ></TD></TR>
<TR><TD bgcolor=linen>EmployeeAddress</TD>
<TD bgcolor=linen><INPUT type="text" name="EmpAddress" value="<%=rsData("EmployeeAddress")%>" ></TD></TR>
<TR><TD bgcolor=linen>EmployeePhoneHome</TD>
<TD bgcolor=linen><INPUT type="text" name="EmpPhone" value="<%=rsData("EmployeePhoneHome")%>" ></TD></TR>
<TR><TD bgcolor=linen>EmployeePostalCode</TD>
<TD bgcolor=linen><INPUT type="text" name="EmpPostal" value="<%=rsData("EmployeePostalCode")%>" > </TD></TR>
<TR><TD bgcolor=linen>EmployeeSalary</TD>
<TD bgcolor=linen><INPUT type="text" name="EmpSalary" value="<%=rsData("EmployeeSalary")%>" ></TD></TR>

</TABLE></P>

<P><CENTER><INPUT id=submit1 style="WIDTH: 85px; HEIGHT: 40px" type=Submit size=28 value=UPDATE name=update>
<INPUT type="button" value=CANCEL OnClick="location.href='MainPage.asp'" style="WIDTH: 88px; HEIGHT: 42px" size=22>
<INPUT type="button" value=BACK OnClick="location.href='SearchUpdate.asp'" style="WIDTH: 88px; HEIGHT: 42px" size=22></CENTER></P>
<P>&nbsp; </P>

</FORM>
</BODY>
</HTML>

-----ConfirmUpdate.asp

<%
Dim intID, varSName, varAddress
Dim intPhoneHome, intPostalCode, varSalary
Dim strsql,Conn,strConnect,rsData

Set Conn = Server.CreateObject ("ADODB.CONNECTION")
Set rsData = Server.CreateObject ("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

intID = Request.Form ("EmpID")
varSName = Request.Form ("EmpName")
varAddress = Request.Form ("EmpAddress")
intPhoneHome = Request.Form ("EmpPhone")
intPostalCode = Request.Form ("EmpPostal")
varSalary = Request.Form ("EmpSalary")

strsql= "UPDATE Data " &_
"SET EmployeeName= '"&varSName&"',EmployeeAddress= '"&varAddress &"', " & _
"EmployeePhoneHome= "&intPhoneHome &",EmployeePostalCode= "&intPostalCode &",EmployeeSalary= '"&varSalary &"' " &_
"WHERE EmployeeID= "&intID &""
Conn.Execute strsql
Conn.Close
set Conn = nothing
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY bgcolor=mistyrose>
<CENTER><H1> CONFIRMATION PAGE </H1>
<P>&nbsp;</P></CENTER>
<CENTER><H2>Data Was Updated</H2>
<P>&nbsp;</P></CENTER>
<CENTER>

<INPUT type="button" value=MainPage OnClick="location.href='MainPage.asp'" style="WIDTH: 96px; HEIGHT: 42px" size=24>
<INPUT type="button" value="List Employee" OnClick="location.href='ListEmployee.asp'" style="WIDTH: 105px; HEIGHT: 42px" size=26>
<P>
</CENTER></P>
</BODY>
</HTML>

Can anyone help me?
Thank's
 
On your ResultUpdate.asp page, you would want to insert code that says if there is no data in the recordset, then give them a message that says it doesn't exist and they can add what they need, else show them the results of their search. Something akin to the following:
Code:
-----ResultUpdate.asp
<%
   Dim intID, varSName, varAddress
   Dim intPhoneHome, intPostalCode, varSalary
   Dim strsql,Conn,strConnect,rsData
   
intID = Request.Form ("EmpID") 
   
Set Conn = Server.CreateObject ("ADODB.CONNECTION")
Set rsData = Server.CreateObject ("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

strsql = "Select * from Data Where EmployeeID ="&intID&"" 
rsData.Open strsql, Conn 

[COLOR=red]if not rs.BOF and rsData.EOF then[/color]
%>
 
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<CENTER><H1>Update Employee Data</H1></CENTER>
<BODY bgcolor="lightblue">
<FORM ACTION= "ConfirmUpdate.asp" METHOD=post Name=frmConfirm >

<CENTER>
<TABLE style="WIDTH: 402px; HEIGHT: 172px" cellSpacing=1 cellPadding=1 width=402 border=1>
  <TR><TD bgcolor=linen><P>EmployeeID</P></TD>
    <TD bgcolor=linen><INPUT type="text" name="EmpID" value="<%=rsData("EmployeeID")%>" ></TD></TR>
   <TR><TD bgcolor=linen><P>EmployeeName</P></TD>
    <TD bgcolor=linen><INPUT type="text" name="EmpName" value="<%=rsData("EmployeeName")%>" ></TD></TR>
  <TR><TD bgcolor=linen>EmployeeAddress</TD>
    <TD bgcolor=linen><INPUT type="text" name="EmpAddress" value="<%=rsData("EmployeeAddress")%>" ></TD></TR>
  <TR><TD bgcolor=linen>EmployeePhoneHome</TD>
    <TD bgcolor=linen><INPUT type="text" name="EmpPhone" value="<%=rsData("EmployeePhoneHome")%>" ></TD></TR>
  <TR><TD bgcolor=linen>EmployeePostalCode</TD>
    <TD bgcolor=linen><INPUT type="text" name="EmpPostal" value="<%=rsData("EmployeePostalCode")%>" > </TD></TR>   
  <TR><TD bgcolor=linen>EmployeeSalary</TD>
    <TD bgcolor=linen><INPUT type="text" name="EmpSalary" value="<%=rsData("EmployeeSalary")%>" ></TD></TR>

</TABLE></P>

<P><CENTER><INPUT id=submit1 style="WIDTH: 85px; HEIGHT: 40px" type=Submit size=28 value=UPDATE name=update>
<INPUT type="button" value=CANCEL OnClick="location.href='MainPage.asp'" style="WIDTH: 88px; HEIGHT: 42px" size=22>
<INPUT type="button" value=BACK OnClick="location.href='SearchUpdate.asp'" style="WIDTH: 88px; HEIGHT: 42px" size=22></CENTER></P>
<P>&nbsp; </P>

</FORM>
</BODY>
</HTML>
[COLOR=red]<% 
else
  'Show them the message that the record doesn't exist and a link to wherever else you want them to go/do.
end if %>[/color]

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein
 
Hi Chopstik, thank for your replay...

I Tried your suggestion but the result show the data is empty from the message I put, eventhough the Employee ID are exists?

I've been trying with this code but it's not working maybe you can help me....

ResultUpdate.asp

<%
Dim intID, varSName, varAddress
Dim intPhoneHome, intPostalCode, varSalary
Dim strsql
Dim Conn
Dim strConnect
Dim rsData

intID = Request.Form ("EmpID")

Set Conn = Server.CreateObject ("ADODB.CONNECTION")
Set rsData = Server.CreateObject ("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

If rsData.RecordCount = "" then
strsql = "Select * From Data Where EmployeeID ="&intID&""
rsData.Open strsql,Conn
Response.Write "No Data was Found"
Response.Redirect ("SearchUpdate.asp")

elseIf rsData.RecordCount >= 1 then

strsql = "Select * from Data Where EmployeeID ="&intID&""
rsData.Open strsql, Conn
end if

%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<CENTER><H1>Update Employee Data</H1></CENTER>
<BODY bgcolor="lightblue">
<FORM ACTION= "ConfirmUpdate.asp" METHOD=post Name=frmConfirm >

<CENTER>
<% do while not rsData.EOF %>
<TABLE style="WIDTH: 402px; HEIGHT: 172px" cellSpacing=1 cellPadding=1 width=402 border=1>
<TR><TD bgcolor=linen><P>EmployeeID</P></TD>
<TD bgcolor=linen><INPUT type="text" name="EmpID" value="<%=rsData("EmployeeID")%>" ></TD></TR>
<TR><TD bgcolor=linen><P>EmployeeName</P></TD>
<TD bgcolor=linen><INPUT type="text" name="EmpName" value="<%=rsData("EmployeeName")%>" ></TD></TR>
<TR><TD bgcolor=linen>EmployeeAddress</TD>
<TD bgcolor=linen><INPUT type="text" name="EmpAddress" value="<%=rsData("EmployeeAddress")%>" ></TD></TR>
<TR><TD bgcolor=linen>EmployeePhoneHome</TD>
<TD bgcolor=linen><INPUT type="text" name="EmpPhone" value="<%=rsData("EmployeePhoneHome")%>" ></TD></TR>
<TR><TD bgcolor=linen>EmployeePostalCode</TD>
<TD bgcolor=linen><INPUT type="text" name="EmpPostal" value="<%=rsData("EmployeePostalCode")%>" > </TD></TR>
<TR><TD bgcolor=linen>EmployeeSalary</TD>
<TD bgcolor=linen><INPUT type="text" name="EmpSalary" value="<%=rsData("EmployeeSalary")%>" ></TD></TR>
<%

rsData.MoveNext
Loop
rsData.Close
set rsData= Nothing

%>
</TABLE></P>

<P><CENTER><INPUT id=submit1 style="WIDTH: 85px; HEIGHT: 40px" type=Submit size=28 value=UPDATE name=update>
<INPUT type="button" value=CANCEL OnClick="location.href='MainPage.asp'" style="WIDTH: 88px; HEIGHT: 42px" size=22>
<INPUT type="button" value=BACK OnClick="location.href='SearchUpdate.asp'" style="WIDTH: 88px; HEIGHT: 42px" size=22></CENTER></P>
<P>&nbsp; </P>

</FORM>
</BODY>
</HTML>

and I got this error message:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/Nur/Project1_ASP/ResultUpdate.asp, line 16

any suggestion
Thank's
 
I'm betting line 16 is where you are first checking the rsData.recordcount. You have not yet opened the recordset, so it is still technically closed. Try this:
Code:
<%
   Dim intID, varSName, varAddress
   Dim intPhoneHome, intPostalCode, varSalary
   Dim strsql
   Dim Conn
   Dim strConnect
   Dim rsData
   
intID = Request.Form ("EmpID") 

   Set Conn = Server.CreateObject ("ADODB.CONNECTION")    
    Set rsData = Server.CreateObject ("ADODB.Recordset")
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"
    rsData.Open "elect * From Data Where EmployeeID ='" & intID & "'", Conn

If rsData.RecordCount = 0 then
    Response.Write "No Data was Found"
    Response.Redirect ("SearchUpdate.asp")    
      
elseIf rsData.RecordCount >= 1 then    
    'Then do whatever you would ordinarily do with the recordset information.
%>

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein
 
Hi Chopstik....thank you for your replay....

the code you gave it's work but I dont know why there is one part doesn't work. If the data wasn't exist it's suppose show message: No Data was Found.in ResultUpdate.asp page. How come it didn't show? did I miss something?

here's my Code:
<%
Dim intID, varSName, varAddress
Dim intPhoneHome, intPostalCode, varSalary
Dim strsql
Dim Conn
Dim strConnect
Dim rsData

intID = Request.Form ("EmpID")

Set Conn = Server.CreateObject ("ADODB.CONNECTION")
Set rsData = Server.CreateObject ("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

rsData.Open "Select * From Data Where EmployeeID =" & intID & "", Conn

If rsData.RecordCount = 0 then
Response.Write "No Data was Found" --this code doesn't work
Response.Redirect("SearchUpdate.asp")

elseIf rsData.RecordCount >= 1 then
strsql = "Select * From Data Where EmployeeID =" & intID & ""
rsData.Open strsql, Conn
rsData.MoveFirst
end if

%>
I also add <%Do While not rsData.EOF %>
<% rsData.MoveNext
Loop
Set rsData= Nothing %>

thank's
 
Actually, I suspect that the code is working but you simply are not seeing it because you are immediately redirecting your page. If you take away the response.redirect immediately following your response.write statement, you should see the code you are looking for.

If you want the user to see the error message, you could put it on the page that you are redirecting to.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein
 
Hi Chopstik...thank's for your replay..

Actually I did try not to use the Response.Redirect statement just Response.Write stetement but it doesn't work either.when the message show the data was not found.

Maybe you could give me idea with my code, I've been working with this code.

ResultUpdate.asp
<%
rsData.Open "Select count (*) As Ccount From Data Where EmployeeID =" & intID & "", Conn
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY bgcolor="lightblue">
<CENTER><H1>Update Employee Data</H1></CENTER><BR>
<%
If strCounts <= 0 then
'Response.Write "Data was Not Found"( I just playing
around with the code in here)
'Response.Write rsData.RecordCount
'Response.End
'Response.Redirect ("SearchUpdate.asp")
end if
If rsData("Ccount") >= 1 then
Response.Write "Data was Found"
strsql = "Select * From Data Where EmployeeID =" & intID & ""
if rsData.State then
rsData.Close
end if
rsData.Open strsql, Conn
rsData.MoveFirst
End if

%>
Is it possible If I add code in this section?
<TR><TD><P>EmployeeID</P></TD>
<INPUT type="text" name="EmpID"
value="<%=If strCounts <= 0 then "Data wasn't Found" Elseif rsData("Ccount") >=1 then rsData("EmployeeID")%>" ></TD></TR>

I'm not sure this will work and I dont know how to write the code the right way...maybe you can give me suggestion?

thank's
 
Does your code work when you are able to return results? The reason I ask is because looking at your original SQL statement, you either have an extra set of parentheses at the end or you are missing your single quotes surrounding your variable. See one of my previous posts for how that should look.

Otherwise, I am having a hard time seeing why the code won't work using your third post in this thread. Perhaps, before you do your if/then statement, you should response.write the recordcount to see why it may/may not be doing what you expect it to do. Try these and see what happens.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein
 
Hi Chopstik...thank's for your replay...

My code work when I return the result.If the Data exists all information for the data will be show in the table in 2nd Form,
but if the Data doesn't exists it still goto the 2nd form but there is no table only buttons and title are shown.

Eventhough the Data are Exists the response.write "No Data was Found" still show in 2nd form.

I did see you pervious code that you posted for the Select statement for the missing single quotes or extra
parentheses...but my code for SQL statement are working.I did double check the Syntax.

I did also check the code using the Response.Write rsData.Recordset the result is -1

any other suggestion...
thank's
 
When you are returning a recordcount = -1, it usually indicates there is a problem with the way you are returning the recordset. You should probably change the line that opens your recordset similar to the following (adjust to your needs):
Code:
rsData.Open strsql, Conn, adOpenStatic, adLockReadOnly
There is a FAQ written by [ignore]link9[/ignore] which can help to explain the purposes of the Cursor Types and Lock Types - faq333-618. Try to review this and see if this can help you.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein
 
Hi Chopstik..

Thank you for your replay...I figure out how to fixed my code...thank you for your suggestion and help I appreciated.

thank's
 
What was your final resolution on this, if I may ask?

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou
 
actually I'm still using some part of your code and add a new code.It's kind a hard for me to explain it, so I'm gonna post my code, so you can see clearly...thank's for your help....

ResultUpdate.asp

intID = Request.Form ("EmpID")

Set Conn = Server.CreateObject ("ADODB.CONNECTION")
Set rsData = Server.CreateObject ("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

rsData.Open "Select count (*) As Ccount From Data Where EmployeeID =" & intID & "", Conn
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY bgcolor="lightblue">
<CENTER><H1>Update Employee Data</H1></CENTER><BR>

<%
strCounts= rsData("Ccount")

If strCounts >= 1 then
strsql = "Select * From Data Where EmployeeID =" & intID & ""
If rsData.State then
rsData.Close
end If
rsData.Open strsql, Conn
rsData.MoveFirst
end if
%>
<FORM ACTION= "ConfirmUpdate.asp" METHOD=post Name=frmConfirm >
<CENTER>
<% do while not rsData.EOF %>
<TABLE style="WIDTH: 402px; HEIGHT: 172px" cellSpacing=1 cellPadding=1 width=402 border=1>
<TR><TD><P>EmployeeID</P></TD>
<TD><INPUT type="text" name="EmpID"
value="<%If strCounts <= 0 then Response.Write("Data wasn't Found") Else Response.Write(rsData("EmployeeID")) end if%>" ></TD></TR>

<TR><TD><P>EmployeeName</P></TD>
<TD><INPUT type="text" name="EmpName"
value="<%If strCounts <= 0 then Response.Write ("Data wasn't Found") Else Response.Write (rsData("EmployeeName")) end if%>" ></TD></TR>
<TR><TD>EmployeeAddress</TD>
<TD><INPUT type="text" name="EmpAddress"
value="<%If strCounts <= 0 then Response.Write ("Data wasn't Found") Else Response.Write (rsData("EmployeeAddress")) end if%>" ></TD></TR>
<TR><TD>EmployeePhoneHome</TD>
<TD><INPUT type="text" name="EmpPhone"
value="<%If strCounts <= 0 then Response.Write ("Data wasn't Found") Else Response.Write (rsData("EmployeePhoneHome")) end if%>" ></TD></TR>
<TR><TD>EmployeePostalCode</TD>
<TD><INPUT type="text" name="EmpPostal"
value="<%If strCounts <= 0 then Response.Write ("Data wasn't Found") Else Response.Write (rsData("EmployeePostalCode"))end if%>" ></TD></TR>
<TR><TD>EmployeeSalary</TD>
<TD><INPUT type="text" name="EmpSalary"
value="<%If strCounts <= 0 then Response.Write ("Data wasn't Found") Else Response.Write (rsData("EmployeeSalary")) end if%>" ></TD></TR>

<%
rsData.MoveNext
Loop
'rsData.Close
set rsData= Nothing
%>
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top