navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

Microsoft MVP

 

Moving from Access to SQL datetime issue.

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> Moving from Access to SQL datetime issue.
Page: [1]
 
vqitinc

 

Posts: 64
Joined: 11/14/2006
From: California
Status: offline

 
Moving from Access to SQL datetime issue. - 7/4/2008 12:51:25   
I am migrating an application from MS Accss to MS SQL 2005. Most things moved OK, however, datetime is, of course an issue.

I need to insert a record into the database including a datetime field. On the data entry form, I have a simple JAVA Calendar date picker that places a string in a text box formatted like 02/02/2008. This worked fine for Access but not for SQL.

How can I use the current method to insert the date in SQL? Can I convert the 02/02/2008 to 20080202 00:00:00?

I currently use an if statement to ensure there is no null values for the date fields.

Here is my insert code:

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<% 
If IsDate(Request.Form("PickupDate")) Then 
PickupDate="#"&Request.Form("PickupDate")&"#" 
Else PickupDate="Null"
End If 
If IsDate(Request.Form("DeliveryDate")) Then 
DeliveryDate="#"&Request.Form("DeliveryDate")&"#" 
Else DeliveryDate="Null" 
End If
If IsDate(Request.Form("CardExpireDate")) Then 
CardExpireDate="#"&Request.Form("CardExpireDate")&"#" 
Else CardExpireDate="Null" 
End If
%>
<%
fp_sQry="INSERT INTO Shpmt (Accessorial01, Accessorial02, Accessorial03, Accessorial04, Accessorial05, AccessorialPrice01, AccessorialPrice02, AccessorialPrice03, AccessorialPrice04, AccessorialPrice05, FuelSurchargePrice, BillTo, DAddress1, DAddress2, DCellPhone, DCity, DCounty, DeliveryFlightsPrice, DeliveryFlightsQty, DeliveryPrice, DEmail, DestInstructions, DExt1, DExt2, DFax1, DFirstName, DLastName, DPrefix, DState, DTelephone1, DTelephone2, DZip, GeneralInstructions, OAddress1, OAddress2, OCellPhone, OCity, OCounty, OEmail, OExt1, OExt2, OFax1, OFirstName, OLastName, OPrefix, OriginFlightsPrice, OriginFlightsQty, OriginInstructions, OState, OTelephone1, OTelephone2, OZip, PickUpPrice, SalesRep, ModifiedDate, Contractor, BillToParty, PickupDate, DeliveryDate, Item, ItemMake, ItemModel, ItemSerialNo, ItemYear, ItemEstWeight, HeardAbout,  CreditCardType, NameOnCard, CardNumber, CardExpireDate, CardSecurityCode, HeardAboutOther, ReferredByOther, Bonus, BFirstName, BLastName, BAddress1, BAddress2, BCity, BState, BZip, ReferredBy, PaymentApproveCode, HoldStatus, HoldReason) VALUES ('::Accessorial01::', '::Accessorial02::', '::Accessorial03::', '::Accessorial04::', '::Accessorial05::', ::AccessorialPrice01::, ::AccessorialPrice02::, ::AccessorialPrice03::, ::AccessorialPrice04::, ::AccessorialPrice05::, ::FuelSurchargePrice::, '::BillTo::', '::DAddress1::', '::DAddress2::', '::DCellPhone::', '::DCity::', '::DCounty::', ::DeliveryFlightsPrice::, '::DeliveryFlightsQty::', ::DeliveryPrice::, '::DEmail::', '::DestInstructions::', '::DExt1::', '::DExt2::', '::DFax1::', '::DFirstName::', '::DLastName::', '::DPrefix::', '::DState::', '::DTelephone1::', '::DTelephone2::', '::DZip::', '::GeneralInstructions::', '::OAddress1::', '::OAddress2::', '::OCellPhone::', '::OCity::', '::OCounty::', '::OEmail::', '::OExt1::', '::OExt2::', '::OFax1::', '::OFirstName::', '::OLastName::', '::OPrefix::', ::OriginFlightsPrice::, '::OriginFlightsQty::', '::OriginInstructions::', '::OState::', '::OTelephone1::', '::OTelephone2::', '::OZip::', ::PickUpPrice::, '::SalesRep::', '::ModifiedDate::', '::Contractor::', '::BillToParty::', "&PickupDate&", "&DeliveryDate&", '::Item::', '::ItemMake::', '::ItemModel::', '::ItemSerialNo::', '::ItemYear::', '::ItemEstWeight::', '::HeardAbout::',  '::CreditCardType::', '::NameOnCard::', '::CardNumber::', "&CardExpireDate&", '::CardSecurityCode::', '::HeardAboutOther::', '::ReferredByOther::', ::Bonus::, '::BFirstName::', '::BLastName::', '::BAddress1::', '::BAddress2::', '::BCity::', '::BState::', '::BZip::', '::ReferredBy::', '::PaymentApproveCode::', '::HoldStatus::', '::HoldReason::')"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%""></td></tr>"
fp_sDataConn="smssql"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>


Thanks

Allan Browning
BeTheBall

 

Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

 
RE: Moving from Access to SQL datetime issue. - 7/4/2008 23:46:33   
The problem is likely your delimiters. Access uses the #, while SQL uses a single quote. Try replacing all the #s with '

_____________________________

Duane

Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.

(in reply to vqitinc)
vqitinc

 

Posts: 64
Joined: 11/14/2006
From: California
Status: offline

 
RE: Moving from Access to SQL datetime issue. - 7/6/2008 14:12:31   
I would that it could be so easy, unfortunately 'tis not to be so... Poetically speakin'.

Here is the error I get:



Database Results Error
Description: Conversion failed when converting datetime from character string.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for SQL Server


(in reply to BeTheBall)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> Moving from Access to SQL datetime issue.
Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts