My query keeps rubbish in my sql table if it will not be complete properly

Question

I'm using a a multiple query with insert and update statement together. The problem is that if query will not be completed(for some reason e.x bad internet connection) my SQL Server table keeps rubbish.

Example of query:

SqlCommand cmd = new SqlCommand("INSERT INTO CustomerTrans (TableName, UserID, UserName, SumQuantity, SumPrice, SumRealPrice, SumExtrasPrice, SumTotal, SumDiscountTotal, DateTime) SELECT " + Connection.TableName + ",' " + Connection.UserID + "', '" + Connection.Username + "',Sum(Quantity),Sum(Price),Sum(RealPrice),Sum(ExtrasPrice), Sum(Quantity * Price),Sum(Quantity * DiscountPrice),'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'  from InventoryTransTemp where active=1 and TableName=" + Connection.TableName + ";update InventorytransTemp set TrnDocumentID=(select max(TrnDocumentID) from CustomerTrans where UserID='" + Connection.UserID + "'),Active=0 where TableName=" + Connection.TableName + " and Active=1", con);
cmd.ExecuteNonQuery();

Take a photo from a query which has not be completed properly look query 2989 it has NULL values. I want to avoid inserting something if query is not be completed properly.

Sorry for my previous Question it was Unclear

It always helps to tell us the error you get. `I need the query to be executed at the same time` Use transactions. — LarsTech, Jan 22 '18 at 17:10
You don't have an updated answer. You have a question. A user can't insert null values, your code does. Don't process the query if the values aren't there. Your question is a bit all over the place. Now your query isn't using parameters. Like I mentioned before, if you want to run two queries, [use a transaction](https://stackoverflow.com/a/19165433/719186). — LarsTech, Jan 22 '18 at 17:27
Thank you for the answer. Transcation is better from Parameters AddWithValue? Or should i use them both? — Dim Chris, Jan 22 '18 at 17:28
@Dim Christ - A transaction and parameters solve two different problems. You need them both. — FishBasketGordo, Jan 22 '18 at 17:30

Joel Coehoorn · Accepted Answer · 2018-01-22T17:40:08.737

Try it like this:

string sql = 
    "INSERT INTO CustomerTrans" +
    "   (TableName, UserID, UserName, SumQuantity, SumPrice, SumRealPrice, SumExtrasPrice, SumTotal, SumDiscountTotal, DateTime)" +
    "  SELECT @TableName, @UserID, @Username, Sum(Quantity), Sum(Price), Sum(RealPrice), Sum(ExtrasPrice), Sum(Quantity * Price), Sum(Quantity * DiscountPrice), current_timestamp" +
    "  FROM InventoryTransTemp" +
    "  WHERE active=1 and TableName= @TableName;\n" +
    "SELECT @TranID = scope_identity;\n"
    "UPDATE InventorytransTemp" + 
    "  SET TrnDocumentID=@TranID ,Active=0" + 
    "  WHERE TableName= @Tablename and Active=1;"; 

using (var con = new SqlConnection("connection string here"))
using (var cmd = new SqlCommand(sql, con))
{
    //I'm guessing at exact column types/lengths here. 
    // You should update this to use your exact column types and lengths.
    // Don't let ADO.Net try to guess this for you.
    cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 20).Value = Connection.TableName;
    cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = Connection.UserID;
    cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 20).Value = Connection.Username;
    cmd.Parameters.Add("@TranID", SqlDbType.Int).Value = 0; //placeholder only

    con.Open();
    cmd.ExecuteNonQuery();
}

Note the improved formatting of the query, the use of scope_identity() to get the new identity value rather than a nested select statement that might not be atomic, that I avoided ALL uses of string concatenation to substitute data into the query, that I avoided the AddWithValue() method entirely in favor of an option that doesn't try to guess at your parameter types, and the use of using blocks to be sure the SqlClient objects are disposed properly.

The only thing I'm still concerned about is if your INSERT/SELECT operation might create more than one new record. In that case, you'll need to handle this a different way that probably involves explicit BEGIN TRANSACTION/COMMIT statements, because this code only gets one @TranID value. But in that case, the original code was broken, too.

My query keeps rubbish in my sql table if it will not be complete properly

1 Answers1