Question Displaying data coming from two tables on one crystal report

[TheOnlyErs]

Good day to all.

I am new here. I decided to join this forum to get some help with a project I'm working on using VB.net

Here's my problem.

I'm trying to display two tables on one crystal report using this code below.

Dim MyCommand As New SqlCommand()
Dim DataAdapter1, DataAdapter2, DataAdapter3 As New SqlDataAdapter
Dim myDS As New DataSet1()


Dim rpt As New StudentWithTheMostViolationsReport()


DataAdapter1 = New SqlDataAdapter("SELECT vd.StudID, info.studLname, info.studFname, info.studMname from tblViolationDetails As vd FULL JOIN tblStudentInfo as info on vd.ViolationDetailsID = info.StudID", con)



DataAdapter1.Fill(myDS, "tblViolationDetails")
DataAdapter1.Fill(myDS, "tblStudentInfo")


rpt.SetDataSource(myDS)
frmReportViewer.ReportViewer.ReportSource = rpt


frmReportViewer.ShowDialog()

The problem I have with this part of the code can be seen by the image I attached with this post. If you take a loot at the image below. The information being displayed on the report are being repeated. Moreover, the student ID 2 should be along side "Raven Talla" and not along side "Erwin Morillo".

What I want to do is to display the StudentID which comes from one table and the full name of the student coming from another table.

A helpful advice is greatly appreciated. Thank you.

 

[IanRyder]

Hi and welcome to the Forum,

That query does not sound right to me based on what you have described. I suspect that you should be creating an INNER JOIN, or at least a LEFT OUTER JOIN, using the StudID field. i.e:-

INNER JOIN tblStudentInfo as info on vd.StudID = info.StudID

Hope that helps.

Cheers,

Ian

 

[TheOnlyErs]

Hi and welcome to the Forum,

That query does not sound right to me based on what you have described. I suspect that you should be creating an INNER JOIN, or at least a LEFT OUTER JOIN, using the StudID field. i.e:-

INNER JOIN tblStudentInfo as info on vd.StudID = info.StudID

Hope that helps.

Cheers,

Ian

Thanks for your quick response Ian. I appreciate it.

I've already tried using the inner join but I'm gonna give the LEFT OUTER JOIN a try and see how it works.

Just to clarify, what exactly is wrong with my query? because I did try that query on the query editor of SQL server and it did display the data that I want it to display but whenever I try to run it on a crystal report, how the data are being displayed differs.

 

[IanRyder]

Well, there are two things that jumped out for me:-

1) The Full Join will return records from both tables. Have a look here:-

http://www.w3schools.com/sql/sql_join_full.asp

2) You are Joining the two tables together using the field ViolationDetailsID from the tblViolationDetails table and the field StudID from the tblStudentInfo table. This does not sound right since you would join two tables based on a related field and the fields ViolationDetailsID and StudID do not sound related to me.

Cheers,

Ian

 

[TheOnlyErs]

Well, there are two things that jumped out for me:-

1) The Full Join will return records from both tables. Have a look here:-

http://www.w3schools.com/sql/sql_join_full.asp

2) You are Joining the two tables together using the field ViolationDetailsID from the tblViolationDetails table and the field StudID from the tblStudentInfo table. This does not sound right since you would join two tables based on a related field and the fields ViolationDetailsID and StudID do not sound related to me.

Cheers,

Ian

I got your point there. I did check the tables of my database and you're right indeed. The primary key of tblStudent is StudID and the primary key of the tblViolationDetails is ViolationDetailsID and that's not how they're related.

The two tables are related through the StudID kkey because the StudID functions as a foreign key in tblViolationDetails. However, when I changed the ViolationDetailsID to StudID, the same output on the report appeared.

Someone told me that it has something to do with the way I was filling the dataset but I don't get it.

 

[IanRyder]

Someone told me that it has something to do with the way I was filling the dataset but I don't get it.

Do you know, I never even stopped to look at what you were doing with your DataAdapter.

You do realise don't you that you are NOT creating the tblViolationDetails table and the tblStudentInfo table in the DataSet? What you are creating is two EXACT SAME tables of your Defined Select Query which is a combination of the two tables in question. This is because you call the Fill method twice using the same DataAdapter. You then pass this DataSet as the DataSource to the Report and without testing I am not sure how the report would act here.

Hope that helps.

Cheers,

Ian