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 3 1800
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" <in*******@yahoo.com> wrote in message
news:6d*************************@posting.google.co m... 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
--------------------------------------------------------------------------
---- ANY HELP S.Kartikeyan
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" <in*******@yahoo.com> wrote in message
news:6d*************************@posting.google.co m... 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
--------------------------------------------------------------------------
---- ANY HELP S.Kartikeyan
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" <kr***********@yahoo.com> wrote in message news:<#m**************@TK2MSFTNGP12.phx.gbl>... 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" <in*******@yahoo.com> wrote in message news:6d*************************@posting.google.co m... 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
-------------------------------------------------------------------------- ---- ANY HELP S.Kartikeyan This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Alban Hertroys |
last post by:
Oh no! It's me and transactions again :)
I'm not really sure whether this is a limitation of psycopg or
postgresql. When I use multiple cursors in a transaction, the records
inserted at the...
|
by: David Bray |
last post by:
I have an Access database on an ISP's web-space which is accessed
through ASP - all standard stuff.
Can anyone tell me whether ASP's object.context methods for
transactions will work with...
|
by: TD |
last post by:
I am trying to add transactions to my code. The original code worked
fine until I followed an example to setup transactions, now the code
does strange things, but no error messages.
Could...
|
by: Ace Calhoon |
last post by:
Hello,
I have a VBA/Database application which reads files, analyzes them,
updates a database, and then moves them to an archive. I would like to
make this an atomic transaction -- that is, if...
|
by: John Wells |
last post by:
On this page: http://www.compiere.org/technology/independence.html, the
project leader of Compiere (a popular ERP package) states that the move to
Postgres failed because of lack of support of...
|
by: TD |
last post by:
Below is the code I have under a "Commit" button on a bound form. It
works fine but this is the first time I have used transactions and I
would like someone to review it and let me know if there...
|
by: Sridhar |
last post by:
Hi,
I am trying to implement sql transactions. But I am not knowing how to
do that. I created a data access layer which contains methods to
select/insert/update tables in a database. I have also...
|
by: Rami |
last post by:
I have some requirement for an automated payment system. The system has
four machines setup as follows:
1- Two machines have a clustered database.
2- Two machines have a .net business logic...
|
by: Ken |
last post by:
I used the classes (SqlCommand, SqlConnection) of part of System.Data
namespace.
I got an error which mentioned "Could not load file or assembly assembly
'System.Transactions, Version=2.0.0.0,...
|
by: psycho |
last post by:
I am working on an N-tier application using following components:
1. Data Access Layer using DLINQ which consists of Data Context class
and Table Mapping classes.
2. Business Logic Layer....
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |