REPORT DEFINITION LANGUAGE CLIENT SIDE (RDLC) STORED PROCEDURE WITH PARAMETERS

1)      Open a web page

Ø  Open a visual studio add a webpage rename it as studentdetails.aspx

Ø  Design the web page like below

2)   Create a student table

Ø  Open sql server create a database student

Ø  In that database create a table like below query

Create table Student (StudentName varchar (50), StudentId int, StudentMarks int, DateofBirth date)

(Or)

3)   create a procedure

Create procedure StudentDetails (pStudentName varchar (50), pStudentId int, pStudentMarks int, pDateofBirth date)

As

Select * from Student

Exec

Ø  Procedure created

Write a query in procedure

ALTER PROCEDURE StudentDetails

(

@pstudentname varchar (50),

@pstudentmarks int)

AS

Select * from Student where StudentName=@pstudentname

Update Student set StudentMarks=@pstudentmarks where StudentName=@pstudentname

     RETURN

4) Add a report in to the project

Ø Go to solution explorer right click on that add new item select report template rename it as StudentReport.rdlc

Ø  Do to toolbox double click on table it will display the dataset properties

Ø Select the dataset or click on new dataset select the connection string click on next select the table name click on ok click on finish

Ø Go to report data it will display the all fields of the student table

Ø Drag and drop all fields into table it will display like below

5)   Write the below code in Stdentdetails.aspx

Ø Binding the data into dropdown list box and bind the data into textbox when a dropdown is selected

Protected Sub ddlstudent_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlstudent.SelectedIndexChanged

        Dim str As String = "Data Source=DV004\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True"

        Dim conn As New SqlConnection(str)

        conn.Open()

        Dim cmd As New SqlCommand("select * from Student where StudentName='" + ddlstudent.SelectedValue + "'", conn)

        Dim r As SqlDataReader = cmd.ExecuteReader()

        If r.HasRows() Then

            If r.Read() Then

                txtstumarks.Text = r.GetInt32(2)

            End If

        End If

        ddl()

    End Sub

Ø Write the below code in page_load()

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'txtstumarks.Text &= ddlstudent.SelectedValue.ToString()

        If (IsPostBack = False) Then

            display()

        End If

    End Sub

Ø We can create a function ddl()

Protected Sub ddl()

        Dim str As String = "Data Source=DV004\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True"

        Dim conn As New SqlConnection(str)

        conn.Open()

        Dim cmd As New SqlCommand("select * from Student where StudentName='" + ddlstudent.SelectedValue + "'", conn)

        'cmd.CommandType = CommandType.StoredProcedure

        'Dim p1 As New SqlParameter()

        'cmd.Parameters.Add("@pstudentname", SqlDbType.VarChar).Value = ddlstudent.SelectedValue

        Dim da As New SqlDataAdapter(cmd)

        Dim ds As New DataSet()

        da.Fill(ds)

        conn.Close()

        Dim rptds As New ReportDataSource("StudentDataSet", ds.Tables(0))

        ReportViewer1.LocalReport.DataSources.Clear()

        ReportViewer1.LocalReport.DataSources.Add(rptds)

        ReportViewer1.LocalReport.Refresh()

    End Sub

Ø Write the code in update buttion

Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim str as String = "Data Source=DV004\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True"

        Dim conn As New SqlConnection (str)

        conn.Open()

        Dim cmd As New SqlCommand("StudentDetals", conn)

        cmd.CommandType = CommandType.StoredProcedure

        Dim p1 As New SqlParameter()

        Dim p2 As New SqlParameter()

        cmd.Parameters.Add("@pstudentmarks", SqlDbType.VarChar).Value = txtstumarks.Text

        cmd.Parameters.Add("@pstudentname", SqlDbType.VarChar).Value = ddlstudent.SelectedValue

        cmd.ExecuteNonQuery()

       

        MsgBox("records updated successfully")

        ddl ()

    End Sub

Ø Build the application

Ø Debug the application the result will be shown like below