Help! SQLTransaction and Identity(?) column in SQL Server 2K

  • Thread starter Thread starter .Net Newbie
  • Start date Start date
N

.Net Newbie

Hello,

I am somewhat new to .Net and currently working on an intranet site using C#
going against SQL Server 2k. I am accepting personal information on a
single webform and trying to insert the information into three separate
tables (all in a single aspx page -- without using stored procedures, yet).

The first SQL Statement accepts the persons most detailed information, like
name, address, phone, etc and inserts the single record into the table.
This main table contains an Identity column and an ntext column (so I am
adding parameters to account for the ntext column).

The next SQL Statement(s) are contained in a a series of if blocks that
basically check some textboxes and dropdownlist for children's information.
If a child's name is found in any of five textboxes, I build an SQL
Statement and push a record into the appropriate table after I have picked
up the Max(Identity column) from the main table.

The last statement collects information from a series of checkboxes (product
and services the person is interested in) and builds a comma list where I am
building a single statement that does an Insert into table
select..from..where field in (comma list). For this I am also picking up the
Max(Identity column) from the main table.

All of this works fine when I don't use a Transaction statment (except for
when there's an error on one of the inserts of course), however when I try
to use this scenerio within an SQLTransaction, I get a time out message and
it does not appear to be closing my connection. People I've discussed this
problem with seem to think it might have something to do with the Identity
column not being commited prior to trying to read it in the next two
sections of inserts, but so far I have been unable to find a solution with
anything I've tried. It seems like this would be a fairly common scenerio
to deal with but I haven't been able to find any examples any where so far.

Does anyone have any suggestions as to how to address this problem, examples
of similar scenerios, or a different approach I might take to accomplish the
same goal?

Any help would be greatly appreciated.

Thanks,
..NetNewbie
 
Absolutely. The current code (which is not currently working) is as follows:

