Transactions Not working ??????????

I have MSSQL SERVER
I have the following C# function
But the database entries are not being created why
Do i need to change any database settings?????

If i remove the transaction it is working


public static bool CreateNewStudent(string userid,string passwd,string
emailid,string roll,string title,string fullname,string batch,string
program,string department)
{
bool success = false;
SqlConnection con = new
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings["SQL_CONNECTION_STRING"]);
con.Open();
SqlCommand cmd = con.CreateCommand();
SqlTransaction createStudentTrans =
con.BeginTransaction(IsolationLevel.Serializable);
cmd.Connection = con;
cmd.Transaction = createStudentTrans;

try
{
//order of commands is important due to foreign key contraints
//command 1 of transaction
cmd.CommandText = "INSERT INTO
ulsusers(userid,passwd,role,lastlogin,createdon,em ailid)
VALUES(@userid,@passwd,@role,@lastlogin,@createdon ,@emailid)";
cmd.Parameters.Add("@userid",userid);
cmd.Parameters.Add("@passwd",FormsAuthentication.H ashPasswordForStoringInConfigFile(passwd,"MD5"));
cmd.Parameters.Add("@role","student");
cmd.Parameters.Add("@lastlogin",DateTime.Now);
cmd.Parameters.Add("@createdon",DateTime.Now);
cmd.Parameters.Add("@emailid",emailid);
int rows = cmd.ExecuteNonQuery();
//command 2 of transaction
cmd.CommandText = "INSERT INTO
ulsstudents(userid,roll,title,fullname,batch,progr am,department) "+
" VALUES(@userid,@roll,@title,@fullname,@batch,@prog ram,@department)";
cmd.Parameters.Add("@userid",userid);
cmd.Parameters.Add("@roll",roll);
cmd.Parameters.Add("@title",title);
cmd.Parameters.Add("@fullname",fullname);
cmd.Parameters.Add("@batch",batch);
cmd.Parameters.Add("@program",program);
cmd.Parameters.Add("@department",department);
rows += cmd.ExecuteNonQuery();

//commit if both successfull
createStudentTrans.Commit();
success = (rows==2);
}
catch(Exception e)
{
try
{
createStudentTrans.Rollback();
}
catch(Exception ex)
{
}
}
con.Close();
return success;

}

------------------------------------------------------------------------------
ANY HELP
S.Kartikeyan

re: Transactions Not working ??????????

I wasn't able to test your code in detail, but I did notice 1 item that
might have been your problem....
basicly, the only thing I did was create a new instance of the command
object.
You where appending additional parameters to the command before executing it
the second time. this adds more parameters to the command without removing
the old ones. So even though you had created a new commandtext, the old
parameters where still there.

I am not convinced this will fix your problem, since I would have expected
you to get an exception with the way it was, but since it can't hurt to try
the changes, I offer them up......

Here is the changed code.

-----------------------------
public static bool CreateNewStudent(string userid,string passwd,string
emailid,string roll,string title,string fullname,string batch,string
program,string department)
{
bool success = false;
SqlCommand cmd;

SqlConnection con = new
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings["SQL_CO
NNECTION_STRING"]);
con.Open();

SqlTransaction createStudentTrans =
con.BeginTransaction(IsolationLevel.Serializable);

try
{
//order of commands is important due to foreign key contraints
//command 1 of transaction
cmd.Connection = con;
cmd.Transaction = createStudentTrans;
cmd.CommandText = "INSERT INTO
ulsusers(userid,passwd,role,lastlogin,createdon,em ailid)"
+ "VALUES(@userid,@passwd,@role,@lastlogin,@createdo n,@emailid)";
cmd.Parameters.Add("@userid",userid);

cmd.Parameters.Add("@passwd",FormsAuthentication.H ashPasswordForStoringInCon
figFile(passwd,"MD5"));
cmd.Parameters.Add("@role","student");
cmd.Parameters.Add("@lastlogin",DateTime.Now);
cmd.Parameters.Add("@createdon",DateTime.Now);
cmd.Parameters.Add("@emailid",emailid);
int rows = cmd.ExecuteNonQuery();
//command 2 of transaction
cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = createStudentTrans;
cmd.CommandText = "INSERT INTO ulsstudents(userid,roll,title,fullname,batc
h,program,department) " +
" VALUES(@userid,@roll,@title,@fullname,@batch,@prog ram,@department)";
cmd.Parameters.Add("@userid",userid);
cmd.Parameters.Add("@roll",roll);
cmd.Parameters.Add("@title",title);
cmd.Parameters.Add("@fullname",fullname);
cmd.Parameters.Add("@batch",batch);
cmd.Parameters.Add("@program",program);
cmd.Parameters.Add("@department",department);
rows += cmd.ExecuteNonQuery();
//commit if both successfull
createStudentTrans.Commit();
success = (rows==2);
}
catch(Exception e)
{
try
{
createStudentTrans.Rollback();
}
catch(Exception ex)
{
}
}
con.Close();
return success;
}
------------------------------------

