SQL Transaction in C#

[Gidi]

Hi,

I want to call a stored procedure that is in my SQL DataBase and i want to
do it with transaction so i could roll-back in case of need.
if i do a simple SP (like select * from table) and i don't send any
parameters i have no problem and it works, but if i'm sending parameters (and
i need to) i get exception of system error.

here is my code:

public void Insert_Shtar_Buy_Tranc(DataGrid dataGrid1)
{

System.Data.SqlClient.SqlTransaction tran
=m_mf.GetDbCon().GetConn().BeginTransaction(System .Data.IsolationLevel.RepeatableRead);
try
{
System.Data.SqlClient.SqlCommand comm =new
System.Data.SqlClient.SqlCommand("Try", m_mf.GetDbCon().GetConn(), tran);
/* System.Data.SqlClient.SqlParameter date_p=
comm.Parameters.Add("@buy_date",SqlDbType.VarChar, 50);
System.Data.SqlClient.SqlParameter time_p=
comm.Parameters.Add("@buy_time",SqlDbType.VarChar, 50);
System.Data.SqlClient.SqlParameter cust_num_p=
comm.Parameters.Add("@cust_num",SqlDbType.VarChar, 50);*/
System.Data.SqlClient.SqlParameter kod_kli_p=
comm.Parameters.Add("@kod_kli",SqlDbType.VarChar,5 0);
/* System.Data.SqlClient.SqlParameter kli_num_p=
comm.Parameters.Add("@kli_num",SqlDbType.VarChar,5 0);
System.Data.SqlClient.SqlParameter
earot_p=comm.Parameters.Add("@earot",SqlDbType.Var Char,50);
System.Data.SqlClient.SqlParameter full_name_p=
comm.Parameters.Add("@full_name",SqlDbType.VarChar ,100);*/
// System.Data.SqlClient.SqlParameter num_p= new
SqlParameter("@num",SqlDbType.Int);
for(int i=0;i<2;i++)
{
// The following lines must ensure that no exception will be thrown,
// or the exception type should be handled in a catch statement below
// The stored procedure should call "Raiserror" if the data insert
should fail. This will generate an SqlException when
// the following line is executed. The catch statment below will catch
the exception and rollback the entire transaction.
/* date_p.Value="1";
time_p.Value="2";
cust_num_p.Value=1;
kod_kli_p.Value="32";
kli_num_p.Value="3";
earot_p.Value="3";
full_name_p.Value="3";*/
// comm.Parameters.Add(num_p);
kod_kli_p.Value="32";

// num_p.Direction=ParameterDirection.Output;
if (comm.ExecuteNonQuery() == 0) // Set noncount off; in procedure
// If no rows are affected, the transaction should be rolled-back
throw new ApplicationException("Data was not inserted.");
}
tran.Commit();
}
catch (ApplicationException ex)
{
tran.Rollback();
// Todo: Handle error, if possible
throw ex;
}
catch (System.Data.SqlClient.SqlException ex)
{
tran.Rollback();
// Todo: Handle error, if possible
throw ex;
}
}
what's the problem here?
thanks,

[James Curran]

It would help if you told us what the error you are getting is.

--
--
Truth,
James Curran
[erstwhile VC++ MVP]

Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com

"Gidi" <sh*****@hotmail.com.dontspam> wrote in message
news:61**********************************@microsof t.com...

Hi,

I want to call a stored procedure that is in my SQL DataBase and i want to
do it with transaction so i could roll-back in case of need.
if i do a simple SP (like select * from table) and i don't send any
parameters i have no problem and it works, but if i'm sending parameters (and i need to) i get exception of system error.

[Wessel Troost]

Can you show the content of this method;

m_mf.GetDbCon().GetConn()

If this returns a new connection, your code is using a transaction on one
connection, and executing a stored procedure on another.

Greetings,
Wessel

[Gidi]

i don't know exactly which error i'm getting, all i know that i'm getting a
system error.
about the m_mf.GetDbCon().GetConn(), this is the open connection i'm using.

again, i tested this with SP that has no parameters and it work just fine,
the problem is when i'm adding parameters.

"Wessel Troost" wrote:

Can you show the content of this method;

m_mf.GetDbCon().GetConn()

If this returns a new connection, your code is using a transaction on one
connection, and executing a stored procedure on another.

Greetings,
Wessel

[Gidi]

Hi,
The error i'm getting is "line 1,incorrect sentax nearTry"

