We help IT Professionals succeed at work.

String was not recognized as a valid DateTime

IT Genesis
IT Genesis asked
on
I am trying to pass a value to a datetime value from a database.  Unfortunately, the my code doesn't exactly like the format.  I get the following error:  

String was not recognized as a valid datetime.

this my c# code

<code>

TextBox reffd = GridView1.Rows[e.RowIndex].FindControl("txt_RefD") as TextBox;
TextBox outd = GridView1.Rows[e.RowIndex].FindControl("txt_Outd") as TextBox;


</code>

and this my sql command

<code>
SqlCommand cmd = new SqlCommand("Update FZMain set Name='" + name.Text + "',Payment = '" + pay.Text + "', Cases = '" + cases.Text + "',  Reffrence = '" + reff.Text + "',ReffrenceDate = " + DateTime.ParseExact(reffd.Text, "dd-mm-yyyy", null).AddHours(-5) + "), OutDate = " + DateTime.ParseExact(outd.Text, "dd-mm-yyyy ", null).AddHours(-5) + "), FamilyRegister = '" + femreg.Text + "',LastOwner='" + lastO.Text + "', ChassisNO='" + Chasses.Text + "', CarModel='" + CarModel.Text + "' where ID=" + Convert.ToInt32(id.Text), con);

</code>

data type in database is date
Comment
Watch Question

AndyAinscowFreelance programmer / Consultant

Commented:
I think you will find the text entered is not dd-mm-yyyy, eg. 10-10-19 will fail, it requires 10-10-2019

Author

Commented:
i change it and try again that the same problem
AndyAinscowFreelance programmer / Consultant

Commented:
Add these two lines BEFORE using the SqlCommand and check which fails along with what the text is in that TextBox.
DateTime dt1 = DateTime.ParseExact(reffd.Text, "dd-mm-yyyy", null);
DateTime dt2 = DateTime.ParseExact(outd.Text, "dd-mm-yyyy", null);

Author

Commented:
the first one get the same error
AndyAinscowFreelance programmer / Consultant

Commented:
What is the text inside the control?
String s = reffd.Text;   What value does s have after this line runs?

Author

Commented:
<asp:TemplateField HeaderText="out date">
                            <EditItemTemplate>
                                <asp:TextBox ID="txt_Outd" runat="server" Text='<%# Eval("OutDate") %>' TextMode="Date"></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="lbl_Outd" runat="server" Text='<%# Eval("OutDate") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="ref date">
                            <EditItemTemplate>
                                <asp:TextBox ID="txt_RefD" runat="server" Text='<%# Eval("ReffrenceDate") %>' TextMode="Date"></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="lbl_RefD" runat="server" Text='<%# Eval("ReffrenceDate") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
Image-2.png
AndyAinscowFreelance programmer / Consultant

Commented:
Can I assume your text control contains something like 10/10/2019

10/10/2019 is NOT the same as 10-10-2019 which is what you say the format of the date should be.

Either change the expected format in the ParseExact or change the required format of the text box.

Author

Commented:
SqlCommand cmd = new SqlCommand("Update FZMain set Name='" + name.Text + "',Payment = '" + pay.Text + "', Cases = '" + cases.Text + "',  Reffrence = '" + reff.Text + "',ReffrenceDate = " + DateTime.ParseExact(reffd.Text, "dd/mm/yyyy", null).AddHours(-5) + "), OutDate = " + DateTime.ParseExact(outd.Text, "dd/mm/yyyy ", null).AddHours(-5) + "), FamilyRegister = '" + femreg.Text + "',LastOwner='" + lastO.Text + "', ChassisNO='" + Chasses.Text + "', CarModel='" + CarModel.Text + "' where ID=" + Convert.ToInt32(id.Text), con);

Open in new window



i change it to (dd/mm/yyyy)
but the same problem
AndyAinscowFreelance programmer / Consultant

Commented:
What is the text inside the control?
String s = reffd.Text;   What value does s have after this line runs?

Author

Commented:
this my code error
AndyAinscowFreelance programmer / Consultant

Commented:
Third time posting this:

What is the text inside the control?
String s = reffd.Text;   What value does s have after this line runs?

Author

Commented:
could you please make it more clear
what you mean exactly by "String s = reffd.Text;   What value does s have after this line runs?"
AndyAinscowFreelance programmer / Consultant

