mine problem is: i have customer table tblPurchaseDetails while inserting data in this table same time i need to update in store table i.e tblMasterDetails from from same place also its should check store if it is empty it should just insert item else if it should update value to store by summing store value to current customer value...
for example
customer item=100kg rice
store item =50kg
finally store=150kg
if store is empty
then store =100kg
Code
try
{
connection.Open();
OleDbCommand cmd = new OleDbCommand("Insert into tblPurchaseDetails (fldId,fldCustomerName,fldCustomerAddress,fldCustomerPhone,fldDate,fldVehicleNo,fldItemName,fldPerBag,fldNetBag,fldNetQuantity,fldUnitRate,fldNetRate,fldStockId) values ('" + txtid.Text + "','" + txtname.Text + "','" + txtaddress.Text + "','" + txtphone.Text + "','" + txtdate.Text + "','" + txtVehicleNo.Text + "','" + txtItemName.Text + "','" + txtPerbag.Text + "','" + txtNetbag.Text + "','" + txtNetQuantity.Text + "','" + txtUnitRate.Text + "','" + txtNetRate.Text + "','" + cmbId.Text + "')", connection);
cmd.ExecuteReader();
connection.Close();
CustomerCode();
}
catch (OleDbException ex)
{
if (ex.ErrorCode == -2147467259)
{
OleDbCommand cmd = new OleDbCommand("Update tblPurchaseDetails SET fldCustomerName='"+txtname.Text+"',fldCustomerAddress='"+txtaddress.Text+"',fldCustomerPhone='"+txtphone.Text+"',fldDate='"+txtdate.Text+"',fldVehicleNo='"+txtVehicleNo.Text+"',fldItemName='"+txtItemName.Text+"',fldPerBag='"+txtPerbag.Text+"',fldNetBag='"+txtNetbag.Text+"',fldNetQuantity='"+txtNetQuantity.Text+"',fldUnitRate='"+txtUnitRate.Text+"',fldNetRate='"+txtNetRate.Text+"',fldStockId='"+cmbId.Text+"' Where fldId='"+txtid.Text+"' ");
cmd.ExecuteNonQuery();
}
else
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
try
{
connection.Open();
OleDbCommand cod = new OleDbCommand("Select fldItemName,fldNetBag,fldNetQuantity from tblMasterDetails Where fldItemName='"+txtItemName.Text+"'",connection);
OleDbDataReader res=cod.ExecuteReader();
while (res.Read())
{
int result = res.GetInt32(0);
if (result == null)
{
OleDbCommand cmd = new OleDbCommand("Insert into tblMasterDetails (fldItemName,fldNetBag,fldNetQuantity) Values('" + txtItemName.Text + "','" + txtNetbag.Text + "' ,'" + txtNetQuantity.Text + "')", connection);
cmd.ExecuteNonQuery();
}
else
{
OleDbCommand con = new OleDbCommand("Update tblMasterDetails SET fldNetBag='" + txtNetbag.Text + "' , fldNetQuantity='" + txtNetQuantity.Text + "' Where fldItemName='" + txtItemName.Text + "'", connection);
con.ExecuteNonQuery();
}