PDA

Click to See Complete Forum and Search --> : edit customer info.

impulse
11-07-2006, 09:15 AM
Need some advice and guidance in the right direction. I'm creating a page called idEdit.asp, this takes the customer id from a previous page and let a user who has permission to update the customer information. The customer info is pulled from the database and display.

See code of idEdit.asp:
<%@LANGUAGE="VBSCRIPT"%>
<% Option Explicit %>
<%
'define variables
Dim custID
Dim strUserName
Dim strPassword
Dim intSLevel
Dim strFN
Dim strIdDet
Dim objConn
Dim objRS
Dim SQL

'get passed variable
custID = Request.Form("id")

'get other sessions
strUserName = Session("username")
strPassword = Session("userpass")
intSLevel = Session("sLevel")
strFN = Session("fn")


' Now, what custID are we going to get the details of???
strIdDet = CLng( "0" & Request("custID") )
If strIdDet = 0 Then
' No custID passed in, so assume it's available from session variable
strIdDet = CLng( "0" & Session("custID") )
If strIdDet = 0 Then
Response.Write "No custID available for making SQL query. Aborting."
Response.End ' or redirect or or
End If
Else
' custID *was* passed in, so set or update session variable
Session("custID") = strIdDet
End If ' this replaces the END IF you had at bottom of page, before

' open connection to the database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\db\IMPCustomers.mdb"

' set SQL statement
SQL = "SELECT * FROM tblCustomerInfo WHERE intCustID = " & strIdDet

' prepare recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objConn, 3, 3

' display all of the customer's information
%>

<html>
<body>

<table>
<form method="post" action="ckEdit.asp" name="edit">
...snip...
<tr>
<td style="text-align:right;">E-mail:</td>
<td style="text-align:left;"><input type="text" name="cemail" value="<%=Response.Write(objRS("strCustEmail"))%>" size="25" maxlength="25" /></td>
</tr>
<tr>
<td style="text-align:right;">Sales Representative:</td>
<td style="text-align:left;"><%=Response.Write(objRS("strCustSalesRep"))%></td>
</tr>
<tr>
<td style="text-align:right;">IMPulse NC Contact:</td>
<td style="text-align:left;"><%=Response.Write(objRS("strCustEmpContact"))%></td>
</tr>
<tr>
<td height="38" colspan="2" style="text-align:center;">
<input type="submit" name="edit" value="Edit Customer" />
<a href="logOut.asp" style="text-decoration:none;"><input type="button" name="logOut" value="Log Out" /></a></td>
</tr>
</form>
</table>
</body>
</html>


The user can then change the customer's information in the form. Then select submit which goes to ckEdit.asp. This page allows the user to double check the data they entered in before it is update. I would like for this page to display the all previous info from idEdit.asp and allow the user to change the Sales Representative and Contact info of the customer if needed.

See code for ckEdit.asp:
<%@LANGUAGE="VBSCRIPT"%>
<% Option Explicit %>
<%
'define variables
Dim custID
Dim strUserName
Dim strPassword
Dim intSLevel
Dim strFN
Dim strIdDet
Dim ncsr
Dim strNewCrs
Dim ncempcontact
Dim strNewContact
Dim objConn
Dim objRS
Dim SQL

'get passed variable
custID = Request.Form("id")

'get other sessions
strUserName = Session("username")
strPassword = Session("userpass")
intSLevel = Session("sLevel")
strFN = Session("fn")
'strIdDet = Session("custID")

' Now, what custID are we going to get the details of???
strIdDet = CLng( "0" & Request("custID") )
If strIdDet = 0 Then
' No custID passed in, so assume it's available from session variable
strIdDet = CLng( "0" & Session("custID") )
If strIdDet = 0 Then
Response.Write "No custID available for making SQL query. Aborting."
Response.End ' or redirect or or
End If
Else
' custID *was* passed in, so set or update session variable
Session("custID") = strIdDet
End If ' this replaces the END IF you had at bottom of page, before

' check to see if passed form value is the same
If Request.Form("strCustSalesRep") <> "ncsr" Then
If strCustSalesRep = "SELECTED" Then
ncsr = "IMPulse NC, INC."

Else
Session("ncsr") = strNewCrs
End If
End If

If Request.Form("strCustEmpContact") <> "ncempcontact" Then
If ncempcontact = "SELECTED" Then
ncempcontact = "Sales & Customer Service"

Else
Session("ncempcontact") = strNewContact
End If
End If

' open connection to the database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\db\IMPCustomers.mdb"

' set SQL statement
SQL = "SELECT * FROM tblCustomerInfo WHERE intCustID = " & strIdDet
'Response.Write(SQL)
'Response.End()

' prepare recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objConn, 3, 3

' display all of the customer's information
%>


