We help IT Professionals succeed at work.

Date Formate in  asp.net

CPSRI
CPSRI asked
on
861 Views
Last Modified: 2012-06-21
Hi,
  i am working with datetime formates . while i am inserting date value into sqlserver database it raises error.. that error was
"String was not recognized as a valid DateTime." . formate is "4/23/2011" . could any help me plz.
Comment
Watch Question

Rahul AgarwalTeam Leader
CERTIFIED EXPERT

Commented:
In your database (SQL), datetime format might be configured as "dd/MM/yyyy" if it is so then you first need to parse your datetime by using DateTime.ParseExact and then pass DateTime object in database.
Either you can also try to pass value as "23/4/2011"
Carlos VillegasFull Stack .NET Developer

Commented:
Well, I want to give you some tips:
1. Use parameters for you SQL commands with the correct Data Type, ex. DateTime for dates not VarChar (in case you dont).
2. When work with string dates in your application, first convert it to a native DateTime variable by parsing it., before make use of this (this can be objectionable but apply in most cases).

First the easy, format my DateTime variable to a string, you can do this:
// You can play with the ToString method of a DateTime var, by indicating how you want format the date, you can put the values in the order you want it.
// Use MM for the month, dd for the day, yyyy for the year, hh for 24hours, h for 12 hours, mm for minutes, ss for seconds, tt for the AM/PM.
// This example output 04/23/2011 13:10:11 (example using my local time)
string myStringDate = myDateTimeVariable.ToString("MM/dd/yyyy hh:mm:ss");

Open in new window


How to parse the date string to a local date time var:
You need to know the source format of your date string
If the date format in your client pc is Day/Month/Year and your server is Month/Day/Year, you will have problems, so parse your client date to your local date format, this is a simplified example:
string myClientDate = "23/04/2011";
System.Globalization.CultureInfo myClientCulture = System.Globalization.CultureInfo.GetCultureInfo("ES-ve");
DateTime myLocalDate = DateTime.MinValue;
if (DateTime.TryParse(myClientDate, myClientCulture, System.Globalization.DateTimeStyles.AssumeLocal, out myLocalDate))
    Console.WriteLine(myLocalDate.ToString());
else
    Console.WriteLine("Cant parse");

Open in new window


To finish I want to let you know this date time format that is valid for Sql Server if you keep using a string for the date:
myDateTimeVariable.ToString("yyyyMMdd hh:mm:ss");

Open in new window



Carlos VillegasFull Stack .NET Developer

Commented:
Or simply (0 hours):
myDateTimeVariable.ToString("yyyyMMdd");

Open in new window

By default SQL server has datetime format as "MM/dd/yyyy" (i.e. US format) but if you have changed its format to "dd/MM/yyyy" or any other format then you will need to pass date in that format.

Apart from that you can also convert your string "4/23/2011" to datetime in SQL server using following code.

CONVERT(datetime, '4/23/2011')

If the problem still persist then kindly elaborate more that how are you passing value to SQL server?
Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
if you send value as datetime c# from code and sp procedure as datetime too then you will not have this problem,
alternatively if you are sending date as string then use yyyy-MM-dd format so pass date as "2011-4-23"
or, in your sql injection, add this line at the beginning
set dateformat mdy

alternatively, parse the date into this format "01 Jan 2011"

I know this is simplistic compared to some of the other answers, which are technically better, but after learning the hard way about how people enter data, I use this just to be safe
(think of a French DBA working on a UK server, who decides he doesn't want to conform to company policy and sets his local format to MDY, then sends out emails to senior partners saying we haven't tested the scripts as the dates are invalid... the reply he got when I saw him demonstrating how "crap" our scripts were was rather scathing :) )
Todd GerbertSenior Engineer
CERTIFIED EXPERT
Top Expert 2010

Commented:
You will need to post your code - from your question it's a little unclear where the problem actually is, and how you're handling the date.  For example, is the column in your database defined as a datetime or varchar?  Are you parsing a string the user has entered into a DateTime object?  Are you converting a DateTime into a string before attempting to insert it into the database?  Are you using SQL parameters?  Are you calling a SQL stored procedure or making an INSERT statement?

Seeing your code will probably answer those questions, and will save us from guessing.

Author

Commented:
Hi, The Problem with Display Date or any Data in Textbox1 , i am using ajax calender control and target id is  textbox1. but when ever page postback the textbox1 contended data is lossing. I don't know why was it happening?could any one help from this .
Thanks in advance.
Carlos VillegasFull Stack .NET Developer

Commented:
Can you post your code?
Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
make sure your calender control, buttone firing event and textbox are in same update panel.

Author

Commented:
<td class="stl"  >
    <asp:Label ID="Label11" runat="server" Text="Date :"></asp:Label></td><td>
        <asp:TextBox ID="txtCalender" Text="" runat="server" Width="180px" AutoPostBack="false" ReadOnly="True"></asp:TextBox>
        <asp:ImageButton ID="imgCalender" runat="server" ImageAlign="Middle" ImageUrl="~/Image/images/Calendar_scheduleHS.png" />
        <asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="txtCalender" PopupButtonID="imgCalender">
        </asp:CalendarExtender>

