Insert only Time in database table using C# .Net

Last Reply on Dec 29, 2014 05:19 AM By Shashikant

Posted on Dec 29, 2014 03:15 AM

In my Database table there are 2 columns named:
Date (datatype 'date')
Time (datatype 'time(7)')

When I try to insert 'system date and time' into above Table:

lblDate.Text = DateTime.Now.ToString("dd-MM-yyyy").ToString();
lblTime.Text = DateTime.Now.ToString("H:mm tt").ToString();
string query = "Insert into Invoice_Details(List_Id, Date, Time)values('" + lblId.Text + "','" + lblDate.Text + "', '" + lblTime.Text + "');
connection.Open();
SqlCommand cmd = new SqlCommand(query, connection);
int result = cmd.ExecuteNonQuery();
connection.Close();
if (result == 1)
{
   lblMsg.Text = "Record inserted successfully";
}

above code gives below exception:

Conversion failed when converting date and/or time from character string

Please reply how to solve it. I tried to find solution but didnt got any proper one. Please help what is wrong in my code

You are viewing reply posted by: Shashikant on Dec 29, 2014 05:19 AM.
Posted on Dec 29, 2014 05:19 AM Modified on on Dec 29, 2014 05:20 AM

Here I have created sample that inserts EmployeeId and his/her Leaving date.

I hope this will help you out.

HTML

<form id="form1" runat="server">
<div>
    <table border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td>
                <asp:Label Text="Employee Id" runat="server" />
            </td>
            <td>
                <asp:TextBox ID="txtEmployeeId" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                <asp:Label Text="Leave Date" runat="server" />
            </td>
            <td>
                <asp:TextBox ID="txtLeaveDate" runat="server" />
            </td>
        </tr>
        <tr>
            <td colspan="2" style="text-align: center">
                <asp:Button Text="Save" ID="btnSave" runat="server" Width="72px" OnClick="SaveEmployeeDetails" />
            </td>
        </tr>
    </table>
</div>
</form>

Namespaces

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

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.txtLeaveDate.Text = DateTime.Now.ToString();
    }
}

private void SaveEmployeeDetails()
{
    string constr = ConfigurationManager.ConnectionStrings["constrSample"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {

        using (SqlCommand cmd = new SqlCommand("INSERT INTO Employee_Leaves(EmpId,LeaveDate) VALUES(@EmpId, @LeaveDate)", con))
        {
            cmd.Parameters.AddWithValue("@EmpId", int.Parse(this.txtEmployeeId.Text.Trim()));
            cmd.Parameters.AddWithValue("@LeaveDate", Convert.ToDateTime(this.txtLeaveDate.Text).ToString("yyyy-MM-dd"));
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

protected void SaveEmployeeDetails(object sender, EventArgs e)
{
    this.SaveEmployeeDetails();
}

SQL

CREATE TABLE [dbo].[Employee_Leaves](
	[EmpId] [int] NULL,
	[Name] [varchar](50) NULL,
	[LeaveStatus] [int] NULL,
	[LeaveDate] [datetime] NULL
) ON [PRIMARY]
GO