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

 

[fredericofonseca]

firmst remove the "msgbox cmd.activeconnection". Thats an object so nothing to show.

Then replace
MsgBox rs.Fields("0").Value
by

if not rs.bof and not rs.eof then
msgbox "record returned"
MsgBox rs.Fields("0").Value
else
msgbox "record not returned"
end if



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SqlHunter]

The problem here if I supply this value "0189167" to the stored procedure there is recordset available..Whyt is not showing in here

 

[BobRodes]

Various troubleshooting things to try:
1. Assuming that you have SQL Server 7 or later, create a simple select statement in Query Analyzer that returns the record
2. Use query analyzer to exec your stored proc with the value passed.
3. Try to return a recordset in VB, using a select statement.

If all of those work, look for differences in the datatypes of the parameter and the field it's evaluating. Sometimes varchar and char can refuse to get along.

HTH

Bob

 

[johnwm]

Don't believe that "0" is a legal fieldname. You maybe mean to use:
MsgBox rs.Fields(0).Value

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[fredericofonseca]

can you also post the contents of your stored procedure


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

firmst remove the "msgbox cmd.activeconnection". Thats an object so nothing to show.

While this isn't necessarily bad advice, for precision's sake, this msgbox will actually show the connectionstring property of the active connection, since connectionstring is the default property of the connection object.

Bob

 

[BobRodes]

One other thing, sqlhunter: you get the error you mention when you're asking for a field that doesn't exist in the table or tables you're querying.

Bob

 

[SqlHunter]

I removed msgbox cmd.activeconnection
Again checked MsgBox rs.Fields(0).Value instead of MsgBox rs.Fields("0").Value

If I run the usp_resale_s "0189167" in sql analyzer it gives me records....

But when I run with the VB applicatio it doesn't work..

 

[SqlHunter]

After executing
Set rs = cmd.Execute

Should I open the record set to call rs.fields(0).value....

 

[BobRodes]

Looks correct, except I don't know where you're instantiating rsActiveLoans from. I'm assuming that's a line unrelated to our code. So, the reason you're getting the error is because you're trying to plug a result into a mismatching recordset. You need to make sure that that rs hasn't been elsewhere created or something. This code

for i = 0 to rs.fields.count -1
   debug.print rs.fields(i).name
next

will print out the names of all the fields in your recordset. Try putting it into a proc somewhere, and then calling it from the debug window while stepping through your code. That will probably give a clue. Anyway, it looks like you've used rs somewhere else in your code. If you do a set rs = new adodb.recordset before setting it to the result of cmd.execute, you'll clear it.

HTH

Bob

 

[SqlHunter]

I changed to
Set rs = New ADODB.Recordset
Now if I debug ,
rs.fields(i).name are showing the corressponding field name but
MsgBox rs.Fields(i).Value gives error still.....

for i = 0 to rs.fields.count -1
debug.print rs.fields(i).name
MsgBox rs.Fields(i).Value
next

 

[ArtieChoke]

After you retrieve the recordset, test the EOF property in the immediate window or print it out with a debug statement. If it's true, then you didn't get any data.

Also, does the stored procedure return a rowset or just an output return value? If the sp doesn't do a select as the last statement, you are not getting a row returned. You are just getting the status from the sp in the recordset.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[fredericofonseca]

Ah. I just noticed it.

the command object has NO declared output parameters so it will never work (as fas as I know).

So once again please show us you Stored Procedure SQL code.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

sorry. Small correction to what I said.

the command object has NO declared output parameters so it will never work (as fas as I know) [bold]if the SP does not return a recordset[/bold].

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SqlHunter]

The stored procedure is as shown below

CREATE PROCEDURE dbo.usp_resale_s

@resale_number varchar(20)
AS
BEGIN
SELECT
rein_id,emp_number,rein_amount,late_charges,
CONVERT(varchar(8), good_through_date, 1)
AS good_through_date
FROM
tbl_reinstate
WHERE
resale_number =@resale_number
END

GO

 

[BobRodes]

Can you send your database schema too, please?

Bob

 

[fredericofonseca]

the following code works.

Option Explicit
Private Sub Command1_Click()
Dim Connect2Sourcedb As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim dbconnstring As String
Dim i As Long

Set Connect2Sourcedb = New ADODB.Connection
    dbconnstring = "dsn=LocalServer;uid=frederico;pwd=frede1;database=factucli;"
    Connect2Sourcedb.Open dbconnstring

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.CommandText = "usp_resale_s;1"
cmd.CommandType = CommandTypeEnum.adCmdStoredProc
Set prm = cmd.CreateParameter("resale_number", adVarChar, adParamInput, 13, "0189167")
cmd.Parameters.Append prm
Set cmd.ActiveConnection = Connect2Sourcedb
Set rs = cmd.Execute
If rs.BOF And rs.EOF Then
  MsgBox "No records returned"
Else
  For i = 0 To rs.Fields.Count - 1
    Debug.Print rs.Fields(i).Name
    If IsNull(rs.Fields(i).Value) Then
      MsgBox "field " & i & " is NULL"
    Else
      MsgBox rs.Fields(i).Value
    End If
  Next
End If
End Sub

Note that as you didn't supply the definition of Connect2Sourcedb I have defined this as a ADODB connecction object. Please change it again to your way of coding.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SqlHunter]

Thanks for all the assistance u all are giving.......
Its giving the message
MsgBox "No records returned"
and when I execute the stored procedure on the same database with the paramter its gives a record....I went into the database table and checked for the data and its available there....Now what should I do ????

 

[fredericofonseca]

can you give us the table definition


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com