|
 |
asp_databases thread: Narrowing data selection based on more than one field
Message #1 by oneil_brown@h... on Mon, 22 Oct 2001 20:05:10
|
|
Below is my syntax to update a record, the problem is that the selection
is to broad, I am not getting the specific record I am looking for. I am
a beginner and would like some help on how I can solve this problem. I
would like to have the users select the last name and first name fields.
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<%
'Declare variables needed
Dim strSQL
Dim adCmdText
'Set required variables
adCmdText = 1
'Step 1: Display a list of Employee names to select
IF Len(Request.Form("FormAction")) = 0 Then
'Create the recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
'Open the recordset getting a list of all employees
objRS.Open "Select lname from phone ORDER BY lname " , "DSN=phone1"
%>
<FORM ACTION=CPHONEDIRECTORY2.ASP METHOD=Post NAME=frmDisplay>
<INPUT TYPE=hidden NAME=FormAction VALUE=Step2>
<TABLE>
<TR>
<TD COLSPAN=2>Select an Employee to Update</TD>
</TR>
<TR>
<TD>Last Name</TD>
<TD><Select Name = cbophone>
<%
'Loop through the recordset adding last and first name to the combo box
Do While Not objRS.EOF
%>
<option Value="<%=objRS("lname")%>">
<%=objRS("lname")%></option>
<%
objRS.MoveNext
Loop
'Close and dereference database objects
objRS.Close
Set objRS = Nothing
%>
</Select></TD>
<TR>
<TD Height=60><INPUT TYPE=submit Name=btnSubmit VALUE=Submit></TD>
</TR>
</TABLE>
</FORM>
<%
'Step 2:Display the update Form
ElseIf Request.Form("FormAction") = "Step2" Then
'Instruct VBScript to ignore the error and continue
'with the next line of code
On Error Resume Next
'Build the SQL string
strSQL = "Select * from phone where lname = '" & _
Cstr(Request.Form("cbophone")) & "'"
'Create the recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
'Open the recordset getting the employee details
objRS.Open strSQL, "DSN=phone1"
%>
<FORM ACTION =CPHONEDIRECTORY2.ASP METHOD=Post Name=frmUpdate>
<INPUT TYPE=hidden Name=FormAction Value=step3>
<Input Type=text Name=txtlname value="<%=objRS("lname")%>">
<TABLE>
<TR>
<TD>Last Name</TD>
<TD><Font color=navy><%=objRS("lname")%></font></TD>
</TR>
<TR>
<TD NOWRAP>First Name</TD>
<TD><Font color=navy><%=objRS("fname")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Nick Name</TD>
<TD><Font color=navy><%=objRS("nname")%></font></TD>
</TR>
<TR>
<TD NOWRAP>TiTle</TD>
<TD><Font color=navy><%=objRS("title")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Extension</TD>
<TD><Font color=navy><%=objRS("extension")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Department</TD>
<TD><Font color=navy><%=objRS("department")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Location</TD>
<TD><Font color=navy><%=objRS("location")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Box Number</TD>
<TD><Font color=navy><%=objRS("boxnumber")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Audix</TD>
<TD><Font color=navy><%=objRS("audix")%></font></TD>
</TR>
<TR>
<TD NOWrap>Departmental Hint</TD>
<TD><Input type=text name=txtkeyfeature size = 20 value=<%=objRS
("keyfeature")%>></TD>
</TR>
<TR>
<TD Height=60>
<Input type=button name=btnSubmit value=submit></TD>
</TR>
</TABLE>
</Form>
<%
'Close and dereference database objects
objRS.Close
set objRS = Nothing
%>
<SCRIPT LANGUAGE=vbscript>
Sub btnSubmit_Onclick
Call frmUpdate.submit()
End Sub
</SCRIPT>
<%
ElseIf Request.Form("FormAction") = "step3" Then
'build the update string
strSQL = "Update phone SET "& _
"keyfeature = '" & _
Cstr(Request.Form("txtkeyfeature")) & "' " & _
"WHERE lname = '" & _
Cstr(Request.Form("txtlname")) & "'"
'Create and open the database object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=phone1"
'Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")
'Set the command object properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strSQL
objCmd.CommandType = adCmdText
'Execute the command
objCmd.Execute
'Display the update string
Response.Write "The following update string was executed and " & _
"the values updated in the phone table.<P>"
Response.Write strSQL
'Close and dereference database objects
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
End IF 'End if for step processing
%>
</BODY>
</HTML>
Message #2 by Kyle Burns <kburns@c...> on Mon, 22 Oct 2001 16:26:04 -0500
|
|
Your best bet is to retrieve the primary key in your query and use that to
idenity the record. This is the only way to make absolute sure that you are
processing the correct record.
=================================
Kyle M. Burns, MCSD
ECommerce Technology Manager
Centra Credit Union
kburns@c...
-----Original Message-----
From: oneil_brown@h... [mailto:oneil_brown@h...]
Sent: Monday, October 22, 2001 3:05 PM
To: ASP Databases
Subject: [asp_databases] Narrowing data selection based on more than one
field
Below is my syntax to update a record, the problem is that the selection
is to broad, I am not getting the specific record I am looking for. I am
a beginner and would like some help on how I can solve this problem. I
would like to have the users select the last name and first name fields.
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<%
'Declare variables needed
Dim strSQL
Dim adCmdText
'Set required variables
adCmdText = 1
'Step 1: Display a list of Employee names to select
IF Len(Request.Form("FormAction")) = 0 Then
'Create the recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
'Open the recordset getting a list of all employees
objRS.Open "Select lname from phone ORDER BY lname " , "DSN=phone1"
%>
<FORM ACTION=CPHONEDIRECTORY2.ASP METHOD=Post NAME=frmDisplay>
<INPUT TYPE=hidden NAME=FormAction VALUE=Step2>
<TABLE>
<TR>
<TD COLSPAN=2>Select an Employee to Update</TD>
</TR>
<TR>
<TD>Last Name</TD>
<TD><Select Name = cbophone>
<%
'Loop through the recordset adding last and first name to the combo box
Do While Not objRS.EOF
%>
<option Value="<%=objRS("lname")%>">
<%=objRS("lname")%></option>
<%
objRS.MoveNext
Loop
'Close and dereference database objects
objRS.Close
Set objRS = Nothing
%>
</Select></TD>
<TR>
<TD Height=60><INPUT TYPE=submit Name=btnSubmit VALUE=Submit></TD>
</TR>
</TABLE>
</FORM>
<%
'Step 2:Display the update Form
ElseIf Request.Form("FormAction") = "Step2" Then
'Instruct VBScript to ignore the error and continue
'with the next line of code
On Error Resume Next
'Build the SQL string
strSQL = "Select * from phone where lname = '" & _
Cstr(Request.Form("cbophone")) & "'"
'Create the recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
'Open the recordset getting the employee details
objRS.Open strSQL, "DSN=phone1"
%>
<FORM ACTION =CPHONEDIRECTORY2.ASP METHOD=Post Name=frmUpdate>
<INPUT TYPE=hidden Name=FormAction Value=step3>
<Input Type=text Name=txtlname value="<%=objRS("lname")%>">
<TABLE>
<TR>
<TD>Last Name</TD>
<TD><Font color=navy><%=objRS("lname")%></font></TD>
</TR>
<TR>
<TD NOWRAP>First Name</TD>
<TD><Font color=navy><%=objRS("fname")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Nick Name</TD>
<TD><Font color=navy><%=objRS("nname")%></font></TD>
</TR>
<TR>
<TD NOWRAP>TiTle</TD>
<TD><Font color=navy><%=objRS("title")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Extension</TD>
<TD><Font color=navy><%=objRS("extension")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Department</TD>
<TD><Font color=navy><%=objRS("department")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Location</TD>
<TD><Font color=navy><%=objRS("location")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Box Number</TD>
<TD><Font color=navy><%=objRS("boxnumber")%></font></TD>
</TR>
<TR>
<TD NOWRAP>Audix</TD>
<TD><Font color=navy><%=objRS("audix")%></font></TD>
</TR>
<TR>
<TD NOWrap>Departmental Hint</TD>
<TD><Input type=text name=txtkeyfeature size = 20 value=<%=objRS
("keyfeature")%>></TD>
</TR>
<TR>
<TD Height=60>
<Input type=button name=btnSubmit value=submit></TD>
</TR>
</TABLE>
</Form>
<%
'Close and dereference database objects
objRS.Close
set objRS = Nothing
%>
<SCRIPT LANGUAGE=vbscript>
Sub btnSubmit_Onclick
Call frmUpdate.submit()
End Sub
</SCRIPT>
<%
ElseIf Request.Form("FormAction") = "step3" Then
'build the update string
strSQL = "Update phone SET "& _
"keyfeature = '" & _
Cstr(Request.Form("txtkeyfeature")) & "' " & _
"WHERE lname = '" & _
Cstr(Request.Form("txtlname")) & "'"
'Create and open the database object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=phone1"
'Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")
'Set the command object properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strSQL
objCmd.CommandType = adCmdText
'Execute the command
objCmd.Execute
'Display the update string
Response.Write "The following update string was executed and " & _
"the values updated in the phone table.<P>"
Response.Write strSQL
'Close and dereference database objects
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
End IF 'End if for step processing
%>
</BODY>
</HTML>
|
|
|
View Cart
