help with my Search code !!

shopgirl · February 25th, 2004, 06:18 AM

Hi...I need help Pllleeeeessseeee....

I have Code where I need to find EmployeeID. In form SearchUpdate I just put dropdown box so the user could choose employeeid and for ResultUpdate form will show the all data I dont know where I went wrong...could someone pleasssseeee see my code.....

SearchUpdate.asp
<%
Dim varID, varName, varAddress
Dim varPhome, intPostal, varSalary
Dim strsql, Conn, strConnect
Dim rsData

varID = Request.Form ("EmployeeID")
varName = Request.Form ("EmployeeName")
varAddress = Request.Form ("EmployeeAddress")
varPhome = Request.Form ("EmployeePhoneHome")
intPostal = Request.Form ("EmployeePostalCode")
varSalary = Request.Form ("EmployeeSalary")

Session ("EmployeeID") = varID
Session ("EmployeeName") = varName
Session ("EmployeeAddress") = varAddress
Session ("EmployeePhoneHome") = varPhome
Session ("EmployeePostalCode") = intPostal
Session ("EmployeeSalary") = varSalary

strConnect= "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\TblEmploy.mdb"

Set Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open strConnect
%>
<FORM NAME=frmSearchUpdate ACTION="ResultUpdate.asp" METHOD="post" >

<P> Type Employee ID   <SELECT
      style="WIDTH: 135px" name="search"> <OPTION selected></OPTION>
      <OPTION>567</OPTION>
      <OPTION>678</OPTION>
      <OPTION>909</OPTION>
      </SELECT></P>
<INPUT id=submit1 type=submit size=21 value=Search name=submit1>

ResultUpdate.asp
<%
Dim varID, varName, varAddress
Dim varPhome, intPostal, varSalary
Dim rsData
Dim Conn
Dim strSQl

varID = Session("EmployeeID")
varName = Session ("EmployeeName")
varAddress = Session ("EmployeeAddress")
varPhome = Session ("EmployeePhoneHome")
intPostal = Session ("EmployeePostalCode")
varSalary = Session ("EmployeeSalary")

Set Conn= Server.CreateObject ("ADODB.Connection")

strConnect= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

Set rsData = Server.CreateObject ("ADODB.Recordset")
strsql= "SELECT * FROM Data WHERE EmployeeID = '"& session(varID)&"'"
%>
<TR><TD>EmployeeID</TD>
<TD><%=rsData("EmployeeID")%></TD></TR>
<TR><TD>EmployeeName</TD>
<TD><%=rsData("EmployeeName")%></TD></TR>
<TR><TD>EmployeeAddress</TD>
<TD><%=rsData("EmployeeAddress")%></TD></TR>
<TR><TD>EmployeePHome</TD>
<TD><%=rsData("EmployeePhoneHome")%></TD></TR>
<TR><TD>EmployeePostalCode</TD>
<TD><%=rsData("EmployeePostalCode")%></TD></TR>
<TR><TD>EmployeeSalary</TD>
<TD><%=rsData("EmployeeSalary")%></TD></TR>
<INPUT id=submit1 style="WIDTH: 95px; HEIGHT: 46px" type=submit size=31 value=UPDATE name=submit1

I got some error message:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/Nur/Project1_ASP/ResultUpdate.asp, line 40

Browser Type:
Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)

Page:
POST 25 bytes to /Nur/Project1_ASP/ResultUpdate.asp

POST Data:
search=678&submit1=Search

Thank's

planoie · February 25th, 2004, 10:20 AM

It looks like you have a mismatch on a field name. Verify that all the fields you are trying to retrieve in the code match up to fields in the DB table.

Imar · February 25th, 2004, 10:30 AM

It looks to me that you never open the Recordset. So, after you have created it using CreateObject, it doesn't contain meta data nor records:

Code:

strConnect= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

Set rsData = Server.CreateObject ("ADODB.Recordset")
strsql= "SELECT * FROM Data WHERE EmployeeID = '"& session(varID)&"'"

You also need something like this:

Code:

Set rsData = Conn.Execute(strsql)

If you use the Execute method of the Connection object, you no longer need to create the Recordset object explicitly, using the CreateObject call.

You also should clean up your page a bit; some stuff is assigned a value twice....

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

DaveGerard · February 25th, 2004, 06:18 PM

It appears that you really don't have a session variable called Session("varId"), but rather one called Session("EmployeeId").

shopgirl · February 26th, 2004, 12:08 AM

I have been trying your suggestion I don't know why its still doesn't work...I also check the field name are correct. heres mya new code maybe someone can see where I still do wrong.

SearchUpdate.asp
Dim varID, varName, varAddress
Dim varPhome, intPostal, varSalary
Dim strsql, Conn, strConnect
Dim rsData

varID = Request.Form ("EmployeeID")
varName = Request.Form ("EmployeeName")
varAddress = Request.Form ("EmployeeAddress")
varPhome = Request.Form ("EmployeePhoneHome")
intPostal = Request.Form ("EmployeePostalCode")
varSalary = Request.Form ("EmployeeSalary")

strConnect= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

Set Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open strConnect
%>
<FORM NAME=frmSearchUpdate ACTION="ResultUpdate.asp" METHOD="POST" >
<TR><P> Type Employee ID   <SELECT name="Search">
      <OPTION selected >567</OPTION>
      <OPTION >678</OPTION>
      <OPTION >909</OPTION>
      </SELECT></P>
<INPUT id=submit1 style="WIDTH: 63px; HEIGHT: 35px" type=submit size=21 value=Search name=submit1>

ResultUpdate.asp
Dim varID, varName, varAddress
Dim varPhome, intPostal, varSalary
Dim rsData
Dim Conn
Dim strSQl

