locked
UK dates issue in sqldatasource RRS feed

  • Question

  • User-1165400470 posted

    Hi

    I am using sqldatasource to store data to backend db as below;

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings: <CString Here>%>"
    SelectCommand="SELECT * FROM [Applicants] WHERE [user_name] = @user_name"
    UpdateCommand="UPDATE [Applicants] SET [DOB] = @DOB WHERE [ApplicantID] = @ApplicantID"
    OnSelecting="SqlDataSource1_Selecting"
    OnSelected="SqlDataSource1_Selected"
    OnUpdating="SqlDataSource1_Updating" CancelSelectOnNullParameter="False">
    <SelectParameters>
    <asp:Parameter Name="user_name" Type="String" />
    </SelectParameters>
    <UpdateParameters>
    <asp:ControlParameter Name="ApplicantID" PropertyName="Text" ControlID="txtApplicantID" Type="String" />
    <asp:ControlParameter Name="DOB" DbType="Date" PropertyName="Text" ControlID="txtDOB" />
    </UpdateParameters>
    </asp:SqlDataSource>

    Problem is that I get error on SqlDataSource1.Update() with UK dates 13/11/1988, 20/01/1991 etc are entered in txtDOB textbox by the user.

    My question is what changes do I need to make to sqldatasource setup to enable UK dates to be saved to db without problem? Thanks.

    The relevant fields in the backend table are defined as follows;

     [ApplicantID] [int] IDENTITY(1,1) NOT NULL,
     [user_name] [nvarchar](255) NULL,
     [DOB] [date] NULL,

    Regards

     

    Friday, April 29, 2011 10:24 AM

Answers

  • User-1313990200 posted

    try this in your code behind 

    Create an event OnSelecting, Add the datetime parameters
    
    protected void sdsArtigos_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
    e.Command.Parameters[1].Value = DateTime.Today;
    e.Command.Parameters[2].Value = DateTime.Today;
    e.Command.Parameters["DataFinal"].Value = DateTime.Today;
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 29, 2011 10:34 PM

All replies

  • User-1313990200 posted

    What is the error you are getting??

    change this

    <asp:ControlParameter Name="DOB" DbType="Date" PropertyName="Text" ControlID="txtDOB" /> 
    
    to
    
    <asp:ControlParameter Name="DOB" DbType="DateTime" PropertyName="Text" ControlID="txtDOB" /> 
    

     

    Friday, April 29, 2011 11:24 AM
  • User-1165400470 posted

    Hi
     
    The message is 'String was not recognized as a valid DateTime.' The value of txtDOB is for instance 14/09/1989. I presume SqlDataSource1.Update() is expecting dates in mm/dd/yyyy format and hence UK date 14/09/1989 is illegal. Any way to get SqlDataSource1 to accept dates in UK format? Alternatively is there another solution to this problem?

    Many Thanks

    Regards

    Friday, April 29, 2011 3:59 PM
  • User-1313990200 posted

    try this in your code behind 

    Create an event OnSelecting, Add the datetime parameters
    
    protected void sdsArtigos_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
    e.Command.Parameters[1].Value = DateTime.Today;
    e.Command.Parameters[2].Value = DateTime.Today;
    e.Command.Parameters["DataFinal"].Value = DateTime.Today;
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 29, 2011 10:34 PM