vb6 call recordset from sql server 2000 - working until using parameters - then empty recordset

Tech-Archive recommends: Speed Up your PC by fixing your registry

I want to get a adodb.recordset back from a stored procedure:
Without parameters it works fine:

CREATE PROCEDURE sp_SuchenAuftragsdokumente
as
SELECT
barcode,doctyp
FROM
Saperion.DEF_KUNDE k
WHERE
k.barcode like 'VTA%'
GO

Private Sub Command2_Click()
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim sp As String

sp = "sp_SuchenAuftragsdokumente"
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = "Provider=SQLOLEDB.1;Password=ccc;Persist
Security Info=True;User ID=sa;Initial Catalog=Saperion;Data
Source=archivtest"
Conn1.Open

Set Cmd1 = New ADODB.Command

With Cmd1
.ActiveConnection = Conn1
.CommandType = adCmdStoredProc
.CommandText = sp
End With

Set Rs1 = Cmd1.Execute
Set Me.VSFlexGrid1.DataSource = Rs1

Rs1.Close
Conn1.Close

Set Rs1 = Nothing
Set Conn1 = Nothing
End Sub

But If I am adding an input parameter,

sp:
(
@aufnr varchar(12)
)


the recordset comes back, too - but all fields are empty - its
amazing:

My both trials dont work

'dont work
' .Parameters.Append .CreateParameter("@aufnrz", adVarChar,
adParamInput, 12, "XXXX")

'dont work
' .Parameters.Refresh
' .Parameters("@aufnr").Value = "test"


Any idea?

Thanks
aaapaul

.

Relevant Pages

  • Re: Passing Dates to stored procedure - parameter headache.
    ... command type because I'm now running a command, not a stored procedure. ... procedure in SQL 2005. ... Dim prm1 As ADODB.Parameter ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO Connection Problem
    ... Dim Source As Variant, Rs1 As ADODB.Recordset ... Dim JobOrderNo As String, Srcresult As String, Connect As String, CoID As ... ' Display Input Box and Get Job Order # ... Set Rs1 = New ADODB.Recordset ...
    (microsoft.public.outlook.program_vba)
  • RE: Object required error...
    ... Set rs1 = db.OpenRecordset ... Dim rs1, rs2, rs3 As Recordset ... Set rs2 = db.OpenRecordset ... Set rs3 = db.OpenRecordset ...
    (microsoft.public.excel.programming)
  • RE: Object required error...
    ... Set rs1 = db.OpenRecordset ... Dim rs1, rs2, rs3 As Recordset ... Set rs2 = db.OpenRecordset ... Set rs3 = db.OpenRecordset ...
    (microsoft.public.excel.programming)
  • Stored procedure Output Parameter headache
    ... I have the following code chunk for a page which accesses an SQL Server 2000 stored procedure that takes an SSN and a Last Name and checks our database for a match. ... Dim cmdDWExport ... .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamInput, 25, txtLName) ... declare @RetMsg varchar ...
    (microsoft.public.inetserver.asp.db)