CODEWITHSUNDEEP
c#asp.net
user959443
user959443

Reputation: 97

check existing record before inserting sql database

I created a simple asp.net form which allow users to view a list of dates for a training and register for that date , they enter their name and employeeid manually ( i dont want to allow dulpicate employe ids), so I need to figure out how to check this on c#..

code:

 public string GetConnectionString()
    {
        //sets the connection string from your web config file "ConnString" is the name of your Connection String
        return System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;


    }

    private void checkContraint()
    {

        SqlConnection conn = new SqlConnection(GetConnectionString());
        string sql = "Select "; //NEED HELP HERE

    }


    private void InsertInfo()
    {
        var dateSelected = dpDate.SelectedItem.Value;
        SqlConnection conn = new SqlConnection(GetConnectionString());

        string sql = "INSERT INTO personTraining (name,department,title,employeeid,training_id, training,trainingDate,trainingHour, trainingSession)SELECT @Val1b+','+@Val1,@Val2,@Val3,@Val4,training_id,training,trainingDate,trainingHour,trainingSession FROM tbl_training WHERE  training_id =@training_id ";


        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);


            cmd.Parameters.AddWithValue("@Val1", txtName.Text);
            cmd.Parameters.AddWithValue("@Val1b", txtLname.Text);
            cmd.Parameters.AddWithValue("@Val2", txtDept.Text);
            cmd.Parameters.AddWithValue("@Val3", txtTitle.Text);
            cmd.Parameters.AddWithValue("@Val4", txtEmployeeID.Text);

            //Parameter to pass for the select statement
            cmd.Parameters.AddWithValue("@training_id", dateSelected);
            cmd.CommandType = CommandType.Text;
            //cmd.ExecuteNonQuery();

            int rowsAffected = cmd.ExecuteNonQuery();
            if (rowsAffected == 1)
            {

                //Success notification            // Sends user to redirect page

                Response.Redirect(Button1.CommandArgument.ToString());
                ClearForm();
            }
            else
            {
                //Error notification      

            }  
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            conn.Close();
        }
    } 

    protected void Button1_Click(object sender, EventArgs e)
    {

        checkContraint();
        InsertInfo();

Upvotes: 0

Views: 3695

Answers (1)

Royi Namir
Royi Namir

Reputation: 148524

this way , your query will insert data only if not exists already

 string sql = "INSERT INTO personTraining (name,department,title,employeeid,training_id, training,trainingDate,trainingHour, trainingSession)SELECT @Val1b+','+@Val1,@Val2,@Val3,@Val4,training_id,training,trainingDate,trainingHour,trainingSession FROM tbl_training WHERE  training_id =@training_id and not exists (select 1 from personTraining pp where pp .employeeid=@Val4) ";

Upvotes: 3

Related Questions