Passing date param to update

I have a gridview (editable) 2 of the columns are date fields, when the user clicks edit and changes the date (or even don't change anything) and presses update then they get the error converting date field from string. I have update parameters of...
        <asp:Parameter Name="StartDate" Type="DateTime" />
        <asp:Parameter Name="EndDate" Type="DateTime" />
I can't see anywhere the gridview properties to specify that the column should be treated as a date value. The data format is dd/mm/yyyy
LVL 29
QPRAsked:
Who is Participating?
 
Salim FayadCommented:
QPR, I found the solution:
1. Use the template field:

                <asp:TemplateField HeaderText="StartDate">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtStartDate" runat="server" Text='<%#Bind("StartDate", "{0:dd/MM/yyyy}") %>' />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblStartDate" runat="server" Text='<%#Eval("StartDate", "{0:dd/MM/yyyy}") %>' />
                    </ItemTemplate>
                </asp:TemplateField>


2. On the "RowUpdating" event, put the code below:

e.NewValues("StartDate") = DateTime.ParseExact(e.NewValues("StartDate").ToString(), "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture)

For this, you should have your Select stored procedure returning the StartDate as a DateTime variable and not a string variable since it is being formatted in ASP.NET
0
 
Dustin HopkinsSenior Web DeveloperCommented:
Is htmlencode set to false?
0
 
Salim FayadCommented:
What type of columns are you using for these 2 parameters in the GridView? BoundField? TemplateField?...
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
QPRAuthor Commented:
<asp:BoundField DataField="StartDate" HeaderText="Start Date" SortExpression="StartDate" />
            <asp:BoundField DataField="EndDate" HeaderText="EndDate" SortExpression="End Date" />
0
 
Salim FayadCommented:
In the Select stored procedure, are the StartDate and EndDate DateTime or strings (formatted)?
0
 
QPRAuthor Commented:
Good thinking!
Yes...
select NoticeID, NoticeText, convert(varchar,StartDate,103) as StartDate, convert(varchar,ExpiryDate,103) as EndDate
from Notices

I needed to do this to drop the time portion returned from the DB
0
 
Salim FayadCommented:
Ok. If you want to keep everything like it is, then do the following: On the "RowUpdating" event of the GridView, parse the text value of both dates from dd/mm/yyyy to DateTime using the following:
       
            e.NewValues["StartDate"] = DateTime.ParseExact(GridView.Rows[e.RowIndex].Cells[StartDateIndex].Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);

Do this for the ExpiryDate also
0
 
QPRAuthor Commented:
Sorry, do you have a vb.net version?
Also... as you seem to be so good on dates etc (grovel grovel :-) any ideas on this one?
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_22892438.html
0
 
Salim FayadCommented:
Here is the VB version:

e.NewValues("StartDate") = DateTime.ParseExact(GridView1.Rows(e.RowIndex).Cells(StartDateIndex).Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture)

Note that "StartDateIndex" is the index of the StartDate column in your GrdiView
0
 
QPRAuthor Commented:
e.RowIndex is not a member of rowupdate args

Also what do I need to replace StartDateIndex with - I am new to altering gridviews (programmatically)
0
 
Salim FayadCommented:
First, It is "RowUpdating" event, and not "RowUpdated".
Second, the "StartDateIndex" is the index of the BoundField of the StartDate column. If it is the first column, then its index is zero.
0
 
QPRAuthor Commented:
Thanks, now I get the error String was not recognized as a valid DateTime.
Here is what I have....
Protected Sub GridView1_RowUpdating1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
        e.NewValues("StartDate") = DateTime.ParseExact(GridView1.Rows(e.RowIndex).Cells(2).Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture)
        e.NewValues("EndDate") = DateTime.ParseExact(GridView1.Rows(e.RowIndex).Cells(3).Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture)
 End Sub
0
 
QPRAuthor Commented:
tried all permutations of this but don't seem to be able to get past the error

String was not recognized as a valid DateTime
0
 
Dustin HopkinsSenior Web DeveloperCommented:
Are the sql columns typed for date? if so them the date format for mssql is MM/dd/yyyy
Try using the templatefield method of doing this below. I gave an example of startdate it wouldn't take much to change it for enddate. Also take those ugly convert functions out of your sql. notice how this below format out the datetime.

<asp:TemplateField HeaderText="Start Date" SortExpression="StartDate">
            <EditItemTemplate>
                         <asp:Label ID="Label4" runat="server" Text='<%# Eval("StartDate", "{0:MM/dd/yyy}") %>'></asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                          <asp:Label ID="Label4" runat="server" Text='<%# Eval("StartDate", "{0:MM/dd/yyy}") %>'></asp:Label>
            </ItemTemplate>
</asp:TemplateField>

         
0
 
QPRAuthor Commented:
You mean the sql in my SP, the convert that chops off the time portion of my datefield from the db?
The datatypes for the 2 columns are Datetime yes.
I've never had any SQL date stored as mm/dd/yyyy on any of my sql boxes!
0
 
QPRAuthor Commented:
"Is htmlencode set to false?"

Not sure how this relates to dates being considered strings - can you elaborate?
0
 
Dustin HopkinsSenior Web DeveloperCommented:
ok in the code i posted before the eval in the labels format the data to cut off the time, so you dont need to convert the format in the sql. if you use a bound column you want to set htmlencode to false because it may interpret the slashes to &frasl;. What sql are you using according to .net best practices datetimes are foratted as such 2/20/2008 12:00:00 AM so if you try to parse 20/2/2008 you will get an error. sql 2000 and sql 2005 both use this format for datetime.
0
 
QPRAuthor Commented:
"according to .net best practices datetimes are foratted as such 2/20/2008 "

only in your part of the world :)

0
 
QPRAuthor Commented:
removing the convert from the SP and displaying the date in all it's glory in the gridview e.g. dd/mm/yyyy hh:mm:ss and everything works perfectly.
However I'd rather not show the hh:mm:ss
So it seems to be a simple case of displaying the date as dd/mm/yyyy in the gridview and then when passing the values back to the update statement appending the time (00:00:00) back on the end of the date and ensuring it is actually a date datatype and not a string.

?
0
 
Dustin HopkinsSenior Web DeveloperCommented:
Just for my reference will this not work?
<asp:TemplateField HeaderText="Start Date" SortExpression="StartDate">
            <EditItemTemplate>
                         <asp:Label ID="Label4" runat="server" Text='<%# Eval("StartDate", "{0:dd/MM/yyyy}") %>'></asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                          <asp:Label ID="Label4" runat="server" Text='<%# Eval("StartDate", "{0:dd/MM/yyyy}") %>'></asp:Label>
            </ItemTemplate>
</asp:TemplateField>
0
 
QPRAuthor Commented:
I tried that before and it showed the date fine but when I try to update I end up back where I started.
Not a date.
0
 
Dustin HopkinsSenior Web DeveloperCommented:
I see, what if you don't specify a type in the param? I messed around in vs05 and tried a few string conversions. When I did this it failed:
string dt = "13/9/2007";
    DateTime.ParseExact(dt, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
    Response.Write(dt);
But when i changed it to this it parsed fine.
string dt = "13/09/2007";
    DateTime.ParseExact(dt, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
    Response.Write(dt);
0
 
Dustin HopkinsSenior Web DeveloperCommented:
That makes sense but for most users its impractical to place the 0 in front of the months or days, so the most secure way would be to add the extra time programmatically, but the easiest way would be to leave out the type property of the param and allow sql to add the extra time, though i think that may open you up to sql injection.
0
 
QPRAuthor Commented:
The affected users are a select few (with better than average brains thankfully!) this is an admin section (content management) for an intranet so sql injection is not an issue here. I'll try your suggestion above and report back
0
 
Salim FayadCommented:
Your solution, dusion, seems to be good (even though I haven't tried it). But there is one thing mission in your solution which is using of "Eval". This function is a one way binding. You have to use "Bind" instead which is a 2 way binding.
0
 
QPRAuthor Commented:
I've tried using bind and eval, both allowed me to format the date (or string as it seems) but neither got me past the update error.
In an ideal world I would take my date from SQL display either as a date dd/mm/yyyy (or as a string if need be) in the gridview, allow the user to edit the date by hand and then do whatever it takes to convert this new date/string value back into whatever it needs to be to allow the update function to work.

If I use this....
<asp:TemplateField HeaderText="Start Date" SortExpression="StartDate">
            <EditItemTemplate>
                         <asp:Label ID="Label4" runat="server" Text='<%# Eval("StartDate", "{0:dd/MM/yyyy}") %>'></asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                          <asp:Label ID="Label4" runat="server" Text='<%# Eval("StartDate", "{0:dd/MM/yyyy}") %>'></asp:Label>
            </ItemTemplate>
</asp:TemplateField>

I get the error that the SP expects parameter @StartDate which is not supplied.
Should the label IDs be called StartDate for this to work?
0
 
Salim FayadCommented:
The error "SP expects parameter @StartDate which is not supplied" is due to using "Eval". That's why you should use "Bind" instead.

Don't forget to remove the formatting from the SQL Statement since it is being done on the ASP.NET side. And in the <EditItemTemplate>, you should put a textbox instead of a label.

If nothing works, best thing is to do the update by yourself by doing the following:
1. on the "RowUpdating" event of your gridview, put the following: e.Cancel = true
2. Get the values of your StartDate and EndDate (this depends on whether you are using a templatefield or a boundfield)
3. Write the update there and do not rely on the update of your GridView

If you have any more questions, let me know
0
 
QPRAuthor Commented:
What should I put in place of e.NewValues ?
I get an error saying that e.NewValues is not a member of system.eventargs
0
 
Dustin HopkinsSenior Web DeveloperCommented:
gridview rowupdating is handled by GridViewUpdateEventArgs

so in your void replace system.EventArgs with GridViewUpdateEventArgs
0
 
Salim FayadCommented:
You have to use the "RowUpdating" event, QPR.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.