VB.NET - Update my VB.Net datareader and use this to for the Crystal Report datasource

Asked By C on 15-Jan-13 10:57 AM


I am trying to use an updated datasource to be use in my crystal report. The crystal report datasource is vwMyView. I want to amend the contents of vwMyView and use the updated datasource to be used in the crystal report. How can I do this and what would the syntax be or is there a better way of doing this. Below is my code, what should I add. Help

Public sub PossibleEmployeeSalary

      Dim adoCmd As New SqlClient.SqlCommand
      Dim adoRdr As SqlDataReader

    Dim cryRpt As New ReportDocument
        adoCmd.Connection = adoConn
        adoCmd.CommandType = CommandType.Text
        adoCmd.CommandText = "SELECT * FROM vwMyView"    

        While adoRdr.Read()
          adoRdr ("Salary")  =  adoRdr("Salary")   * 0.05
          adoRdr("Surname") =  adoRdr("Surname")
        End While


      Catch ex As Exception

      End Try

    End Sub

Tom Wilson replied to C on 15-Jan-13 11:54 AM
 I am not sure that CR will see the changes you make in the datareader's data.  You cannot update from a datareader and you cannot update a database view.

CR requires the datasource to be an IDataReader, not a SQLDataReader.  So change this line:

 Dim adoRdr As SqlDataReader to Dim adoRdr as IDataReader

and this line:
 adoCmd.ExecuteReader() to   adoRdr = adoCmd.ExecuteReader()  (The ExecuteReader is a function and returns a datareader.

CrystalReportViewer1.ReportSource = cryRpt

This line does nothing:
 adoRdr("Surname") = adoRdr("Surname")

Again, I do not think the report will show the changes. You will need to use a datatable, make the changes in the datatable, then pass the datatable to CR.

Dim dt As new Datatable
Dim da as new DataAdapter(adoCmd)
For each row as datarow in dt.rows
 row("Salary") = row("Salary") * .05

C replied to Tom Wilson on 15-Jan-13 12:53 PM
Hi Tom,

Thanks for taking timeout to answer my query which has been driving me crazy. I will try this tomorrow and let you know how I got on.
Thanks again
C replied to Tom Wilson on 16-Jan-13 09:42 AM
Hi Tom,

Applied changes and from your sample code realised where I was going wron.

Thanks again for your help.