Datasets

[Former Member]

I need to gather information from 5 different tables.

I am writing to the datasets, but at runtime it does not show any data (an error).

I know the statement is correct as I have done it in a SQL browser.

I am using MySQL to call it and using a dataset to design the crystal report.

I did try calling it within one dataset, but there are more records in some tables than others so it dosen't show true values in the data, this is why I am using 1 tMySQL able to 1 dataset table.

CODE:

Dim con As MySqlConnection

Dim myReport As New ReportDocument

Dim myData, myDataClient As New DataSet

Dim cmdMySQLData, cmdClient As New MySqlCommand

Dim daData, daClient As New MySqlDataAdapter

Dim proRef As String

Dim cliRef As String

proRef = "PRA50377"

cliRef = "CLA51291"

con = New MySqlConnection()

'Connection String

con.ConnectionString = "Location"

Try

con.Open()

cmdMySQLData.CommandText = "select ref as PROPERTY_ref, address_line1 as PRO_address_line1, address_line2 as PRO_address_line2, address_line3 AS PRO_address_line3, address_line4 AS PRO_address_line4, town as PRO_town, county AS PRO_county, fk_client_sell_ref, postcode AS PRO_postcode FROM tblproperty WHERE ref = '" & proRef & "' "

cmdMySQLData.Connection = con

daData.SelectCommand = cmdMySQLData

daData.Fill(myData)

cmdClient.CommandText = "SELECT ref, contact_name, no_name, address_line1, address_line2, address_line3, address_line4, town, county, postcode, phone1, phone2, fax, mobile, email FROM tblclient WHERE ref = '" & cliRef & "' "

cmdClient.Connection = con

daClient.SelectCommand = cmdClient

daClient.Fill(myDataClient)

myReport.Load("Location")

myReport.SetDataSource(myData)

myReport.SetDataSource(myDataClient)

myReport.Database.Tables(0).SetDataSource(myData.Tables(0))

myReport.Database.Tables(1).SetDataSource(myDataClient.Tables(0))

CrystalReportViewer1.ReportSource = myReport '

Catch myerror As MySqlException

MsgBox(myerror.Message)

End Try

Please help

[Former Member]

Visual Studios 2010 and im sure CR 11

thanks for your help debi !!!

[Former Member]

This may be as far as I can help you. I do all my report design through CR, selecting and linking the tables in the database expert, and selecting data through the select data expert (well almost all-I dabble a bit in SQL statements).

If you know the relationship between your tables, you should be able to modify your links in your DIM statement.

If not, try putting it together in crystal, and then look at the SQL statement (under DATABASE)to see how the links are made in SQL

[Former Member]

Please post in .net forum

[Former Member]

Ok - I get you, but rather than put it into one statement I could call them separately (datasets) - i am unsure of how the correct coding to display the strings/datasets ?

[Former Member]

still unsure sorry !

[Former Member]

When you have a 'one-to-many, or none' relationship between tables, you should use an outer join. An outter join will give you all records from the first table and the records from second table where a match is found. And if there are multiple records in the second table, the records from the first table will be repeated.

As I said, I am not up on DIM statements, and I do not know the relationships between your tables, so I do not know where you need to substitute the left outer join for the = join.

Edited by: Debi Herbert on Sep 21, 2011 10:26 AM

[Former Member]

Are you doing this in Crystal Reports? What version?

[Former Member]

I am not well versed with doing formulas like this, but I do know that when you have tables that do not have matching records you need an outer link to get all records from table #1 and related records from table #2 (& 3 & 4 & etc.). Equal links will not work

Debi