Problem with Search and Update data

shopgirl · March 2nd, 2004, 05:38 AM

Help Please....Thanks in advance for your help
I Need to Update my data... I create search form first if the user want to update certain data they only need to enter EmployeeIDthis in SearchUpdate.asp form,then the data list show in ResultUpdate.asp form.and the 3rd form is confirmationUpdate.asp form,which for tell the data have been updated or the updated is not working.
My search coding is working I dont know where to put the Update code.I have been trying but still not working and there are no error show either.
this is my code

SearchUpdate.asp

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<FORM METHOD="post" ACTION="ResultUpdate.asp" id=form1 name=form1>
<P><input name="EmployeeID" > <INPUT type="submit" value="Submit" name="B1"></P>
</FORM>
</BODY>
</HTML>

ResultUpdate.asp

<%
Dim varID, varName, varAddress
Dim varPhome, intPostal, varSalary
Dim strsql
Dim Conn
Dim 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")

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 "Data", Conn
strsql = "Select * from Data "
strsql=strsql & "where EmployeeID=" & varID& ";"
Set rsData = Conn.Execute(strsql)

If Request.Form ("submit1") = "UPDATE" then

strsql = "UPDATE DATA SET " &_
         "EmployeeName = '"& varName &"',EmployeeAddress =
         '"& varAddress &"', " & _
         "EmployeePhoneHome = '"& varPhoneHome &"',EmployeePostalCode = '"& varPostalCode &"',EmpployeeSalary = '"& varSalary &"'," &_
         "WHERE EmployeeID = '"& varID &"'"
Response.Write strsql
Conn.Execute strsql
Conn.Close
Set Conn = nothing
end if
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<FORM ACTION= "ConfirmUpdate.asp" METHOD=post Name=frmConfirm >
<TABLE style="WIDTH: 402px; HEIGHT: 172px" cellSpacing=1 cellPadding=1 width=402
border=1>

   <TR><TD><P>EmployeeName</P></TD>
    <TD><INPUT type="text" name="EmpName" value="<%=rsData
        ("EmployeeName")%>" ></TD></TR>
  <TR><TD>EmployeeAddress</TD>
    <TD><INPUT type="text" name="EmpAddress" value="<%=rsData
        ("EmployeeAddress")%>" ></TD></TR>
  <TR><TD>EmployeePhoneHome</TD>
    <TD><INPUT type="text" name="EmpPhone" value="<%=rsData
         ("EmployeePhoneHome")%>" ></TD></TR>
  <TR><TD>EmployeePostalCode</TD>
    <TD><SELECT type="dropdown" name="EmpPostal" value="<%=rsData
         ("EmployeePostalCode")%>" style="WIDTH: 154px" >
        <OPTION>1234</OPTION>
        <OPTION>2345</OPTION></SELECT></TD></TR>
  <TR><TD>EmployeeSalary</TD>
    <TD><INPUT type="text" name="EmpSalary" value="<%=rsData
         ("EmployeeSalary")%>" ></TD></TR>

</TABLE></P>
<INPUT id=submit1 style="WIDTH: 85px; HEIGHT: 40px" type=Submit size=28 value=UPDATE name=submit1></P>

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

any suggestion really Appreciate ......
thank's

Imar · March 2nd, 2004, 06:02 AM

It looks to me that you pass only one variable from page 1 (SearchUpdate) to page 2 (ResultUpdate).

How, then, do you expect page 2 to be able to retrieve the right stuff, like:

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

This should come from the form that has been submitted to this page, not the current form on this page.

Again, I recommend getting a few books about ASP. You seem to be missing a few elementary concepts, that are hard to explain in a web forum.

Cheers,

Imar

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

shopgirl · March 3rd, 2004, 12:21 PM

ooh my god I always forget to pass the variable...
I know you mention before that I need read the ASP book, got the book yesterday and I'm reading it.I still in process to understand the subject, but I have to continue my project, If you don't mind helping me.

In my 1st form I put the EmpID in the session. then in my second page I only show the employeeID because
I called the rest of the data using select statement. Is that right? so I don't have to put the rest of the
fields in session("SName") = Request.Form("EmpName") etc? in my 2nd form? did I make any sense?

DaveGerard · March 3rd, 2004, 02:48 PM

I posted the following on your other posting, but here it is again if it helps.

