Global variable to be inserted as parameter [RESOLVED]

[angelica]

hey,

Im trying to use stored procedures for the first time so please bear with me.
My stored procedure compiles from the SQL Server Studio Manager so it should be fine. Now with this procedure Im trying to insert a record and get its Primary key which is an auto incremented one.

CREATE PROCEDURE dbo.AddNewDelivery
-- Add the parameters for the stored procedure here
@schoolFID int,
@bookFID int,
@qty int,
@deliveryDate smalldatetime,
@userFID int,
@deliveryID 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

INSERT INTO tb_delivery (schoolFID, bookFID, qty, deliveryDate, userFID)
VALUES (@schoolFID, @bookFID, @qty, @deliveryDate, @userFID)
SET @deliveryID = Scope_Identity()
END

My VB.NET error states that it expects @userId as a parameter that is not supplied. I am declaring it global and giving it a value within the function. So what's the mistake?? Here the code for the insert command

Public userId as integer

--------------------------------------
Private Sub NewDelivery()
        Dim query As String = "AddNewdelivery"
        Dim deliveryID As Integer
        userId = 2
        Dim deliveryboolean As Boolean

        Try
            Using cmd As New SqlCommand(query, MyConn)

                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@schoolFID", cboSchD.SelectedValue)
                cmd.Parameters.AddWithValue("@bookFID", cboBookD.SelectedValue)
                cmd.Parameters.AddWithValue("@qty", txtQtyD.Text)
                cmd.Parameters.AddWithValue("@deliveryDate", dtpDelivery.Value)
                cmd.Parameters.AddWithValue("@userID", userId)

                MyConn.Open()
                cmd.ExecuteNonQuery()
                deliveryID = cmd.Parameters("@deliveryID").Value
                ShowScopeIdentity.Text = deliveryID.ToString
                deliveryboolean = True
            End Using
        Catch ex As Exception

            MessageBox.Show(ex.ToString)
            deliveryboolean = False
        Finally
            If MyConn.State = ConnectionState.Open Then
                MyConn.Close()
                Me.Dispose()
            End If
        End Try
    End Sub

Is this the right way to get the Scope Identity (PK) into a textbox(a variable later on ) so I can insert in another table.

[mendhak]

You've declared the parameter @userFID int, but are callint it @userID in your code.

[jmcilhinney]

You also haven't added a @deliveryID parameter to your command amd set its Direction to Output.

[angelica]

Hi Mendak and JMC,

First of all thanks always for your help, appreciate it a lot.

problem solved. Never done it so I didnt know I should be declaring it in vb.net as well.

Thanks loads to both of you guys.

[riteshjain1982]

try this

 cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@schoolFID", cboSchD.SelectedValue)
                cmd.Parameters.AddWithValue("@bookFID", cboBookD.SelectedValue)
                cmd.Parameters.AddWithValue("@qty", txtQtyD.Text)
                cmd.Parameters.AddWithValue("@deliveryDate", dtpDelivery.Value)
                cmd.Parameters.AddWithValue("@userID", userId)
cmd.Parameters.Add( "@deliveryID", SqlDbType.int)
cmd.parameter("@deliveryID").Direction = ParameterDirection.Output

[angelica]

ah riteshjain1982,

bump on the solution, yes exactly as yours. Thanks anyway.

[riteshjain1982]

ahhh i missed JMC's post