Calling A Stored Procedure

  • Thread starter Thread starter Jim Heavey
  • Start date Start date
J

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!!!
 
Jim Heavey said:
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
)
Click to expand...

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

David
 
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
 
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.
__________________________________
 
Back
Top