Output Parameter not working.... 1

[lance59]

We have this SP that we what it to return the value of the @maxid. We where trying to use @CERT_ID_Out to send the value back, but it only returns the value we put in.

Here is the SP:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[CERT_Add_Registration]
-- Add the parameters for the stored procedure here
@Course_ID_Reg int = 5, --initialize to 0 after initial testing
@First_Name_Reg nvarchar(30),
@Last_Name_Reg nvarchar(30),
@Address_Reg nvarchar(50),
@City_Reg nvarchar(10),
@State_Reg nchar(2),
@Zip_Code_Reg nchar(10),
@Day_Phone_Reg nchar(10),
@Eve_Phone_Reg nchar(10),
@Email_Reg nvarchar(50),
@Emerg_Contact_Reg nvarchar(50),
@Emerg_Relation_Reg nvarchar(30),
@Emerg_Phone_Reg nchar(10),
@Team_Name nvarchar(30),
@Cert_ID_Out int OUTPUT

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
-- Get next value for keys

declare @maxid int,
@maxreg int,
@currentdate datetime,
@teamreg nchar(3)
--
set @maxid=0
set @maxreg=0
set @currentdate = getdate()
set @teamreg = ' '
--
select @maxid = max(cert_id) from ufa_cert
select @maxreg = max(reg_id) from ufa_registration
select @teamreg = city_code from ufa_team where city_name = @Team_Name
--
set @maxid = @maxid + 1
set @maxreg = @maxreg + 1
Set @Cert_ID_Out = @maxid
--
INSERT INTO [CERTData].[dbo].[UFA_Registration]
([Reg_ID]
,[Reg_Last_Name]
,[Reg_First_Name]
,[Reg_Address1]
,[Reg_Address2]
,[Reg_City]
,[Reg_State]
,[Reg_ZipCode]
,[Reg_DayPhone]
,[Reg_EvePhone]
,[Reg_Email]
,[Reg_Emerg_Contact]
,[Reg_Emerg_Relation]
,[Reg_Emerg_Phone])
VALUES
(@maxreg
,@Last_Name_Reg
,@First_Name_Reg
,@Address_Reg
,null
,@City_Reg
,@State_Reg
,@Zip_Code_Reg
,@Day_Phone_Reg
,@Eve_Phone_Reg
,@Email_Reg
,@Emerg_Contact_Reg
,@Emerg_Relation_Reg
,@Emerg_Phone_Reg)
--
INSERT INTO [CERTData].[dbo].[UFA_CERT]
([CERT_ID]
,[Reg_ID]
,[Course_ID]
,[City_Code]
,[Reg_Date]
,[Cert_Date]
,[Cert_Print]
,[Tier2_Check]
,[Inactive_Flag])
VALUES
(@maxid
,@maxreg
,@Course_ID_Reg
,@teamreg
,@currentdate
,@@dbts
,0
,0
,0)

END

And here is the relevant VB.net portion:

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Cert_ID_Out", SqlDbType.Int, 5, ParameterDirection.Output))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Cert_ID_Out").Value = 1


Myconnection.Open()
MyDataAdapter.SelectCommand.ExecuteNonQuery()
TextBox1.Text = MyDataAdapter.SelectCommand.Parameters("@Cert_ID_Out").Value
MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
Myconnection.Close() 'Close the Connection

This ends up returning the value we send into the SP in this case "1". If we don't send a value to the SP we get an error that it is looking for the value. When we run the SP from 2005 SQL Studio, @Cert_ID_Out returns the correct value, but in vb.net side it returns "1". So we think it maybe a VB.net problem but we're not sure the SQL side is 100% correct.

TIA

 

[bborissov]

What is your SelectCommand text of the DataAdapter?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[lance59]

Here is the full vb.net side:

Dim Myconnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

'Create a connection to the SQL Server.
Myconnection = New SqlConnection("server=FIREFDM;" & "UID=xx;" & "Pwd=xxxxxx;" & ";database=CERTData")

'Create a DataAdapter, and then provide the name of the stored procedure
MyDataAdapter = New SqlDataAdapter("CERT_Add_Registration", Myconnection)

'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Course_ID_Reg", SqlDbType.Int, 5))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Course_ID_Reg").Value = class_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@First_Name_Reg", SqlDbType.NVarChar, 30))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@First_Name_Reg").Value = first_name_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Last_Name_Reg", SqlDbType.NVarChar, 30))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Last_Name_Reg").Value = last_name_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Address_Reg", SqlDbType.NVarChar, 50))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Address_Reg").Value = address_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@City_Reg", SqlDbType.NVarChar, 10))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@City_Reg").Value = city_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@State_Reg", SqlDbType.NChar, 2))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@State_Reg").Value = state_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Zip_Code_Reg", SqlDbType.NChar, 10))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Zip_Code_Reg").Value = zip_code_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Day_Phone_Reg", SqlDbType.NChar, 10))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Day_Phone_Reg").Value = zip_code_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Eve_Phone_Reg", SqlDbType.NChar, 10))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Eve_Phone_Reg").Value = eve_phone_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Email_Reg", SqlDbType.NVarChar, 50))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Email_Reg").Value = email_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Emerg_Contact_Reg", SqlDbType.NVarChar, 50))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Emerg_Contact_Reg").Value = emerg_contact_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Emerg_Relation_Reg", SqlDbType.NVarChar, 30))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Emerg_Relation_Reg").Value = emerg_relation_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Emerg_Phone_Reg", SqlDbType.NChar, 10))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Emerg_Phone_Reg").Value = emerg_phone_reg.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Team_Name", SqlDbType.NVarChar, 30))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Team_Name").Value = team_name.Text

'Create and add a parameter to paramaters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Cert_ID_Out", SqlDbType.Int, 5, ParameterDirection.ReturnValue))

'Assign a value to the parameter
MyDataAdapter.SelectCommand.Parameters("@Cert_ID_Out").Value = 1


Myconnection.Open()
MyDataAdapter.SelectCommand.ExecuteNonQuery()
TextBox1.Text = MyDataAdapter.SelectCommand.Parameters("@Cert_ID_Out").Value
MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
Myconnection.Close() 'Close the Connection

 

[bborissov]

Change this:

  'Create and add a parameter to paramaters collection for the stored procedure.
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Cert_ID_Out", SqlDbType.Int, 5, ParameterDirection[COLOR=red].ReturnValue[/color]))

to this:

  'Create and add a parameter to paramaters collection for the stored procedure.
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Cert_ID_Out", SqlDbType.Int, 5, ParameterDirection[COLOR=red].InputOutput[/color]))

and see what happens.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[lance59]

Hi,

Still returns a "1".

Bob

 

[bborissov]

With:

'Create and add a parameter to paramaters collection for the stored procedure.
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Cert_ID_Out", SqlDbType.Int, 5, ParameterDirection.Output))

????

I am not a NET guy, that is because I try each one by one
Maybe if you ask in .NET forum you will get answer faster.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[lance59]

OK. We did get it to work. We had to add the second line to make it work. It seems like you can't add the direction to the add line. Thanks, you got us going in the right direction.

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Cert_ID_Out", SqlDbType.Int, 5))
MyDataAdapter.SelectCommand.Parameters("@Cert_ID_Out").Direction = ParameterDirection.Output