UPDATE of smalldatetime field not working 1

[MCuthill]

Hi Everyone,

I am using Visual Studio to develop a web page which has to interact with an SQL server database. The problem I am running into is that when I am trying to UPDATE (or INSERT) a date, it is showing up in the database as "1/1/1900" when i enter a date in this field manually on the server, it wants it of the form "mm/dd/yyyy". When I step through my code, the variable I am inserting shows as '#4/19/2007#' - same form as required on maual entry.

I am wondering if anyone knows why this may be happening, or where I should look for a resolution.

Information:
SQL Fields are of type: smalldatetime

VB Code executing UPDATE Method:

[blue]Dim[/blue] TransDate [blue]As Date[/blue] = Today()

[blue]Dim[/blue] SQL [blue]As String[/blue]
SQL = [maroon]"UPDATE dbo_Resource SET DateSignedOut ="[/maroon] & TransDate & [maroon]" WHERE ResourceID ="[/maroon] & primaryKey
SqlDataSource1.UpdateCommand = SQL
SqlDataSource1.Update()

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[AlexCuse]

SQL = "UPDATE dbo_Resource SET DateSignedOut ='" & TransDate & "' WHERE ResourceID =" & primaryKey


Notice the single quotes around DateSignedOut.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Not DateSignedOut, TransDate. SQL Server needs ' ' around strings in order to recognize them as such.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mrdenny]

Yep that would be it.

The reason that you kept getting 1/1/1900 in the field is because SQL was seeing the date that you were passing it as a math function to be executed and the numerical value that resulted from it was being used to find the date. When you take 4/16/2007 you get 1.0489e-4. If memory servers from math class that translates to .0000104 which would be a smaller number than the smalldatetime data type can deal with, so it simply defaulted you to it's lower bound 1/1/1900.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

Actually 4/16/2007 would evaluate to 0 because of integer math.

Select 4/16/2007

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MCuthill]

Thanks All,

Worked a charm! I figured it was something simple that i was overlooking.

Regards,


Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"