p2p.wrox.com: Archives - asp

asp_databases thread: problems with add, edit records
Message #1 by "Gary Cappelletti" <gacapp@a...> on Sun, 13 Oct 2002 16:54:55
Hello everyone,
   This post is slighly different than the other one i have up now.

Again I am in chapter 10 and I am running into problems adding and editing 
records in my table.

I don't know if someis willing to look at my code, but i have copied it 
pretty much word for word from the book and a few things are happening.

First, when I pull up the form for updating a record, the drop-down form 
won't pull in all my records from my table.  It just grabs the first one - 
WHY?

Second, so if I select the only record that the form pulls up and modify 
the contents in the form, I get a syntax error in the update statement.

I don't know why these things are happening, but I would sure appreciate 
someone checking out my pages.

Thanks,
Gary

-----------------------------------------------------------------

Try It Out 3:

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE>Updating a Record in Event table</TITLE>
</HEAD>
<BODY>

<%
'***********************************************************
'* Step 1: Display a list of events to select from
'***********************************************************
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 events
	objRS.Open "Select Event_Name from Events","dsn=Businesses"
%>
	<form action=event3.asp method=post name=frmDisplay>
		<input type=hidden name=FormAction value=Step2>
		<input type=hidden name=txtEvent_Name>
		<table>
			<tr>
			<td colspan=2>Select an event to update</td>
			</tr>
			<tr>
			<td>Event</td>
			<td><select name=cboEvent size="1">
				<option value="<%=objRS("Event_Name")%>"><%
=objRS("Event_Name")%></option>
               <%
			'Loop through the recordset adding each event to 
the combo box
			Do While Not objRS.EOF
%>
<%
				objRS.MoveNext
			Loop

			'Close and dereference database objects
			objRS.Close
			Set objRS = Nothing
%>
				</select></td>
			</tr>
			<tr>
			<td height=60><input type=submit name=btnSubmit 
value=Submit></td>
			</tr>
		</table>
	</form>
	
	<script language=vbscript>
	Sub Window_OnLoad()
		'Save the event currently displayed, just in case the
		'user wants to update this event
		frmDisplay.txtEvent_Name.value = _
			frmDisplay.cboEvent
(frmDisplay.cboEvent.selectedIndex).text
	End Sub
	
	Sub cboEvent_OnClick()
		'Save the event the user selects by clicking on the combo 
box
		frmDisplay.txtEvent_Name.value = _
			frmDisplay.cboEvent
(frmDisplay.cboEvent.selectedIndex).text
	End Sub
	
	Sub cboEvent_OnChange()
		'Save the event the user selects by scrolling the combo box
		'with the arrow keys
		frmDisplay.txtEvent_Name.value = _
			frmDisplay.cboEvent
(frmDisplay.cboEvent.selectedIndex).text
	End Sub
	</script>
	
<%
'***********************************************************
'* Step 2: Display the form for editing a club
'***********************************************************
ElseIf Request.Form("FormAction") = "Step2" Then

	'Create the recordset object
	Set objRS = Server.CreateObject("ADODB.Recordset")

	'Set the SQL string
	strSQL = "Select * from Events Where Event_Name = '" & _
		Request.Form("cboEvent") & "'"
	
	'Open the recordset getting the club details for this club
	objRS.Open strSQL,"dsn=Businesses"
%>
<form action=event4.asp method=post name=frmUpdate>
	<input type=hidden name=Action value=Update>
	<input type=hidden name=txtEvent_Name 
		value=<%=Request.Form("cboEvent")%>>
	
	<table border="0" cellpadding="0" cellspacing="0">
 <tr>
  <td>Event:</td>
  <td><font color=red><%=Request.Form("txtEvent_Name")%></font></td>
 </tr>
<tr>
  <td>Location:</td>
  <td><input type="text" name="txtLocation" size="40" value=<%=objRS
("Location")%>></td>
 </tr>
<tr>
  <td>City:</td>
  <td><input type="text" name="txtCity" size="40" value=<%=objRS("City")%
>></td>
 </tr>
<tr>
  <td>State:</td>
  <td><input type="text" name="txtState" size="40" value=<%=objRS("State")%
>></td>
 </tr>
