Thursday 10 November 2011

How to access Output parameter value of Stored Procedure in ASP.NET (Look on Carefully Colored Area)


Protected Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click

'Define a sql parameter for SP Output Argument

        Dim MaxClientId As New SqlParameter("@MaxClientId", SqlDbType.Int)
        MaxClientId.Direction = ParameterDirection.Output

        Dim cmd As SqlCommand = New SqlCommand("SpClientMaster", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Client_id", Val(TxtClientId.Text))
        cmd.Parameters.AddWithValue("@Client_name", TxtName.Text)

        If TxtDob.Text.Length > 0 Then
            cmd.Parameters.AddWithValue("@Client_dob", (mf.mdyDate(TxtDob.Text)))
        Else
            cmd.Parameters.AddWithValue("@Client_dob", DBNull.Value)
        End If
        cmd.Parameters.AddWithValue("@Client_gender", DdlGender.SelectedValue)
        cmd.Parameters.AddWithValue("@Client_mar_status", DdlMaritalStatus.SelectedValue)
        cmd.Parameters.AddWithValue("@Client_mobile", TxtMobile1.Text)
        cmd.Parameters.AddWithValue("@Client_mobile2", TxtMobile2.Text)

        cmd.Parameters.AddWithValue("@Client_email", TxtEmail1.Text)
        cmd.Parameters.AddWithValue("@Client_email2", TxtEmail2.Text)

        cmd.Parameters.AddWithValue("@Login_id", Val(Session("Login_id")))

        If (ChkActive.Checked = True) Then
            cmd.Parameters.AddWithValue("@Client_active", 1)
        Else
            cmd.Parameters.AddWithValue("@Client_active", 0)
        End If

        cmd.Parameters.Add(MaxClientId) 'Value parameter 'being passed to sqlcommand object

        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

        If Val(TxtClientId.Text) = 0 Then
            TxtClientId.Text = MaxClientId.Value.ToString()
        End If
    End Sub


Stored Procedure:

Alter Proc SpClientMaster
(
@Client_id int,
@Client_name varchar(50),
@Login_id int,
@Client_gender char(1),
@Client_dob datetime,
@Client_mar_status char(1),
@Client_mobile varchar(12),
@Client_mobile2 varchar(12),
@Client_email varchar(50),
@Client_email2 varchar(50),
@Client_active bit,

@MaxClientId int output  /* define a Output parameter valiable*/
)
as
begin

set @Client_id = (select isnull(max(Client_id),0) + 1 from Client_master)
set @MaxClientId = @Client_id  /* setting Output parameter value */
insert Client_master(Client_id,Name,Login_id,Password,Mobile,Mobile2,Email,Email2,Dob,Gender,Active,Create_dt,
Marital_status)

values(@Client_id,@Client_name,@Login_id,@Client_id,@Client_mobile,@Client_mobile2,@Client_email,@Client_email2,
@Client_dob,@Client_gender,@Client_active,getdate(),@Client_mar_status)
end

No comments:

Post a Comment