locked
Problem converting string to Date in SqlDatasource control parameter RRS feed

  • Question

  • User-1874496678 posted

    Hi

    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

    <SelectParameters>
    <asp:ControlParameter ControlID="lblVisitDate" PropertyName="Text" Type="DateTime" DefaultValue="Null" Name="VisitDate"></asp:ControlParameter>
    </SelectParameters>

    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.

    Thanks

    Hamid

    Saturday, February 23, 2013 5:57 PM

Answers

  • User-1874496678 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 11, 2013 6:08 PM

All replies

  • User281315223 posted
    What kind of format are the Dates that you are recieving from your Combobox within your Label? This could be occuring if you have any formatting errors within the string and it can't properly be parsed.
    Saturday, February 23, 2013 6:21 PM
  • User-1874496678 posted
    I am getting it as string from combobox. In database it is Date type
    Saturday, February 23, 2013 6:26 PM
  • User281315223 posted

    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.

    Saturday, February 23, 2013 6:42 PM
  • User-1716253493 posted
    DateTime dt =DateTime.ParseExact(str,frmt ,null). don't use control parameter then set the value manual. or use hidden field as control parameter then set the value with correct format
    Saturday, February 23, 2013 9:53 PM
  • User1534498098 posted

    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"))
    

     

     

    Sunday, February 24, 2013 12:39 AM
  • User-1874496678 posted

    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
    
    
            RadGridClinicList.DataBind()

     

    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 

    Conversion failed when converting date and/or time from character string.

    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.

    Thanks

    Hamid

    Sunday, February 24, 2013 9:32 AM
  • User1711093882 posted

    try it

    VisitDate=Convert.ToDateTime((@VisitDate.ToString().ToTrim())

    Must the VisitDate coloumn DataType is DateTime 

    Sunday, February 24, 2013 9:41 AM
  • User-1874496678 posted

    Sorry if It sounds silly

    Where shall I put this - in the code behind - where will I get the parameter @VisitDate

    Sunday, February 24, 2013 10:20 AM
  • User-1874496678 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 11, 2013 6:08 PM