[RESOLVED] VB.NET app + ReportViewer + MySQL

[makko]

hi

I'm developing an VB.NET app which gets datas from an mysql database . So far so good...
I need to display some basic raports and I' trying to use ReportViewer component from VS
I created an Raport but my problem is how to bind the raport with my mysql table. If I choose Object - MySQL.Data - MySQL.Data.MySQLClient i cant get any fields and rows. In fact I cant set even the connection string

How and where I can set the connection string of the raport to MySQL database

[jmcilhinney]

From what I've read you don't. You get your data into a DataSet, as you would for binding to other controls. You then pass that DataSet to the ReportViewer. That said, I've never actually used the ReportViewer; only read about it some months ago.

[makko]

thx jmcilhinney for the reply ... that's what was in my mind too ... to link a DataSet to the ReportViewer ... but i cant find any methods in ReportViewer to link to a DataSet

[rjv_rnjn]

You can't attach a dataset to report viewer. You attach a report to the viewer and then attach a dataset to the report. I remember doing Report.DataSource = ds.
The current system I'm on doesn't have the environment to give you the code piece but you can do it with VS2005 & CR.

[masfenix]

Me.ReportViewer1.Reset()
Me.ReportViewer1.ProcessingMode =
Me.ReportViewer1.LocalReport.ReportPath = "reports\orderInvoiceReport.rdlc"
Me.ReportViewer1.LocalReport.DataSources.Clear()
Me.ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource("orderInvoice_orders", ordersTableAdapter.GetData(orderid)))

Me.ReportViewer1.RefreshReport()

[makko]

thank you very much masfenix ...

the code you gave me it's good but now it seems that I have another problem ... I cant put in the raport designer an Textbox or an table with the expression set to " = Fields!Name.value" because it gives me the following error :

Error 1 The Value expression for the textbox ‘textbox1’ refers to the field ‘Name’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

[masfenix]

are you sure your datatable has the field "Name"?

[makko]

masf ... of course I double checked the names of the fields ... but it seems that isnt the problem

Anyway ... I got another source and I tried to solve the problem from other angle, so ...

I added to the project a DataSet Item named DataSet1 ... I dragged from toolbox an DataTable object named DataTable1 ... manually inserted the columns from my MySQL table (id,date,name,etc...) into DataTable1 and assigned the right data types for each column.

Now if I go to the report designer I can see my DataSet1 and its fields and I can put them into report ... So far so good ... But I still have to enter some code so if I put the following code :

Dim conn As MySqlConnection
Dim cmd As MySqlCommand
Dim adap As MySqlDataAdapter

conn = New MySqlConnection("Data Source=localhost;Database=mydatabase;User ID=root;")
conn.Open()
cmd = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM Date"

adap = New MySqlDataAdapter()
adap.SelectCommand = cmd
Dim custDB = New DataSet1()
custDB.Clear()
adap.Fill(custDB, "Date")
Dim aaa As DataTable
aaa = custDB.tables(0)

Me.ReportViewer1.ProcessingMode = ProcessingMode.Local
ReportViewer1.LocalReport.ReportPath = "...\Report1.rdlc"
Me.ReportViewer1.LocalReport.DataSources.Clear()
Me.ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("Date", aaa))
Me.ReportViewer1.RefreshReport()

i get the error :

A datasource instance has not been supplied for the datasource DataSet1_DataTable1



if I enter only the next code :

Dim conn As MySqlConnection
Dim cmd As MySqlCommand
Dim adap As MySqlDataAdapter

conn = New MySqlConnection("Data Source=localhost;Database=mydatabase;User ID=root;")
conn.Open()
cmd = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM Date"

adap = New MySqlDataAdapter()
adap.SelectCommand = cmd
Dim custDB = New DataSet1()
custDB.Clear()
adap.Fill(custDB, "Date")

Me.ReportViewer1.RefreshReport()


So to explain better ... I put an table from toolbox into the report and at the table's header are the column names at the table's detail should be the fields values and the table's footer is empty.
So I only get the column names in this version of code ...

I know I'm close to the solution but it seems now I cant see the forest for the trees

Thx guys

[makko]

problem resolved ...

the full article for this problem is at http://www.vbmysql.com/articles/vbne...eating-reports