bar code data and Update/insert

Guys i am new to C# and stuck on below problem

1. i am not able to update data as per below code. insert is working normally but not the update. some syntax error which i cant figure out.

2. barcode is populated but not able to either insert/ update. gives me error. while if input normal number from my keyboard is gets inserted in database. so why barcode is not getting checked on SELECT command.

private void button1_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(global::Inventory_System.Properties.Settings.Default.Database1ConnectionString);
cn.Open();
SqlCommand cmdCount = new SqlCommand("SELECT count(*) from Stock WHERE ProductCode = @ProductCode", cn);
cmdCount.Parameters.AddWithValue("ProductCode", tbstockpc.Text);
int count = (int) cmdCount.ExecuteScalar();
if (count > 0)
{
try
{
int Stprice = 0;
int qty = Convert.ToInt32(tbstockqty.Text);
int puprice = Convert.ToInt32(tbstockpurprice.Text);
Stprice = qty * puprice;
string sql = "Update Stock(ProductCode,ProductDesc,Quantity,Stockdate,UnitPrice,UnitPricePur,StockPrice) values('" + tbstockpc.Text + "','" + tbstockdesc.Text + "','" + tbstockqty.Text + "','" + tbstockdate.Text + "','" + tbstocksaleprice.Text + "','" + tbstockpurprice.Text + "', '" + Stprice + "' )";
SqlCommand exeSql = new SqlCommand(sql, cn);
exeSql.ExecuteNonQuery();
this.StockTableAdapter.Update(this.database1DataSet.Stock);
MessageBox.Show("Data is Updated to Database", "Stock Entered", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
cn.Close();
tbstockdesc.Text = " ";
tbstockpc.Text = " ";
tbstockpurprice.Text = " ";
tbstockqty.Text = " ";
tbstocksaleprice.Text = " ";
}
}
else
{
try
{
int Stprice = 0;
int qty = Convert.ToInt32(tbstockqty.Text);
int puprice = Convert.ToInt32(tbstockpurprice.Text);
Stprice = qty * puprice;
string sql = "insert Stock(ProductCode,ProductDesc,Quantity,Stockdate,UnitPrice,UnitPricePur,StockPrice) values('" + tbstockpc.Text + "','" + tbstockdesc.Text + "','" + tbstockqty.Text + "','" + tbstockdate.Text + "','" + tbstocksaleprice.Text + "','" + tbstockpurprice.Text + "', '" + Stprice + "' )";
SqlCommand exeSql = new SqlCommand(sql, cn);
exeSql.ExecuteNonQuery();
MessageBox.Show("Data is Inserted to Database", "Stock Entered", MessageBoxButtons.OK, MessageBoxIcon.Information);
//this.stockTableAdapter.Fill(this.database1DataSet.stock);
this.StockTableAdapter.Fill(this.database1DataSet.Stock);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
cn.Close();
tbstockdesc.Text = " ";
tbstockpc.Text = " ";
tbstockpurprice.Text = " ";
tbstockqty.Text = " ";
tbstocksaleprice.Text = " ";
}
}
}

2 Replies

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

Ramesh Palanivel8y ago
Accepted answer
Hi fanan,

Your update query is wrong, Thats the reason you updating failed. Please try with below code,
string sql = "Update Stock set ProductCode='" + tbstockpc.Text + "',ProductDesc='" + tbstockdesc.Text + "',Quantity='" + tbstockqty.Text + "',Stockdate='" + tbstockdate.Text + "',UnitPrice='" + tbstocksaleprice.Text + "',UnitPricePur='" + tbstockpurprice.Text + "',StockPrice='" + Stprice + "'";
Fanan Hassan8y ago
Thanks alot buddy. this worked.

Now one more thing is that barcodes are different and as above code i tried using biginteger as some of the bar codes exeeds the int range. tired biginteger but its giving me error. i have added the reference dll and using system numerics.
on int below is error

The conversion of the nvarchar value '8964000977736' overflowed an int column.

changed int to biginteger in code and (changed in SQL table also)
Specified cast is not valid.