Crystal Reports

[bghanim]

Hi All,
I need to do with my application dynamic report i.e. to make the report read its data source from a dataSet.

look at this code:

Dim con As New OleDb.OleDbConnection
Dim dataAdp As New OleDb.OleDbDataAdapter
Dim dataSetObj As New dataSet <<<--------------------
Dim sql As New OleDb.OleDbCommand

con.ConnectionString = conStr
sql.CommandText = "select * from cl_doctors where dr_name like '" & me.textBox1.text & "'"
sql.Connection = con
dataAdp.SelectCommand = sql

Try
con.Open()
dataAdp.SelectCommand.ExecuteNonQuery()
dataAdp.Fill(dataSetObj, "cl_doctors")

Catch ex As Exception
MsgBox(ex.Message)
Finally
con.Close()
End Try


I need to make the dataSetObj as the data source of the crystal report.
can any one help me ?


Regards,
Baha

 

[lingsn]

You need to create a form, and put the report view in it.

For crystal report to read data from the datasource, you can try out this code:

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rpt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] <your report>
rpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape
rpt.PrintOptions.PaperSize = PaperSize.PaperA4
[/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rptViewer [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] frmReport ' Your report viewer
rpt.SetDataSource(dataSetObj.Tables("cl_doctors")[/SIZE][SIZE=2])
rptViewer.crpViewer.ReportSource = rpt
[/SIZE][SIZE=2]rptViewer.crpViewer.Zoom(7)
[/SIZE][SIZE=2]rptViewer.Show()
[/SIZE]

 

[bghanim]

I tried what you told me, but it didnt work :-( [ Invalid table name ]

here is the code:

Dim sqlConObj As New sqlConnector
Dim dataSetObj As New DataSet
dataSetObj = sqlConObj.connectToDBAndReturnDS("cl_doctors", "select * from cl_doctors")

Dim rpt As New Tester2
rpt.PrintOptions.PaperOrientation = CrystalDecisions.[Shared].PaperOrientation.Landscape
rpt.PrintOptions.PaperSize = CrystalDecisions.[Shared].PaperSize.PaperA4

' Your report viewer
rpt.SetDataSource(dataSetObj.Tables(0))
Me.CrystalReportViewer1.ReportSource = rpt
Me.CrystalReportViewer1.Zoom(7)
Me.CrystalReportViewer1.Show()

 

[lingsn]

At which line you got the error? And can you try to debug and see what is inside dataSetObj before you set the crystal report data source?

 

[bghanim]

Dear lingsn
the error occured in the line
rpt.SetDataSource(dataSetObj.Tables(0))


Error Msg:
An unhandled exception of type 'CrystalDecisions.CrystalReports.Engine.InvalidArgumentException' occurred in crystaldecisions.crystalreports.engine.dll

Additional information: Error in File C:\DOCUME~1\bghanim\LOCALS~1\Temp\temp_d2ad75a3-1bbc-4660-8ae0-cd6002766850.rpt:
Invalid table number.



the code is :
Dim sqlConObj As New sqlConnector
Dim dataSetObj As New DataSet
dataSetObj = sqlConObj.connectToDBAndReturnDS("cl_doctors", "select id, dr_name from cl_doctors")

Dim rpt As New bbb
rpt.PrintOptions.PaperOrientation = CrystalDecisions.[Shared].PaperOrientation.Landscape
rpt.PrintOptions.PaperSize = CrystalDecisions.[Shared].PaperSize.PaperA4

' Your report viewer
rpt.SetDataSource(dataSetObj.Tables(0))
Me.CrystalReportViewer1.ReportSource = rpt
Me.CrystalReportViewer1.Zoom(7)
Me.CrystalReportViewer1.Show()





Note: 1- I have create a blank report named bbb
2- I tested the dataSet and it was filled i.e. I was able to print its rows

 

[lingsn]

Please define the database to use in crystal report, and run again after you finish designing the crystal report. You will get "Invalid table name" if you didn't define the database to be use in crystal report.

 

[bghanim]

How?

Please how? and what should I do with the blank report.
I need to every thing be in code
I hate wizards



Regards,
Baha

 

[lingsn]

There are few ways to achieve this. This is one of the example

http://www.codeproject.com/vb/net/NoDBReport.asp

 

[bghanim]

Hello Mr. lingsn,
This is not what Im looking for, as I explained previously, I need to do that by code i.e. I need to link the report with a dynamic dataSet as I did in my code.

The problem is always an error thrown to me which is "Invalid table number"

Please I need to solve this big problem, any advice will be appreciated.

Thanx in advance,
Baha

 

[DavidT_macktool]

Try this:

dim tblTest As DataTable
tblTest = dataSetObj.Tables("cl_doctors") ' Or dataSetObj.Tables(0)
rpt.SetDataSource(tblTest)

If this code errors on the 2nd line there is a problem with the dataset - probably not defined or filled correctly.

Next step would be to set up a grid and bind it to your dataset to see if you have collected any records.

 

[lingsn]

Well David, I think he will get the same error "Invalid table number". You can simulate this by creating an empty crystal report, and use the code that you had post, you will get the same error. To get rid of this, you have to set the database to be use in the crystal report. I had stated in post #6, you have to define the database to use in the crystal report.

 

[bghanim]

I tried what you have stated in the previous posts, and nothing is working well, I finally solve my problem by creating a stored procedure that takes some parameters and return a dataset. in the vb.net I create a blank report and define the connection to it to include my stored procedure.

 

[lingsn]

I tried what you have stated in the previous posts, and nothing is working well, I finally solve my problem by creating a stored procedure that takes some parameters and return a dataset. in the vb.net I create a blank report and define the connection to it to include my stored procedure.

Where you define the connection? Is it inside crystal report database as I stated in post #6? Sorry if it didn't give you any sense.

Cheers.

 

[bghanim]

I define the connection by:
right click on the crystal rep.
point to database
then
Add/Remove databases
then
expand the OLE DB
to choose the SQL Server 2000 driver and the database name
after that,
I choose the stored procedure from the databse


Hope I answered your question

Regards
Baha

 

[lingsn]

So now we will understand that if none of the databases (connection) is used and we try to set the datasource, it will cause the error "Invalid table number".