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

Posted on Dec 29, 2014 04:29 AM Modified on on Dec 29, 2014 05:03 AM

This is the working solution : Use any sql option but do not change the C# .

SQL OPTION 1 :

CREATE TABLE [dbo].[tempTable](
	[List_Id] [int] NULL,
	[Date] [datetime] NULL,
	[Time] [datetime] NULL
) ON [PRIMARY]

GO

SQL OPTION 2 :

CREATE TABLE [dbo].[tempTable](
    [List_Id] [int] NULL,
    [Date] date NULL,
    [Time] [datetime] NULL
) ON [PRIMARY]
 
GO

C# :

string query = "Insert into tempTable(List_Id, Date, Time) VALUES('" + lblId.Text + "', @Date, @Time)";
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
TimeSpan time = new TimeSpan();
time.ToString();
cmd.Parameters.AddWithValue("@Date", Convert.ToDateTime(DateTime.Now.ToShortDateString()));
cmd.Parameters.AddWithValue("@Time", Convert.ToDateTime(DateTime.Now.ToString("HH:mm:ss")));
cmd.ExecuteNonQuery();
connection.Close();

NOTE :

To save only time in HH:mm format you will have to use nvarchar , which is a bad idea ... so save it the way i did , then when you are about to show the records in gridview at that time format it in gridview so it will show you the time in HH:mm:ss or HH:mm


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