|
 |
access_asp thread: Help with UPDATE stmt
Message #1 by "NAJ" <noni_j@h...> on Tue, 22 Oct 2002 16:15:07
|
|
I am still having a time with my update stmt. I used the example in ASP
Databases Ch. 10 as a template. I need to update any fields that may have
been changed. Below is the code for the update form:
Update.asp:
<%
varEmployeeID = Request.Form("Employee_ID")
dim oRSp
Set oRSp=server.createobject("ADODB.Recordset")
sqltext="Select * FROM Employees INNER JOIN FutureCurve"
sqltext=sqltext & " ON Employees.EmployeeID=FutureCurve.EmployeeID"
sqltext=sqltext & " WHERE Employees.EmployeeID='" & varEmployeeID & "';"
oRSp.open sqltext, "dsn=noni"
%>
<FORM ACTION="noni_response.asp" METHOD="POST" name=frmUpdate>
<input type=hidden name=Action value=Update>
<table>
<tr>
<td height = "22" colspan=2></td>
</tr>
<tr>
<td>Future curve</td>
<td><font color=navy><%=oRSp("FutureCurve")%></font></td>
</tr>
<table>
<tr>
<td>GOAL: The goal of the "Future Curve" is to enhance
our Individual Development Plan (IDP) preparation and implementation
with a tool to facilitate continued supervisor and
employee interaction regarding "Employee Development".</td>
</tr>
<tr>
<td><br>DIRECTIONS: Complete the information below, keeping in
mind that your training and educational objectives may be met through
developmental
assignments, formal training and/or a combination of
both. Print, sign and date this form and discuss the contents with your
immediate or acting supervisor during your performance
review, but not later than 30 June. Your Future Curve should answer the
following questions:</td>
</tr>
<tr>
<td>5. What skills and personal attributes do you currently enjoy
employing in your daily tasks?<p>
<textarea rows=6 name=txtQuestion1 cols=40><%=oRSp("Question1")%
></textarea></td><br>
</tr>
<tr>
<td>6. What types of work assignments do you find the most
challenging and rewarding?<p>
<textarea rows=6 name=txtQuestion2 cols=40><%=oRSp("Question2")%
></textarea></td><br>
</tr>
<tr>
<td>7. Other than the typical federal promotional career path,
what short-term (1-5 years) professional goals do you have?
Long term (5 to 10) professional goals.<p>
<textarea rows=6 name=txtQuestion3 cols=40><%=oRSp("Question3")%
></textarea></td><br>
</tr>
<td>8. Which, if any, of the four requirements portfolios can you
most closely identify with at this stage of your career?
How do the career goals you defined above relate to the
requirements portfolio(s)?
&nb
sp;
<p>
<textarea rows=6 name=txtQuestion4 cols=40><%=oRSp("Question4")%
></textarea></td><br>
</tr>
<td>9. What skills do you need to further develop to achieve
these goals? What course(s) of study or training program(s)
will help you develop your skills and move you towards
reaching the goals you have established? (This is your input to the
Individual Development Plan)<p>
<textarea rows=6 name=txtQuestion5 cols=40><%=oRSp("Question5")%
></textarea></td><br>
</tr>
<td>10. How would the development of the skills you have
identified above benefit the command? The taxpayer? The warfighter?<p>
<textarea rows=6 name=txtQuestion6 cols=40><%=oRSp("Question6")%
></textarea></td><br>
</tr>
</table>
<br><br><br>
<tr>
<td height=60><input type=button name=btnSubmit value=SUBMIT></td>
</tr>
</table>
</form>
<SCRIPT LANGUAGE=vbscript>
Sub btnSubmit_OnClick()
'If we make it this far then submit the form
Call frmUpdate.submit()
End Sub
</SCRIPT>
Here is the code for the response page UpdateResponse.asp:
<%
'Declare variables needed
Dim strInsert
Dim strValues
Dim strSQL
Dim adCmdText
Dim blnFirstParameter
'Set required variables
adCmdText = 1
'***********************************************************
'* If an Add was requested, add the new club to the database
'***********************************************************
If Request.Form("Action") = "Add" Then
'Start building the SQL strings with the required fields
strInsert = "Insert into FutureCurve (FutureCurve"
strValues = "Values ('" & CStr(Request.Form("txtFutureCurve"))
& "'"
'Add EmployeeID
If Len (Request.Form("txtEmployeeID")) > 0 Then
'Add the employees ID to the insert string
strInsert = strInsert & ", EmployeeID"
'Add the value to the value string
strValues = strValues & ",'" &_
Cstr(Request.Form("txtEmployeeID")) & "'"
End If
'Add Question 1
If Len (Request.Form("txtQuestion1")) > 0 Then
'Add the employees answer to question 1 to the insert
string
strInsert = strInsert & ", Question1"
'Add the value to the value string
strValues = strValues & ",'" &_
Cstr(Request.Form("txtQuestion1")) & "'"
End If
'Add Question 2
If Len (Request.Form("txtQuestion2")) > 0 Then
'Add the employees answer to question 2 to the insert
string
strInsert = strInsert & ", Question2"
'Add the value to the value string
strValues = strValues & ",'" &_
Cstr(Request.Form("txtQuestion2")) & "'"
End If
'Add Question 3
If Len (Request.Form("txtQuestion3")) > 0 Then
'Add the employees answers to question 3 to the insert
string
strInsert = strInsert & ", Question3"
'Add the value to the value string
strValues = strValues & ",'" &_
Cstr(Request.Form("txtQuestion3")) & "'"
End If
'Add Question 4
If Len (Request.Form("txtQuestion4")) > 0 Then
'Add the employees answers to question 4 to the insert
string
strInsert = strInsert & ", Question4"
'Add the value to the value string
strValues = strValues & ",'" &_
Cstr(Request.Form("txtQuestion4")) & "'"
End If
'Add Question 5
If Len (Request.Form("txtQuestion5")) > 0 Then
'Add the employees answers to question 5 to the insert
string
strInsert = strInsert & ", Question5"
'Add the value to the value string
strValues = strValues & ",'" &_
Cstr(Request.Form("txtQuestion5")) & "'"
End If
'Add Question 6
If Len (Request.Form("txtQuestion6")) > 0 Then
'Add the employees answers to question 6 to the insert
string
strInsert = strInsert & ", Question6"
'Add the value to the value string
strValues = strValues & ",'" &_
Cstr(Request.Form("txtQuestion6")) & "'"
End If
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=noni"
'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 Employees table.<P>"
Response.Write strInsert & ") " & strValues & ")"
'**************************************************************************
***
'If an Update is requested, update the new information in the employees
table
'**************************************************************************
***
ElseIf Request.Form("Action") = "Update" Then
'Start building the SQL string
@@@@@@@ strSQL = "Update FutureCurve Set (SELECT * FROM Employees INNER
JOIN FutureCurve ON Employees.EmployeeID=FutureCurve.EmployeeID)"@@@@@
'Set the first parameter flag to true
blnFirstParameter = True
'Update Question 1 if present
If Len(Request.Form("txtQuestion1")) > 0 Then
'Add the value to the SQL string
strSQL = strSQL & " Question1 = '" & _
Cstr(Request.Form("txtQuestion1")) & "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update Question 2 if present
If Len(Request.Form("txtQuestion2")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " Question2 = '"
Else
strSQL = strSQL & ", Question2 = '"
End If
strSQL = strSQL & Cstr(Request.Form("txtQuestion2"))
& "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update Question 3 if present
If Len(Request.Form("txtQuestion3")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " Question3 = '"
Else
strSQL = strSQL & ", Question3 = '"
End If
strSQL = strSQL & Cstr(Request.Form("txtQuestion3"))
& "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update Question 4 if present
If Len(Request.Form("txtQuestion4")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " Question4 = '"
Else
strSQL = strSQL & ", Question4 = '"
End If
strSQL = strSQL & Cstr(Request.Form("txtQuestion4"))
& "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update Question 5 if present
If Len(Request.Form("txtQuestion5")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " Question5 = '"
Else
strSQL = strSQL & ", Question5 = '"
End If
strSQL = strSQL & Cstr(Request.Form("txtQuestion5"))
& "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update Question 6 if present
If Len(Request.Form("txtQuestion6")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " Question6 = '"
Else
strSQL = strSQL & ", Question6 = '"
End If
strSQL = strSQL & Cstr(Request.Form("txtQuestion6"))
& "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Set the Where clause
@@@@@@@ strSQL= strSQL & " WHERE Employees.EmployeeID='" & varEmployeeID
& "';"@@@@@@@@@@@@
'Create and open the database object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=noni"
'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 FutureCurve table.<P>"
Response.Write strSQL
End If
'Close and dereference database objects
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
When I execute the pages I get the following error:
Syntax error in UPDATE statement.
@@@@ denotes the update stmts. Someone PLEASE help.
Thanx
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 23 Oct 2002 15:01:45 +1000
|
|
Hi,
I suggest you alter your code at each point where you are getting an error
executing an update statement. Change your code so that it does a
Response.Write() of your SQL statement, then calls Response.End *before*
your current objConn.Execute strSQL
<%
Response.Write(strSQL)
Response.End
objConn.Execute strSQL
%>
This way you can see what you are trying to send to the database. If you are
still having a problem working out what is wrong, then please post the
*output* of the Response.Write() statement.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "NAJ" <noni_j@h...>
Subject: [access_asp] Help with UPDATE stmt
: I am still having a time with my update stmt. I used the example in ASP
: Databases Ch. 10 as a template. I need to update any fields that may have
: been changed. Below is the code for the update form:
:
: Update.asp:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<snipped for your viewing sanity>
|
|
|
View Cart
