How to include stored procedure input parameters when using ado data control; VB 6.0, SQL Server 2000

I have been unable to find instructions regarding this question in help files, at microsoft, or via google.
I am using VB 6.0 sp6 w/ WinXP Pro sp2, and SQL Server 2000.
I am trying to use an ado data control to fill a dbgrid.
I am trying to call a stored procedure with 2 input parameters.
I need to know how to include the parameters in the subprocedure that calls the stored procedure.

The error I receive is, "Adodc1  - Procedure'WHTSP_FetechReporderDetails' expects parameter '@emp_id' which was not supplied."

I know this isn't correct - can someone provide an example?  My thanks in advance for any helpful responses.

Here is the code for the subprocedure:
   Dim cmd As New ADODB.Command
    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = "WHTSP_FetchReporderDetails"
        .ActiveConnection = conn
        .Parameters.Append .CreateParameter("@emp_id", adVarChar, adParamInput, 5, strEMP_ID)
        .Parameters.Append .CreateParameter("@date_scanned", adsmalldatetime, adParamInput, , dtmTodaysDate)
    End With
   
    With Adodc1
        .ConnectionString = CommonConnString
        .CursorLocation = adUseServer
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CommandType = adCmdStoredProc
        .RecordSource = WHTSP_FetchReporderDetails
       
        .Refresh
    End With
LVL 1
michael801Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try the following change:
        .RecordSource = cmd


    Dim cmd As New ADODB.Command
    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = "WHTSP_FetchReporderDetails"
        .ActiveConnection = conn
        .Parameters.Append .CreateParameter("@emp_id", adVarChar, adParamInput, 5, strEMP_ID)
        .Parameters.Append .CreateParameter("@date_scanned", adsmalldatetime, adParamInput, , dtmTodaysDate)
    End With
   
    With Adodc1
        .ConnectionString = CommonConnString
        .CursorLocation = adUseServer
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CommandType = adCmdStoredProc
        .RecordSource = cmd
       
        .Refresh
    End With
0
Anthony PerkinsCommented:
Best advice would be lose the Data Control and bind the data grid directly to a resultset that supports bookmarks.
0
michael801Author Commented:
Hi angelIII:, thanks for the FAST response - I tried your suggestion, but I received a compile eror: 'Type mismatch'.  Here is the code with the suggested change:
    Dim conn As New ADODB.Connection
    conn.Open CommonConnString
    Dim cmd As New ADODB.Command
    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = "WHTSP_FetchReporderDetails"
        .ActiveConnection = conn
        .Parameters.Append .CreateParameter("@emp_id", adVarChar, adParamInput, 5, strEMP_ID)
        .Parameters.Append .CreateParameter("@date_scanned", adDBTime, adParamInput, , dtmTodaysDate)
    End With
   With Adodc1
        .ConnectionString = CommonConnString
        .CursorLocation = adUseServer
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CommandType = adCmdStoredProc
        .RecordSource = cmd     '<-- Compile error Type Mismatch
         .Refresh
    End With
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Anthony PerkinsCommented:
You need to execute the Recordset open using the Command object as the Source and then assign the recordset to your data control.  Again, this is overkill.  You do not need a Data Control to do this.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see I submit before finishing to write...
.RecordSource = cmd.Execute

but as acperkins indicated, Data Controls are usually avoided, as they take too much control from the developer for a pro application.
they are fine to quickly prototype forms etc...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
michael801Author Commented:
>angelIII:I see I submit before finishing to write...
.RecordSource = cmd.Execute<
Thanks.
The compile error still occurs, highlighting 'RecordSource = '

It may be that what I'm asking cannot be done; or in any event will take more time than coding without the control.
I agree with both angellll and acperkins, my attempt to shortcut by retaining the ado dc is most likely ill-advised.  I accept both solutions, I weight the percentage split in favor of angellll for the extra effort.  My thanks to both.

0
Anthony PerkinsCommented:
>>It may be that what I'm asking cannot be done<<
Sure it can.

You cannot do this (as you have discovered, they are different animals):
 .RecordSource = cmd

And you cannot bind to a recordset that does not support bookmarks.  When you use the Command's Execute method you create a firehose (forward-only, read-only) cursor.  This resultset cannot be used to bind to a grid. So as I stated previously the workaround is to use the Recordset's Open method to create the appropriate resultset.  As in (plagiarizing AngelIII's code):

Dim conn As ADODB.Connection, cmd As ADODB.Command, rs as ADODB.Recordset

Set conn = New ADODB.Connection
conn.Open CommonConnString
Dim cmd As New ADODB.Command
With cmd
      .CommandType = adCmdStoredProc
      .CommandText = "WHTSP_FetchReporderDetails"
      .ActiveConnection = conn
      .Parameters.Append .CreateParameter("@emp_id", adVarChar, adParamInput, 5, strEMP_ID)
      .Parameters.Append .CreateParameter("@date_scanned", adDBTime, adParamInput, , dtmTodaysDate)
      Set rs = New ADODB.Recordset
      With rs
            .CursorType = adOpenDynamic
            .CursorLocation = adUseClient
            .LockType = adLockReadOnly
            .Open Cmd
      End With
End With
Set Cmd = Nothing

Rest of your code goes here.  If you insist on using the ADO Data Control than the rest of the code would look something like this:

Set  Adodc1.Recordset = rs
Adodc1.Refresh
0
michael801Author Commented:
Thank you very much - I had to put the first end with back where it came from and things seem to work ok.  I'm posting this for anyone in the future that may be interested.  Kind regards, m801.

Dim conn As ADODB.Connection, cmd As ADODB.Command, rs as ADODB.Recordset

Set conn = New ADODB.Connection
conn.Open CommonConnString
Dim cmd As New ADODB.Command
With cmd
      .CommandType = adCmdStoredProc
      .CommandText = "WHTSP_FetchReporderDetails"
      .ActiveConnection = conn
      .Parameters.Append .CreateParameter("@emp_id", adVarChar, adParamInput, 5, strEMP_ID)
      .Parameters.Append .CreateParameter("@date_scanned", adDBTime, adParamInput, , dtmTodaysDate)
End With
      Set rs = New ADODB.Recordset
      With rs
            .CursorType = adOpenDynamic
            .CursorLocation = adUseClient
            .LockType = adLockReadOnly
            .Open Cmd
      End With
Set Cmd = Nothing

Rest of your code goes here.  If you insist on using the ADO Data Control than the rest of the code would look something like this:

Set  Adodc1.Recordset = rs
Adodc1.Refresh
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.