How to save values in SQL in two different columns in a table without crashing of the values in a network.
Ex: There are 20 users in my network.All wants to update one particular row at a time.
Here the crashing has been taken place.That means suppose I have taken one table in that many columns there.The user wants to update two columns a,b with their values at a time. when we click on update sometimes it swaps the data.
a=10,
b=20 i want to update
but it updated like this a=20,b=10
sometimes it has been taken null value also.
Why does it happen?
If you explain how to solve this problem.It would be appreciated.
I have written this code in c# to save.But while users saving at a time one row.The swapping of column values saving taken place.
sqlstr = "Insert into invINVOICEDETAILS (comment_NAME30, PRODUCT_NO, PRODUCT_NAME, QTY, UOM, PRICE, ITEM1_NO, DISCOUNT_PC, AMOUNT, SC_NO, GL_CODE, PROJ_NO, Gp_NO, WH_NO, Sales_exempt,FACTOR," +
"Category, IMPORTED_FROM_NO, IMPORTED_FROM, GST_Amt, DISCOUNT_PC_2,Ref_No, GL_DESC, PRODUCT_NAME_LONG, CREATED_BY," +
"DATE_CREATED, Deleted, INV_NO,Transfer, TICK, ID, AC_NO, AC_NAME, ITEM10, invNo_user, AVERAGE_COST, LP_COST, STD_COST,QTY_UNFILLED,DATETIME,Batch_No," +
"Expiry_Date,PRINT_ITEM,QTY_BILLED,ITEM6_NO,ITEM7_NO,ITEM8_NO,IMPORT_KEY) values (@comment_NAME30,@PRODUCT_NO,@PRODUCT_NAME,@QTY,@UOM,@PRICE," +
"@ITEM1_NO,@DISCOUNT_PC,@AMOUNT,@SC_NO,@GL_CODE,@PROJ_NO,@Gp_NO," +
"@WH_NO,@Sales_exempt,@FACTOR,@Category,@IMPORTED_FROM_NO," +
"@IMPORTED_FROM,@GST_Amt,@DISCOUNT_PC_2,@Ref_No,@GL_DESC," +
"@PRODUCT_NAME_LONG, @CREATED_BY, @DATE_CREATED, @Deleted, @INV_NO," +
"@Transfer,@TICK,@ID,@AC_NO,@AC_NAME,@ITEM10,@invNo_user,@AVERAGE_COST," +
"@LP_COST,@STD_COST,@QTY_UNFILLED,@DATETIME,@Batch_No,@Expiry_Date," +
"@PRINT_ITEM, @QTY_BILLED,@ITEM6_NO,@ITEM7_NO,@ITEM8_NO,@IMPORT_KEY)";
}
else
{
sdata += "PRODUCT_NAME_LONG=N'" + cfs.singlequotconver(strdesc) + "', UPDATED_BY ='" + uid.ToString() + "',DATE_UPDATED='" + cfs.sqldateconverion() + "',Deleted='False',invNo_user='" + ddl_Inv.Text + uid.ToString() + "',CREATED_BY='" + uid + "' ," +
"Transfer='U',TICK='C',AC_NO='" + cfs.singlequotconver(txt_Custcode.Text) + "',AC_NAME=N'" + cfs.singlequotconver(txt_Cus_Name.Text) + "',QTY_BILLED=" + Prodqty + ",ITEM10='" + cfs.singlequotconver(LineNo) + "',AVERAGE_COST=(select distinct AVERAGE_COST from PRODUCT where PRODUCT_NO='" + strpcode + "'),LP_COST=(select distinct LP_COST from PRODUCT where PRODUCT_NO='" + strpcode + "'),STD_COST=(select distinct STD_COST from PRODUCT where PRODUCT_NO='" + strpcode + "'),QTY_UNFILLED='" + strqtyfilled + "',DATETIME='" + cfs.sqldateconverion(txt_trans_Date.Value) + "'";
try
{
if (row.Cells["no"].Value.ToString() == "1")
{
sdata += ",PRINT_ITEM='GD',Batch_No='" + row.Cells["BATCH_CODE"].Value.ToString() + "',Expiry_Date='" + row.Cells["EXPIRY_DATE"].Value.ToString() + "'";
}
}
catch (Exception Ex) { }
if (ZeroQtySave)
{
try
{
sdata += ",ITEM6_NO=" + cfs.IsNumber(row.Cells["Qty"].Value.ToString()) + ",ITEM7_NO='" + pri + "',ITEM8_NO='" + cfs.IsNumber(row.Cells["NetAmount"].Value.ToString()) + "' ";
}
catch (Exception Ex)
{
}
}
sqlstr = "update invINVOICEDETAILS set comment_NAME30=@comment_NAME30," +
"PRODUCT_NO=@PRODUCT_NO,PRODUCT_NAME=@PRODUCT_NAME,QTY=@QTY,UOM=@UOM,PRICE=@PRICE," +
"ITEM1_NO=@ITEM1_NO,DISCOUNT_PC=@DISCOUNT_PC,AMOUNT=@AMOUNT,SC_NO=@SC_NO," +
"GL_CODE=@GL_CODE,PROJ_NO=@PROJ_NO,Gp_NO=@Gp_NO,WH_NO=@WH_NO,Sales_exempt=@Sales_exempt,FACTOR=@FACTOR,GST_Amt=@GST_Amt,DISCOUNT_PC_2=@DISCOUNT_PC_2," +
"PRODUCT_NAME_LONG=@PRODUCT_NAME_LONG,Category=@Category,IMPORTED_FROM_NO=@IMPORTED_FROM_NO,UPDATED_BY =@UPDATED_BY,DATE_UPDATED=@DATE_UPDATED," +
"Deleted=@Deleted,CREATED_BY=@CREATED_BY ,Transfer=@Transfer,TICK=@TICK,AC_NO=@AC_NO," +
"AC_NAME=@AC_NAME,QTY_BILLED=@QTY_BILLED,ITEM10=@ITEM10,invNo_user=@invNo_user,IMPORTED_FROM=@IMPORTED_FROM,AVERAGE_COST=@AVERAGE_COST,LP_COST=@LP_COST,STD_COST=@STD_COST," +
"QTY_UNFILLED=@QTY_UNFILLED,DATETIME=@DATETIME,Batch_No=@Batch_No,Expiry_Date=@Expiry_Date,PRINT_ITEM=@PRINT_ITEM," +
"ITEM6_NO=@ITEM6_NO,ITEM7_NO=@ITEM7_NO,ITEM8_NO=@ITEM8_NO,IMPORT_KEY=@IMPORT_KEY where LINE = @LINE";
}
cmdTemp.CommandText = sqlstr;
try
{
cmdTemp.Parameters.AddWithValue("@comment_NAME30", ((object)row.Cells[0].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("PRODUCT_NO", ((object)row.Cells[1].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@PRODUCT_NAME", ((object)row.Cells[2].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@QTY", ((object)row.Cells[3].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@UOM", ((object)row.Cells[4].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@PRICE", ((object)row.Cells[5].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@ITEM1_NO", ((object)row.Cells[6].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@DISCOUNT_PC", ((object)row.Cells[7].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@AMOUNT", ((object)row.Cells[8].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@SC_NO", ((object)row.Cells[10].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@GL_CODE", ((object)row.Cells[11].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@PROJ_NO", ((object)row.Cells[12].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@Gp_NO", ((object)row.Cells[13].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@WH_NO", ((object)row.Cells[14].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@Sales_exempt", ((object)row.Cells[15].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@FACTOR", ((object)row.Cells[16].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@Category", ((object)row.Cells[18].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@IMPORTED_FROM_NO", ((object)row.Cells[19].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@IMPORTED_FROM", ((object)row.Cells[20].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@GST_Amt", ((object)row.Cells[23].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@DISCOUNT_PC_2", ((object)row.Cells[24].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@Ref_No", ((object)row.Cells[25].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@GL_DESC", ((object)row.Cells[27].Value) ?? DBNull.Value);
cmdTemp.Parameters.AddWithValue("@PRODUCT_NAME_LONG", cfs.singlequotconver(strdesc));
cmdTemp.Parameters.AddWithValue("@CREATED_BY", uid.ToString());
cmdTemp.Parameters.AddWithValue("@DATE_CREATED", cfs.sqldateconverion());
cmdTemp.Parameters.AddWithValue("@Deleted", "False");
cmdTemp.Parameters.AddWithValue("@INV_NO", ddl_Inv.Text);
cmdTemp.Parameters.AddWithValue("@Transfer", "U");
cmdTemp.Parameters.AddWithValue("@TICK", "C");
cmdTemp.Parameters.AddWithValue("@ID", strInvId);
cmdTemp.Parameters.AddWithValue("@AC_NO", cfs.singlequotconver(txt_Custcode.Text));
cmdTemp.Parameters.AddWithValue("@AC_NAME", cfs.singlequotconver(txt_Cus_Name.Text));
cmdTemp.Parameters.AddWithValue("@ITEM10", cfs.singlequotconver(LineNo));
cmdTemp.Parameters.AddWithValue("@invNo_user", ddl_Inv.Text + uid.ToString());
cmdTemp.Parameters.AddWithValue("@AVERAGE_COST", cfs.get_data("select distinct AVERAGE_COST from PRODUCT where PRODUCT_NO='" + strpcode + "'"));
cmdTemp.Parameters.AddWithValue("@LP_COST", cfs.get_data("select distinct LP_COST from PRODUCT where PRODUCT_NO='" + strpcode + "'"));
cmdTemp.Parameters.AddWithValue("@STD_COST", cfs.get_data("select distinct STD_COST from PRODUCT where PRODUCT_NO='" + strpcode + "'"));
cmdTemp.Parameters.AddWithValue("@QTY_UNFILLED", strqtyfilled);
cmdTemp.Parameters.AddWithValue("@DATETIME", cfs.sqldateconverion(txt_trans_Date.Value));
cmdTemp.Parameters.AddWithValue("@Batch_No", batchno);
cmdTemp.Parameters.AddWithValue("@Expiry_Date", Expirydate);
cmdTemp.Parameters.AddWithValue("@PRINT_ITEM", printitem);
cmdTemp.Parameters.AddWithValue("@QTY_BILLED", Prodqty);
cmdTemp.Parameters.AddWithValue("@ITEM6_NO", Prodqty);
cmdTemp.Parameters.AddWithValue("@ITEM7_NO", pri);
cmdTemp.Parameters.AddWithValue("@ITEM8_NO", net);
cmdTemp.Parameters.AddWithValue("@IMPORT_KEY", ((object)row.Cells[21].Value) ?? DBNull.Value);
if (isexisted == true)
{
cmdTemp.Parameters.AddWithValue("@LINE", strid);
cmdTemp.Parameters.AddWithValue("@UPDATED_BY", uid.ToString());
cmdTemp.Parameters.AddWithValue("@DATE_UPDATED", cfs.sqldateconverion());
}
}
catch (Exception EX) { }
Im
Tarun Kumar
|