Stored procedure output parameter issue 1

[HezMac]

I have created a VB 6 app where users select certain parameters and I run a stored proc to give them a number of how many records meet their parameters.

I have been trying get the output value (a number) to return from a stored procedure in PL/SQL.

Here is the code I'm using to call the stored proc.

Public Function CB_Test_Count(SelectedUser As String) As Integer

Dim ConnTest As New ADODB.Connection
Dim CmdTest As New ADODB.Command
Dim PrmUser As New ADODB.Parameter
Dim PrmTestCount As New ADODB.Parameter
Dim rsTest As New ADODB.Recordset
Dim strSQL As String

'Open connection
With ConnTest
ConnectionString = ConnectionString$
CursorLocation = adUseClient
.Open
End With

'set role
ConnTest.Execute RoleCommand$

'Open command object
With CmdTest
.CommandText = "PKG_BATCH_REASSIGN.P_TEST_COUNT"
.CommandType = adCmdStoredProc
End With

'get parameter value and append parameter
With CmdTest
Set PrmUser = CmdTest.CreateParameter("User ID", adChar, adParamInput, 10, SelectedUser)
Set PrmTestCount = CmdTest.CreateParameter("Test Result", adInteger, adParamOutput, out_TestCount)
.Parameters.Append PrmUser 'this is the input parameter
.Parameters.Append PrmTestCount 'this is the output parameter
End With

'create recordset by executing the command
With TestConn
Set .ActiveConnection = ConnTest
Set rsTest = CmdTest.Execute
End With

'print current data in the recordset

Debug.Print "Collection officer: " & PrmUser

Debug.Print "Number of folders that meet the parameters: " & cmdTestCount; ""
Do While Not rsTest.EOF
rsTest.MoveNext
Loop

Right now I'm getting a runtime error or "Syntax Error or access violation" on the .Open of the connection. But, I've had lots of other errors when I've moved the code around.

I've tried setting the output as a recordset and keeing it just as an output parameter, but I can't get it going.

What am I doing wrong?

I also want to populate a textbox with the output, and have been unable to figure that out as well.

Any suggestions?

Thanks very much.

 

[HezMac]

This problem is fixed.

It was a problem with the database. I didn't fully call it.

 

[NoCoolHandle]

Right now I'm getting a runtime error or "Syntax Error or access violation" on the .Open of the connection. But, I've had lots of other errors when I've moved the code around.

ConnectionString = ConnectionString$

I would tend to believe that your problem is in your connectionstring$.. it probably has an invalid parameter..

It might also be that it doesn't support the cursorlocation argument..
try commenting that out first.. and see what happens.



The following code works for a MS Sqlserver stored proc (proc code at end for reference - in case that helps)

Dim cm As ADODB.Command
Set cm = New ADODB.Command

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

With cm
    .CommandText = "test"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("FirstName", adVarChar, adParamInput, 300)
    .Parameters.Append .CreateParameter("LastName", adVarChar, adParamOutput, 300)
    .Parameters(0).Value = "Nan"
cn.CursorLocation = adUseClient
cn.Open "provider=sqloledb;server=(local);integrated security=sspi;database=crap"
    .ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.Open .Execute
    MsgBox .Parameters(1).Value
End With
Do While Not rs.EOF
    MsgBox rs(3).Value
    rs.MoveNext
Loop
MsgBox cm.Parameters(1).Value
rs.Close
cn.Close
Set cm = Nothing
Set rs = Nothing
Set cn = Nothing

also
you could replace the
rs.Open .Execute
with
Set rs = .Execute

Both seem to work..

For what it is worth


Rob
proc code

create Proc test 
@name varchar(300)
,@Last varchar(300) output
as
select @last = min(lastname) from northwind.dbo.employees where firstname like '%' +  @name + '%'
select * from northwind.dbo.employees order by 2