------

I would recommend NOT using session variables unless you plan on using those variables and their respective values repeatedly in your app, which you are not as you are reseting them in Form3.asp. Just try requesting those values and setting plain old variables. If you need to show those values on the confirmation page, which it doesn't look like you do, then just query those out for that specific ID.

EmployeeId = request("EmployeeId")
EmployeeName = request("EmployeeName")

and so on.

If it were me, I would also go with an update query rather than an insert. Even though it may be a millisecond slower, it will give you more flexability in your select statement to either add a new record or update an existing record.

'--start page2 code--
cn = "Your connection string"

EmployeeId = request("EmployeeId")
EmployeeName = request("EmployeeName")
EmployeeAddress = request("EmployeeAddress")
EmployeePhoneHome = request("EmployeePhoneHome")
EmployeePostalCode = request("EmployeePostalCode")
EmployeeSalary = request("EmployeeSalary")

set rs = server.createobject("adodb.recordset")
sql = "select * from Data where EmployeeId = " & EmployeeId
rs.open sql, cn, 3, 3
if rs.eof then
    rs.addnew
    rs("EmployeeId") = EmployeeId
end if
    rs("EmployeeName") = EmployeeName
    rs("EmployeeAddress") = EmployeeAddress
    rs("EmployeePhoneHome") = EmployeePhoneHome '--this like ET or something?--
    rs("EmployeePostalCode") = EmployeePostalCode
    rs("EmployeeSalary") = EmployeeSalary
    rs.update
'--end page2 code--

That's just my opinion though.

Imar · March 3rd, 2004, 05:06 PM

Unfortunately, you're not making too much sense.

If I were you, I'd draw out an application like this. Take some time to think about design. Try to describe what you're trying to do. e.g.:

1. Page1.asp displays a form, where users can type in an ID to search for a record
2. Page2.asp will accept this record ID, do a database lookup, and display the record found in text boxes so it can be submitted to page3.asp for an UPDATE query
3. Page3.asp collects the info form the form on page 2, constructs an UPDATE query and submits it to the server.

If you are able to describe your application like this, you'll have the following benefits (at least)

1. *You* understand what it is all about. That's good.
2. Anyone updating your site in six months from now (could be you as well) understands what it is all about
3. It's easier to communicate the problem to others, like this forum ;)

So, together with the book and some common sense, we should be able to solve this problem.

If you explain what your requirements are, and how you have layed out your application, I'll take a look and try to suggest a fix.

Cheers,

Imar

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

shopgirl · March 3rd, 2004, 10:01 PM

Thanks Dave but My system doesnt support using recordset and I'm not suppose to using it...Don't ask it's another stroy...

Sorry Imar If my question make no sense...I'm so confused...I have been trying catching up with my Project.
Actually your description about my program is just a little bit different.
What I like to do with my program is:
1.SearchUpdate.asp (is my 1st Form)
  where in this form If user want to update data they need to search
  employeeID.
2.ResultUpdate.asp (2nd form)
  will show all the data based on searching with employeeID, in here
  I'm using Select statement.
  In this form also the user will do some changing in the data.after
  changing the data user click the submit
  button, so the data that have been updated are saved in the table.
3.ConfirmUpdate.asp (3rd Form)
  It's only show message data have been updated.

Actually I will do all the coding only in 2 forms searchUpdate and ResultUpdate forms.

And also I have been putting this code in my ResultUpdate.asp
Session("SName") = Request.Form("EmpName")
Session("Address") = Request.Form("EmpAddress")
Session("PhoneHome") = Request.Form("EmpPhone")
Session("PostalCode") = Request.Form("EmpPostal")
Session("Salary") = Request.Form("EmpSalary")

but someone told me that I dont have to put those code any more because I already using select statement
I only need to show
Session("ID") = Request.Form("EmpID") in the beginning only? is that right?

I Have been trying to do with this code and I make some changes:

searchUpdate.asp
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<FORM METHOD="post" ACTION="ResultUpdate.asp" id=form1 name=form1>
<P><input type="text" name="EmpID" value="<%=session("ID")%>" > <INPUT type="submit" value="Submit" name="B1"></P>
</FORM>
</BODY>
</HTML>

ResultUpdate.asp
<%
Dim strsql
Dim Conn
Dim strConnect
Dim rsData

