Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Web Programming > Adobe Web Programming > Dreamweaver (all versions)
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Dreamweaver (all versions) section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 2nd, 2004, 05:04 PM
Authorized User
 
Join Date: Jun 2003
Location: Dublin, , Ireland.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL2000 Date/Time field update

Hi there.
I hope you's had a Happy New Year!

I'm having a problem trying to update a field in the database (SQL2000) called dtStart which holds the date/time when a customer makes a payment.
I allready have a field that holds the date when they registered like this getdate(), just in case you are womndering why am I not using it,

but I need the dtStart field to update when the customer makes his first payment to that particular order so I could calculate the dtEnd(Yes, another date/time field).
I am using the command update in MX but when I click on submit button it throughs me an error as invalid field after 12:

I tried sending the information from a hidden field value = NOW()
and then request.form("hiden field name"), then I tried using thei directly in the command update as dtStart = A where A = getdate() or Now() or date(). Nothing works.

What's strang is today I couldn't get a drop down list to update a nvchar field. It was purely text as weekly payments. It just gives this error. Incorrect syntax after payments
What am I doing wrong?




L Ion
__________________
L Ion
Reply With Quote
  #2 (permalink)  
Old January 2nd, 2004, 05:28 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi Lucian,

I think you may need to post some code. That will help in diagnosing the problem.

Are you trying to use GetDate() in a WHERE clause? This isn't likely to give you any results. GetDate() returns a date accurate to the millisecond (well, almost), so it isn't likely that any record in your table is going to match that date exactly.

When you post some code, please try to provide just the relevant parts. Dreamweaver is quite verbose in it's coding and it's easier for you to decide what's important and what's not.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #3 (permalink)  
Old January 2nd, 2004, 05:33 PM
Authorized User
 
Join Date: Jun 2003
Location: Dublin, , Ireland.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There is the code for the commmand update:

<%

if(Request.Form("NoRates") <> "") then cmdUpdateFirstPayment__A = Request.Form("NoRates")

if(Request.form("min") <> "") then cmdUpdateFirstPayment__B = Request.form("min")

if(Request.Form("PayMode") <> "") then cmdUpdateFirstPayment__E = Request.Form("PayMode")

if(Request.form("dtStart") <> "") then cmdUpdateFirstPayment__F = Request.form("dtStart")

if(session("MM_UserAuthorization") <> "") then cmdUpdateFirstPayment__C = session("MM_UserAuthorization")

if(Trim(Request.Form("RatesID")) <> "") then cmdUpdateFirstPayment__D = Trim(Request.Form("RatesID"))

%>
<%

set cmdUpdateFirstPayment = Server.CreateObject("ADODB.Command")
cmdUpdateFirstPayment.ActiveConnection = MM_storeSQL_STRING
cmdUpdateFirstPayment.CommandText = "UPDATE dbo.tblRates SET NoRates=" + Replace(cmdUpdateFirstPayment__A, "'", "''") + ", Paid =" + Replace(cmdUpdateFirstPayment__B, "'", "''") + ", PayMode = " + Replace(cmdUpdateFirstPayment__E, "'", "''") + ", dtFirstPay = " + Replace(cmdUpdateFirstPayment__F, "'", "''") + " WHERE CustomerID =" + Replace(cmdUpdateFirstPayment__C, "'", "''") + " and RatesID = " + Replace(cmdUpdateFirstPayment__D, "'", "''") + " "
cmdUpdateFirstPayment.CommandType = 1
cmdUpdateFirstPayment.CommandTimeout = 0
cmdUpdateFirstPayment.Prepared = true
cmdUpdateFirstPayment.Execute()

%>

On the other page in a form I have this hidden field called dtStart value = NOW()

The error I get is this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '21'.
 were 21 is actually the time 21:37

L Ion
Reply With Quote
  #4 (permalink)  
Old January 2nd, 2004, 05:38 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Well, you could try something like this:

Code:
mdUpdateFirstPayment.CommandText = "UPDATE dbo.tblRates  SET 
NoRates=" + Replace(cmdUpdateFirstPayment__A, "'", "''") + ", Paid 
=" + Replace(cmdUpdateFirstPayment__B, "'", "''") + ", PayMode = " + 
Replace(cmdUpdateFirstPayment__E, "'", "''") + ", dtFirstPay = 
GetDate() WHERE
This will let the database handle the Date part. Note that this only works when you're using SQL Server.

For the other problem, write out the Command text just before you execute the SQL statement. Something like this:

Code:
cmdUpdateFirstPayment.Prepared = true
Response.Write("SQL is " & cmdUpdateFirstPayment.CommandText)
Response.End
cmdUpdateFirstPayment.Execute()
This allows you to examine the SQL that is passed to the database, so you can spot the error.


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #5 (permalink)  
Old January 2nd, 2004, 05:49 PM
Authorized User
 
Join Date: Jun 2003
Location: Dublin, , Ireland.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Now I get this error:
Microsoft VBScript compilation error '800a0401'

Expected end of statement

/besecure2000-new/rates_payment.asp, line 28

cmdUpdateFirstPayment.CommandText = "UPDATE dbo.tblRates SET NoRates=" + Replace(cmdUpdateFirstPayment__A, "'", "''") + ", Paid =" + Replace(cmdUpdateFirstPayment__B, "'", "''") + ", PayMode = " + Replace(cmdUpdateFirstPayment__E, "'", "''") + ", dtFirstPay = GetDate() <s>" + Replace(cmdUpdateFirstPayment__F, "'", "''") + </s>" WHERE CustomerID =" + Replace(cmdUpdateFirstPayment__C, "'", "''") + " and RatesID = " + Replace(cmdUpdateFirstPayment__D, "'", "''") + " "
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
the arrow points here: [/s]"<


L Ion
Reply With Quote
  #6 (permalink)  
Old January 2nd, 2004, 06:27 PM
Authorized User
 
Join Date: Jun 2003
Location: Dublin, , Ireland.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Now I am trying to store this date in a string as follow:
dim dtStart
dtStart = Left(request.form("dtStart"),10)
and I use the <%=dtStart%> statement to view an output of the date which looks just fine without the time - but in the database it comes up as 01/01/1900
WHY????????????

L Ion
Reply With Quote
  #7 (permalink)  
Old January 2nd, 2004, 06:33 PM
Authorized User
 
Join Date: Jun 2003
Location: Dublin, , Ireland.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry Imar, but I rushed into it and I copied the code in the email I got.
Now I now what you mean. I tried that before as NOW() or Date() but never tried getdate().

Sorry and Thanks for your help.


L Ion
Reply With Quote
  #8 (permalink)  
Old January 2nd, 2004, 06:41 PM
Authorized User
 
Join Date: Jun 2003
Location: Dublin, , Ireland.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am on to the second part and I get this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'installments'.

/besecure2000-new/rates_payment.asp, line 31

I wrote the SQL statement and it looks liek this:
SQL is UPDATE dbo.tblRates SET NoRates=8, Paid =204.67, PayMode = Weekly installments, dtFirstPay = getdate() WHERE CustomerID =40 and RatesID = 20

Any Ideeas WHY?



L Ion
Reply With Quote
  #9 (permalink)  
Old January 2nd, 2004, 07:57 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Yeah, sorry for that odd post. I hit Send too soon, and then changed the message right away. However, the wrong mail had already been sent.

The problem you mentioned is caused by this:
Code:
PayMode = Weekly installments
Text types, like (n)char, (n)varchar etc should be enclosed by ', like this:

PayMode = 'Weekly installments'

It looks like you're a bit new to all of this, so if that's true I suggest you get a book on various topics like ASP, SQL etc. Real-time debugging through forums isn't gonna get your anywhere quick.

<plug type="shameless">
You may want to take a look here: http://www.wrox.com/books/0764544047.shtml It discusses everything you need, including Dreamweaver MX, ASP, SQL, JavaScript, HTML and CSS and may be just what you need.
</plug>

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #10 (permalink)  
Old January 2nd, 2004, 09:14 PM
Authorized User
 
Join Date: Jun 2003
Location: Dublin, , Ireland.
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Imar. It works like something else.
BTW, I just bought the book you rec. from Amazon.co.uk
I can not wait to get it.
I have so many in my library, and most of them are from wrox publisher.

Have a look @ this website and tell me what you think, if you get a chance.
http://www.besecure2000.com/

L Ion
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Date Field desireemm Access 1 October 16th, 2005 12:25 AM
Trying to update a date field jfrizelle Classic ASP Databases 3 November 4th, 2004 08:55 AM
Date Time Mayhem in SQL2000 dfalconer SQL Server ASP 0 February 9th, 2004 06:57 AM
Update a date field levinll SQL Language 1 January 16th, 2004 02:41 PM
Format Date to time field KennethMungwira VB.NET 3 November 17th, 2003 11:48 AM



All times are GMT -4. The time now is 04:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.