P
PegL
I have a problem in using ADO to retrieve data from a UDB 7.1 stored procedure.
The SP has 1 input parm and 2 output parms. I can't seem to be able to get
any
values into the output parms. I should receive text in ERR_MSG, but it
remains blank. What am I missing???
Here's my code:
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strConnStr As String, strSQL As String
Dim prmIN As ADODB.Parameter, prmOUT1 As ADODB.Parameter
Dim prmOUT2 As ADODB.Parameter
strConnStr = "ODBC;Provider=DB2OLEDB;DATABASE=UDPY001U;DSN=UDPY001U"
strSQL = "dvlp.SDG0007X"
Set cn = New ADODB.Connection
With cn
.ConnectionString = strConnStr
.Mode = adModeReadWrite
.Open strConnStr, "myUID", "mypw", -1
End With
Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdStoredProc
.CommandText = strSQL
.CommandType = adCmdStoredProc
Set prmIN = .CreateParameter("MODE_IND", adChar, adParamInput, 1, "X")
.Parameters.Append prmIN
Set prmOUT1 = .CreateParameter("ERR_MSG", adChar, adParamOutput, 25, " ")
.Parameters.Append prmOUT1
Set prmOUT2 = .CreateParameter("SQL_Return_Code", adInteger,
adParamOutput, 8, 0)
.Parameters.Append prmOUT2
.ActiveConnection = cn
End With
Set rst = cmd.Execute(lngRecs, prmIN, adOptionUnspecified)
Debug.Print prmOUT1.Value; "&"; prmOUT2.Value;" ";rst.RecordCo
The SP has 1 input parm and 2 output parms. I can't seem to be able to get
any
values into the output parms. I should receive text in ERR_MSG, but it
remains blank. What am I missing???
Here's my code:
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strConnStr As String, strSQL As String
Dim prmIN As ADODB.Parameter, prmOUT1 As ADODB.Parameter
Dim prmOUT2 As ADODB.Parameter
strConnStr = "ODBC;Provider=DB2OLEDB;DATABASE=UDPY001U;DSN=UDPY001U"
strSQL = "dvlp.SDG0007X"
Set cn = New ADODB.Connection
With cn
.ConnectionString = strConnStr
.Mode = adModeReadWrite
.Open strConnStr, "myUID", "mypw", -1
End With
Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdStoredProc
.CommandText = strSQL
.CommandType = adCmdStoredProc
Set prmIN = .CreateParameter("MODE_IND", adChar, adParamInput, 1, "X")
.Parameters.Append prmIN
Set prmOUT1 = .CreateParameter("ERR_MSG", adChar, adParamOutput, 25, " ")
.Parameters.Append prmOUT1
Set prmOUT2 = .CreateParameter("SQL_Return_Code", adInteger,
adParamOutput, 8, 0)
.Parameters.Append prmOUT2
.ActiveConnection = cn
End With
Set rst = cmd.Execute(lngRecs, prmIN, adOptionUnspecified)
Debug.Print prmOUT1.Value; "&"; prmOUT2.Value;" ";rst.RecordCo