Session("ID") = 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 "Data", Conn
strsql = "Select * from Data "
strsql=strsql & "where EmployeeID=" &session("ID")& ""
Set rsData = Conn.Execute(strsql)

If Request.Form ("submit1") = "UPDATE" then
strsql = "UPDATE Data " &_
         "SET EmployeeID= '"&session("ID") &"',EmployeeName= '"&session("SName")&"',EmployeeAddress= '"&session("Address")&"', " & _
         "EmployeePhoneHome= '"&session("PhoneHome")&"',EmployeePostalCode= '"&session("PostalCode")&"',EmpployeeSalary= '"&session("Salary")&"'" &_
         "WHERE EmployeeID= '" &session("ID")&"'"
Response.Write strsql
Conn.Execute (strsql)
Conn.Close
end if
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<FORM ACTION= "ConfirmUpdate.asp" METHOD=post Name=frmConfirm >
<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="<%=rsData("EmployeeID")%>" ></TD></TR>
   <TR><TD><P>EmployeeName</P></TD>
    <TD><INPUT type="text" name="EmpName" value="<%=rsData("EmployeeName")%>" ></TD></TR>
  <TR><TD>EmployeeAddress</TD>
    <TD><INPUT type="text" name="EmpAddress" value="<%=rsData("EmployeeAddress")%>" ></TD></TR>
  <TR><TD>EmployeePhoneHome</TD>
    <TD><INPUT type="text" name="EmpPhone" value="<%=rsData("EmployeePhoneHome")%>" ></TD></TR>
  <TR><TD>EmployeePostalCode</TD>
    <TD><INPUT type="text" name="EmpPostal" value="<%=rsData("EmployeePostalCode")%>" > </TD></TR>
  <TR><TD>EmployeeSalary</TD>
    <TD><INPUT type="text" name="EmpSalary" value="<%=rsData("EmployeeSalary")%>" ></TD></TR>

</TABLE></P>
<INPUT id=submit1 style="WIDTH: 85px; HEIGHT: 40px" type=Submit size=28 value=UPDATE name=update></P>
</FORM>
</BODY>
</HTML>

thanks

Imar · March 4th, 2004, 04:02 AM

Hi there,

It looks to me you're missing an important concept, something that is generally referred to as postback.

Here's what happens:

1. Page1: A user types in an empID and hits the submit button.
2. The page POSTs to Page2. The EmpID from Request.Form is used to retrieve the Emp Details and a new form with the details is displayed. This is at the end of the initial load of page2
3. The user changes some details and then hits the Save Details (or whatever) button. The page POSTs back to ResultUpdate.asp where the update is performed. Alternatively, the page POSTs to DoUpdate.asp that does the update.

Right now, ResultUpdate.asp tries to retrieve the values from a form that does not exist. Where is Request.Form ("EmployeeID") defined? Nowhere, as far as I can see. So, this is what you should do if you want to use multiple pages. (You could do this all with one page, but maybe at this time, 3 pages is easier). Make sure that page2 submits back to itself, retrieves the changed values from the form, performs the update and redirects. There is no need for Session variables; you're passing all the required info from page to page:

1. SearchUpdate.asp
On this page, you have a simple form with one text box and a submit button. The form submits to ResultUpdate.asp.

2. ResultUpdate.asp
On ResultUpdate, you check whether the form on that page has already been submitted. If not, you retrieve the ID from the previous form, do a database lookup and display the editable details. Something like this should work:

Code:

If Request.Form("update") = "" Then
  ' First load of the page. Retrieve EmpID from SearchUpdate.asp
  ' EmpID = Request.Form("EmpID")
  ' Retrieve details from database using a SELECT statement
Else
  ' User pressed the Update Details button
  ' Retrieve EmpID from the new form.
  ' Retrieve updated name, address, whatever from the form
  EmpID = Request.Form("EmpID")
  Name = Request.Form("txtName")
  ' Do database update here
  ' When succesful, redirect to ConfirmUpdate.asp
End If

To save the EmpID between the initial page load and the postback, you'll need to add it to a hidden form field somewhere:
<form name="frmUpdateEmp" action="ResultUpdate.asp">
<input type=="hidden" name="EmpID" value="<%=EmpID%>" />

So, in effect, here is the order of pages you see:

