Insert datatime value from ASP.NET gridview to SQL Server Datatable

Question

I have a column in ASP.NET gridview that displays the date value in this format (dd/mm/yyyy 00:00:00) upon page load. This is bound to an SQL table with a column declared as "Date" type.

In the footer template, there is an "Insert" button as well as textboxes in the various columns of the gridview. Upon clicking the button, the new record should be inserted into the SQL server Datatable.

However, there is a debug error with an error saying: SQLException was unhandled by usercode. Conversion failed when converting date and/or time from character string

Here is the code for this method:

protected void insertRowToDOitems(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Add")
        {
            string strDO_no = ((TextBox)GridView1.FooterRow.FindControl("txtdo_no")).Text;
            string strDO_item_no = ((TextBox)GridView1.FooterRow.FindControl("txtdo_item_no")).Text;
            string strMaterial = ((TextBox)GridView1.FooterRow.FindControl("txtmaterial")).Text;
            string strDeliveryQty = ((TextBox)GridView1.FooterRow.FindControl("txtdelivery_qty")).Text;
            string strSO_no = ((TextBox)GridView1.FooterRow.FindControl("txtso_no")).Text;
            string strSOItem_no = ((TextBox)GridView1.FooterRow.FindControl("txtso_item_no")).Text;
            string strUOM = ((TextBox)GridView1.FooterRow.FindControl("txtuom")).Text;
            string strDeliveryDate = ((TextBox)GridView1.FooterRow.FindControl("txtDelivery_date")).Text;

            //SQLDataSource parameters
            DOitem.InsertParameters.Clear();
            DOitem.InsertParameters.Add("do_no", strDO_no);
            DOitem.InsertParameters.Add("do_item_no", strDO_item_no);
            DOitem.InsertParameters.Add("material", strMaterial);
            DOitem.InsertParameters.Add("delivery_qty", strDeliveryQty);
            DOitem.InsertParameters.Add("so_no", strSO_no);
            DOitem.InsertParameters.Add("so_item_no", strSOItem_no);
            DOitem.InsertParameters.Add("uom", strUOM);
            DOitem.InsertParameters.Add("delivery_date", strDeliveryDate);

            DOitem.Insert();
        }
    }

And this method was called from the GridView tag:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                    DataKeyNames="do_no,do_item_no" DataSourceID="DOitem" CellPadding="4" 
                    GridLines="None" 
                    AllowSorting="True" ShowFooter="True" style="margin-top: 1px" 
                    ForeColor="#333333" Height="58px" Width="546px" OnRowCommand="insertRowToDOitems">

If the error is due to difference in Date format or type, how can this be solved?

Also, how can I display the date retrieved from SQL table in the gridview like this ("dd/MM/yyyy")? I've tried the following but it doesn't work:

<EditItemTemplate>
                            <asp:TextBox ID="txtDelivery_date" runat="server"
                                         Text='<%#Bind("delivery_date", "{0:dd/MM/yyyy}") %>'>
                            </asp:TextBox>
                        </EditItemTemplate>

score 0 · Answer 1 · answered Mar 21 '13 at 14:15

Yes it's probably a date format issue giving the insert error for example if you are using UK time format at the front-end and the SQL server may be using US date format. I'd always stick to date strings in ISO universal format in code to avoid these issues so i would change this line:

DOitem.InsertParameters.Add("delivery_date", strDeliveryDate);

to this

DOitem.InsertParameters.Add("delivery_date", DateTime.Parse(strDeliveryDate).ToString("s"));

the "s" converts the time to a format like this: 2013-03-12T21:15:07

Sorry i don't understand the issue in the EditTemplate, that code looks ok to me.

Insert datatime value from ASP.NET gridview to SQL Server Datatable

1 Answers1