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?
Re: Sending Datetime from VB 2005 Form to Sql Sever
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)
Re: Sending Datetime from VB 2005 Form to Sql Sever
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?
Re: Sending Datetime from VB 2005 Form to Sql Sever
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.
Re: Sending Datetime from VB 2005 Form to Sql Sever
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.
Re: Sending Datetime from VB 2005 Form to Sql Sever
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"
Re: Sending Datetime from VB 2005 Form to Sql Sever