Author

Commented:
Hi, cpsri do u want whole aspx page.

Author

Commented:
Hi yv989c, this is my code
<table width="890px">

<tr><td class="stl"  >
    <asp:Label ID="Label11" runat="server" Text="Date :"></asp:Label></td><td>
        <asp:TextBox ID="txtCalender" Text="" runat="server" Width="180px" ReadOnly="True" ></asp:TextBox>
        <asp:ImageButton ID="imgCalender" runat="server" ImageAlign="Middle" ImageUrl="~/Image/images/Calendar_scheduleHS.png" />
        <asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="txtCalender" PopupButtonID="imgCalender">
        </asp:CalendarExtender>
       
       
        </td></tr>
        <tr>
  <td class="stl">ZONE :</td>
 
  <td width="50%">
      <asp:DropDownList ID="drdZoneColor" runat="server" Height="25px" Width="186px"
          onselectedindexchanged="drdZoneColor_SelectedIndexChanged" CssClass="dropdownlist" AutoPostBack="True">
          <asp:ListItem Value="None">Select Zone</asp:ListItem>
      <asp:ListItem Value="Yellow"></asp:ListItem>
      <asp:ListItem Value="White"></asp:ListItem>
      <asp:ListItem Value="Green"></asp:ListItem>
      <asp:ListItem Value="Purple"></asp:ListItem>
      <asp:ListItem Value="Blue"></asp:ListItem>
      </asp:DropDownList>
  </td>  
  </tr>
  <tr>
  <td class="stl"> ZONE PASTOR :</td>
  <td width="50%">
      <asp:TextBox ID="txtEZonePastor" runat="server" Width="181px" ReadOnly="True"></asp:TextBox></td>
  </tr>
  <tr>
  <td class="stl">MCA :</td>
  <td width="50%">
      <asp:DropDownList ID="drdEMCA" runat="server" CssClass="dropdownlist" Height="25px" Width="186px"
          AutoPostBack="True" onselectedindexchanged="drdEMCA_SelectedIndexChanged">
          <asp:ListItem>--Select MCA--</asp:ListItem>        
      </asp:DropDownList>
       </td>
  </tr>
  <tr>
  <td class="stl">Members :</td>
  <td width="50%">
      <asp:DropDownList ID="drdEMembers" runat="server" Height="25px" Width="186px"        
          AutoPostBack="True" CssClass="dropdownlist"
          onselectedindexchanged="drdEMembers_SelectedIndexChanged">
          <asp:ListItem>--Select Member--</asp:ListItem>
          </asp:DropDownList>
      </td>
  </tr>
  <tr>
  <td class="stl">Name :</td>
  <td width="50%">
      <asp:TextBox ID="txtEName" runat="server" Width="181px" ReadOnly="True"></asp:TextBox>
      <asp:Label ID="lblMemberID" runat="server" Text="" Visible="false"></asp:Label>
                            </td>
  </tr>
  <tr>
  <td class="stl">Phone :</td>
  <td width="50%">
      <asp:TextBox ID="txtEPhone" runat="server" Width="181px" ReadOnly="True"></asp:TextBox></td>
  </tr>
  <tr>
  <td class="stl">EmailID :</td>
  <td width="50%">
      <asp:TextBox ID="txtEEmailID" runat="server" Width="181px" ReadOnly="True"></asp:TextBox></td>
  </tr>
  <tr>
  <td class="stl">Address :</td>
  <td width="50%">
      <asp:TextBox ID="txtEAddress" TextMode="MultiLine" runat="server" Width="181px"
          ReadOnly="True"></asp:TextBox>
      <asp:Label ID="lblFoundation" runat="server"></asp:Label>
      </td>
  </tr>
        <tr><td class="stl">
    <asp:Label ID="Label6" runat="server" Text="Number Of Weeks Attended"></asp:Label>&nbsp;:</td><td>
       
        <asp:DropDownList ID="drdFoundation" runat="server" Height="20px" CssClass="dropdownlist" Width="186px">
        <asp:ListItem Value="null" Selected="True" >-- Select Weeks--</asp:ListItem>
        <asp:ListItem>One</asp:ListItem>
        <asp:ListItem>Two</asp:ListItem>
        <asp:ListItem>Three</asp:ListItem>
        <asp:ListItem>Four</asp:ListItem>
        <asp:ListItem>Five</asp:ListItem>
        <asp:ListItem>Six</asp:ListItem>
        <asp:ListItem>Seven</asp:ListItem>
        <asp:ListItem>Eight</asp:ListItem>
        <asp:ListItem>Nine</asp:ListItem>        
        <asp:ListItem>Ten</asp:ListItem>
        <asp:ListItem>Eleven</asp:ListItem>
        <asp:ListItem>Twelve</asp:ListItem>        
        </asp:DropDownList>
       
        <asp:RequiredFieldValidator ID="rfvNumWeeksAttnd" runat="server" ErrorMessage="Select The Week" ControlToValidate="drdFoundation"
         InitialValue=null></asp:RequiredFieldValidator>
       
        </td>
           
           
            </tr>
        <tr><td class="stl"></td><td>
            <asp:Button ID="btnSubmit" runat="server" Text="Submit" Width="181px" onclick="btnSubmit_Click"/>
            <asp:Label ID="lblSub" runat="server" ForeColor="#FF3300"></asp:Label>
            </td></tr>
        </table>
