Last post Mar 11, 2013 06:08 PM by SHR
Feb 23, 2013 05:57 PM|SHR|LINK
I have a sqldatasource with WHERE clause as VisitDate=@VisitDate. the date comes from a label - lblVistDate, which gets its value from a multicolumn combobox.
the SelectParameters are as follows
<asp:ControlParameter ControlID="lblVisitDate" PropertyName="Text" Type="DateTime" DefaultValue="Null" Name="VisitDate"></asp:ControlParameter>
I get error
"The string was not recognized as a valid DateTime. "
I have tried various things as CType, CDate, Convert.DateTime - but the error persist.
Any help please is very much appreciated. I am sure this is one of those basic mistake I am making somewhere.
Feb 23, 2013 06:21 PM|Rion Williams|LINK
Feb 23, 2013 06:26 PM|SHR|LINK
Feb 23, 2013 06:42 PM|Rion Williams|LINK
I was referring to the actual format of your Date strings within the ComboBox (such as "MM/dd/yyyy" etc.)
If they are incorrect, then they won't be able to be parsed properly and would thrown a error like the one you are receiving.
Feb 23, 2013 09:53 PM|oned_gk|LINK
Feb 24, 2013 12:39 AM|raghavendra ms|LINK
If you are using datetime format as dd/MM/yyyy then use DateTime.TryParseExact method to convert it to required date time format like
DateTime dt= DateTime.Parse(DateTime.ParseExact("Your_dateTime_control(string)", dd/MM/yyyy", null).ToString("MM/dd/yyyy"))
Feb 24, 2013 09:32 AM|SHR|LINK
Sorry still not working
I tried the hidden field approach. Here is what I did - it is rather long winded - but will try to explain.
I have multi column RadCombobox, to select a row on which to base a Grid. The row is on a query with GroupBy, so there is no ID field as such and I have to get results in the Grid with 4 different criteria, and one of them is the date.
I don't think there is any way to access values of columns in ComboBox, so I used _ItemDataBound to get the values of different fields in the textbox portion of combobox, seperated by a character '|'. Then used an array to get the values with split(separator).
I put that value in a label control and set it as criteria in SqlDataSource but it gave the error for the date, other fields are fine.
Now have used hidden field approach and on SelectedIndexChange I have this
Dim enGB As New CultureInfo("en-GB")
Dim visitDateString As String = Trim(ClinicArray(1))
Dim format As String = "dd/MM/yyyy"
Dim visitDate As Date = Date.ParseExact(visitDateString, format, enGB)
hfVisitDate.Value = visitDate
And this is the control parameter in SqlDataSource
<asp:ControlParameter ControlID="hfVisitDate" PropertyName="Value" DefaultValue="Null" Name="VisitDate"></asp:ControlParameter>
If I make the hiddenfield visible I can see that the date is formatted exactly I want but I still get the error
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
In my SQLdatabase the VisitDate is Date type and it is in the same format, although I don't think you can specify format in database.
Where and what I am doing wrong please.
Feb 24, 2013 09:41 AM|dotnetsolution|LINK
Must the VisitDate coloumn DataType is DateTime
Feb 24, 2013 10:20 AM|SHR|LINK
Sorry if It sounds silly
Where shall I put this - in the code behind - where will I get the parameter @VisitDate
Mar 11, 2013 06:08 PM|SHR|LINK
Solved it now ...
Did Format(Visit.VisitDate), 'dd/MM/yyyy') As SomeDate in the SQL query and
for the textbox as control parameter did textbox.text = VisitDate.ToString("dd/MM/yyyy")
Now it seems so simple.