Sending Datetime from VB 2005 Form to Sql Sever

[sweizone]

Sql Server has column date time. I am required to send date in the format
"Mm/dd/yyyy hh:mm:ss tt" from my windows form.

I get date from datetimepicker control in form & I tried converting by doing this before i sent it to database through arraylist

Dim dt As DateTime = Convert.ToDateTime(DOB.Text)
canArraylst.Add(dt.ToString("MM/dd/yyyy hh:mm:ss tt"))

& current date as
nowDt = "MM/dd/yyyy hh:mm:ss tt"
canArraylst.Add(System.DateTime.Now.ToString(nowDt))

the error I get is cannot convert string to date time.

When i send the date in this format

canArraylst.Add("1/1/2008")

The SQl Procedure works fine.
Any suggestion to resolve this issue?

[jmcilhinney]

You are not required to send dates in any format. Dates do not have a format. Only string representations of dates have format and you do NOT want to send a string representation of a date to a database. Do NOT, under any circumstances, convert a date to a string UNLESS it is for display or serialisation purposes. This is neither. Sorry if I seem a bit forceful but I must post this same information at least once a day and this is not the first time today.

You send all data to databases in exactly the same way: in binary form using parameters. Follow the Data Access link in my signature to see examples of how that's done. If your database expects a date then you assign a binary Date to the parameter's Value, e.g.

myCommand.Parameters.AddWithValue("@CreatedDate", Date.Now)

[sweizone]

hi jmcilhinney ,
I totally agree with what you are saying here.
What I am doing is I am using a webservice which uses Business Layer Dll. The method in the Business layer accepts arraylist to insert the Data. o my Form is sending Arraylist to the Webservice in turn.
That is why I use the array list.

canArraylst.Add(dt.ToString("MM/dd/yyyy hh:mm:ss tt"))

"canArraylst" is my arraylist here, I want to send the date format with time to the database that is why i am converting it to format "MM/dd/yyyy hh:mm:ss tt"
This is my C# code which works fine without any problem. the onyl this is this is in Business Layer & there is no webservice involved.
all is the arraylist, & SetDateTimePattern is "MM/dd/yyyy hh:mm:ss tt"

al.Add(System.DateTime.Now.ToString(SetDateTimePattern));

this al is sent to my DAL for insertion. I am using SQLhelper class to perform my Database insertions & updations.

I am missing somethign here or ignoring something to cause this issue to crop up?

[jmcilhinney]

Is this your own Web service? If so then you should be changing both that and the application. I already said:

Do NOT, under any circumstances, convert a date to a string UNLESS it is for display or serialisation purposes. This is neither.

so, instead of you converting a Date to a String and then the Web service converting the String back to a Date, you should be sending a Date and the web service should be receiving a Date. No conversion necessary so no chance of messing things up.

[jmcilhinney]

Oh, hang on!

I am using SQLhelper class to perform my Database insertions & updations.

Let me guess. You have to send a literal string to that class so you can't use parameters to insert binary Date values into your command. If that's so then that's a fine example of a poorly designed DAL.

[sweizone]

Thank you. But wanting time to be inserted in databse along with date i tried this

Dim strdt As String = System.DateTime.Now()
Dim strtime As String = DateTime.Parse(strdt).ToLongTimeString()
strdt = DateTime.Parse(strdt).ToShortDateString()
strdt = strdt + " " + strtime
canArraylst.Add(strdt)
CanArraylst is sent to BLL Via a webservice.
Then just to be sure :-

If IsDate(strdt) Then
MsgBox("Correct Format")
Else
MsgBox("invalid")
End IfThis is the format in which date was inserted in DB

"2008-07-28 15:52:33.000"

[sweizone]

Thank you jmcilhinney