Stored Procedures Parameter issues 
Author Message
 Stored Procedures Parameter issues

1)    Just doesn't work, and I can't figure it out.
thanks very much.

   Set com = New ADODB.Command
    With com
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = "SELECT First_Name, Last_Name FROM Students"
    End With

   Set rs = New ADODB.Recordset
   rs.CursorLocation = adUseClient
   rs.LockType = adLockBatchOptimistic
   rs.CursorType = adOpenStatic
   rs.ActiveCommand = com    (PROBLEM MIGHT BE HERE!!!)
   rs.Open

2)    Just to confirm! I can pass just some parameters to a stored procedure
in any order by using the property NamedParameter from ADO 2.6 or later. Is
that right?

3)    Private Sub Command1_Click()
...
Set cmd = New Command
With cmd
    .ActiveConnection = cn
    .CommandType = adCmdStoredProc
    .CommandText = "GetOrders"

adParamReturnValue, , 0)

10, Text1)
    Set rs = .Execute

PROBLEMS HERE: ---------------------------------------

 'Set DataGrid1.DataSource = rs

 ------------------------------------------------------------------

End With
End Sub

STORED PROCEDURE CODE (TESTED!)

'''CREATE PROCEDURE GetOrders -----------------------------

'''AS
'''
'''   SELECT OrderID, CustomerID, EmployeeID
'''   From [Orders]


'''GO
'''
'''-- SHOWING THE RETURN VALUE





Sun, 08 May 2005 21:43:32 GMT  
 Stored Procedures Parameter issues
Hi,

When you are working with object , then use SET

1)
    Set com = New ADODB.Command
     With com
         Set .ActiveConnection = cn '**********************
         .CommandType = adCmdText
         .CommandText = "SELECT First_Name, Last_Name FROM Students"
     End With

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.LockType = adLockBatchOptimistic
    rs.CursorType = adOpenStatic
    Set rs.ActiveCommand = com    '********************** >
    rs.Open

2) You have to declare them in the same order as you have those parameters
in your SP. Later on you could populate them with values in any order

3) What is problem here?

--
Val Mazur
Microsoft MVP



Quote:
> 1)    Just doesn't work, and I can't figure it out.
> thanks very much.

>    Set com = New ADODB.Command
>     With com
>         .ActiveConnection = cn
>         .CommandType = adCmdText
>         .CommandText = "SELECT First_Name, Last_Name FROM Students"
>     End With

>    Set rs = New ADODB.Recordset
>    rs.CursorLocation = adUseClient
>    rs.LockType = adLockBatchOptimistic
>    rs.CursorType = adOpenStatic
>    rs.ActiveCommand = com    (PROBLEM MIGHT BE HERE!!!)
>    rs.Open

> 2)    Just to confirm! I can pass just some parameters to a stored
procedure
> in any order by using the property NamedParameter from ADO 2.6 or later.
Is
> that right?

> 3)    Private Sub Command1_Click()
> ...
> Set cmd = New Command
> With cmd
>     .ActiveConnection = cn
>     .CommandType = adCmdStoredProc
>     .CommandText = "GetOrders"

> adParamReturnValue, , 0)

adParamInput,
> 10, Text1)
>     Set rs = .Execute

> PROBLEMS HERE: ---------------------------------------

>  'Set DataGrid1.DataSource = rs

>  ------------------------------------------------------------------

> End With
> End Sub

> STORED PROCEDURE CODE (TESTED!)

> '''CREATE PROCEDURE GetOrders -----------------------------

> '''AS
> '''
> '''   SELECT OrderID, CustomerID, EmployeeID
> '''   From [Orders]


> '''GO
> '''
> '''-- SHOWING THE RETURN VALUE






Sun, 08 May 2005 22:14:56 GMT  
 Stored Procedures Parameter issues
Marcelo

See reply in-line

Sukesh



Quote:
> 1)    Just doesn't work, and I can't figure it out.
> thanks very much.

>    Set com = New ADODB.Command
>     With com
>         .ActiveConnection = cn
>         .CommandType = adCmdText
>         .CommandText = "SELECT First_Name, Last_Name FROM Students"
>     End With

>    Set rs = New ADODB.Recordset
>    rs.CursorLocation = adUseClient
>    rs.LockType = adLockBatchOptimistic

      rs.CursorType = adOpenStatic
###

    rs.ActiveCommand = com    (PROBLEM MIGHT BE HERE!!!)
    rs.Open

    WHAT SORT OF PROBLEM ?

    NOT KNOWING THE PROBLEM - INSTEAD TRY

    Set rs=com.Execute()

###

Quote:
> 2)    Just to confirm! I can pass just some parameters to a stored
procedure
> in any order by using the property NamedParameter from ADO 2.6 or later.
Is
> that right?
> 3)    Private Sub Command1_Click()
> ...
> Set cmd = New Command
> With cmd
>     .ActiveConnection = cn
>     .CommandType = adCmdStoredProc
>     .CommandText = "GetOrders"

> adParamReturnValue, , 0)

adParamInput,
> 10, Text1)
>     Set rs = .Execute

> PROBLEMS HERE: ---------------------------------------

###

WHAT SORT OF PROBLEMS ?

###

- Show quoted text -

Quote:
>  'Set DataGrid1.DataSource = rs

>  ------------------------------------------------------------------

> End With
> End Sub

> STORED PROCEDURE CODE (TESTED!)

> '''CREATE PROCEDURE GetOrders -----------------------------

> '''AS
> '''
> '''   SELECT OrderID, CustomerID, EmployeeID
> '''   From [Orders]


> '''GO
> '''
> '''-- SHOWING THE RETURN VALUE






Sun, 08 May 2005 22:16:39 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. ado recordsets from stored procedures, stored procedures have input parameters

2. Performance Issue with SQL Stored Procedure

3. Performance Issues with Stored Procedures

4. Performance Issue with SQL Stored Procedure

5. To Stored Procedure or not to stored procedure

6. To stored procedure or not to stored procedure

7. sub report changes its source stored procedure to main report's stored procedure

8. To stored procedure or not to stored procedure

9. Passing Parameters to Store Procedures

10. Passing a parameter to a stored procedure from VBA

11. ADO: Running a stored procedure that takes parameters (?!?!?)

12. Passing parameter to Stored Procedures from VB

 

 
Powered by phpBB® Forum Software