<tr>
  <td>Zip:</td>
  <td><input type="text" name="txtZip_Code" size="40" value=<%=objRS
("Zip_Code")%>></td>
 </tr>
<tr>
  <td>Description:</td>
  <td><input type="text" name="txtDescription" size="40" value=<%=objRS
("Description")%>></td>
 </tr>
<tr>
  <td>Start Date:</td>
  <td><input type="text" name="txtStart_Date" size="40" value=<%=objRS
("Start_Date")%>></td>
 </tr>
<tr>
  <td>End Date:</td>
  <td><input type="text" name="txtEnd_Date" size="40" value=<%=objRS
("End_Date")%>></td>
 </tr>
<tr>
  <td>Time:</td>
  <td><input type="text" name="txtTime" size="40" value=<%=objRS("Time")%
>></td>
 </tr>
<tr>
  <td>Phone:</td>
  <td><input type="text" name="txtPhone" size="40" value=<%=objRS("Phone")%
>></td>
 </tr>
<tr>
  <td>Website:</td>
  <td><input type="text" name="txtWebsite" size="40" value=<%=objRS
("Website")%>></td>
 </tr>
<tr>
  <td>Email:</td>
  <td><input type="text" name="txtEmail" size="40" value=<%=objRS("Email")%
>></td>
 </tr>
<tr>
  <td>Picture Image Path:</td>
  <td><input type="text" name="txtevent_image" size="40" value=<%=objRS
("event_image")%>></td>
 </tr>
</table>
<p>

<input type="submit" name="btnSubmit" value="Update">


</form>

<%
		'Close and dereference database objects
		objRS.Close
		Set objRS = Nothing

End If	'End If for step processing
%>

</BODY>
</HTML>

---------------------------------------------------------------------

Try It Out 4:

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE>Event update</TITLE>
</HEAD>
<BODY>
Updating the Event Table<br><br>
<%

'Declare variables needed
Dim strInsert
Dim strValues
Dim strSQL
Dim adCmdText
Dim blnCriticalError
Dim blnFirstParameter

'Set required variables
adCmdText = 1

'***********************************************************
'* If an Add was requested, add the event to the database
'***********************************************************
If Request.Form("Action") = "Add" Then
	
	'Start building the SQL strings with the required fields
	strInsert = "Insert into Events (Event_Name,"
	strValues = "Values('" & CStr(Request.Form("txtEvent_Name")) 
& "','"
		
