Hello,
I created a gridview in asp.net which has two column of DATETIME datatype, when I connected to mysql database to save it in, it shows me an error " Incorrect datetime value: '01/01/2017 00:07:26' for column 'LogInDate_Time' at row 1" in cmd.ExecuteNonQuery(); line
How to solve it?
HTML code
<asp:GridView ID="GridView1" runat="server" align="center" BackColor="White" BorderColor="#CCCCCC"
BorderStyle="None" BorderWidth="1px" CellPadding="4" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1" DataKeyNames="Attendance_ID" ShowFooter="True"
Style="margin-left: 183px; margin-top: 0px;">
<Columns>
<asp:TemplateField>
<FooterTemplate>
<asp:LinkButton ValidationGroup="INSERT" OnClick="lbInsert_Click" ID="lbInsert" runat="server"
ForeColor="#000066">Insert</asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:TemplateField HeaderText="No." SortExpression="Attendance_ID">
<EditItemTemplate>
<asp:TextBox ID="txtAttendanceID" runat="server" Text='<%# Bind("Attendance_ID") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblAttendanceID" runat="server" Text='<%# Bind("Attendance_ID") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtID" runat="server" BorderStyle="None"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name" SortExpression="Attendance_Name">
<EditItemTemplate>
<asp:TextBox ID="txtAttendanceName" runat="server" Text='<%# Bind("Attendance_Name") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblAttendanceName" runat="server" Text='<%# Bind("Attendance_Name") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtName" runat="server" BorderStyle="None"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Attendance" SortExpression="Attendance_Con">
<EditItemTemplate>
<asp:TextBox ID="txtAttendanceCon" runat="server" Text='<%# Bind("Attendance_Con") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblAttendanceCon" runat="server" Text='<%# Bind("Attendance_Con") %>'></asp:Label>
<asp:CheckBox ID="CheckBox2" runat="server" BorderStyle="None" AutoPostBack= "True" OnCheckedChanged="LoggedIn"></asp:CheckBox>
</ItemTemplate>
<FooterTemplate>
<asp:CheckBox ID="cbAttendanceCon" runat="server" BorderStyle="None" AutoPostBack= "True" OnCheckedChanged="LoggedIn"></asp:CheckBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Login Time" SortExpression="LogInDate_Time">
<EditItemTemplate>
<asp:TextBox ID="txtLogInDateTime" runat="server" Text='<%# Bind("LogInDate_Time") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblLoggedInDateTime" runat="server" Text='<%# Bind("LogInDate_Time") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtLogIn" runat="server" BorderStyle="None"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Leaving" SortExpression="Leaving_Con">
<EditItemTemplate>
<asp:TextBox ID="txtLeavingCon" runat="server" Text='<%# Bind("Leaving_Con") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblLeavingCon" runat="server" Text='<%# Bind("Leaving_Con") %>'></asp:Label>
<asp:CheckBox ID="CheckBox1" runat="server" BorderStyle="None" AutoPostBack="True" OnCheckedChanged="LoggedOut"></asp:CheckBox>
</ItemTemplate>
<FooterTemplate>
<asp:CheckBox ID="cbLeavingCon" runat="server" BorderStyle="None" AutoPostBack="True" OnCheckedChanged="LoggedOut"></asp:CheckBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Logout Time" SortExpression="LogOutDate_Time">
<EditItemTemplate>
<asp:TextBox ID="txtLogOutDateTime" runat="server" Text='<%# Bind("LogOutDate_Time") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblLoggedOutDateTime" runat="server" Text='<%# Bind("LogOutDate_Time") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtLogOut" runat="server" BorderStyle="None"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
<br />
C#
protected void Page_Load(object sender, EventArgs e)
{
}
protected void LoggedIn(object sender, EventArgs e)
{
CheckBox checkedCheckBox = (sender as CheckBox);
GridViewRow checkedRow = (checkedCheckBox.NamingContainer as GridViewRow);
Label loggedInDateTime = checkedRow.FindControl("lblLoggedInDateTime") as Label;
if (checkedCheckBox.Checked)
{
loggedInDateTime.Text = DateTime.Now.ToString();
}
else
{
loggedInDateTime.Text = "";
}
}
protected void LoggedOut(object sender, EventArgs e)
{
CheckBox checkedCheckBox = (sender as CheckBox);
GridViewRow checkedRow = (checkedCheckBox.NamingContainer as GridViewRow);
Label loggedOutDateTime = checkedRow.FindControl("lblLoggedOutDateTime") as Label;
if (checkedCheckBox.Checked)
{
loggedOutDateTime.Text = DateTime.Now.ToString();
}
else
{
loggedOutDateTime.Text = "";
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
foreach (GridViewRow row in GridView1.Rows)
{
string Attendance_ID = (row.FindControl("lblAttendanceID") as Label).Text;
string Attendance_Name = (row.FindControl("lblAttendanceName") as Label).Text;
string LogInDate_Time = (row.FindControl("lblLoggedInDateTime") as Label).Text;
string LogOutDate_Time = (row.FindControl("lblLoggedOutDateTime") as Label).Text;
InsertData(Attendance_ID, Attendance_Name, LogInDate_Time, LogOutDate_Time);
}
lblMessage.Text = "All Records Saved Successfully!!";
}
public void InsertData(string Attendance_ID, string Attendance_Name, string LogInDate_Time, string LogOutDate_Time)
{
//Your saving code.
string A = "server=localhost; userid=; password=; database=admindb; allowuservariables=True; Convert Zero Datetime=True; Allow Zero Datetime=True ";
using (MySqlConnection connection = new MySqlConnection(A))
{
string UpdateQuery = " INSERT INTO Attendance_Table (Attendance_ID, Attendance_Name,LogInDate_Time, LogOutDate_Time)" + " VALUES (@Attendance_ID,@Attendance_Name,@LogInDate_Time,@LogOutDate_Time)";
MySqlCommand cmd = new MySqlCommand(UpdateQuery, connection);
MySqlParameter paramAttendance_ID = new MySqlParameter("@Attendance_ID", Attendance_ID);
cmd.Parameters.Add(paramAttendance_ID);
MySqlParameter paramAttendance_Name = new MySqlParameter("@Attendance_Name", Attendance_Name);
cmd.Parameters.Add(paramAttendance_Name);
MySqlParameter paramLogInDate_Time = new MySqlParameter("@LogInDate_Time", LogInDate_Time);
cmd.Parameters.Add(paramLogInDate_Time);
MySqlParameter paramLogOutDate_Time = new MySqlParameter("@LogOutDate_Time", LogOutDate_Time);
cmd.Parameters.Add(paramLogOutDate_Time);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
protected void lbInsert_Click(object sender, EventArgs e)
{
ObjectDataSource1.InsertParameters["Attendance_ID"].DefaultValue = ((TextBox)GridView1.FooterRow.FindControl("TxtID")).Text;
ObjectDataSource1.InsertParameters["Attendance_Name"].DefaultValue = ((TextBox)GridView1.FooterRow.FindControl("TxtName")).Text;
ObjectDataSource1.InsertParameters["Attendance_Con"].DefaultValue = ((CheckBox)GridView1.FooterRow.FindControl("cbAttendanceCon")).Text;
ObjectDataSource1.InsertParameters["LogInDate_Time"].DefaultValue = ((TextBox)GridView1.FooterRow.FindControl("txtLogIn")).Text;
ObjectDataSource1.InsertParameters["Leaving_Con"].DefaultValue = ((CheckBox)GridView1.FooterRow.FindControl("cbLeavingCon")).Text;
ObjectDataSource1.InsertParameters["LogOutDate_Time"].DefaultValue = ((TextBox)GridView1.FooterRow.FindControl("txtLogOut")).Text;
ObjectDataSource1.Insert();
}
}