Can you use Stored Procedures and transactions?

  • Thread starter Thread starter Namshub
  • Start date Start date
N

Namshub

I've have 3 stored procedures, the trouble is 1 will always execute but the
other two depending opon the clients selection may not but i need to wrap up
into a transaction all 3 procedures. When i excute the SP it return an
error that i must implement IConvertible.

Dim objConn As New SqlConnection(Application("Connectionstring"))

Dim objSQLTrans As SqlTransaction

Dim objCmdReferral As SqlCommand

Dim objCmdTreatments As SqlCommand <----- This is done further down and is
fired twice with different parameters



objConn.Open()

objCmdReferral = objConn.CreateCommand

objCmdTreatments = objConn.CreateCommand

objSQLTrans = objConn.BeginTransaction("Update3Tables")

objCmdReferral.Transaction = objSQLTrans

objCmdTreatments.Transaction = objSQLTrans

With objCmdReferral

.CommandText = "spDBT_InsReferral"

.CommandType = CommandType.StoredProcedure

.Parameters.Add("@ABC", SqlDbType.VarChar, 7)

.Parameters.Add("@DFG", SqlDbType.VarChar, 10)

.Parameters.Add("@EFG", SqlDbType.VarChar, 35)

.....values are added later down in code -------

objCmdReferral.ExecuteNonQuery() <---- it will crash here

--- excute more sps

Cheers
 
Hi,

What kind of values are you passing?
According to your parameters you should pass strings.
 
Many thanks, i think the problem is that the error message is some what
mysterious.

After your intial prompting i decided to examine closely all the parameters
and the values (i didn't show all the valid params in the example) and i
inadventantly was setting a control instead of its value.

Thanks

Miha Markic said:
Hi,

What kind of values are you passing?
According to your parameters you should pass strings.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Namshub said:
I've have 3 stored procedures, the trouble is 1 will always execute but
the
other two depending opon the clients selection may not but i need to wrap
up
into a transaction all 3 procedures. When i excute the SP it return an
error that i must implement IConvertible.

Dim objConn As New SqlConnection(Application("Connectionstring"))

Dim objSQLTrans As SqlTransaction

Dim objCmdReferral As SqlCommand

Dim objCmdTreatments As SqlCommand <----- This is done further down and
is
fired twice with different parameters



objConn.Open()

objCmdReferral = objConn.CreateCommand

objCmdTreatments = objConn.CreateCommand

objSQLTrans = objConn.BeginTransaction("Update3Tables")

objCmdReferral.Transaction = objSQLTrans

objCmdTreatments.Transaction = objSQLTrans

With objCmdReferral

.CommandText = "spDBT_InsReferral"

.CommandType = CommandType.StoredProcedure

.Parameters.Add("@ABC", SqlDbType.VarChar, 7)

.Parameters.Add("@DFG", SqlDbType.VarChar, 10)

.Parameters.Add("@EFG", SqlDbType.VarChar, 35)

....values are added later down in code -------

objCmdReferral.ExecuteNonQuery() <---- it will crash here

--- excute more sps

Cheers
Click to expand...
Click to expand...
 
Back
Top