Commented:
I want to see just what text you have in the control.  The text that is failing in the ParseExact function call.  (I can't see your screen)
AndyAinscowFreelance programmer / Consultant

Commented:
ps. A screenshot is NOT what I am asking for.  I want to see the string that your code gets to work with.
AndyAinscowFreelance programmer / Consultant

Commented:
pps.  "10/10/2019" is not the same as " 10/10/2019" or "10/10/2019 "

Author

Commented:
you need this code?

<asp:TemplateField HeaderText="out date">
                            <EditItemTemplate>
                                <asp:TextBox ID="txt_Outd" runat="server" Text='<%# Eval("OutDate") %>' TextMode="Date"></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="lbl_Outd" runat="server" Text='<%# Eval("OutDate") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="ref date">
                            <EditItemTemplate>
                                <asp:TextBox ID="txt_RefD" runat="server" Text='<%# Eval("ReffrenceDate") %>' TextMode="Date"></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="lbl_RefD" runat="server" Text='<%# Eval("ReffrenceDate") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>

Open in new window

Author

Commented:
Image-4.png
AndyAinscowFreelance programmer / Consultant

Commented:
No.

I want to see the string your code extracts from the textbox and passes into the ParseExact function.
AndyAinscowFreelance programmer / Consultant

Commented:
You have
TextBox reffd = GridView1.Rows[e.RowIndex].FindControl("txt_RefD") as TextBox;
TextBox outd = GridView1.Rows[e.RowIndex].FindControl("txt_Outd") as TextBox;

add this code AND DO WHAT THE SECOND COMMENT REQUESTS
//Now I want to see just what is the contents of the textbox
String s = reffd.Text;
//Stop the code here and show me what the debugger has as the value for s
Two things:

1) You need "MM", not "mm".  mm means minutes, MM means months, so even when this works you'll get the incorrect date back.
2) It looks from the screenshot like you're using the standard input type='date' element, hence that calendar.  Assuming that's right, then the format it SHOWS you is completely different than the format it's actually sending.
The browser control shows you the date in your local format, but will ALWAYS pass the value to the server as yyyy-MM-dd

Try that in the parse statement instead.
ZvonkoSystems architect
Top Expert 2006

Commented:
Simple syntax error: your SqlCommand is missing quotation character for begining and ending the Date string.
Partha MandayamTechnical Director

Commented:
There is a syntax error
Date string should be within quotes
ReffrenceDate = '" + DateTime.ParseExact(reffd.Text, "dd-mm-yyyy", null).AddHours(-5) + "')

OutDate = '" + DateTime.ParseExact(outd.Text, "dd-mm-yyyy ", null).AddHours(-5) + "'),

Make these changes and it should work
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try with the more forgiving Parse - and the date value must be converted to a string expression - and remove the two no orphan closing parenthesis:

// For SQL Server:
.. "',ReffrenceDate = '" + DateTime.Parse(reffd.Text).AddHours(-5).ToString("yyyy'/'MM'/'dd) + "', OutDate = " ..

// For Access:
.. "',ReffrenceDate = #" + DateTime.Parse(reffd.Text).AddHours(-5).ToString("yyyy'/'MM'/'dd) + "#, OutDate = " ..

Open in new window

Author

Commented:
Image-1.png
I change it and still has this error and change text box mode to date time instead of date and still have this problem but when the old data fill from database it fill date and time like this pic
Image-2.pngi guess that problem here
AndyAinscowFreelance programmer / Consultant

Commented:
You have multiple problems in your code.  Try to solve the first problem before the others.
Please tell us what the text is your code extracts from the textbox and passes into the ParseExact.  
Once that part is corrected then one can approach the next problem.  (You will never solve the other problems if your code always passes garbage into it.)

Author

Commented:
it brings a date from a table in a database and when i click on edit button i can write new date when i click update this exception occur
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, but you have several other syntax errors as well.
Check out my post.
AndyAinscowFreelance programmer / Consultant

Commented:
>>it brings a date from a table in a database and when i click on edit button i can write new date when i click update this exception occur

That is what you want.  Your error seems to say that is not happening.  That is why I keep asking you to check and you seems not to want to do that.
AndyAinscowFreelance programmer / Consultant

Commented:
Also this is from your question:
TextBox reffd = GridView1.Rows[e.RowIndex].FindControl("txt_RefD") as TextBox;
which you use as the source for one of your dates.

So you aren't even getting a date from a database table with that code, that is from something on the GUI.

Author

Commented:
@Gustav Brock
this code not fully correct it missing some thing
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, it only points out the things to change. The rest you must type yourself.

Author

Commented:
@AndyAinscow sorry I couldn't understand you yesterday

but when i show data in table before click edit i can show my data from database like this Image-2.png
AndyAinscowFreelance programmer / Consultant

Commented:
Making some assumptions.
The date in your text box is 30/03/1995 12:00:00.  So the string you retrieve is "30/03/1995 12:00:00".  
You then pass that string into a function stating the format of that string should be 30/03/1995 - which then would fail because the string is not in that format, it has an extra time section.

Now do you see why I have been asking you to check just what you get from the text box.

ps.  My assumptions could be wrong, the string you retrieve could be "90968" or "Edit" or....
Partha MandayamTechnical Director

Commented:
Just use datetime.parse(date) without giving any format
Partha MandayamTechnical Director

Commented:
Try just DateTime.Parse(outd.Text)

Author

Commented:
@Partha Mandayam
it retrieve the same error
AndyAinscowFreelance programmer / Consultant

Commented:
Parse is BAD.  If you know the format of the date use ParseExact.

Why, because 10/11 could mean 10 November or 11 October, with ParseExact you specify which of those is meant.
Commented:
the problem was  in pc date format

DateTime.ParseExact(reffd.Text, "yyyy-MM-dd", null)

Open in new window


Thanks all
I believe that's exactly what I pointed out a couple days ago ;)