How to insert data in the same table and row in vb.net

Question

Good day everyone! I want to record the time in and time out of an employee using date time picker. Saving the time in is good, but the time out it will not save. I have to buttons labeled Time in and Time out. what I want to do is when I press the time out button I want to record the time of the said employee. I hope someone can help me, I am still searching in the internet but nothing seems to work. Thanks in advance guys!

My code looks like this.

cmd.CommandText = "INSERT INTO timelogTB (Time_Out) select Time_Out from timelogTB WHERE EmpNo= '" & comboxEmpno.Text & "'" & _
            "VALUES (@Time_Out)"

                  'add parameters
        With cmd.Parameters
            .Add(New SqlParameter("@Time_Out", dtpTime.Value.TimeOfDay))
        End With

Your sql statement makes no sense. What exactly do you want to do? — Zohar Peled
It was inside a command button it will record the time using date time picker I want to insert it in the same row of an employee that has already time in. — AtanHokage04
Please edit your question to include the relevant table DDL statement. — Zohar Peled
You cannot INSERT into an existing row - that is called an UPDATE — Ňɏssa Pøngjǣrdenlarp

Fabio Fabio · Accepted Answer · 2017-01-02T07:06:11

Use Update because row for employee already exists in database and use WHERE condition to save out time in row of correct user.
And use SqlParameters for all values you use in the query.

cmd.CommandText = "UPDATE timelogTB SET Time_Out = @Time_Out WHERE EmpNo=@EmpNo"
Dim parameters As SqlParameter() =
{
    New SqlParameter With 
    { 
        .ParameterName = "@Time_Out",
        .SqlDbType = SqlDbType.DateTime,
        .Value = dtpTime.Value.TimeOfDay
    },
    New SqlParameter With 
    { 
        .ParameterName = "@EmpNo",
        .SqlDbType = SqlDbType.VarChar,
        .Value = comboxEmpno.Text
    }
}

cmd.Parameters.AddRange(parameters)

' Execute query

And as Zohar Peled noticed, if timelogTB table contain input-output times for different days, then you need to add more WHERE condition for updating correct row.
For example you need update only row where Time_Out is null.

"UPDATE timelogTB SET Time_Out = @Time_Out WHERE EmpNo=@EmpNo AND Time_Out IS NULL"

How to insert data in the same table and row in vb.net

1 Answers