Code Works Differently Based On The Provider

Hello



The following code does not function if I use SQLOLEDB if I omit the provide and default to ODBC OLE DB it works correctly. I am assume I am coding something wrong for a SQLOLEDB provide. Any help is greatly appricated.



VB Code




Public Function SqlExecuteResult(xSQL As String, sServer As String, sDatabase As String, sUserName As String, sPassword As String, sCaller As String, Optional bLog As Boolean = False) As Object


Dim oDB As Object
Dim oRS As Object

Set oDB = CreateObject("adodb.connection")
Set oRS = CreateObject("adodb.recordset")

oDB.open "driver={SQL Server};provider=sqloledb;server=" & sServer & ";database=" & sDatabase & ";uid=" & sUserName & ";pwd=" & sPassword & ";"
oRS.CursorLocation = adUseClient
oRS.CursorType = adOpenStatic
Set oRS.ActiveConnection = oDB
oRS.open xSQL
Set oRS.ActiveConnection = Nothing
Set SqlExecuteResult = oRS
oDB.Close
Set oDB = Nothing

End Function


Private Sub Form_Load()
Dim rs As Object
Set rs = SqlExecuteResult("exec NextEntry 'SentMessages'", "surecomp-bob", "pmsureus33", "sa", "", "")
MsgBox rs.fields(0)
End Sub




SQL proceedure



CREATE PROCEDURE NextEntry @CounterName Varchar(20) AS

begin
declare @counter int
select @counter = counter from counters where countername = @counterName
select @counter = @counter + 1
update counters set counter = @counter where countername = @countername
select counter from counters where countername = @counterName
End
GO




Thanks



Bob Jenkin