How to check if a row exist in sql table using datagridview column?

leonard krasniqi

5y1.6kLearn iOS Programming

I am trying to check if a row exist in sql using a datagridview column

I am using these lines of code:

SqlCommand cmdcontrol= new SqlCommand("checkbarcode", conn);

cmdcontrol.CommandType = CommandType.StoredProcedure;

cmdcontrol.Parameters.AddWithValue("@barcode", Convert.ToString(tgproduct.Rows[i].Cells[0].Value));

int product = (int)cmdcontrol.ExecuteScalar();

if (product > 0)

{

//update qty of product based on barcode (from column index 0)

}

else

{

//do a insertion process with all details from datagridview

}

Note that the datagridview may have product that aren't inserted in sql , and products that are inserted in sql and need to update only QTY.

I tested the code to do insertion or update lonely (not both cases in same time) and code works, but when i mix both cases on of the processes isn't done(update or insert process).

I think the problem is in the loop code, where data from datagridview are checked to see if they exist in sql(and based on result the insertion/update process is done).

Could someone help me why when I put a product in datagridview which exist in sql( and need to update qty), and a procut that doesn't exist in sql table(need to insert the product), both process doesn't happen?

Thanks to everyone

5 Replies

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

leonard krasniqi5y ago
Sachin Singh, do you have any suggestion?
leonard krasniqi5y ago
Here is the full code:
for (int i = 0; i < dtgartikulli.Rows.Count; i++)
{
SqlCommand cmdkontrolla = new SqlCommand("barkodikontrollo", conn);
cmdkontrolla.CommandType = CommandType.StoredProcedure;
cmdkontrolla.Parameters.AddWithValue("@barkodi", Convert.ToString(dtgartikulli.Rows[i].Cells[0].Value));
SqlDataAdapter adapt = new SqlDataAdapter(cmdkontrolla);
DataSet ds = new DataSet();
adapt.Fill(ds);
conn.Close();
int count = ds.Tables[0].Rows.Count;
//nese është barazi me 1, shfaqe dritaren pasuese
if (count == 1)
{
foreach (DataGridViewRow row in dtgartikulli.Rows)
{
if (!row.IsNewRow)
{
SqlCommand cmdeditimi = new SqlCommand("updatestokublerja", conn);
cmdeditimi.CommandType = CommandType.StoredProcedure;
cmdeditimi.Parameters.AddWithValue("@barkodi", row.Cells[0].Value);
cmdeditimi.Parameters.AddWithValue("@sasi", row.Cells[3].Value);
conn.Open();
cmdeditimi.ExecuteNonQuery();
conn.Close();
}
}
}
else
{ SqlCommand cmdinsertimi = new SqlCommand("insertprodukti", conn);
cmdinsertimi.CommandType = CommandType.StoredProcedure;
SqlParameter ibarkodi = cmdinsertimi.Parameters.Add("@barkodi", SqlDbType.Int);
SqlParameter iemertimi = cmdinsertimi.Parameters.Add("@emertimi", SqlDbType.VarChar, 200);
SqlParameter isasia = cmdinsertimi.Parameters.Add("@sasia", SqlDbType.Int);
SqlParameter istoku = cmdinsertimi.Parameters.Add("@stoku", SqlDbType.Int);
SqlParameter icmimi = cmdinsertimi.Parameters.Add("@cmimi", SqlDbType.Float);
SqlParameter icmimif = cmdinsertimi.Parameters.Add("@cmimif", SqlDbType.Float);
SqlParameter ifitimi = cmdinsertimi.Parameters.Add("@fitimi", SqlDbType.Float);
SqlParameter itvsh = cmdinsertimi.Parameters.Add("@tvsh", SqlDbType.Float);
SqlParameter ikategoria = cmdinsertimi.Parameters.Add("@kategoria", SqlDbType.VarChar, 100);
SqlParameter idata = cmdinsertimi.Parameters.Add("@data", SqlDbType.DateTime);
SqlParameter ishtuarnga = cmdinsertimi.Parameters.Add("@shtuarnga", SqlDbType.VarChar, 100); S
qlParameter injesia = cmdinsertimi.Parameters.Add("@njesia", SqlDbType.VarChar, 50);
foreach (DataGridViewRow row in dtgartikulli.Rows)
{ if (!row.IsNewRow)
{ ibarkodi.Value = row.Cells[0].Value;
iemertimi.Value = row.Cells[1].Value;
isasia.Value = "1";
istoku.Value = row.Cells[3].Value;
icmimi.Value = row.Cells[2].Value;
icmimif.Value = row.Cells[4].Value;
ifitimi.Value = row.Cells[5].Value;
itvsh.Value = row.Cells[7].Value;
ikategoria.Value = row.Cells[6].Value;
idata.Value = DateTime.Now;
ishtuarnga.Value = lbluseri.Text;
injesia.Value = row.Cells[9].Value;
conn.open();
cmdinsertimi.ExecuteNonQuery();
conn.close();
}
}
Sachin Singh5y ago
please show your full code including loop
leonard krasniqi5y ago
@
Sachin Singh
Yes i followed this logic code, but its not working in same time update and insert process.
I think something is wrong with loop code.
Becasue when i have in same datagridivew the both cases (if and else) one of the cases isnt running. But when i runn seperated the data its works. For example if in the dtg i have only data to be updated the update process, same alsoif i have data to do the insert process the insertion process is done fine. But when i have in same dtg data to be updated an inserted one of the operation don't run. Something mus be wrong with loop code
Sachin Singh5y ago
If(product>0) { string command="update table set quantity="+ qty; SqlCommand cmd=new SqlCommand(command, con) ; Cmd.ExecuteNonQuery() ; } Else { call and execute Insert store procedure }

How to check if a row exist in sql table using datagridview column?

Sachin Singh, do you have any suggestion?