' stored procedure
CREATE PROCEDURE admin_sp_InsertStudent
(
     @in_strStudFName VARCHAR(35),
     @in_strStudLName VARCHAR(35),
     @in_strStudEmail VARCHAR(100),
     @in_intStudPhone BIGINT,
     @out_intIdentity INTEGER OUTPUT
)

AS

DECLARE @ERR INT

SET NOCOUNT ON

BEGIN

    INSERT INTO tblStudent (studFName, studLName,
            studEmail, studPhone)
    VALUES (@in_strStudFName, @in_strStudLName,
            @in_studEmail, @in_intStudPhone)

    SELECT @out_intIdentity = SCOPE_IDENTITY(), @ERR = @@ERROR

END

' .NET code to call stored procedure and fill intStudID
    Dim param1 As New SqlClient.SqlParameter

    sqlCmd = New SqlClient.SqlCommand("admin_sp_InsertStudent", sqlConn)

    With sqlCmd

        .CommandType = CommandType.StoredProcedure

        .Parameters.AddWithValue("@in_strStudFName", Me.txtStudFName.Text.Trim)
        .Parameters.AddWithValue("@in_strStudLName", Me.txtStudLName.Text.Trim)
        .Parameters.AddWithValue("@in_strEmail", Me.txtEmail.Text.Trim)
        .Parameters.AddWithValue("@in_intPhone", Me.txtPhone.Text.Trim)
        .Parameters.AddWithValue("@out_intIdentity", SqlDbType.Int)

        .Parameters("@out_intIdentity").Direction = ParameterDirection.Output

        param1.ParameterName = "@out_intIdentity"

        Try
            .Connection.Open()
            sqlCmd.ExecuteScalar()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally

            If .Connection.State = ConnectionState.Open Then
                .Connection.Close()
            End If

        End Try

    End With

    MessageBox.Show(param1.ToString)

CREATE PROC insert_ticket (
					@ClientID integer,
					@FacilityID bigint=NULL,
					@CategoryID bigint=NULL,
					@PriorityID varchar(50),
					@CreatedBy bigint,
					@TargetDate datetime,
					@Ticket varchar(5000),
					@TicketResolution varchar(5000)
				    )
AS

INSERT INTO	Ticket(	ClientID,
			FacilityID,	
			CategoryID,
			PriorityID,
			CreatedBy,
			TargetDate,
			Ticket,
			TicketResolution
		         ) 
SELECT
	@ClientID,
	@FacilityID,
	@CategoryID,
	@PriorityID,
	@CreatedBy,
	@TargetDate,
	@Ticket,
	@TicketResolution
RETURN SCOPE_IDENTITY()
GO

Dim conMyData As SqlConnection
        Dim cmdInsert As SqlCommand
        Dim parmReturnValue As SqlParameter
        Dim intResult As Long

        Try
            conMyData = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
            cmdInsert = New SqlCommand("insert_ticket", conMyData)

            With cmdInsert
                .CommandType = CommandType.StoredProcedure
                parmReturnValue = .Parameters.Add("RETURN_VALUE", SqlDbType.BigInt)
                parmReturnValue.Direction = ParameterDirection.ReturnValue
                'add the parameters
                .Parameters.Add("@ClientID", SqlDbType.Int).Value = Me.ddlClient.SelectedItem.Value

                If (Me.ddlFacility.SelectedIndex = -1) Then
                    .Parameters.Add("@FacilityID", SqlDbType.BigInt).Value = Nothing
                Else
                    .Parameters.Add("@FacilityID", SqlDbType.BigInt).Value = Me.ddlFacility.SelectedItem.Value
                End If

                If (Me.ddlCategory.SelectedIndex = -1) Then
                    .Parameters.Add("@CategoryID", SqlDbType.BigInt).Value = Nothing
                Else
                    .Parameters.Add("@CategoryID", SqlDbType.BigInt).Value = Me.ddlCategory.SelectedItem.Value
                End If

                If (Me.ddlPriority.SelectedIndex = -1) Then
                    .Parameters.Add("@PriorityID", SqlDbType.VarChar).Value = Nothing
                Else
                    .Parameters.Add("@PriorityID", SqlDbType.VarChar).Value = Me.ddlPriority.SelectedItem.Value
                End If

                'now we check if an admin is assigning
                'the call for a user...
                'this may happen if the user calls the admin department
                'rather than using the tool
                If Me.tblAdmin.Visible = True Then
                    'its an admin
                    'now check if its for a user
                    If Me.rbUser.Checked = True Then
                        'admin assigning to a user
                        .Parameters.Add("@CreatedBy", SqlDbType.BigInt).Value = CType(Me.ddlUsers.SelectedValue, Long)
                    Else
                        'self assign
                        .Parameters.Add("@CreatedBy", SqlDbType.BigInt).Value = lngLoginID
                    End If
                Else
                    'its only a user so dont even bother...
                    .Parameters.Add("@CreatedBy", SqlDbType.BigInt).Value = lngLoginID
                End If

                .Parameters.Add("@TargetDate", SqlDbType.DateTime).Value = Me.lblTargetDate.Text
                .Parameters.Add("@Ticket", SqlDbType.VarChar).Value = Me.txtTicketDescription.Text
                .Parameters.Add("@TicketResolution", SqlDbType.VarChar).Value = Me.txtTicketResolution.Text
                conMyData.Open()
                .ExecuteNonQuery()
                intResult = CType(.Parameters("RETURN_VALUE").Value, Long)
            End With
            InsertTicket = intResult
        Catch e As Exception
            Response.Write("An Error Occurred: " & e.ToString())
            InsertTicket = 0
            'clean up and close resources
        Finally
            cmdInsert = Nothing
            conMyData.Close()
            conMyData = Nothing
        End Try