SQL and .NET passing parameters...

[Scott]

I'm trying to pass parameters out of a SQL 2000 procedure to VB .NET. I
used the wizard with'in .NET to create my SP's and they work fine for
updates except I can't get any values back out of them. By default the
wizard set up a ReturnValue with'in my parameters collection called
@RETURN_VALUE. I assume I need to set the SourceColumn, and SqlDbType for
it to return the correct info. But it will only return a 0 as a value.

Here is the SQL code:

Alter PROCEDURE wgo.Members_Update

(

@wit_no char(10),

@active bit,

@attn char(30),

@name1_last varchar(30),

@name1_first varchar(30),

@name1_init char(1),

@name2_last varchar(30),

@name2_first varchar(30),

@name2_init char(1),

@last_flag bit,

@addr1 varchar(30),

@city varchar(20),

@state char(2),

@zip char(10),

@mail_class tinyint,

@snow_attn varchar(30),

@snow_addr1 varchar(30),

@snow_city varchar(30),

@snow_state char(2),

@snow_zip char(10),

@snow_bdate datetime,

@snow_edate datetime,

@snow_flag bit,

@snow_class tinyint,

@serial_no char(8),

@wit_news bit,

@annual_dir bit,

@phone1_type char(1),

@phone1_no char(10),

@phone2_type char(1),

@phone2_no char(10),

@email1 varchar(50),

@email2 varchar(50),

@new_free smallint,

@total_free smallint,

@amt_paid numeric(18,2),

@current_paid numeric(18,2),

@status char(1),

@memb_type char(1),

@renew datetime,

@expiration datetime,

@setup datetime,

@new_paid numeric(18,2),

@Original_wit_no char(10),

@Original_tstamp timestamp

)

AS

SET NOCOUNT OFF;

UPDATE dbo.Members SET wit_no = @wit_no, active = @active, attn = @attn,
name1_last = @name1_last, name1_first = @name1_first, name1_init =
@name1_init, name2_last = @name2_last, name2_first = @name2_first,
name2_init = @name2_init, last_flag = @last_flag, addr1 = @addr1, city =
@city, state = @state, zip = @zip, mail_class = @mail_class, snow_attn =
@snow_attn, snow_addr1 = @snow_addr1, snow_city = @snow_city, snow_state =
@snow_state, snow_zip = @snow_zip, snow_bdate = @snow_bdate, snow_edate =
@snow_edate, snow_flag = @snow_flag, snow_class = @snow_class, serial_no =
@serial_no, wit_news = @wit_news, annual_dir = @annual_dir, phone1_type =
@phone1_type, phone1_no = @phone1_no, phone2_type = @phone2_type, phone2_no
= @phone2_no, email1 = @email1, email2 = @email2, new_free = @new_free,
total_free = @total_free, amt_paid = @amt_paid, current_paid =
@current_paid, status = @status, memb_type = @memb_type, renew = @renew,
expiration = @expiration, setup = @setup, new_paid = @new_paid WHERE (wit_no
= @Original_wit_no) and (tstamp = @Original_tstamp);

SELECT wit_no, active, attn, name1_last, name1_first, name1_init,
name2_last, name2_first, name2_init, last_flag, addr1, city, state, zip,
mail_class, snow_attn, snow_addr1, snow_city, snow_state, snow_zip,
snow_bdate, snow_edate, snow_flag, snow_class, serial_no, wit_news,
annual_dir, phone1_type, phone1_no, phone2_type, phone2_no, email1, email2,
new_free, total_free, amt_paid, current_paid, status, memb_type, renew,
expiration, setup, new_paid, tstamp FROM dbo.Members WHERE (wit_no =
@wit_no)


Here is my VB code calling the returned value:

MsgBox("WIT NUMBER " &
Memb_adp.UpdateCommand.Parameters("@RETURN_VALUE").Value)



If anyone can tell me how to get a value I would really appreciate it. I'm
really trying to get the tstamp so I can verify if the record has been
modified since last read, but I can't get anything back.



Thanks!

Scott

 

[William Ryan]

It doesn't look like you've set the Parameter direction to ReturnValue.

Check out this article by Bill Vaughn, he does a great job of explaining it.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp
Cheers,

Biill