Questions about transaction processing ~ I hope everyone helps

seshot214

This is the case. I now want to insert multiple pieces of data into A. At the same time, because there is another field in table A, MAX (ID) + a string of users. During the implementation process, it was found that the first record could be inserted, but the second one was not. I do n’t know where the problem is, because the code is too long, I ’ll take a look at it for everyone
SqlCommand command = DataConn.Connection.CreateCommand ();
            SqlTransaction transaction;
            transaction = DataConn.Connection.BeginTransaction ();
            command.Connection = DataConn.Connection;
            command.Transaction = transaction;
            try
            {
                for (int i = 0; i <mydv.Count; i ++)
                {
                    bm = this.GetFbNo (Convert.ToInt16 (oper));
                    mysql = "insert ...";
                    command.CommandText = mysql;
                    command.ExecuteNonQuery ();
                }
                transaction.Commit ();
                return 1;
            }
            catch
            {
                transaction.Rollback ();
                return 0;
            }
            finally
            {
                transaction.Dispose ();
                DataConn.Connection.Close ();
            }
 public string GetFbNo (int sPersID)
        {
            //DataConn.Connection.Open ();
            string sNo = "";
            string sSql = "SELECT MAX (fb_Id) +1 AS MaxNo FROM t_fbalance";
            sNo = DBHelp.ExecuteString (sSql);
            return sNo;
        }
public string ExecuteString (string commandText)
        {
            DataSet ds = new DataSet ();
            SqlCommand cmd = new SqlCommand ();

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = commandText;
            cmd.Connection = this.myconn;

            SqlDataAdapter da = new SqlDataAdapter (cmd);
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill (ds, "TableName");

            mydv = ds.Tables ["TableName"]. DefaultView;
            if (mydv.Count> 0)
            {
                return mydv [0] [0] .ToString (). Trim ();
            }
            else
            {
                return "";
            }
        }

btx221

public string GetFbNo (int sPersID)
{
// The problem should be here, you define a static variable, add one (or other value) on this basis and return directly after the assignment.

//DataConn.Connection.Open ();
string sNo = "";
string sSql = "SELECT MAX (fb_Id) +1 AS MaxNo FROM t_fbalance";
sNo = DBHelp.ExecuteString (sSql);
return sNo;
}

kalian2000

Can I only insert one at a time, but not the second?

It's better to debug with breakpoints to see if the bm value of the loop is the same every time?

seshot214

:btx221
It's a good method. I tried several times and finally found the problem. When the transaction is inserted in the loop, the first insert or update cannot be read. I don’t know why, so I’m still looking for information. I don’t know much about .NETADO.

kalian2000

In this case, it is best to design an auto-increment field...

No need to worry about the ID field when inserting..

Sqlserver uses identity..

oracle uses Sequence...

seshot214

It has been resolved. It is actually very simple. Just write a lookup function that brings in the transaction. Thank you.