'Add location if present
	If Len(Request.Form("txtLocation")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",Location"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtLocation")) & "'"
	End If
	

	'Add city if present
	If Len(Request.Form("txtCity")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",City"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtCity")) & "'"
	End If

	'Add state if present
	If Len(Request.Form("txtState")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",State"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtState")) & "'"
	End If

	'Add zip if cheked
	If Len(Request.Form("txtZip_Code")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",Zip_Code"
		'Add the value to the value string
		strValues = strValues & ",'" & _
		Cstr(Request.Form("txtZip_Code")) & "'"
	End If

	'Add description if present
	If Len(Request.Form("txtDescription")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",Description"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtDescription")) & "'"
	End If

'Add start date if present
	If Len(Request.Form("txtStart_Date")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",Start_Date"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtStart_Date")) & "'"
	End If

'Add end date if present
	If Len(Request.Form("txtEnd_Date")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",End_Date"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtEnd_Date")) & "'"
	End If



	'Add time if present
	If Len(Request.Form("txtTime")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",Time"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtTime")) & "'"
    End If
    
    'Add phone if present
	If Len(Request.Form("txtPhone")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",Phone"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtPhone")) & "'"
    End If

	'Add website if present
	If Len(Request.Form("txtWebsite")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",Website"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtWebsite")) & "'"
    End If

	'Add email if present
	If Len(Request.Form("txtEmail")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",Email"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtEmail")) & "'"
    End If

	'Add image path if present
	If Len(Request.Form("txtevent_image")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",event_image"
		'Add the value to the value string
		strValues = strValues & ",'" & _
			Cstr(Request.Form("txtevent_image")) & "'"
    End If


	'Create and open the database object
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Open "DSN=Businesses"

	'Create the command object
	Set objCmd = Server.CreateObject("ADODB.Command")

	'Set the command object properties
	Set objCmd.ActiveConnection = objConn
	objCmd.CommandText = strInsert & ") " & strValues & ")"
	objCmd.CommandType = adCmdText

	'Execute the command
	objCmd.Execute
		
	'Display the insert string
	Response.Write "The following insert string was executed and " & _
		"the values inserted into the Events table.<P>"
	Response.Write strInsert & ") " & strValues & ")"
	
'*************************************************************
'* If an Update was requested, update the event in the database
'*************************************************************
ElseIf Request.Form("Action") = "Update" Then

	'Start building the SQL string
	strSQL = "Update Events"
	
	'Set the first parameter flag to true
	blnFirstParameter = True
		
	
'Update location if present
	If Len(Request.Form("txtLocation")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " Location = " 
		Else
			strSQL = strSQL & ", Location = " 
		End If
		strSQL = strSQL &	Cstr(Request.Form("txtLocation"))
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If


	'Update city if present
	If Len(Request.Form("txtCity")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " City = " 
		Else
			strSQL = strSQL & ", City = " 
		End If
		strSQL = strSQL &	Cstr(Request.Form("txtCity"))
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

	'Update state if present
	If Len(Request.Form("txtState")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " State = '" 
		Else
			strSQL = strSQL & ", State = '" 
		End If
		strSQL = strSQL &	Cstr(Request.Form("txtState")) 
& "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

	'Update zip if present
	If (Request.Form("txtZip_Code")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " Zip_Code = '" 
		Else
			strSQL = strSQL & ", Zip_Code = '" 
		End If
		strSQL = strSQL &	Cstr(Request.Form("txtZip_Code")) 
& "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

	'Update description if present
	If Len(Request.Form("txtDescription")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " Description = " 
		Else
			strSQL = strSQL & ", Description = " 
		End If
		strSQL = strSQL &	CStr(Request.Form
("txtDescription"))
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

	'Update start date if present
	If Len(Request.Form("txtStart_Date")) > 0 Then
		'Add the value to the SQL string
			strSQL = strSQL & " Start_Date = '" & _
			Cstr(Request.Form("txtStart_Date")) & "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If
	
'Update end date if present
	If Len(Request.Form("txtEnd_Date")) > 0 Then
		'Add the value to the SQL string
			strSQL = strSQL & " End_Date = '" & _
			Cstr(Request.Form("txtEnd_Date")) & "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

'Update time if present
	If Len(Request.Form("txtTime")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " Time = '" 
		Else
			strSQL = strSQL & ", Time = '" 
		End If
		strSQL = strSQL &	Cstr(Request.Form("txtTime")) & "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If


	'Update phone if present
	If Len(Request.Form("txtPhone")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " Phone = '" 
		Else
			strSQL = strSQL & ", Phone = '" 
		End If
		strSQL = strSQL &	Cstr(Request.Form("txtPhone")) 
& "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

'Update website if present
	If Len(Request.Form("txtWebsite")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " Website = '" 
		Else
			strSQL = strSQL & ", Website = '" 
		End If
		strSQL = strSQL &	Cstr(Request.Form("txtWebsite")) 
& "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

'Update email if present
	If Len(Request.Form("txtEmail")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " Email = '" 
		Else
			strSQL = strSQL & ", Email = '" 
		End If
		strSQL = strSQL &	Cstr(Request.Form("txtEmail")) 
& "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

'Update image path if present
	If Len(Request.Form("txtevent_image")) > 0 Then
		'Add the value to the SQL string
		If blnFirstParameter Then
			strSQL = strSQL & " event_image = '" 
		Else
			strSQL = strSQL & ", event_image = '" 
		End If
		strSQL = strSQL &	Cstr(Request.Form
("txtevent_image")) & "'"
		'Set the first parameter flag to false
		blnFirstParameter = False
	End If

	
	'Set the Where clause
	strSQL = strSQL & " Where Event_Name = '" & _
		Request.Form("txtEvent_Name") & "'"

	'Create and open the database object
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Open "DSN=Businesses"

	'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 Events table.<P>"
	Response.Write strSQL 

End If	'End If for step processing

'Close and dereference database objects
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>

</BODY>
</HTML>
Return to Index
P2P Archives