<html>
<body>
<table>
<form method="post" action="edited.asp" name="edit">
...snip...
<tr>
<td style="text-align:right;">Fax:</td>
<td style="text-align:left;"><input type="text" name="cfax" value="<%=Response.Write(Request.Form("cfax"))%>" size="25" maxlength="25" /></td>
</tr>
<tr>
<td style="text-align:right;">Mobile:</td>
<td style="text-align:left;"><input type="text" name="cmobile" value="<%=Response.Write(Request.Form("cmobile"))%>" size="25" maxlength="25" /></td>
</tr>
<tr>
<td style="text-align:right;">E-mail:</td>
<td style="text-align:left;"><input type="text" name="cemail" value="<%=Response.Write(Request.Form("cemail"))%>" size="25" maxlength="25" /></td>
</tr>
<tr>
<td style="text-align:right;">Sales Representative:</td>
<td style="text-align:left;">
<select name="ncsr">
<option value="??">-- SELECT ONE --</option>
<option value="IMPulse NC, INC." <%If objRS.Fields("strCustSalesRep") = "IMPulse" Then Response.Write("selected")%>>IMPulse NC</option>
<option value="Bob Poetsch" <%If objRS.Fields("strCustSalesRep") = "Bob Poetsch" Then Response.Write("selected")%>>Bob Poetsch</option>
<option value="Chris Dennison" <%If objRS.Fields("strCustSalesRep") = "Chris Dennison" Then Response.Write("selected")%>>Chris Dennison</option>
<option value="Chris Pagni" <%If objRS.Fields("strCustSalesRep") = "Chris Pagni" Then Response.Write("selected")%>>Chris Pagni</option>
<option value="Hemant Khemka" <%If objRS.Fields("strCustSalesRep") = "Hemant Khemka" Then Response.Write("selected")%>>Hemant Khemka</option>
<option value="Ignacio Kohler Dios" <%If objRS.Fields("strCustSalesRep") = "Ignacio Kohler Dios" Then Response.Write("selected")%>>Ignacio Kohler Dios</option>
<option value="Mike Cottone" <%If objRS.Fields("strCustSalesRep") = "Mike Cottone" Then Response.Write("selected")%>>Mike Cottone</option>
<option value="Ricardo Capiello" <%If objRS.Fields("strCustSalesRep") = "Ricardo Capiello" Then Response.Write("selected")%>>Ricardo Capiello</option>
<option value="Scott Johnson" <%If objRS.Fields("strCustSalesRep") = "Scott Johnson" Then Response.Write("selected")%>>Scott Johnson</option>
<option value="Timothy Tang" <%If objRS.Fields("strCustSalesRep") = "Timothy Tang" Then Response.Write("selected")%>>Timothy Tang</option>
<option value="Zheng Li" <%If objRS.Fields("strCustSalesRep") = "Zheng Li" Then Response.Write("selected")%>>Zheng Li</option>
</select></td>
</tr>
<tr>
<td style="text-align:right;">IMPulse NC Contact:</td>
<td style="text-align:left;">
<select name="ncempcontact">
<option value="??">-- SELECT ONE --</option>
<option value="Engineering" <%If objRS.Fields("strCustEmpContact") = "Engineering" Then Response.Write("selected")%>>Engineering</option>
<option value="Finance" <%If objRS.Fields("strCustEmpContact") = "Finance" Then Response.Write("selected")%>>Finance</option>
<option value="General Manager/President" <%If objRS.Fields("strCustEmpContact") = "General Manager/President" Then Response.Write("selected")%>>General Manager/President</option>
<option value="Manufacturing" <%If objRS.Fields("strCustEmpContact") = "Manufacturing" Then Response.Write("selected")%>>Manufacturing</option>
<option value="Marketing" <%If objRS.Fields("strCustEmpContact") = "Marketing" Then Response.Write("selected")%>>Marketing</option>
<option value="Purchasing" <%If objRS.Fields("strCustEmpContact") = "Purchasing" Then Response.Write("selected")%>>Purchasing</option>
<option value="Quality" <%If objRS.Fields("strCustEmpContact") = "Quality" Then Response.Write("selected")%>>Quality</option>
<option value="Sales & Customer Service" <%If objRS.Fields("strCustEmpContact") = "Sales & Customer Service" Then Response.Write("selected")%>>Customer Service</option>
<option value="Sales Representative" <%If objRS.Fields("strCustEmpContact") = "Sales Representative" Then Response.Write("selected")%>>Sales Representative</option>
</select>
</td>
</tr>
<tr>
<td height="38" colspan="2" style="text-align:center;">
<input type="submit" name="edit" value="Edit Customer" />
<a href="logOut.asp" style="text-decoration:none;"><input type="button" name="logOut" value="Log Out" /></a></td>
</tr>
</form>
</table>
</body>
</html>



