inserting a datetime value into sql

[Newbie]

howdy...

i am trying to execute a statement where i insert a record with datetime
values into a sql database.

Dim sqlcmd As New SqlCommand

sqlcmd.CommandText = "update tbl_event set last_run = '" &
nowTime & "', event_action =1, event_time = '" & nextPollTime & "' where
event_id = " & IntEventID
Debug.Print(sqlcmd.CommandText)

sqlcmd.Connection = dbConnectionIn
sqlcmd.CommandType = CommandType.Text
sqlcmd.ExecuteNonQuery()

the commandtext equals

update tbl_event set last_run = '15/07/2008 13:07:27', event_action =1,
event_time = '15/07/2008 13:07:42' where event_id = 1

I get the following error....

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Whats wrong with what I am doing ????

Thanks for your help

 

[Kerry Moorman]

Newbie,

Does the following update statement work:

update tbl_event set last_run = '07/15/2008 13:07:27', event_action =1,
event_time = '07/15/2008 13:07:42' where event_id = 1

If this statement works then the problem is with the datetime format.

Kerry Moorman

 

[Newbie]

Hi, Yes it did...thanks

is it going to be a localisation issue on the server or on the client....

is there a way to code the date so that localisation is not an issue ?

Thanks

 

[Stephany Young]

Yes and Yes!

Dim sqlcmd = New SqlCommand("update tbl_event set
last_run=@last_run,event_action=1,event_time=@event_time where
event_id=@event_id", dbConnectionIn)

sqlcmd.Parameters.Add("@last_run", SqlDbType.DateTime).Value = nowTime

sqlcmd.Parameters.Add("@event_time", SqlDbType.DateTime).Value =
nextPollTime

sqlcmd.Parameters.Add("@event_id", SqlDbType.IntValue = IntEventID

sqlcmd.ExecuteNonQuery()

 

[Rich P]

Try using a dataAdapter and a datatime Parameter:

imports system
imports system.data.sqlclient

dim daB as sqlDataAdapter, ds as DataSet, conn1 as sqlconnection

ds = New Dataset
daB.UpdateCommand = New SqlCommand
daB.UpdateCommand.Connection = conn1
daB.UpdateCommand.Parameters.Add("@P", SqlDbType.DateTime, 8,
"last_run")

daB.UpdateCommand.CommandText = "Update tbl_event Set last_run =@P Where
ID = @ID"
daB.Update(dsMain, "tbl_Steve_Local")

The way this works is that you have to pull the desired record into a
local dataTable contained in a dataset that I call ds. I named my local
dataTable "tbl_Steve_Local" to illustrate that the dataTable name is
independent of the server table - but you are pulling from your
respective table "tbl_event". Of course, you will also need to
instantiate a daB.SelectCommand = New SqlCommand for pulling the data
into your local dataset ds. When you edit the local table you have to
call the dataAdapter UpdateCommand, and it will update your dateTime
field without the issues you are experiencing. The DataAdapter does a
lot of stuff under the hood for you (it deals with apostrophe's real
nicely too).


Rich

 

[SurturZ]

I use the following routine to convert a date to an ISO8601 string, which is
an unambiguous format accepted by SQL Server:

''' <summary>
''' Converts a Date to a unicode string in ISO8601 format for use in
SQL ('yyyy-MM-ddTHH:mm:ss.fff' including apostrophes). Converts a New Date
into Oldest possible date (1753-01-01). Note that this rounds the Date to the
nearest millisecond
''' </summary>
''' <param name="INDate"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ISO8601Date(ByVal INDate As Date) As String
Dim dteTemp As Date = INDate
If dteTemp = New Date Then dteTemp = DateSerial(1753, 1, 1)
'Oldest date possible for a datetime field in SQLS2005
Return "'" & Format(dteTemp, "yyyy-MM-dd") & "T" &
Format(dteTemp, "HH:mm:ss.fff") & "'"
End Function

 

[Chris Dunaway]

I use the following routine to convert a date to an ISO8601 string, which is
an unambiguous format accepted by SQL Server:

If you already have a Date variable, why go through the trouble to
converting it to a string and then inserting it into the database?
Why not just insert the date directly as Stephany shows? The extra
conversion to string is not necessary.

Chris

 

[Kerry Moorman]

Rich,

You should read Stephany's reply. Her solution is much more straightforward
than yours.

Also, it is the use of parameters that deals with apostrophes, etc., not the
dataadapter.

Kerry Moorman

 

[Rich P]

You should read Stephany's reply. Her solution is much more
straightforward
than yours.

Also, it is the use of parameters that deals with apostrophes, etc., not
the
dataadapter.

Kerry Moorman
<<

Right - on the parameters. But I used to not use dataAdapters when I
first started out with .Net. Then I learned how to use them. I work
with datagridviews a lot, insert, editing/updating, deleting
records/data. It is way easier - at least when working with
datagridviews - to use a DataAdapter, I find, for performing these
operations. For addhoc updates, yes, Stephany's approach is simpler.

Rich

 

[SurturZ]

If you already have a Date variable, why go through the trouble to
converting it to a string and then inserting it into the database?
Why not just insert the date directly as Stephany shows? The extra
conversion to string is not necessary.

Because there is more than one way to skin a cat, and some of us like to
roll our own SQL rather than trust ADO.NET to do it.

Also, I suspect my method is actually less code. There is nothing wrong with
Stephany's method though.

 

[Cor Ligthert[MVP]]

The way I like it more (Probably just a matter of preference)

Dim sqlcmd = New SqlCommand("update tbl_event set
last_run=@last_run,event_action=1,event_time=@event_time where
event_id=@event_id", dbConnectionIn)

sqlcmd.Parameters.Add(New SQLParameter("@last_run", nowTime))
sqlcmd.Parameters.Add(New SQLParameter("@event_time", nextPollTime))
sqlcmd.Parameters.Add(New SQLParamter("@event_id", IntEventID))

sqlcmd.ExecuteNonQuery()

I have placed it here, because it's in fact another way of handling than
Stephany wrote.

Cor