Carlos VillegasFull Stack .NET Developer

Commented:
Hi, please in your form tag be sure to set submitdisabledcontrols="true", something like this:
<form id="form1" runat="server" submitdisabledcontrols="true">

Open in new window


Maybe that is the problem, Im not sure...

Author

Commented:
Could you please solve this problem as early as possible.. Thank you
Carlos VillegasFull Stack .NET Developer

Commented:
Hello, did you try it?
<form id="form1" runat="server" submitdisabledcontrols="true">
You know what I meant?
Todd GerbertSenior Engineer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Hi yv989c, this is my code
<table width="890px">

Actually, I was the one who asked you to post your code yesterday.  SubmitDisabledControls doesn't apply here since there's no client-side JavaScript that disables any HTML input elements.

You still haven't really posted the whole file, including any code-behind C# code you might have, but I can guess that your problem is 1) You don't have a scriptmanager on your page, and 2) you don't have a calendar control on your page (the CalendarExtender does not work by itself, it requires a Calendar control).
Todd GerbertSenior Engineer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Oh, I misread the documentation, you don't need a Calendar control.  You still will need a ScriptManager though.  And we still need to see the rest of your code (the entire .aspx page, and .cs).

Author

Commented:
hi v989c, i tried this submitdisabledcontrols="true" but it no use .. same problem raising..
Todd GerbertSenior Engineer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Didn't I just say that didn't apply here? ;)

Author

Commented:
hi tgerbert, Calender control problem solved by my  own idea.. now it is working properly. Now  the problem is when ever page post back the data in textbox is loosing.. i placed the code also could any one help me..
Carlos VillegasFull Stack .NET Developer

Commented:
Ok tgerbert, do this one, seems that you have anything under control.
My post was:
Hi, please in your form tag be sure to set submitdisabledcontrols="true", something like this:
1:
<form id="form1" runat="server" submitdisabledcontrols="true">
Toggle HighlightingOpen in New WindowSelect All

Maybe that is the problem, Im not sure...
Todd GerbertSenior Engineer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Okay, it seems ASP.Net is deciding the value of your txtCalender doesn't need to be maintained between post-backs because it's set to ReadOnly=True.  A quick fix would be to simple make ReadOnly=False.

Otherwise, you can add an <asp:Hidden .../> field to your page, and use client-side JavaScript to set it's value equal to whatever's in txtCalender.  Then in your code-behind you can get at the value of the hidden field and explicitly re-set the value of txtCalender yourself.

<script language="javascript" type="text/javascript">
    function dateChange() {
        var hiddenField = document.getElementById('<%= hiddenSelectedDate.ClientID %>');
        var dateField = document.getElementById('<%=txtCalender.ClientID %>');
        hiddenField.value = dateField.value;
    }
</script>

<asp:CalendarExtender ID="CalendarExtender1" runat="server" 
            TargetControlID="txtCalender" PopupButtonID="imgCalender" 
            onclientdateselectionchanged="dateChange">
</asp:CalendarExtender>

Open in new window


protected void btnSubmit_Click(object sender, EventArgs e)
{
    txtCalender.Text = hiddenSelectedDate.Value;
}

Open in new window

Full Stack .NET Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Carlos VillegasFull Stack .NET Developer

Commented:
That will do exactly what you want, the user can't be able to edit the text box and the selected date is going to reach the server.
Carlos VillegasFull Stack .NET Developer

Commented:
Of course, do that only for the controls that you need to read the changes in the server side, if only you have problems reading the changed txtCalender text, just do that for that control.
Todd GerbertSenior Engineer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Carlos VillegasFull Stack .NET Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Carlos VillegasFull Stack .NET Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Todd GerbertSenior Engineer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Carlos VillegasFull Stack .NET Developer

Commented:
Is good to know your viewpoint, and good to know that ALL the browsers will have that behaviour.

Author

Commented:
Thank you to all.
Carlos VillegasFull Stack .NET Developer

Commented:
Thanks cpsri.

BTW, @tgerbert:
The standard are there to be honored, it is readonly="readonly" for one reason, because following your logic, what do you think about this? readonly="no"  this is valid too? as you say "the browsers will honor it" and keep it as read only, but that not was my intention... well is just an advise.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.