*Resolved* Help With Calling a SP from VB.Sincere Thanks to VBForums :)

[NewbieVB2003]

Here is the code that I am working on : I need to call the Stored Procedure from VB and fetch the data. Please tell me where I am going wrong :

Public Function RS1(ByVal pId As Integer, ByVal cId As Integer, ByVal FrmDt As String, ByVal ToDt As String) As Integer
 
    Dim adoCon As New ADODB.Connection
    Dim adoRset As New ADODB.Recordset
    Dim adoCmd As New ADODB.Command
    Dim retCode As Integer
    Dim rStr As String  
 
    On Error GoTo ErrorHandler
        
    With adoCmd
        .ActiveConnection = connString 
        .CommandType = adCmdStoredProc
        .CommandText = "proc_name"
         
        .Parameters.Append .CreateParameter("@retrn", adInteger, adParamReturnValue, ,0)
.Parameters.Append .CreateParameter("@output", adInteger, adParamOutput, , 999)
         
        .Parameters.Append .CreateParameter("@p_id", adInteger, adParamInput, , pId)
        .Parameters.Append .CreateParameter("@c_id", adInteger, adParamInput, , cId)
        .Parameters.Append .CreateParameter("@frm_dt", adVarChar, adParamInput, 12, FrmDt)
        .Parameters.Append .CreateParameter("@to_dt", adLongVarChar, adParamInput, 12, ToDt)
     End With
        
     Set adoRset = adoCmd.Execute
     Do While (Not adoRset Is Nothing)
          If adoRset.State = adStateClosed Then Exit Do
          While Not adoRset.EOF
              For i = 0 To adoRset.Fields.Count - 1
                  rStr = rStr & " : " & adoRset(i)
              Next i
              Debug.Print Mid(rStr, 3, Len(rStr))
              adoRset.MoveNext
              rStr = ""
          Wend
          Set adoRset = adoRset.NextRecordset
          Loop
      
      retCode = adoCmd.Parameters("@retrn").Value
      Debug.Print "Return: " & adoCmd.Parameters ("@retrn").Value         
      GoTo Shutdown
      
    
ErrorHandler:
 
    Set adoCmd = Nothing
    RS1 = -100
 
Shutdown:
          Set adoCmd = Nothing
          Set adoRset = Nothing
          Set adoCon = Nothing
 
End Function
 
Public Function PpltRS() As Integer
         RS1 p1_Id, c1_Id, Frm_Dt, To_Dt
End Function

Piece of SQL code :

create procedure proc_name
@p_id 	int=null,
@cr_id	int=null,
@frm_dt	varchar(12),
@to_dt	varchar(12),
@retrn	int output
as

Also,how can I call this function at a click of a button ?

Thanks.Any help is appreciated .

[Briantcva]

Active Connection property of the command object should be a cn object - not the cn string of a cn object.

[NewbieVB2003]

Thank you for replying back ;but I have no problem with the Connection String.The issue is something to do with fetching recordset.

[Briantcva]

What line and what error?

[NewbieVB2003]

Thank you Briantcva for spontaneous reply.

I have updated my Original Post with another function from which I am calling RS1 function.

What I am trying to do here is execute the SP and then return the OUTPUT value if successful.

On executing the code, it is displaying the default value set in PpltRs function,namely , pRtrn =10
whereas it should be displaying a different value.

[ARPRINCE]

Active Connection property of the command object should be a cn object - not the cn string of a cn object.

I agree here. I just don't know why you are not getting any error. Here's how I usually build mine:

'-BUILD ADOs
   With adoCN
      .ConnectionString = CNSTR
      .CursorLocation = adUseClient
      .Open
   End With
   With adoCMD
      .ActiveConnection = adoCN
      .CommandType = adCmdStoredProc
   End With

[Memnoch1207]

Public Function PpltRS() As Integer
    Dim pRtrn As Integer 'I got This
    pRtrn = 10 ' I got this but why is it here? it is not being used by anything?
         RS1 p1_Id, c1_Id, Frm_Dt, To_Dt 'Opening the Rs I assume?
    PpltRS = pRet 'Where is pRet coming from????
    Debug.Print "pRet ::::: " & Str(pRet)
End Function

[NewbieVB2003]

Thank you all for replying back.

My Bad ...Correct Code Snippet :

Public Function PpltRS() As Integer
    Dim pRtrn As Integer 'I got This
    pRtrn = 10 ' I got this but why is it here? it isn't being used by anything?
         RS1 p1_Id, c1_Id, Frm_Dt, To_Dt 'Opening the Rs I assume?
    PpltRS =pRtrn 
    Debug.Print "pRet ::::: " & Str(pRtrn)
End Function

Basically what I want to achieve is that the function RS1 will be invoked and we will pass parameters p1_Id, c1_Id, Frm_Dt, To_Dt and,it should give me an Output which confirms that the procedure is run sucessfully .I can be sure if the pRet value matches the Return value after running procedure.

You are right!!! I have messed up this part of the code. As it is not setting the value from the 1st Function. But how can I fix it.
Thanks

[NewbieVB2003]

Thank you ARPRINCE and Briantcva ,
I have made the changes ;but the Output field is still showing the wrong resultant.
Thanks

[NewbieVB2003]

Using Debug tool , I found out that it is skipping this piece of code and directly going to ErrorHandler :

Do While (Not adoRset Is Nothing)
          If adoRset.State = adStateClosed Then Exit Do
          While Not adoRset.EOF
              For i = 0 To adoRset.Fields.Count - 1
                  rStr = rStr & " : " & adoRset(i)
              Next i
              Debug.Print Mid(rStr, 3, Len(rStr))
              adoRset.MoveNext
              rStr = ""
          Wend
          Set adoRset =adoRset.NextRecordset
          Loop

[ARPRINCE]

Remove your error handler and run your routine again to know where exactly your code craps out and will give you a description of the error.

Anyway, if it is skipping that code lines entirely, then it would fall into the line where you are trying to retrieve your recordset.

set adoRset = adocmd.execute

Did you ever change your active connection = adocn?

[NewbieVB2003]

You were so very correct.
I have been able to fix my error now.

Thanks