Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
|
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
 
Old February 28th, 2006, 02:16 AM
Authorized User
 
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 28th, 2006, 03:30 AM
Authorized User
 
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

%>

 
Old February 28th, 2006, 05:29 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old February 28th, 2006, 06:27 AM
Authorized User
 
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 28th, 2006, 06:34 AM
Authorized User
 
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old February 28th, 2006, 06:41 AM
Authorized User
 
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 28th, 2006, 06:41 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old March 1st, 2006, 01:44 AM
Authorized User
 
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old March 1st, 2006, 03:40 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Update one record jemacc SQL Server 2000 0 September 12th, 2007 11:47 AM
not update all record mohiddin52 Access VBA 10 May 9th, 2007 11:41 AM
cannot update record keyvanjan ASP.NET 1.0 and 1.1 Basics 0 September 9th, 2006 05:25 AM
You can't save this record at this time avd Access 4 June 12th, 2006 08:29 AM
Inserting a Record and sending an email ambirC Classic ASP Databases 2 October 30th, 2003 07:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.