Kirk Graves
KRGIT Software

"S.Kartikeyan" <indexnode@yahoo.com> wrote in message
news:6d393f67.0402210238.8491757@posting.google.co m...[color=blue]
> I have MSSQL SERVER
> I have the following C# function
> But the database entries are not being created why
> Do i need to change any database settings?????
>
> If i remove the transaction it is working
>
> --------------------------------------------------------------------------[/color]
----[color=blue]
> ANY HELP
> S.Kartikeyan[/color]

re: Transactions Not working ??????????

I wasn't able to test your code in detail, but I did notice 1 item that
might have been your problem....
basicly, the only thing I did was create a new instance of the command
object.
You where appending additional parameters to the command before executing it
the second time. this adds more parameters to the command without removing
the old ones. So even though you had created a new commandtext, the old
parameters where still there.

I am not convinced this will fix your problem, since I would have expected
you to get an exception with the way it was, but since it can't hurt to try
the changes, I offer them up......

Here is the changed code.

-----------------------------
public static bool CreateNewStudent(string userid,string passwd,string
emailid,string roll,string title,string fullname,string batch,string
program,string department)
{
bool success = false;
SqlCommand cmd;

SqlConnection con = new
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings["SQL_CO
NNECTION_STRING"]);
con.Open();

SqlTransaction createStudentTrans =
con.BeginTransaction(IsolationLevel.Serializable);

try
{
//order of commands is important due to foreign key contraints
//command 1 of transaction
cmd.Connection = con;
cmd.Transaction = createStudentTrans;
cmd.CommandText = "INSERT INTO
ulsusers(userid,passwd,role,lastlogin,createdon,em ailid)"
+ "VALUES(@userid,@passwd,@role,@lastlogin,@createdo n,@emailid)";
cmd.Parameters.Add("@userid",userid);

cmd.Parameters.Add("@passwd",FormsAuthentication.H ashPasswordForStoringInCon
figFile(passwd,"MD5"));
cmd.Parameters.Add("@role","student");
cmd.Parameters.Add("@lastlogin",DateTime.Now);
cmd.Parameters.Add("@createdon",DateTime.Now);
cmd.Parameters.Add("@emailid",emailid);
int rows = cmd.ExecuteNonQuery();
//command 2 of transaction
cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = createStudentTrans;
cmd.CommandText = "INSERT INTO ulsstudents(userid,roll,title,fullname,batc
h,program,department) " +
" VALUES(@userid,@roll,@title,@fullname,@batch,@prog ram,@department)";
cmd.Parameters.Add("@userid",userid);
cmd.Parameters.Add("@roll",roll);
cmd.Parameters.Add("@title",title);
cmd.Parameters.Add("@fullname",fullname);
cmd.Parameters.Add("@batch",batch);
cmd.Parameters.Add("@program",program);
cmd.Parameters.Add("@department",department);
rows += cmd.ExecuteNonQuery();
//commit if both successfull
createStudentTrans.Commit();
success = (rows==2);
}
catch(Exception e)
{
try
{
createStudentTrans.Rollback();
}
catch(Exception ex)
{
}
}
con.Close();
return success;
}
------------------------------------

Kirk Graves
KRGIT Software

"S.Kartikeyan" <indexnode@yahoo.com> wrote in message
news:6d393f67.0402210238.8491757@posting.google.co m...[color=blue]
> I have MSSQL SERVER
> I have the following C# function
> But the database entries are not being created why
> Do i need to change any database settings?????
>
> If i remove the transaction it is working
>
> --------------------------------------------------------------------------[/color]
----[color=blue]
> ANY HELP
> S.Kartikeyan[/color]

