Hi: I am starting to rip my hair out. I pretty much tried everything. I want to insert following values: sqlString = "INSERT into myTable (ProductID, Name, Title, CategoryID, Organization, Address, City, State, Zip, Country, Phone, Fax, Email, Heard, Referral, Computer, Implement, Description, RepMail, Counter, Password, Authenticated, Created, LastAccessed) VALUES (" & strSoftware & ",'" & strName & "','" & strTitle & "'," & strInst & ",'" & strOrganization & "','" & strAddress & "','" & strCity & "','" & strState & "','" & strPostal & "','" & strCountry & "','" & strPhone & "','" & strFax & "','" & strEmail & "','" & strHeard & "','" & strReferral & "','" & strComputer & "','" & strImplement & "','" & strDescription & "','" & strRepMail & "'," & "0" & ",'" & strPassword & "'," & "0" & "," & formatdatetime(now,2) & "," & formatdatetime(now,2) & ")" Everything works great, but the formatdatetime values are showing in sql as 1/1/1900. The fields in sql all smalldatetime.
Do you want the date and the time, or just the date? Why don't you prepare the value before the statement? I also recommend using a standard format (e.g. YYYYMMDD) rather than any of this formatdatetime nonsense, which is vulnerable to regional settings ambiguity (e.g. on an English system with a US database, is 06/05/2004 June 5 or May 6?). -- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/ [quoted text, click to view] "rapp25" <rapp25@hotmail.com> wrote in message
news:1b1323c9.0403311128.3b3224d6@posting.google.com...
> Hi:
>
> I am starting to rip my hair out. I pretty much tried everything. I
> want to insert following values:
>
>
>
> sqlString = "INSERT into myTable (ProductID, Name, Title, CategoryID,
> Organization, Address, City, State, Zip, Country, Phone, Fax, Email,
> Heard, Referral, Computer, Implement, Description, RepMail, Counter,
> Password, Authenticated, Created, LastAccessed) VALUES (" &
> strSoftware & ",'" & strName & "','" & strTitle & "'," & strInst &
> ",'" & strOrganization & "','" & strAddress & "','" & strCity &
> "','" & strState & "','" & strPostal & "','" & strCountry & "','" &
> strPhone & "','" & strFax & "','" & strEmail & "','" & strHeard &
> "','" & strReferral & "','" & strComputer & "','" & strImplement &
> "','" & strDescription & "','" & strRepMail & "'," & "0" & ",'" &
> strPassword & "'," & "0" & "," & formatdatetime(now,2) & "," &
> formatdatetime(now,2) & ")"
>
> Everything works great, but the formatdatetime values are showing in
> sql as 1/1/1900. The fields in sql all smalldatetime.
>
> Thanks in advance.
One more thing, you need to delimit your dates. Should be sqlString = "... ,'" & dateVariable & "', '" & dateVariable & "'") If you don't delimit the dates, it will treat them as a numeric expression, so 6/5/2004 = 6 divided by 5 divided by 2004, which is a very small number indeed. Since SQL Server will take an integer as a date, this basically becomes day 0, and hence 1/1/1900. Finally, consider letting SQL Server insert the date... it knows what day it is, and doesn't need to be told by VBScript. sqlString = "... , {fn CURDATE()}, {fn CURDATE()})" For some good treatment on date topics and formats, please see the following articles: http://www.karaszi.com/sqlserver/info_datetime.asp http://www.aspfaq.com/2460 http://www.aspfaq.com/2464 http://www.aspfaq.com/2313 http://www.aspfaq.com/2347 http://www.aspfaq.com/2040 http://www.aspfaq.com/2023 -- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/
Hi The easist way to do correct this is to profile the program and see when is being sent to the DB. I think you may need to put quotes around the date/times. John [quoted text, click to view] "rapp25" <rapp25@hotmail.com> wrote in message
news:1b1323c9.0403311128.3b3224d6@posting.google.com...
> Hi:
>
> I am starting to rip my hair out. I pretty much tried everything. I
> want to insert following values:
>
>
>
> sqlString = "INSERT into myTable (ProductID, Name, Title, CategoryID,
> Organization, Address, City, State, Zip, Country, Phone, Fax, Email,
> Heard, Referral, Computer, Implement, Description, RepMail, Counter,
> Password, Authenticated, Created, LastAccessed) VALUES (" &
> strSoftware & ",'" & strName & "','" & strTitle & "'," & strInst &
> ",'" & strOrganization & "','" & strAddress & "','" & strCity &
> "','" & strState & "','" & strPostal & "','" & strCountry & "','" &
> strPhone & "','" & strFax & "','" & strEmail & "','" & strHeard &
> "','" & strReferral & "','" & strComputer & "','" & strImplement &
> "','" & strDescription & "','" & strRepMail & "'," & "0" & ",'" &
> strPassword & "'," & "0" & "," & formatdatetime(now,2) & "," &
> formatdatetime(now,2) & ")"
>
> Everything works great, but the formatdatetime values are showing in
> sql as 1/1/1900. The fields in sql all smalldatetime.
>
> Thanks in advance.
Don't see what you're looking for? Try a search.
|
