Program

//insert data gridview data to database table and insentive display insentive name but store database to insentive id //

private void btnsave_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(constr);

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

//cmd.CommandText = "select EmployeeID from TK_Employee where EmployeeID='" + txtemployeeid.Text + "'";

//SqlDataReader dr1 = cmd.ExecuteReader();

//if (dr1.HasRows)

//{

// int a = 0;

// while (dr1.Read() == true)

// {

// a = Convert.ToInt32(dr1[0].ToString());

// }

// dr1.Close();

cmd.CommandText = "select DesignationID from TK_Designation where DesignationName='" + cmbdesignationname.Text + "'";

SqlDataReader dr2 = cmd.ExecuteReader();

if (dr2.HasRows)

{

int b = 0;

while (dr2.Read() == true)

{

b = Convert.ToInt32(dr2[0].ToString());

}

dr2.Close();

cmd.CommandText = "select SNO from TK_MasterIncentives where IncentiveName='" + cmbincentives.Text + "'";

SqlDataReader dr3 = cmd.ExecuteReader();

if (dr3.HasRows)

{

int c = 0;

while (dr3.Read() == true)

{

c = Convert.ToInt32(dr3[0].ToString());

}

dr3.Close();

cmd.CommandText = "select CompanyID from TK_Company ";

SqlDataReader dr4 = cmd.ExecuteReader();

if (dr4.HasRows)

{

int d = 0;

while (dr4.Read() == true)

{

d = Convert.ToInt32(dr4[0].ToString());

}

dr4.Close();

int DesignationID = b;

int IncentivesID = c;

int CompanyID = d;

SqlDataAdapter da = new SqlDataAdapter();

for (int i = 0; i < dgvincentives.Rows.Count; i++)

{

// cmd.CommandText = "insert into TK_EmployeeGroupEarnings(DesignationID,IncentivesID,Percentage,CompanyID) values("+DesignationID+","+IncentivesID+","+Convert.ToDecimal(txtpercentage.Text)+","+CompanyID+")";

//// SqlCommand cmd = new SqlCommand(insertData, con);

// cmd.Parameters.AddWithValue("@IncentivesID", dgvincentives.Rows[i].Cells[0].Value);

// cmd.Parameters.AddWithValue("@Percentage", dgvincentives.Rows[i].Cells[1].Value);

// //cmd.Parameters.AddWithValue("@PartsID", textBox1.Text);

// da.InsertCommand = cmd;

// cmd.ExecuteNonQuery();

cmd.CommandText = "insert into TK_EmployeeGroupIncentives(DesignationID,IncentivesID,Percentage,CompanyID) values(" + DesignationID + ",'" + dgvincentives.Rows[i].Cells["Incentives Name "].Value + "','" + dgvincentives.Rows[i].Cells["Percentage"].Value + "'," + CompanyID + ")";

int n = cmd.ExecuteNonQuery();

if (n > 0)

{

MessageBox.Show("Successfully Inserted");

}

else

{

MessageBox.Show("Not Inserted");

}

Why don't you use the 'using' statement for SQL connections? You don't have to close the SQL connections and it will avoid errors.

So instead of:

SqlConnection con = new SqlConnection(constr);

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

Do:

using (var con = new SqlConnection(constr))
{
con.Open();
using(var cmd = new SqlCommand("//some command", con))

{

        //do whatever, example : cmd.ExecuteNonQuery(); 

}

}

You are also vulnerable to SQL Injection.

So instead of:

cmd.CommandText = "insert into TK_EmployeeGroupEarnings(DesignationID,IncentivesID,Percentage,CompanyID) values("+DesignationID+","+IncentivesID+","+Convert.ToDecimal(txtpercentage.Text)+","+CompanyID+")";

cmd.ExecuteNonQuery();

Do:

cmd.CommandText = "INSERT INTO TK_EmployeeGroupEarnings (DesignationID,IncentivesID,Percentage,CompanyID) values(@name1, @name2, @name3)";

var parameter1 = cmd.Parameters.Add("@name1");
var parameter2 = cmd.Parameters.Add("@name2");
var parameter3 = cmd.Parameters.Add("@name3");
//continue the same way
parameter1.Value = //DesignationID
parameter2.Value = //IncentivesID
parameter3.Value = //Percentage
//continue the same way

cmd.ExecuteNonQuery();

Final code:

string commandd = "INSERT INTO TK_EmployeeGroupEarnings (DesignationID,IncentivesID,Percentage,CompanyID) values(@name1, @name2, @name3)"

using (var con = new SqlConnection(constr))
{
con.Open();
using(var cmd = new SqlCommand(comandd, con))

{

var parameter = cmd.Parameters.Add("@name");

var parameter2 = cmd.Parameters.Add("@name2");
var parameter3 = cmd.Parameters.Add("@name3");

parameter1.Value = //DesignationID
parameter2.Value = //IncentivesID
parameter3.Value = //Percentage

cmd.ExecuteNonQuery();

}

}

**I HAVE NOT TESTED THIS CODE**

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Raghav Jhavar10y ago
Accepted answer
Why don't you use the 'using' statement for SQL connections? You don't have to close the SQL connections and it will avoid errors.

So instead of:

SqlConnection con = new SqlConnection(constr);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;

Do:

using (var con = new SqlConnection(constr))
{
con.Open();
using(var cmd = new SqlCommand("//some command", con))
{
     //do whatever, example : cmd.ExecuteNonQuery();
}
}

You are also vulnerable to SQL Injection.

So instead of:

cmd.CommandText = "insert into TK_EmployeeGroupEarnings(DesignationID,IncentivesID,Percentage,CompanyID) values("+DesignationID+","+IncentivesID+","+Convert.ToDecimal(txtpercentage.Text)+","+CompanyID+")";

cmd.ExecuteNonQuery();

Do:

cmd.CommandText = "INSERT INTO TK_EmployeeGroupEarnings (DesignationID,IncentivesID,Percentage,CompanyID) values(@name1, @name2, @name3)";

var parameter1 = cmd.Parameters.Add("@name1");
var parameter2 = cmd.Parameters.Add("@name2");
var parameter3 = cmd.Parameters.Add("@name3");
//continue the same way
parameter1.Value = //DesignationID
parameter2.Value = //IncentivesID
parameter3.Value = //Percentage
//continue the same way

cmd.ExecuteNonQuery();

Final code:

string commandd = "INSERT INTO TK_EmployeeGroupEarnings (DesignationID,IncentivesID,Percentage,CompanyID) values(@name1, @name2, @name3)"

using (var con = new SqlConnection(constr))
{
   con.Open();
   using(var cmd = new SqlCommand(comandd, con))
   {
          var parameter = cmd.Parameters.Add("@name");
              var parameter2 = cmd.Parameters.Add("@name2");
var parameter3 = cmd.Parameters.Add("@name3");

     parameter1.Value = //DesignationID
parameter2.Value = //IncentivesID
parameter3.Value = //Percentage

cmd.ExecuteNonQuery();
   }
}

**I HAVE NOT TESTED THIS CODE**