re: Transactions Not working ??????????

your Suggestion was correct regarding extra parameters.
I tested the thing you showed.It was working fine.
Also the following thing was working.
before start of new sub transaction i am calling
cmd.Parameters.Clear();

Thanks for your suggestion.
Unfortunately i followed one of the example in the msdn help itself which
did not point out this.

S.Kartikeyan

"Kirk Graves" <krgitsoftware@yahoo.com> wrote in message news:<#mBOp#K#DHA.1672@TK2MSFTNGP12.phx.gbl>...[color=blue]
> I wasn't able to test your code in detail, but I did notice 1 item that
> might have been your problem....
> basicly, the only thing I did was create a new instance of the command
> object.
> You where appending additional parameters to the command before executing it
> the second time. this adds more parameters to the command without removing
> the old ones. So even though you had created a new commandtext, the old
> parameters where still there.
>
> I am not convinced this will fix your problem, since I would have expected
> you to get an exception with the way it was, but since it can't hurt to try
> the changes, I offer them up......
>
> Here is the changed code.
>
> -----------------------------
> public static bool CreateNewStudent(string userid,string passwd,string
> emailid,string roll,string title,string fullname,string batch,string
> program,string department)
> {
> bool success = false;
> SqlCommand cmd;
>
> SqlConnection con = new
> SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings["SQL_CO
> NNECTION_STRING"]);
> con.Open();
>
> SqlTransaction createStudentTrans =
> con.BeginTransaction(IsolationLevel.Serializable);
>
> try
> {
> //order of commands is important due to foreign key contraints
> //command 1 of transaction
> cmd.Connection = con;
> cmd.Transaction = createStudentTrans;
> cmd.CommandText = "INSERT INTO
> ulsusers(userid,passwd,role,lastlogin,createdon,em ailid)"
> + "VALUES(@userid,@passwd,@role,@lastlogin,@createdo n,@emailid)";
> cmd.Parameters.Add("@userid",userid);
>
> cmd.Parameters.Add("@passwd",FormsAuthentication.H ashPasswordForStoringInCon
> figFile(passwd,"MD5"));
> cmd.Parameters.Add("@role","student");
> cmd.Parameters.Add("@lastlogin",DateTime.Now);
> cmd.Parameters.Add("@createdon",DateTime.Now);
> cmd.Parameters.Add("@emailid",emailid);
> int rows = cmd.ExecuteNonQuery();
> //command 2 of transaction
> cmd = new SqlCommand();
> cmd.Connection = con;
> cmd.Transaction = createStudentTrans;
> cmd.CommandText = "INSERT INTO ulsstudents(userid,roll,title,fullname,batc
> h,program,department) " +
> " VALUES(@userid,@roll,@title,@fullname,@batch,@prog ram,@department)";
> cmd.Parameters.Add("@userid",userid);
> cmd.Parameters.Add("@roll",roll);
> cmd.Parameters.Add("@title",title);
> cmd.Parameters.Add("@fullname",fullname);
> cmd.Parameters.Add("@batch",batch);
> cmd.Parameters.Add("@program",program);
> cmd.Parameters.Add("@department",department);
> rows += cmd.ExecuteNonQuery();
> //commit if both successfull
> createStudentTrans.Commit();
> success = (rows==2);
> }
> catch(Exception e)
> {
> try
> {
> createStudentTrans.Rollback();
> }
> catch(Exception ex)
> {
> }
> }
> con.Close();
> return success;
> }
> ------------------------------------
>
> Kirk Graves
> KRGIT Software
>
> "S.Kartikeyan" <indexnode@yahoo.com> wrote in message
> news:6d393f67.0402210238.8491757@posting.google.co m...[color=green]
> > I have MSSQL SERVER
> > I have the following C# function
> > But the database entries are not being created why
> > Do i need to change any database settings?????
> >
> > If i remove the transaction it is working
> >
> > --------------------------------------------------------------------------[/color]
> ----[color=green]
> > ANY HELP
> > S.Kartikeyan[/color][/color]