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 27th, 2006, 02:10 AM
Authorized User
 
Join Date: Feb 2006
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default update record and email the data at the same time

I'm trying to make a page that updates a recordset in a ms access database, the same data must also be send to an email address, I'm using dreamweaver to create asp pages, but dreamweaver has limited functions with asp so I have to modify the code by hand, until sofar this is what i have, but it does not work:

Can anyone help me out with this?


code:


'First lets Dim all the variables we need
Dim U_Mail
Dim U_Body
Dim U_Company
Dim U_Phone
Dim U_PhoneExt
Dim U_PhoneMobile
Dim U_Fax
Dim U_Email
Dim U_Address
Dim U_Amphur
Dim U_Tambon
Dim U_Province
Dim U_PostalCode
Dim U_AltPhone
Dim U_AltPhoneExt
Dim U_EmPrefix
Dim U_EmName
Dim U_EmPhone

'Now lets get some values for the variables from the form
U_Company = Request.Form("company")
U_Phone = Request.Form("phone")
U_PhoneExt = Request.Form("phone_ext")
U_PhoneMobile = Request.Form("phone_mobile")
U_Fax = Request.Form("fax")
U_Email = Request.Form("email")
U_Address = Request.Form("address")
U_Amphur = Request.Form("amphur")
U_Tambon = Request.Form("tambon")
U_Province = Request.Form("province")
U_PostalCode = Request.Form("postalcode")
U_AltPhone = Request.Form("alt_phone")
U_AltPhoneExt = Request.Form("alt_phone_ext")
U_EmPrefix = Request.Form("em_prefix")
U_EmName = Request.Form("em_name")
U_EmPhone = Request.Form("em_phone")

'Now lets build the body of the email from the data in the form
U_Body = "Company: "& U_Company & vbcrlf
U_Body = U_Body & "Phone: "& U_Phone & "Ext: "& U_PhoneExt & vbcrlf & vbcrlf
U_Body = U_Body & "Phone mobile: "& U_PhoneMobile & vbcrlf
U_Body = U_Body & "Alternative phone number: "& U_AltPhone & U_AltPhoneExt & vbcrlf
U_Body = U_Body & "Fax: "& U_Fax & vbcrlf
U_Body = U_Body & "Email: "& U_Email & vbcrlf & vbcrlf
U_Body = U_Body & "Street address: "& U_Address & vbcrlf
U_Body = U_Body & "Tambon: "& U_Tambon & vbcrlf
U_Body = U_Body & "Province: "& U_Province & vbcrlf & vbcrlf
U_Body = U_Body & "Emergency contact details" & vbcrlf
U_Body = U_Body & "Emergency contact person: "& U_EmPrefix & U_EmName & vbcrlf
U_Body = U_Body & "Emergency contact phone: "& U_EmPhone & vbcrlf

'Now lets put the variables and other information we need into the mailing script
Set U_Mail = Server.CreateObject ("CDONTS.NewMail")
U_Mail.From = "mail@mymail.com"
U_Mail.To = "mail@mymail.com"
U_Mail.Subject = "Contact details updated "& U_Company
U_Mail.Body = U_Body
U_Mail.Send
set U_Mail=nothing

' *** 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+successf ully"
  fieldsStr = "login_pass|value|prefix|value|first_name|value|la st_name|value|position|value|phone|value|phone_ext |value|phone_mobile|value|fax|value|email|value|ad dress|value|amphur|value|tambon|value|province|val ue|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,''|Work Phone|',none,''|WorkPhoneExt|',none,''|MobilePhone |',none,''|FaxNumber|',none,''|Email|',none,''|Add ress|',none,''|Amphur|',none,''|Tambon|',none,''|P rovince|',none,''|PostalCode|',none,''|Alternative Phone|',none,''|AlternativePhoneExt|',none,''|Emrg cyContactPrefix|',none,''|EmrgcyContactName|',none ,''|EmrgcyContactPhone|',none,''|DateModified|',no ne,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
 
Old February 27th, 2006, 05:47 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can you be more specific - what doesn't work, do you have any errors returned, if so what are they and on which line?

Thanks,

Chris

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

Quote:
quote:Originally posted by ChrisScott
 Can you be more specific - what doesn't work, do you have any errors returned, if so what are they and on which line?

Thanks,

Chris

Thanks for your reply Chris,

The way I have it now, it mails the data but it does not update the database and I'm getting the following error:

Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/customer/contact_details.asp, line 179


Line 179 =
editCmd.Execute
 
Old February 27th, 2006, 07:09 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

What does line 179 contain?

What do you get if you Response.Write your query to the page?

Cheers,

Chris

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

Quote:
quote:Originally posted by ChrisScott
 What does line 179 contain?

What do you get if you Response.Write your query to the page?

Cheers,

Chris

Thanks for your quick reply chris!
Well, line 179 contains:

editCmd.Execute

It mails the data, but it does not update the database.
Thanks again.

Richard.
 
Old February 27th, 2006, 07:22 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Richard,

What do you get written to the page if you insert the line
Code:
Response.Write editQuery
to your page before your Execute() call?

Cheers,

Chris

 
Old February 27th, 2006, 07:32 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,

What do you get written to the page if you insert the line
Code:
Response.Write editQuery
to your page before your Execute() call?

Cheers,

Chris


update CUSTOMERS set LoginPass = 'test',Prefix = 'Ms.',FirstName = 'test',LastName = 'Zomer',Position = 'Executive',WorkPhone = '456545665',WorkPhoneExt = '4565',MobilePhone = '456546546',FaxNumber = '',Email = 'test@hotmail.com',Address = 'dfsg',Amphur = 'sdf',Tambon = 'sdf',Province = 'bangkok',PostalCode = '10240',AlternativePhone = '',AlternativePhoneExt = '',EmrgcyContactPrefix = 'Mr.',EmrgcyContactName = 'fghf',EmrgcyContactPhone = '456464564',DateModified = '27/2/2549',TimeModified = '18:26:57' where Id = 4

Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/customer/contact_details.asp, line 182



On line 182:

editCmd.Execute


Richard.

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

Hi Richard,

As you are using an access db, if your DateModified and TimeModified fields are date/time fields (rather than string fields), you need to use # to surround values for them in your query rather than '

HTH,

Chris

 
Old February 27th, 2006, 07:52 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,

As you are using an access db, if your DateModified and TimeModified fields are date/time fields (rather than string fields), you need to use # to surround values for them in your query rather than '

HTH,

Chris

Yes those fields are date/time, but everything is working fine when I only use it to update the database or when I only use it to mail, but just both at the same time will nbot work yet..

For updating date/time I use:

          <input name="date" type="hidden" id="date" value="<% response.write FormatDateTime(Now, 2) %>">
          <input name="time" type="hidden" id="time" value="<% response.write FormatDateTime(Now, 3) %>">

thanks again.

Richard.
 
Old February 27th, 2006, 10:56 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

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







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.