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

Hi,

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
      cryRpt.Load("C:\TEMP\REPORT1.RPT")
      Try
        adoCmd.Connection = adoConn
        adoCmd.CommandType = CommandType.Text
        adoCmd.CommandText = "SELECT * FROM vwMyView"    
        adoCmd.ExecuteReader()

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

        CrystalReportViewer1.Zoom(1)
        CrystalReportViewer1.Refresh()

      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.

cryRpt.SetDataSource(adoRdr)
CrystalReportViewer1.ReportSource = cryRpt
CrystalReportViewer1.Zoom(1)
CrystalReportViewer1.Refresh()

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)
da.Fill(dt)
For each row as datarow in dt.rows
 row("Salary") = row("Salary") * .05
Next

cryRpt.SetDataSource(dt)
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.