executing stored procedures

[SqlHunter]

I was trying to execute a a stored procedure from VB application and its giving me some error."Item canot be found in the collection corressponding to the requested name or ordinal".
Is there anything Iam missing.....Please help me with that.....

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset

Call Connect2Sourcedb.connect_dm_qa
Set rsActiveLoans = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.CommandText = "usp_resale_s;1"
MsgBox cmd.CommandText
cmd.CommandType = CommandTypeEnum.adCmdStoredProc
Set prm = cmd.CreateParameter("resale_number", adVarChar, adParamInput, 13, "0189167")
cmd.Parameters.Append prm
Set cmd.ActiveConnection = Connect2Sourcedb.conn
MsgBox cmd.ActiveConnection
Set rs = cmd.Execute
MsgBox rs.Fields("0").Value

 

[SqlHunter]

Here is the table definition.....

CREATE TABLE [dbo].[tbl_reinstate] (
[rein_id] [int] IDENTITY (1, 1) NOT NULL ,
[resale_number] [varchar] (20) NOT NULL ,
[emp_number] [varchar] (13) NOT NULL ,
[rein_amount] [money] NULL ,
[late_charges] [money] NULL ,
[good_through_date] [datetime] NULL

) ON [PRIMARY]

 

[SqlHunter]

The stored procedure used to query and get the records is usp_resale_s @resale_number .... please help me with this...

 

[fredericofonseca]

copy and paste the following code in to your query analyzer window and execute it. DO NOT CHANGE IT.

execute usp_resale_s '0189167'

I will be very surprised if it returns any record.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

Yes, I suspected as much. I believe I got it.

Set prm = cmd.CreateParameter("resale_number", adVarChar, adParamInput, 13, "0189167")

   [resale_number] [varchar] (20) NOT NULL

Theory: You're trying to pack a 20 character return into a 13 character parameter. The fact that it's varchar doesn't matter; all that means is that it doesn't pack the result with trailing spaces. Suggest you change the parameter to 20 from 13 and see if that fixes the problem.

Bob

 

[fredericofonseca]

Nah.

The same SP and code worked fine with me.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

Well, darn. Nice theory, though.