 |
| Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0 |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
February 28th, 2006, 02:16 AM
|
|
Authorized User
|
|
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by ChrisScott
It seems very odd that you get a query syntax error when you send an email, and no problems when you don't.
Are you sure there is no difference in the sql when you get a successful db update - have you tried Response.Write()'ing and comparing the queries?
Cheers,
Chris
|
Hi chris,
I found out that the problem occurs only on the server when testing live.On my local IIS i can update a record without mailing the data, because I don't use a mailserver on my local system.
I don't know exactly what you mean with putting the date and time between #, but everything works when I test it local.
Other record update pages work fine without any problems when testing live, and I also update the date and time the same way.
?
Richard
|
|
February 28th, 2006, 03:30 AM
|
|
Authorized User
|
|
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
page 1 is NOT updating a record when testing live, page 2 is working and updating a record when testing live.
Please take a look at the code below.
Code of update page 1
Code:
<%
' *** Edit Operations: declare variables
Dim editAction
Dim abortEdit
Dim editQuery
Dim editCmd
Dim editConnection
Dim editTable
Dim editRedirectUrl
Dim editColumn
Dim recordId
Dim fieldsStr
Dim columnsStr
Dim fields
Dim columns
Dim typeArray
Dim formVal
Dim delim
Dim altVal
Dim emptyVal
Dim i
editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
editAction = editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If
' boolean to abort record edit
abortEdit = false
' query string to execute
editQuery = ""
%>
<%
' *** Update Record: set variables
If (CStr(Request("update")) = "update_form" And CStr(Request("recordId")) <> "") Then
editConnection = conn_string
editTable = "CUSTOMERS"
editColumn = "Id"
recordId = "" + Request.Form("recordId") + ""
editRedirectUrl = "/?content=Customer+contact+details+updated+successfully"
fieldsStr = "login_pass|value|prefix|value|first_name|value|last_name|value|position|value|phone|value|phone_ext|value|phone_mobile|value|fax|value|email|value|address|value|amphur|value|tambon|value|province|value|postalcode|value|alt_phone|value|alt_phone_ext|value|em_prefix|value|em_name|value|em_phone|value|date|value|time|value"
columnsStr = "LoginPass|',none,''|Prefix|',none,''|FirstName|',none,''|LastName|',none,''|Position|',none,''|WorkPhone|',none,''|WorkPhoneExt|',none,''|MobilePhone|',none,''|FaxNumber|',none,''|Email|',none,''|Address|',none,''|Amphur|',none,''|Tambon|',none,''|Province|',none,''|PostalCode|',none,''|AlternativePhone|',none,''|AlternativePhoneExt|',none,''|EmrgcyContactPrefix|',none,''|EmrgcyContactName|',none,''|EmrgcyContactPhone|',none,''|DateModified|',none,NULL|TimeModified|',none,NULL"
' create the fields and columns arrays
fields = Split(fieldsStr, "|")
columns = Split(columnsStr, "|")
' set the form values
For i = LBound(fields) To UBound(fields) Step 2
fields(i+1) = CStr(Request.Form(fields(i)))
Next
' append the query string to the redirect URL
If (editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
editRedirectUrl = editRedirectUrl & "?" & Request.QueryString
Else
editRedirectUrl = editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Update Record: construct a sql update statement and execute it
If (CStr(Request("update")) <> "" And CStr(Request("recordId")) <> "") Then
' create the sql update statement
editQuery = "update " & editTable & " set "
For i = LBound(fields) To UBound(fields) Step 2
formVal = fields(i+1)
typeArray = Split(columns(i+1),",")
delim = typeArray(0)
If (delim = "none") Then delim = ""
altVal = typeArray(1)
If (altVal = "none") Then altVal = ""
emptyVal = typeArray(2)
If (emptyVal = "none") Then emptyVal = ""
If (formVal = "") Then
formVal = emptyVal
Else
If (altVal <> "") Then
formVal = altVal
ElseIf (delim = "'") Then ' escape quotes
formVal = "'" & Replace(formVal,"'","''") & "'"
Else
formVal = delim + formVal + delim
End If
End If
If (i <> LBound(fields)) Then
editQuery = editQuery & ","
End If
editQuery = editQuery & columns(i) & " = " & formVal
Next
editQuery = editQuery & " where " & editColumn & " = " & recordId
If (Not abortEdit) Then
' execute the update
Set editCmd = Server.CreateObject("ADODB.Command")
editCmd.ActiveConnection = editConnection
editCmd.CommandText = editQuery
editCmd.Execute
editCmd.ActiveConnection.Close
If (editRedirectUrl <> "") Then
Response.Redirect(editRedirectUrl)
End If
End If
End If
%>
Code of update page 2
Code:
<%
' *** Edit Operations: declare variables
Dim editAction
Dim abortEdit
Dim editQuery
Dim editCmd
Dim editConnection
Dim editTable
Dim editRedirectUrl
Dim editColumn
Dim recordId
Dim fieldsStr
Dim columnsStr
Dim fields
Dim columns
Dim typeArray
Dim formVal
Dim delim
Dim altVal
Dim emptyVal
Dim i
editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
editAction = editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If
' boolean to abort record edit
abortEdit = false
' query string to execute
editQuery = ""
%>
<%
' *** Update Record: set variables
If (CStr(Request("update")) = "update" And CStr(Request("recordId")) <> "") Then
editConnection = conn_string
editTable = "SITE_CONTENT_ENG"
editColumn = "ID"
recordId = "" + Request.Form("recordId") + ""
editRedirectUrl = "/2006/edit/updated/"
fieldsStr = "page_title|value|page_header|value|link_name|value|content|value|date|value|time|value"
columnsStr = "PAGE_TITLE|',none,''|PAGE_HEADER|',none,''|LINK_NAME|',none,''|PAGE_CONTENT|',none,''|TIMESTAMP_DATE|',none,NULL|TIMESTAMP_TIME|',none,NULL"
' create the fields and columns arrays
fields = Split(fieldsStr, "|")
columns = Split(columnsStr, "|")
' set the form values
For i = LBound(fields) To UBound(fields) Step 2
fields(i+1) = CStr(Request.Form(fields(i)))
Next
' append the query string to the redirect URL
If (editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
editRedirectUrl = editRedirectUrl & "?" & Request.QueryString
Else
editRedirectUrl = editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Update Record: construct a sql update statement and execute it
If (CStr(Request("update")) <> "" And CStr(Request("recordId")) <> "") Then
' create the sql update statement
editQuery = "update " & editTable & " set "
For i = LBound(fields) To UBound(fields) Step 2
formVal = fields(i+1)
typeArray = Split(columns(i+1),",")
delim = typeArray(0)
If (delim = "none") Then delim = ""
altVal = typeArray(1)
If (altVal = "none") Then altVal = ""
emptyVal = typeArray(2)
If (emptyVal = "none") Then emptyVal = ""
If (formVal = "") Then
formVal = emptyVal
Else
If (altVal <> "") Then
formVal = altVal
ElseIf (delim = "'") Then ' escape quotes
formVal = "'" & Replace(formVal,"'","''") & "'"
Else
formVal = delim + formVal + delim
End If
End If
If (i <> LBound(fields)) Then
editQuery = editQuery & ","
End If
editQuery = editQuery & columns(i) & " = " & formVal
Next
editQuery = editQuery & " where " & editColumn & " = " & recordId
If (Not abortEdit) Then
' execute the update
Set editCmd = Server.CreateObject("ADODB.Command")
editCmd.ActiveConnection = editConnection
editCmd.CommandText = editQuery
editCmd.Execute
editCmd.ActiveConnection.Close
If (editRedirectUrl <> "") Then
Response.Redirect(editRedirectUrl)
End If
End If
%>
|
|
February 28th, 2006, 05:29 AM
|
|
Friend of Wrox
|
|
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Hi Richard,
Very frustrating.
I take it you are still getting the update syntax error?
If so, I would try removing all fields from your update statement and then try adding them back in until you find which one(s) is causing the error to occur.
Access very often used to barf when supplied with dates surrounded by ' and # used to solve this issue in many cases, that was why I suggested it.
HTH,
Chris
|
|
February 28th, 2006, 06:27 AM
|
|
Authorized User
|
|
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by ChrisScott
Hi Richard,
Very frustrating.
I take it you are still getting the update syntax error?
If so, I would try removing all fields from your update statement and then try adding them back in until you find which one(s) is causing the error to occur.
Access very often used to barf when supplied with dates surrounded by ' and # used to solve this issue in many cases, that was why I suggested it.
HTH,
Chris
|
Hi Chris,
Yes I'm still getting the syntax error, local testing results zero problems, once uploaded and test the files live it doesn't work..
The site is located on a server in Bangkok, that server is using thai date format, and I use a "normal" date format on my local server.
But that is not the problem, since 1 page is working fine, wich is updating the date and time in default format(non thai).
I just checked the database time date fields, but could not find the problem there either, is it possible that the webhost can set the "maximum amount of fields updating" in one time ?
Yes frustrating!
Thanks again for your reply:)
Richard
|
|
February 28th, 2006, 06:34 AM
|
|
Authorized User
|
|
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:
Access very often used to barf when supplied with dates surrounded by ' and # used to solve this issue in many cases, that was why I suggested it.
|
Ok, I will try it with using #
'|DateModified|',none,NULL|TimeModified|',none,NUL L"
#|DateModified|#,none,NULL|TimeModified|#,none,NUL L"
As above?
|
|
February 28th, 2006, 06:41 AM
|
|
Authorized User
|
|
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:
Access very often used to barf when supplied with dates surrounded by ' and # used to solve this issue in many cases, that was why I suggested it.
|
Chris,
I just tested it with using #, but I'm getting the same error...
Microsoft JET Database Engine error '80040e14'
Syntax error in UPDATE statement.
/customer/contact_details.asp, line 117
The code on line 117:
editCmd.Execute
Richard
|
|
February 28th, 2006, 06:41 AM
|
|
Friend of Wrox
|
|
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Hi Richard,
Quote:
|
quote:is it possible that the webhost can set the "maximum amount of fields updating" in one time
|
I think that this is unlikely.
If your live server is using a different datetime format, IMHO you would be better off converting your dates / times to ISO format.
Have you tried removing the various fields from your update query and then adding back in - this will isolate which fields are causing the problems?
Cheers,
Chris
|
|
March 1st, 2006, 01:44 AM
|
|
Authorized User
|
|
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by ChrisScott
Have you tried removing the various fields from your update query and then adding back in - this will isolate which fields are causing the problems?
|
Hi Chris,
I have found the problem, in the database I had a field named "Position", for some weird reason the fieldname "Position"  did not work on the live server and I renamed the database field to "CustPosition" and it worked. I can't see any reason why that fieldname was causing such a problem, is the word "Position" a reserved asp function on my live server?
About 5 years ago I had a same problem at another asp webhost with a fieldname "email"!?...
Chris thanks for your help!
Anywayz, everything is working now as expected.:D
Richard.
|
|
March 1st, 2006, 03:40 AM
|
|
Friend of Wrox
|
|
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Hi Richard,
Excellent! Glad it's working :)
It looks like your live server's jet drivers may be running in a different mode, resulting in a different reserved word set...
http://support.microsoft.com/default...b;EN-US;321266
Cheers,
Chris
|
|
|
