Sending Datetime from VB 2005 Form to Sql Sever

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?

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.

vb.net Code:

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

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?

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

Quote:

---

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.

Oh, hang on!

Quote:

---

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.

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"

Thank you jmcilhinney