*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 .