"James Curran" wrote:

It would help if you told us what the error you are getting is.

--
--
Truth,
James Curran
[erstwhile VC++ MVP]

Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com

"Gidi" <sh*****@hotmail.com.dontspam> wrote in message
news:61**********************************@microsof t.com...

Hi,

I want to call a stored procedure that is in my SQL DataBase and i want to
do it with transaction so i could roll-back in case of need.
if i do a simple SP (like select * from table) and i don't send any
parameters i have no problem and it works, but if i'm sending parameters

(and

i need to) i get exception of system error.

[Wessel Troost]

> The error i'm getting is "line 1,incorrect sentax nearTry"

It looks like you're not invoking a stored procedure, but passing a
command text. The command text has to be valid SQL,like:

SELECT * FROM MyTable

or

EXEC MyStoredProcedure 'par1'

You seem to be passing the text "Try" which is not valid SQL.

There are more things wrong with your code, from the looks of it. I'd
suggest reading up on ADO.NET ?

Good luck,
Wessel

[Ron Allen]

Gidi,
You need to set the CommandType of the SqlCommand to
CommandType.StoredProcedure. Also if your procedure parameters are typed
you need to set the correct types on the parameters being added so that they
match.

Ron Allen
"Gidi" <sh*****@hotmail.com.dontspam> wrote in message
news:61**********************************@microsof t.com...

Hi,

I want to call a stored procedure that is in my SQL DataBase and i want to
do it with transaction so i could roll-back in case of need.
if i do a simple SP (like select * from table) and i don't send any
parameters i have no problem and it works, but if i'm sending parameters
(and
i need to) i get exception of system error.

here is my code:

public void Insert_Shtar_Buy_Tranc(DataGrid dataGrid1)
{

System.Data.SqlClient.SqlTransaction tran
=m_mf.GetDbCon().GetConn().BeginTransaction(System .Data.IsolationLevel.RepeatableRead);
try
{
System.Data.SqlClient.SqlCommand comm =new
System.Data.SqlClient.SqlCommand("Try", m_mf.GetDbCon().GetConn(), tran);
/* System.Data.SqlClient.SqlParameter date_p=
comm.Parameters.Add("@buy_date",SqlDbType.VarChar, 50);
System.Data.SqlClient.SqlParameter time_p=
comm.Parameters.Add("@buy_time",SqlDbType.VarChar, 50);
System.Data.SqlClient.SqlParameter cust_num_p=
comm.Parameters.Add("@cust_num",SqlDbType.VarChar, 50);*/
System.Data.SqlClient.SqlParameter kod_kli_p=
comm.Parameters.Add("@kod_kli",SqlDbType.VarChar,5 0);
/* System.Data.SqlClient.SqlParameter kli_num_p=
comm.Parameters.Add("@kli_num",SqlDbType.VarChar,5 0);
System.Data.SqlClient.SqlParameter
earot_p=comm.Parameters.Add("@earot",SqlDbType.Var Char,50);
System.Data.SqlClient.SqlParameter full_name_p=
comm.Parameters.Add("@full_name",SqlDbType.VarChar ,100);*/
// System.Data.SqlClient.SqlParameter num_p= new
SqlParameter("@num",SqlDbType.Int);
for(int i=0;i<2;i++)
{
// The following lines must ensure that no exception will be thrown,
// or the exception type should be handled in a catch statement below
// The stored procedure should call "Raiserror" if the data insert
should fail. This will generate an SqlException when
// the following line is executed. The catch statment below will catch
the exception and rollback the entire transaction.
/* date_p.Value="1";
time_p.Value="2";
cust_num_p.Value=1;
kod_kli_p.Value="32";
kli_num_p.Value="3";
earot_p.Value="3";
full_name_p.Value="3";*/
// comm.Parameters.Add(num_p);
kod_kli_p.Value="32";

// num_p.Direction=ParameterDirection.Output;
if (comm.ExecuteNonQuery() == 0) // Set noncount off; in procedure
// If no rows are affected, the transaction should be rolled-back
throw new ApplicationException("Data was not inserted.");
}
tran.Commit();
}
catch (ApplicationException ex)
{
tran.Rollback();
// Todo: Handle error, if possible
throw ex;
}
catch (System.Data.SqlClient.SqlException ex)
{
tran.Rollback();
// Todo: Handle error, if possible
throw ex;
}
}
what's the problem here?
thanks,