Then edited.asp does the update.

Am I over thinking this process? I'm still some what of a newbie. Is there a better way to handle this task?
so_is_this
11-07-2006, 11:07 AM
Personally, I use just two pages for my maintenance processes. The first page is a one-line tabular display (possibly, with paging) of all rows in the database -- not, necessarily, all columns but just key information and at least one description column. The second page displays a single database row and all editable and informational columns related to that single row. This page submits to itself for validation purposes and can be used for adds, updates, and/or deletes of a single database row.
impulse
11-08-2006, 07:27 AM
On my edited.asp page I'm receiving the following message. Why? I have checked the db and it does update.

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


<%@LANGUAGE="VBSCRIPT"%>
<% Option Explicit %>
<%
Dim strUserName
Dim strPassword
Dim intSLevel
Dim strIdDet
Dim objConn
Dim objRS
Dim SQL
Dim idToEdit

'get pass parameters
strUserName = Session("username")
strPassword = Session("userpass")
intSLevel = Session("sLevel")
strIdDet = Request.Form("custID")

'check to see if idToEdit is blank
idToEdit = Trim("" & Request("custID"))
Response.Write "idToEdit is [" & idToEdit & "]. Is it blank? " & (idToEdit = "") & "<hr>"
If idToEdit = "" Then
Response.Write "No custID available for making SQL query. Aborting."
Response.End ' or redirect or or

Else

' custID *was* passed in, so set or update session variable
Session("custID") = idToEdit

' open connection to the database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\db\IMPCustomers.mdb"

' set SQL statement
SQL = "SELECT * FROM tblCustomerInfo WHERE intCustID = " & idToEdit

' prepare recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objConn, 3, 3

Do While Not objRS.EOF
objRS("strCustLName") = Request.Form("clname")
objRS("strCustCompany") = Request.Form("ccomp")
objRS("strCustJobTitle") = Request.Form("cjob")
objRS("strCustAdd1") = Request.Form("cadd1")
objRS("strCustAdd2") = Request.Form("cadd2")
objRS("strCustCity") = Request.Form("ccity")
objRS("strCustState") = Request.Form("cstate")
objRS("strCustZipCode") = Request.Form("czip")
objRS("strCustCountry") = Request.Form("ccountry")
objRS("strCustBus") = Request.Form("cbus")
objRS("strCustFax") = Request.Form("cfax")
objRS("strCustMobile") = Request.Form("cmobile")
objRS("strCustEmail") = Request.Form("cemail")
objRS("strCustSalesRep") = Request.Form("ncsr")
objRS("strCustEmpContact") = Request.Form("cempcontact")
objRS.Update
objRS.MoveNext
Loop
'End If
%>

<!DOCTYPE html PUBLIC "-//W3C/DTD/ XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<!-- This is the title of the page -->
<title>IMPulse NC Customer Database - Customer Details</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<!-- This starts the CSS for the page -->
<style type="text/css">
body {
font-family: Arial, Helvetica, sans-serif;
font-size: relative;
font-weight: bold;
}
table {
background-color:#cccccc;
border-color:#CCCCCC;
}
</style>
<!-- CSS ends -->

<link href="dbcss.css" rel="stylesheet" type="text/css" />
</head>
<body>
<table width="80%" border="1">
<tr>
<td>
<img src="art/dblogo.gif" width="322" height="74" />
</td>
</tr>
<table width="80%" border="1">
<tr>
<td colspan="2">Changes made to <%=objRS("strCustFName")%>&nbsp;<%=objRS("strCustLName")%> were successful. The database has been updated. Thank you <%=strUserName%> for your update.
</td>
</tr>
<tr>
<td>
<form action="custAdd.asp">
<input type="submit" name="Added" value="Add Customer" />
</form>
</td>
<form action="results.asp">
<input type="hidden" name="custID" value="<%=strIdDet%>" />
<input type="submit" name="submit" value="Search Results" />
</form>
</tr>
</table>
</table>
</body>
</html>

<%

' closes the objRS and objConn
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

End If

' writes passed sessions to the browser for debugging
Response.Write(Session("custID")) & "<br />"
Response.Write(Session("username")) & "<br />"
Response.Write(Session("userpass")) & "<br />"
Response.Write(Session("sLevel")) & "<br />"
Response.Write "My query looks like " & SQL & ""
%>
impulse
11-08-2006, 10:26 AM
Problem solved - changed td colspan="2">Changes made to <%=objRS("strCustFName")%>&nbsp;<%=objRS("strCustLName")%> were successful. The database has been updated. Thank you <%=strUserName%> for your update. </td>

to

<td colspan="2">Changes made to <%=Request.Form("cfname")%>&nbsp;<%=Request.Form("clname")%> were successful. The database has been updated. Thank you <%=strFN%> for your update.
</td>