Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old February 11th, 2004, 06:02 PM
Authorized User
 
Join Date: Dec 2003
Location: Middletown, CT, USA.
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need help inserting a date

I am having a brain freeze and need help. I have a routine that inserts a record into a database. I NOW want it to auto include the current date. The 'date' field in my db is called date.

What is the proper value that i use...i presume it is date() with some sort of character, ie ampersand or quotation, but I cannot get the right syntax



Set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout=10
conn.Open "DSN=mydatabase"
sqlStr="INSERT INTO roster(ssn,first,last,address,city,state,zip,pin,d ate)"
sqlStr=sqlStr & "VALUES('" & ssn & "','" & fname & "','" & lname & "','" & address & "','" & city & "','" & st & "','" & zip & "','" & pin & "',date())"
conn.Execute sqlStr

conn.Close
Set conn=nothing

Thanks
John


  #2 (permalink)  
Old February 11th, 2004, 09:37 PM
Friend of Wrox
Points: 2,376, Level: 20
Points: 2,376, Level: 20 Points: 2,376, Level: 20 Points: 2,376, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , Australia.
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

jrwlkn,
If your using SQL Server, MSDE (Probably Access, not sure) you need to use getDate() at the database or date() at the server.
eg
SQL Server
sqlStr="INSERT INTO roster(ssn,first,last,address,city,state,zip,pin,r Date)"
sqlStr=sqlStr & "VALUES('" & ssn & "','" & fname & "','" & lname & "','" & address & "','" & city & "','" & st & "','" & zip & "','" & pin & "',getDate())"

or In Code
sqlStr="INSERT INTO roster(ssn,first,last,address,city,state,zip,pin,r Date)"
sqlStr=sqlStr & "VALUES('" & ssn & "','" & fname & "','" & lname & "','" & address & "','" & city & "','" & st & "','" & zip & "','" & pin & "','" & date() & "')"

BUT BUT BUT

I stongly recommend you call your field anything but 'date'. Date is used as a keyword in almost every database and language. It will, at best, need to be treated differently in the statement like [date] and at worst you will wrestle with for ages, get nowhere, then change it.

In the examples i have used rDate.



======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
  #3 (permalink)  
Old February 23rd, 2004, 02:38 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Minneapolis, MN, USA.
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post
Default

Try this. Let me know how it works.

MyDate = date()

set rs = server.createobject("adodb.recordset")
sql = "select * from Roster"
rs.open sql, "dsn=MyDatabase", 3, 3
    rs.addnew
    rs("ssn") = ssn
    rs("first") = first
    rs("last") = last
    rs("address") = address
    rs("city") = city
    rs("state") = state
    rs("zip") = zip
    rs("pin") = pin
    rs("date") = MyDate
    rs.update



Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting Date in Sql Server lamdog ASP.NET 1.0 and 1.1 Basics 5 March 18th, 2005 01:05 PM
Inserting/updating with empty date field takabyte Classic ASP Databases 3 December 23rd, 2004 02:14 PM
SQL2000 and inserting Date value lucian Dreamweaver (all versions) 1 November 26th, 2004 12:22 PM
DTS Import ( Date string to Date field) gfowajuh SQL Server 2000 1 September 30th, 2003 06:28 AM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





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