if (Page.IsValid == true) {
SqlConnection myConnection = new SqlConnection(strConn); // Table Insert 1
############################################################################
###############
String insertCmd = "INSERT INTO Cases( " +
" CaseStyle, CaseDescription, CaseCourt, CaseCity, CaseState,
CaseJudge, CaseLength, CaseTypeID, CaseStatusID, DocketNumber, TrialDate,
MoveDate"+
" ) VALUES ("+
" @CaseStyle, @Description, @Court, @City, @State, @Judge, @Length,
@CaseType, @Status, @Docket, @TrialDate, @MoveDate"+
" ) Set @CASEID = @@Identity ";
SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
myCommand.Parameters.Add(new SqlParameter("@CaseStyle", SqlDbType.NVarChar,
255));
myCommand.Parameters["@CaseStyle"].Value = CaseStyle.Text;
myCommand.Parameters.Add(new SqlParameter("@Description", SqlDbType.NText,
4000));
myCommand.Parameters["@Description"].Value = Description.Text;
myCommand.Parameters.Add(new SqlParameter("@Court", SqlDbType.NVarChar,
80));
myCommand.Parameters["@Court"].Value = Court.Text;
myCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar,
50));
myCommand.Parameters["@City"].Value = City.Text;
myCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar,
2));
myCommand.Parameters["@State"].Value = State.SelectedItem.Value;
myCommand.Parameters.Add(new SqlParameter("@Judge", SqlDbType.NVarChar,
50));
myCommand.Parameters["@Judge"].Value = Judge.Text;
myCommand.Parameters.Add(new SqlParameter("@Length", SqlDbType.NVarChar,
80));
myCommand.Parameters["@Length"].Value = TrialLength.Text;
myCommand.Parameters.Add(new SqlParameter("@CaseType", SqlDbType.Int, 4));
myCommand.Parameters["@CaseType"].Value = CaseType.SelectedItem.Value;
myCommand.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int, 4));
myCommand.Parameters["@Status"].Value = Status.SelectedItem.Value;
myCommand.Parameters.Add(new SqlParameter("@Docket", SqlDbType.NVarChar,
50));
myCommand.Parameters["@Docket"].Value = Docket.Text;
myCommand.Parameters.Add(new SqlParameter("@TrialDate",
SqlDbType.SmallDateTime));
myCommand.Parameters["@TrialDate"].Value = TrialDate.Text;
myCommand.Parameters.Add(new SqlParameter("@MoveDate",
SqlDbType.SmallDateTime));
myCommand.Parameters["@MoveDate"].Value = MoveDate.Text;
myCommand.Connection.Open();
SqlTransaction myTrans = myConnection.BeginTransaction();
myCommand.Transaction = myTrans;
try {
myCommand.ExecuteNonQuery();
// Table Insert 2
############################################################################
###############
if ((Parties1.Text != null) && (Parties1.Text != "") && (Parties1.Text !=
" ")){
myCommand.CommandText = " INSERT INTO CasesParties(CaseID, CasePartyName,
CasePartyPosture) VALUES ("+
"@CaseID, @Parties1, @Posture1)";
myCommand.Parameters.Add(new SqlParameter("@Parties1",
SqlDbType.NVarChar, 150));
myCommand.Parameters["@Parties1"].Value = Parties1.Text;
myCommand.Parameters.Add(new SqlParameter("@Posture1",
SqlDbType.NVarChar, 50));
myCommand.Parameters["@Posture1"].Value = Posture1.SelectedItem.Text;
myCommand.ExecuteNonQuery();
}
if ((Parties2.Text != null) && (Parties2.Text != "") && (Parties2.Text !=
" ")){
myCommand.CommandText = " INSERT INTO CasesParties(CaseID, CasePartyName,
CasePartyPosture) VALUES ("+
"@CaseID, @Parties2, @Posture2)";
myCommand.Parameters.Add(new SqlParameter("@Parties2",
SqlDbType.NVarChar, 150));
myCommand.Parameters["@Parties2"].Value = Parties2.Text;
myCommand.Parameters.Add(new SqlParameter("@Posture2",
SqlDbType.NVarChar, 50));
myCommand.Parameters["@Posture2"].Value = Posture2.SelectedItem.Text;
myCommand.ExecuteNonQuery();
}
if ((Parties3.Text != null) && (Parties3.Text != "") && (Parties3.Text !=
" ")){
myCommand.CommandText = " INSERT INTO CasesParties(CaseID, CasePartyName,
CasePartyPosture) VALUES ("+
"@CaseID, @Parties3, @Posture3)";
myCommand.Parameters.Add(new SqlParameter("@Parties3",
SqlDbType.NVarChar, 150));
myCommand.Parameters["@Parties3"].Value = Parties3.Text;
myCommand.Parameters.Add(new SqlParameter("@Posture3",
SqlDbType.NVarChar, 50));
myCommand.Parameters["@Posture3"].Value = Posture3.SelectedItem.Text;
myCommand.ExecuteNonQuery();
}
if ((Parties4.Text != null) && (Parties4.Text != "") && (Parties4.Text !=
" ")){
myCommand.CommandText = " INSERT INTO CasesParties(CaseID, CasePartyName,
CasePartyPosture) VALUES ("+
"@CaseID, @Parties4, @Posture4)";
myCommand.Parameters.Add(new SqlParameter("@Parties4",
SqlDbType.NVarChar, 150));
myCommand.Parameters["@Parties4"].Value = Parties4.Text;
myCommand.Parameters.Add(new SqlParameter("@Posture4",
SqlDbType.NVarChar, 50));
myCommand.Parameters["@Posture4"].Value = Posture4.SelectedItem.Text;
myCommand.ExecuteNonQuery();
}
if ((Parties5.Text != null) && (Parties5.Text != "") && (Parties5.Text !=
" ")){
myCommand.CommandText = " INSERT INTO CasesParties(CaseID, CasePartyName,
CasePartyPosture) VALUES ("+
"@CaseID, @Parties5, @Posture5)";
myCommand.Parameters.Add(new SqlParameter("@Parties5",
SqlDbType.NVarChar, 150));
myCommand.Parameters["@Parties5"].Value = Parties5.Text;
myCommand.Parameters.Add(new SqlParameter("@Posture5",
SqlDbType.NVarChar, 50));
myCommand.Parameters["@Posture5"].Value = Posture5.SelectedItem.Text;
myCommand.ExecuteNonQuery();
}
// Table Insert 3
############################################################################
###############
string commalist = null;
int j = 0;
int k = 0;
int ServicesCount = Services.Items.Count;
ListItem[] myListItemArray = new ListItem[Services.Items.Count];
Services.Items.CopyTo(myListItemArray, 0);
foreach(ListItem i in myListItemArray){
if(i.Selected){
j++;
}
}
foreach(ListItem i in myListItemArray){
if(i.Selected){
k++;
if (k == j){
commalist += i.Value;
} else {
commalist += i.Value + ',';
}
}
}
myCommand.CommandText = " INSERT INTO CasesServices (CaseID, ServiceID)
" +
" Select @CASEID, A.ServiceTypeID " +
" From Services A " +
" Where A.ServiceTypeID IN ("+ commalist + ")";
myCommand.ExecuteNonQuery();
// End Inserts
############################################################################
###############
Message.Text = "<b>Base Information Added</b><br />" +
insertCmd.ToString();
Response.Redirect((String) ViewState["UrlReferrer"]);
} catch (SqlException f) {
myTrans.Rollback();
if (f.Number == 2627)
Message.Text = "ERROR: A record already exists with the same primary
key<br />";
else
Message.Text = "ERROR: Could not add record, please ensure the fields are
correctly filled out<br />";
} finally {
myCommand.Connection.Close();
}
Message.Text = insertCmd + "<br />";
//Response.Redirect((String) ViewState["UrlReferrer"]); } // End Code
############################################################################
###############

I had just bagun replacing everywhere I had "Declare @CASEID = Select
MAX(CaseID) from Cases" with "Set @CASEID = @@Identity" and am still picking
up on the concept of @@Identity so the code is not working, but hopefully
gives you an idea of what I am trying to do. Any assistance you can provide
would be greatly appreciated.

Thanks,
Jeff
 
Back
Top