How to change value on column in database

Question

I'm working on Registration page for Lucky Draw system. I have created a database Employees which contain emp_id , emp_name, emp_deparment and attendance. I have set the column attendance to default value "Absent".But on database is show null.Click this link to view employees database.

So the process for registration is user need to scan their barcode using usb barcode scanner then the system will show their name and update the default value on Attendance column to "Present".I stuck on that part.Can anyone give me some ideas/solution.

attendance code :

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


public partial class Attendance : System.Web.UI.Page
{
    SqlCommand cmd = new SqlCommand();
    SqlConnection con = new SqlConnection();
    string str;
    protected void Page_Load(object sender, EventArgs e)
    {
        con.ConnectionString = @"Data Source= (LocalDB)\MSSQLLocalDB; AttachDbFilename = 
           C:\Users\Nor  Asyraf  Mohd No\source\repos\LuckyDraw\LuckyDraw\App_Data\ticket.mdf; 
            Integrated Security = True";

        con.Open();
        txtText.Focus();
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {



    }

void idcheck()
        {
            string selectQuery = "SELECT count(*) FROM EMPLOYEES where EMP_ID = '" + txtText.Text + "'";
            SqlCommand cmd = new SqlCommand(selectQuery, con);
            SqlDataReader myReader = null;
            int count = Convert.ToInt32(cmd.ExecuteScalar());
            if (count > 0)

            {
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                    lblError.Text = myReader["EMP_NAME"].ToString();
                lblError.ForeColor = System.Drawing.Color.Green;

            }
            else
            {
                lblError.Text = "Employee not available";
                lblError.ForeColor = System.Drawing.Color.Red;
            }

        }

}

The default on the column now is null @Coding [https://i.stack.imgur.com/B9GCA.png] — Ash93, Mar 22 '18 at 02:38

score 0 · Answer 1 · answered Mar 22 '18 at 03:17

0

If I understand your question correctly, you want to update the value for Attandence to 'Present' inside the btnSave_Click function. I think you should be able to use the following code to do that:

protected void btnSave_Click(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection(connectionString)) {
        using (SqlCommand command = connection.CreateCommand()) {
            command.Text = "UPDATE EMPLOYEES SET Attendance = 'Present' WHERE EMP_ID = @id";
            command.Parameters.AddWithValue("@id", txtText.Text);
            connection.Open();

            command.ExecuteNonQuery();

            connection.Close();
        }
    }
}

The benefit of using command.Parameters.AddWithValue is that it helps to prevent SqlInjections.

The using statements make sure that a connection is disposed after the code leaves the using block. You might want to use them inside your other function too, if you don't it might be that the above code throws an exception because you have multiple concurrent connection to the same database.

answered Mar 22 '18 at 03:17

user2074945

537
2
8
22

I think your solution going to work,but i still have a problem with my id check code so i cannot run yet the system @user2074945 – Ash93 Mar 22 '18 at 03:39
How can i check emp_id on database and display emp_name based on the id that have been scan? Below are my code that i have tried. `public void idcheck() { string query = "select EMP_ID from EMPLOYEES where EMP_ID='" + txtText.Text + "'"; SqlDataAdapter ada = new SqlDataAdapter(query, con); DataTable dt = new DataTable(); ada.Fill(dt); if (dt.Rows.Count > 0) { lblError.Text = dt.Rows[0]["EMP_NAME"].ToString(); } }` – Ash93 Mar 22 '18 at 06:13
What exactly is the problem with your code? Where do errors occur? What is the error message? – user2074945 Mar 22 '18 at 15:01
Now i have a problem with id check, i cannot check id on database and display employee name on label. – Ash93 Mar 23 '18 at 03:27

score 0 · Answer 2 · answered Mar 22 '18 at 15:14

Your question is not clear, but in order to set the default value of your column, go to SQL Management studio and do the following:

Right click your table
Select Design
Select the column you want, in this case "Attendance"
Change the "Default Value or Binding" Property

How to change value on column in database

2 Answers2