Calling A Stored Procedure

[Jim Heavey]

I have been successful in calling a stored procedure and getting the result
set. Now I am trying to figure out how to get the "return Value". The VB
documentation says that I need to set up a parameter with a "direction of
return value". So here is my code to set it up

da.SelectCommand.Parameters.Add(New SqlParameter("TotalRecords",
SqlDbType.Int, 4, ParameterDirection.ReturnValue))

When I invoke the DataAdapter Fill method, I get an error message which tells
me the query has no parameters. So I create a parameter and call it
"TotalRecords". When I execute again, it tells me that "TotalRecords" is not
a parameter of the query. Here is my query.
alter procedure proc_MoviesOverdue4
(
@TotalRecords int =0 out
)
as
select m.title as 'Title', t.tape_number as 'Tape_Number', c.first_name,
c.last_name,
convert(varchar, r.date_due,101) as Due_date,
datediff(day, date_due, todays_date) as 'Days_Overdue' into #tmp_table
from movies m, customer c, tapes t, rentals r, Video_store_values
where(m.movie_id = t.movie_id)
and t.tape_number = r.tape_number
and r.cust_number = c.cust_number
and return_date is null
and date_due < todays_date
order by Days_Overdue desc, Title asc, Tape_Number asc
SET @TotalRecords = @@ROWCOUNT

I also tried changing the last statement in the Proc to "Return @@RowCount"
figuring that this would set the return value, but this did not work either.
So I am missing something very simple, but I do not know what it is.

Thanks in advance for your assistance!!!

 

[David Browne]

I have been successful in calling a stored procedure and getting the result
set. Now I am trying to figure out how to get the "return Value". The VB
documentation says that I need to set up a parameter with a "direction of
return value". So here is my code to set it up
....

alter procedure proc_MoviesOverdue4
(
@TotalRecords int =0 out
)

That's not a return value, it's just an output parameter. Bind it with the
correct direction and it should work.

David

 

[William Ryan]

Your param name according to what you created it "TotalRecords" but the
actual name is "@TotalRecords", the rest looks ok from what I can see. Try
making that change and see what hapens.

HTH,

Bill

 

[William \(Bill\) Vaughn]

Jim, check out my article on handling RETURN value and OUTPUT parameters.
http://www.betav.com/msdn_magazine.htm

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________