ASP.Net Error: String was not recognized as a valid DateTime while inserting to Database

Hi Thuyai,

Check this example. Now please take its reference and correct your code.

HTML

 <asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />
<asp:GridView ID="gvDate" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField HeaderText="Date & Time" HeaderStyle-HorizontalAlign="Center"
            SortExpression="DateAndTime">
            <ItemTemplate>
                <%--<asp:Label ID="DateAndTime" runat="server" Text='<%#Eval("DateAndTime")%>'></asp:Label>--%>
                <asp:Label ID="DateAndTime" runat="server" Text='<%#Eval("BirthDate", "{0:dd/MM/yyyy}" + "<br />" + "{0:HH:mm tt}")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient

Code

C#

string str = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        BindGrid();
    }
}

private void BindGrid()
{
    SqlConnection conn = new SqlConnection(str);
    conn.Open();
    SqlCommand cmd = new SqlCommand("SELECT startDateTime FROM UserTest", conn);
    gvDate.DataSource = cmd.ExecuteReader();
    gvDate.DataBind();
    conn.Close();
}
protected void btnInsert_Click(object sender, EventArgs e)
{
    foreach (GridViewRow row in gvDate.Rows)
    {
        string DateAndTime = row.Cells[0].Text;
        SqlConnection conn = new SqlConnection(str);
        SqlCommand cmd = new SqlCommand("insert into UserTest (startDateTime) values (@startDateTime)", conn);
        conn.Open();
        Label lbl = (Label)row.FindControl("DateAndTime");
        string str1 = lbl.Text.Replace("<br />", " ");
        SqlParameter dteParam = cmd.Parameters.Add("@startDateTime", SqlDbType.DateTime);
        dteParam.Value = Convert.ToDateTime(str1, new CultureInfo("en-GB"));
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    BindGrid();
}

VB.Net

Private str As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    If Not Me.IsPostBack Then
        BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim conn As SqlConnection = New SqlConnection(str)
    conn.Open()
    Dim cmd As SqlCommand = New SqlCommand("SELECT startDateTime FROM UserTest", conn)
    gvDate.DataSource = cmd.ExecuteReader()
    gvDate.DataBind()
    conn.Close()
End Sub

Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs)
    For Each row As GridViewRow In gvDate.Rows
        Dim DateAndTime As String = row.Cells(0).Text
        Dim conn As SqlConnection = New SqlConnection(str)
        Dim cmd As SqlCommand = New SqlCommand("insert into UserTest (startDateTime) values (@startDateTime)", conn)
        conn.Open()
        Dim lbl As Label = CType(row.FindControl("DateAndTime"), Label)
        Dim str1 As String = lbl.Text.Replace("<br />", " ")
        Dim dteParam As SqlParameter = cmd.Parameters.Add("@startDateTime", SqlDbType.DateTime)
        dteParam.Value = Convert.ToDateTime(str1, New CultureInfo("en-GB"))
        cmd.ExecuteNonQuery()
        conn.Close()
    Next

    BindGrid()
End Sub

Screenshot

Please refer below 

<asp:TemplateField HeaderText="Date & Time" HeaderStyle-HorizontalAlign="Center" SortExpression="DateAndTime">
    <itemtemplate>
       
        <asp:Label ID="DateAndTime" runat="server" Text='<%#Eval("DateAndTime",  "{0:dd/MM/yyyy hh:mm:ss}")%>'></asp:Label>
    </itemtemplate>
</asp:TemplateField>

Dim lblDateAndTime= TryCast(row.FindControl("DateAndTime"), Label).Text
Dim oDate As DateTime = DateTime.ParseExact(lblDateAndTime, "dd/MM/yyyy hh:mm:ss", System.Globalization.CultureInfo.InvariantCulture);

Dim dteParam As SqlParameter = sqlCmd.Parameters.Add("@DateAndTime", Data.SqlDbType.DateTime)
DateAndTimeParam.Value = oDate