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



Bob is 100% correct with his comments. I would add :
Did you build a Parameters collection to handle the Parameters?
What errors are you getting?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:u6Gl2x0BIHA.2004@xxxxxxxxxxxxxxxxxxxxxxx
aaapaul wrote:
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"

Nothing to do with your problem, but you should avoid using the "sp_"
prefix for your stored procedures. "sp_" indicates to the query engine
that the procedure is a system stored procedure, which means that it
will waste time by first looking for the procedure in the Master
database, only looking in the current database when it is not found in
Master. Granted, the time wasted in very small, but the real problem
comes in when you give your procedure the same name as a current system
procedure. You will find that the system procedure will inexplicably be
executed when you attempt to call your procedure. Instead of trying to
remember to avoid names that conflict with system procedure names, you
can make your life a lot simpler by simply never using "sp_" to prefix
your procedure names unless you are creating a system procedure.

Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = "Provider=SQLOLEDB.1;Password=ccc;Persist
Security Info=True;User ID=sa;Initial Cat