varID = Session("EmployeeID")
varName = Session ("EmployeeName")
varAddress = Session ("EmployeeAddress")
varPhome = Session ("EmployeePhoneHome")
intPostal = Session ("EmployeePostalCode")
varSalary = Session ("EmployeeSalary")

Set Conn= Server.CreateObject ("ADODB.Connection")
strConnect= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

Set rsData = Server.CreateObject ("ADODB.Recordset")
rsData.Open "SELECT * FROM Data WHERE EmployeeID = '"&Session("EmployeeID")& "'"
rsData.Open strSQL
%>
<TR><TD>EmployeeName</TD>
<TD><%=rsData("EmployeeName")%></TD></TR>
<TR><TD>EmployeeAddress</TD>
<TD><%=rsData("EmployeeAddress")%></TD></TR>
<TR><TD>EmployeePHome</TD>
<TD><%=rsData("EmployeePhoneHome")%></TD></TR>
<TR><TD>EmployeePostalCode</TD>
<TD><%=rsData("EmployeePostalCode")%></TD></TR>
<TR><TD>EmployeeSalary</TD>
<TD><%=rsData("EmployeeSalary")%></TD></TR>
<INPUT id=submit1 type=submit size=31 value=UPDATE name=submit1>

And know I got different error message like this :
Error Type:
ADODB.Recordset (0x800A0E7D)
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
/Nur/Project1_ASP/ResultUpdate.asp, line 30

Browser Type:
Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)

Page:
POST 25 bytes to /Nur/Project1_ASP/ResultUpdate.asp

POST Data:
Search=678&submit1=Search

any suggestion? Thanks

Imar · February 26th, 2004, 03:49 AM

Right, I see it's actually two pages, not one.

Why are you declaring all those variables and the connection on SearchUpdate? You're not really using them. Or isn't this all the code for the page? All you need in that page is the <form> element.

In ResultUpdate.asp, you're not using an open connection with your recordset:

ResultUpdate.asp

Code:

<%
Dim varID, varName, varAddress
Dim varPhome, intPostal, varSalary
Dim rsData 
Dim Conn
Dim strSQl

' You're getting this from the Session. Aren't you supposed 
' to get this from the Request.Form collection instead?
' That way you get the stuff te user filled in on the previous page
varID = Session("EmployeeID") 
varName = Session ("EmployeeName")
varAddress = Session ("EmployeeAddress")
varPhome = Session ("EmployeePhoneHome")
intPostal = Session ("EmployeePostalCode")
varSalary = Session ("EmployeeSalary")

Set Conn = Server.CreateObject ("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TblEmploy.mdb"

'You were not opening the Connection
Conn.Open strConnect

' This should be varID, no?
strSQL = "SELECT * FROM Data WHERE EmployeeID = '" & varID "'"

' Assign the Recordset by using the Execute method.
' See http://p2p.wrox.com/topic.asp?TOPIC_ID=10224 for more details
Set rsData = Conn.Execute(strSQL)

If Not rsData.EOF Then' If the record was found

%>
<TR><TD>EmployeeName</TD>
<TD><%=rsData("EmployeeName")%></TD></TR>
<TR><TD>EmployeeAddress</TD>
<TD><%=rsData("EmployeeAddress")%></TD></TR>
<TR><TD>EmployeePHome</TD>
<TD><%=rsData("EmployeePhoneHome")%></TD></TR>
<TR><TD>EmployeePostalCode</TD>
<TD><%=rsData("EmployeePostalCode")%></TD></TR>
<TR><TD>EmployeeSalary</TD>
<TD><%=rsData("EmployeeSalary")%></TD></TR>   
<INPUT id=submit1 type=submit size=31 value=UPDATE name=submit1>

<%
Else
  Response.Write("No Records Found")
End If
%>

I haven't tested this, of course, but it should work roughly like this.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

shopgirl · February 26th, 2004, 05:14 AM

Thank You thank you thank you Imar and everybody who has been helping me...it's works like a charm.....
I need to ask one more question regarding request.form and session.
If in my 1st form I only use as entry the datas so in my 2nd form i only use request.form is that right?
thank's

Imar · February 26th, 2004, 05:38 AM

Well, of course it depends on your requirements, but usually, yes, that's the way to do it.

Page1 has an HTML form where a user can type in information. This information is then submitted to Page2. On Page2, you can use Request.Form("txtMyTextField") to get the information out of the form from the previous page. So, there is usually no need to use Session variables.

Alternatively, you can have the page submit to itself. So, SearchUpdate.asp has a form with an action attribute of SearchUpdate.asp. The advantage of this is that you can show the values your user search for again in the appropriate textboxes when you reload the page.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

shopgirl · February 26th, 2004, 06:14 AM

Sorry I have another question if you dont mind it's kind a similar problem different task do I need to make new Topic? heres the problem I have 2 forms, Form 1 just entering data, in form 2 show the current data from form 1 but the data not yet been save in the table, but after click the submit then the data will be save using insert statement.in this situation I should use the request.form in form 2?
thanks

Imar · February 26th, 2004, 06:30 AM

A new topic makes things clearer and easier, IMO.

Anyway, yes. Again you need the form stuff to retrieve the info from page1.

How you deal with the second page (e.g. insert the data after the user has confirmed) depends on what you want. Like I said in my previous post, you could have Page2 submit to itself, so the form fields are available in the same page when the user has confirmed the data to be inserted.

If I were you, I'd get a book about ASP. A good understanding of the technology will really help speed things up. I recommend Beginning Active Server Pages 3.0 and Beginning Dreamweaver MX (2004)

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.