1. SearchUpdate.asp
2. ResultUpdate.asp initial load. Show text boxes for edit
3. ResultUpdate.asp PostBack. Page has been submitted back to server
Update is performed and user is redirected to ConfirmUpdate.asp
4. ConfirmUpdate.asp Shows confiirm message

Does this help? It's important you realize what happens in ResultUpdate.asp. This page is hit twice: first to display the old data so the user can edit the details, and then to perform the actual update and redirect the user away.

You already have something very close to this solution. In ResultUpdate you have some code that checks for Request.Form("update"). However, since the page posts to ConfirmUpdate.asp, that code will never be hit. Change the action attribute of ResultUpdate so the page posts back to itself.

The description of the app I gave you was just something I made up. Since I didn't quite understand what you wanted, it was hard to describe your situation ;)

Cheers,

Imar

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

shopgirl · March 5th, 2004, 04:00 AM

My coding are working but I make it with 3 pages, like you told me it's easier with 3 pages and it's true. I thought after My code working with my 3 pages then i will changed with 2 pages , but is not working I dont know why? did i miss something?

ResultUpdate.asp (my 2nd Forms)

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<FORM ACTION= "ResultUpdate.asp" METHOD=post Name=frmConfirm >

<%
   Dim intID, varSName, varAddress
   Dim intPhoneHome, intPostalCode, varSalary
   Dim strsql
   Dim Conn
   Dim strConnect
   Dim 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"

If Request.Form ("update") = "" then
   intID = Request.Form ("EmpID")
strsql = "Select * from Data Where EmployeeID ="&intID&""
rsData.Open strsql, Conn

else
  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 &""
  Response.Write strsql
  Response.Redirect ("ConfirmUpdate.asp")
  Conn.Execute strsql
  Conn.Close
  set Conn = nothing
end if
%>
<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="<%=rsData("EmployeeID")%>" ></TD></TR>
   <TR><TD><P>EmployeeName</P></TD>
    <TD><INPUT type="text" name="EmpName" value="<%=rsData("EmployeeName")%>" ></TD></TR>
  <TR><TD>EmployeeAddress</TD>
    <TD><INPUT type="text" name="EmpAddress" value="<%=rsData("EmployeeAddress")%>" ></TD></TR>
  <TR><TD>EmployeePhoneHome</TD>
    <TD><INPUT type="text" name="EmpPhone" value="<%=rsData("EmployeePhoneHome")%>" ></TD></TR>
  <TR><TD>EmployeePostalCode</TD>
    <TD><INPUT type="text" name="EmpPostal" value="<%=rsData("EmployeePostalCode")%>" > </TD></TR>
  <TR><TD>EmployeeSalary</TD>
    <TD><INPUT type="text" name="EmpSalary" value="<%=rsData("EmployeeSalary")%>" ></TD></TR>

</TABLE></P>

<P>     <INPUT id=submit1 style="WIDTH: 85px; HEIGHT: 40px" type=Submit size=28 value=UPDATE name=update></P>
<P>  </P>
</FORM>
<INPUT Type="hidden" name="EmpID" value="<%=varID%>" >
<INPUT Type="hidden" name="EmpName" value="<%=varSName%>" >
<INPUT Type="hidden" name="EmpAddress" value="<%=varAddress%>" >
<INPUT Type="hidden" name="EmpPhone" value="<%=varPhoneHome%>" >
<INPUT Type="hidden" name="EmpPostal" value="<%=varPostalCode%>" >
<INPUT Type="hidden" name="EmpSalary" value="<%=varSalary%>" >
</BODY>
</HTML>

thanks

Imar · March 5th, 2004, 04:08 AM

I can see at least two problems.

First of all, why do you add all the values to hidden form fields (with the same name) as well? The values are already displayed on the input boxes, so you can't put them in hidden fields as well. If you do, the results will be concatenated at the server, so you'll end up with double values.

Secondly look at this:

Response.Write strsql
Response.Redirect ("ConfirmUpdate.asp")
Conn.Execute strsql

You are redirecting the user away before you execute the UPDATE statement. Change the order of things, and don't forget to comment out the Response.Write line when the UPDATE works.....

Cheers,

Imar

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

shopgirl · March 9th, 2004, 10:14 AM

Thank You Imar...it's working..thank you,thank you, thank you