?
Solved

Passing date param to update

Posted on 2007-10-15
30
Medium Priority
?
354 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:QPR
  • 14
  • 9
  • 7
30 Comments
 
LVL 14

Expert Comment

by:Dustin Hopkins
ID: 20081176
Is htmlencode set to false?
0
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 20081232
What type of columns are you using for these 2 parameters in the GridView? BoundField? TemplateField?...
0
 
LVL 29

Author Comment

by:QPR
ID: 20081245
<asp:BoundField DataField="StartDate" HeaderText="Start Date" SortExpression="StartDate" />
            <asp:BoundField DataField="EndDate" HeaderText="EndDate" SortExpression="End Date" />
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:Salim Fayad
ID: 20081412
In the Select stored procedure, are the StartDate and EndDate DateTime or strings (formatted)?
0
 
LVL 29

Author Comment

by:QPR
ID: 20081486
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
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 20081564
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
 
LVL 29

Author Comment

by:QPR
ID: 20081579
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
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 20081607
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
 
LVL 29

Author Comment

by:QPR
ID: 20081679
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
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 20081698
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
 
LVL 29

Author Comment

by:QPR
ID: 20081728
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
 
LVL 29

Author Comment

by:QPR
ID: 20089217
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
 
LVL 14

Expert Comment

by:Dustin Hopkins
ID: 20089333
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
 
LVL 29

Author Comment

by:QPR
ID: 20090469
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
 
LVL 29

Author Comment

by:QPR
ID: 20090472
"Is htmlencode set to false?"

Not sure how this relates to dates being considered strings - can you elaborate?
0
 
LVL 14

Expert Comment

by:Dustin Hopkins
ID: 20090564
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
 
LVL 29

Author Comment

by:QPR
ID: 20090578
"according to .net best practices datetimes are foratted as such 2/20/2008 "

only in your part of the world :)

0
 
LVL 29

Author Comment

by:QPR
ID: 20090632
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
 
LVL 14

Expert Comment

by:Dustin Hopkins
ID: 20090665
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
 
LVL 29

Author Comment

by:QPR
ID: 20090674
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
 
LVL 14

Expert Comment

by:Dustin Hopkins
ID: 20090697
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
 
LVL 14

Expert Comment

by:Dustin Hopkins
ID: 20090723
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
 
LVL 29

Author Comment

by:QPR
ID: 20090736
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
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 20091113
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
 
LVL 29

Author Comment

by:QPR
ID: 20091190
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
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 20091631
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
 
LVL 11

Accepted Solution

by:
Salim Fayad earned 2000 total points
ID: 20096593
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
 
LVL 29

Author Comment

by:QPR
ID: 20098483
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
 
LVL 14

Expert Comment

by:Dustin Hopkins
ID: 20098537
gridview rowupdating is handled by GridViewUpdateEventArgs

so in your void replace system.EventArgs with GridViewUpdateEventArgs
0
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 20099035
You have to use the "RowUpdating" event, QPR.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question