我需要在插入时从客户表更新商店表吗？ [英] i need to update store table from customer table while insertion?

问题描述

我的问题是：我有客户表tblPurchaseDetails同时在这个表中插入数据我同时需要在商店表中更新，即tblMasterDetails来自同一个地方也应该检查商店是否为空它应该只是插入项目如果它应该通过将商店价值与当前客户价值相加来更新存储价值...

例如

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();
                       }

推荐答案

你看过触发器，你可以借助触发器来实现这种行为。在主表即tblPurchaseDetails上，必须创建一个Insert Trigger，在触发器逻辑中，你可以检查Magic Tables中的值，即插入并写入辅助表的逻辑，即tblMasterDetails insert。





对于触发器，请参阅 https：// msdn .microsoft.com / zh-CN / library / ms189799.aspx [ ^ ]

Have you looked at Triggers, you can achieve this behavior with the help of triggers. On the Primary Table i.e. tblPurchaseDetails , one must create a Insert Trigger, inside the trigger logic, you can check the value in Magic Tables i.e. inserted and write the logic for the secondary table i.e. tblMasterDetails insert.


For Triggers refer to https://msdn.microsoft.com/en-us/library/ms189799.aspx[^]

这篇关于我需要在插入时从客户表更新商店表吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！