How to save values in table in SQL without crashing when it is used by multiple users

TarunKumarSusarapu

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)";

                        //  cfs.Audit\_Log("Invoice", ddl\_Inv.Text, txt\_Custcode.Text, strpcode, strUom, Prodqty, true, false, false);
                        //sqlstr = "insert into invINVOICEDETAILS (" + sfield + ") values (" + sdata + ")";
                        //sqlstr = "insert into invINVOICEDE

Afzaal Ahmad Zeeshan

Does the code work properly when there is no concurrent request in the network? In database systems, especially when there are multiple users requesting or modifying the data there are going to be problems. This has been haunting database systems since the days of [Codd](https://en.wikipedia.org/wiki/Edgar\_F.\_Codd). If your data is being stored accurately (or as expected) when only one user access it, then the problem is with concurrency. [Concurrency control - Wikipedia](https://en.wikipedia.org/wiki/Concurrency\_control#Concurrency\_control\_in\_databases). Please also share the results of my suggestion, so that we may look into this again. Also, did you try [stored procedures](https://msdn.microsoft.com/en-us/library/ms190782.aspx)? Database systems although try their best to ensure that synchrony is maintained but sometimes that is not enough. My first solution (although a very naive one) would be, to lock the thread and perform one action and then perform the action of other user.

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

Simon_Whale

Have a read of this TransactionScope Class (System.Transactions)[^]

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

TarunKumarSusarapu

Thank you Mr.Afzaal.I have also even tried stored procedures and Parameterised queries.But when the huge amount of data has been taken place then this problem appear at my side.So can u please explain the concept of concurrency and locking the thread.I have already used threading in my project.At customer side when multiple users are creating transactions at the single time, crashing occurred.

TarunKumarSusarapu

Thank you Mr.Simon_Whale.Can u please explain it briefly.What is the use of it?

Pete OHanlon

If you read the documentation link, it tells you what its scope is.

This space for rent

Simon_Whale

it requires more reading from yourself but have a read of this All About TransactionScope[^]

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

TarunKumarSusarapu

Thank you Simon for your document shared.I will go through it and let you know the result.

Afzaal Ahmad Zeeshan

That happens. Well, for further explanations kindly see the links I provided. They have a good resource start up that you can use for your application.

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

Richard Deeming

It looks like you're part-way through rewriting your code to avoid SQL Injection. :thumbsup: As a result, you can remove all of the code that's building up the sdata string, since it's no longer used. The lines which call cfs.get_data are still vulnerable, and need to be rewritten to use parameters. You need to remove the calls to cfs.singlequotconver. Based on the name, it's trying to avoid SQL Injection by "escaping" quote characters. Since you're now using parameters, you don't need to do that. You should also remove the call to cfs.sqldateconverion, which is probably converting the date to a string. Dates should be passed and stored as dates.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Lost User

You're probably updating way more fields than the "business rule" requires; possibly fouling up foreign keys in the process. 2) Maybe a "Delete and insert" makes more sense than an "update" here. 3) It seems you're arbitrarily overwriting everything. You will probably have better success if you incorporate "versioning"; which will also make your "WHERE" clauses more intelligent instead of blindly accessing by line# only. That, and a transaction.

"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal

TarunKumarSusarapu

Thank you Mr.Ricard.These may be one of the reasons of data crashing.I will try to do modifications at my side and let you know the concerns about this.

TarunKumarSusarapu

Thank you Mr.Gerry.These may be one of the reasons of data crashing.Can u please explain the point 3 briefly and give me ways to write short code for that.

TarunKumarSusarapu

Here Passing a query as a parameter to get_data method.How can we use parameters here?

public string get_data(string sqlstr)
{
string strTemp = "";
try
{
double sum = 0;
if (cn.State != ConnectionState.Open) cn.Open();
TRcmd2.Connection = cn;
TRcmd2.CommandText = sqlstr;
TRdr = TRcmd2.ExecuteReader();
if (TRdr.Read())
strTemp = TRdr.GetValue(0).ToString();
TRdr.Close();
}
catch
{
try
{
if (!TRdr.IsClosed == true) TRdr.Close();
}
catch (Exception EX) { }
}
return (strTemp);
}

Lost User

[rowversion (Transact-SQL)](https://msdn.microsoft.com/en-us/library/ms182776.aspx)

"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal

TarunKumarSusarapu

I tried all the points as you mentioned here.But still, invoice number clash happens when the multiple users save at a time.Here the invoice number is unique.But in details part we can save multiple products for a particular invoice.But here clash happens when multiple users enter data at once. for example

users-->invoice no Products
Raj 1 J,K
Tarun 2 O,M

But at the time of saving it swapped like this

users-->invoice no Products
Raj 1 J,K,M-------->Clashing taken place
Tarun 2 O

Please give me suggestions to solve this.

TarunKumarSusarapu

I tried all the points as you mentioned here.But still, invoice number clash happens when the multiple users save at a time.Here the invoice number is unique.But in details part we can save multiple products for a particular invoice.But here clash happens when multiple users enter data at once. for example

users-->invoice no Products
Raj 1 J,K
Tarun 2 O,M

But at the time of saving it swapped like this

users-->invoice no Products
Raj 1 J,K,M-------->Clashing taken place
Tarun 2 O

Please give me suggestions to solve this.

Richard Deeming

For a start, don't store connection and command objects in fields. Instead, create them when you need them, and wrap them in a using block to ensure that they're always disposed of properly. You don't need to call ExecuteReader to get the value of the first column of the first row; use ExecuteScalar[^] instead. And you need to add a params parameter[^] to your method to pass parameters:

private static void PrepareCommand(SqlCommand command, string commandText, object[] parameters)
{
if (parameters != null && parameters.Length != 0)
{
string[] parameterNames = new string[parameters.Length];
for (int index = 0; index < parameters.Length; index++)
{
string name = "@p" + index;
parameterNames[index] = name;
command.Parameters.AddWithValue(name, parameters[index]);
}

    commandText = string.Format(commandText, parameterNames);
}

command.CommandText = commandText;

}

public string get_data(string commandText, params object[] parameters)
{
using (var connection = new SqlConnection(ConnectionString))
using (var command = new SqlCommand(string.Empty, connection))
{
PrepareCommand(command, commandText, parameters);

    connection.Open();
    object result = command.ExecuteScalar();
    return Convert.ToString(result);
}

}

You can then pass parameters to the command using the auto-generated names:

cfs.get_data("select top 1 AVERAGE_COST from PRODUCT where PRODUCT_NO = @p0", strpcode)

or by position:

cfs.get_data("select top 1 AVERAGE_COST from PRODUCT where PRODUCT_NO = {0}", strpcode)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Lost User

Where's the version number? I don't see any evidence of you "trying all the points".

"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal

palikhelsanjeeb

Hi Tarun, may b helpful to use the required isolation levels while locking the transactions. may b u could watch the video tutorial in mva site course:

Developing Microsoft SQL Server Databases

chapter 4: managing transaction if you prefer watching rather than reading link: Developing Microsoft SQL Server Databases – Microsoft Virtual Academy[^]