Re: Problem Calling Stored Procedure Inside a Transaction

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Mon, 29 Dec 2008 07:46:03 -0800, Paul Yule <Paul Yule@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

¤ First let me start by saying I have posted this question several days ago here:
¤
¤ http://www.xtremevbtalk.com/showthread.php?t=303140
¤
¤ First I setup the ADO Connection
¤
¤ Dim con As New ADODB.Connection
¤ con.ConnectionString = "Provider=MSDASQL.1;Password=xxxxxx;Persist
¤ Security Info=True;User Id=xxxxxx;Data Source=xxxxxx;Initial Catalog=xxxxxx"
¤ con.Open()
¤ con.BeginTrans()
¤
¤ Next I created the command to call the stored procedure and set the
¤ connection on the command
¤
¤ Dim insertCommand As New ADODB.Command
¤ With insertCommand
¤ .Prepared = True
¤ .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
¤ .ActiveConnection = con
¤ .CommandText = "VALID_SP_NAME"
¤ Dim nbDate = CDate("1/2/3662 12:21:29.310 AM")
¤ .Parameters.Append(.CreateParameter("@pNTNB_ID", _
¤ DataTypeEnum.adDBTimeStamp,
¤ ParameterDirectionEnum.adParamInput, 40, nbDate))
¤ End With
¤ insertCommand.Execute()
¤
¤ Now, when I execute the command without con.BeginTrans() it works just fine.
¤ If I start the transaction it doesn't work at all and gives me a generic
¤ Unspecified Error. Is it possible the provider does not support transactions?
¤ I'm an idiot when it comes to VB so be gentle. ANY ideas at all are
¤ completely welcome, I'm totally spent.

I can't really think of any reason to use MSDASQL (OLEDB to ODBC bridge). I don't know which version
of SQL Server you are working with but I would highly recommend using one of the native SQL OLEDB
providers with ADO.

http://www.connectionstrings.com/sql-server-2005


Paul
~~~~
Microsoft MVP (Visual Basic)
.

Relevant Pages