2 Table JOIN and Show To DATAGRIDVIEW..

Question

1.00/5 (2 votes)

See more:

I have Two Table

FeesMaster

REGNO		CLASS		SECTION	        NAME    FEETYPE  FEEAMOUNT
A600      	I         	A         	AAAA	Team-1	750
A700      	I         	A         	DDDDD	Team-1	750
A900      	I         	A         	EEEEE	Team-1	750
A600      	I         	A         	BBBB	Jan	650
A700      	I         	A         	DSDSDSDSDJan	650
A900      	I         	A         	EEEEE	Jan	650

Fees_Transaction	

REGNO           NAME    CLASS          SECTION         AMOUNT_PAID FEETYPE				
A600           	AAAA	I              	A              	150	Team-1         
A600           	AAAA	I              	A              	500	Team-1         
A700           	DDDDD	I              	A              	500	Team-1         
A700           	DDDDD	I              	A              	250	Team-1         
A700           	DDDDD	I              	A              	300	Jan            
A700           	DDDDD	I              	A              	350	Jan            
A900           	EEEEE	I              	A              	200	Jan            
A900           	EEEEE	I              	A              	750	Team-1

How can I JOIN above the Table (Using Condition Regno and Class is Matching) I want Diaply to DATAGRIDVIEW..Same Time Amount_Paid and FeeAmount Calculate and Display To DataGridView TOTAL AMOUNT..
I Want Like This..

Reg no	ST_Name	Amount_Paid	FeeAmount	FeeType	Class	Section
						
A600           	AAAA	650	1400	TOTAL	I	A
A700           	DDDDD  1400	1400	TOTAL	I	A
A900           	EEEEE	950	1400	TOTAL	I	A

I am Using Code This

VB

Dim strSQL4 As String
    con.Open()

    strSQL4 = "Select Fees_Transaction.Regno, Fees_Transaction.ST_Name, SUM(Fees_Transaction.Amount_Paid) as Amount_Paid, Fees_Transaction.Class, Fees_Transaction.Section, SUM(FeesMaster.FeeAmount) as FeeAmount  FROM Fees_Transaction LEFT OUTER JOIN FeesMaster ON Fees_Transaction.Regno=FeesMaster.Regno AND and Fees_Transaction.FeeType=FeesMaster.FeeType WHERE Fees_Transaction.class='" & cboClass.Text & "'  and FeesMaster.class='" & cboClass.Text & "' GROUP By Fees_Transaction.Regno, Fees_Transaction.ST_Name, Fees_Transaction.Class, Fees_Transaction.Section"
    Dim DaAp4 As New SqlDataAdapter(strSQL4, con)
    Dim DSet4 As New DataTable
    DaAp4.Fill(DSet4)
    DataGridView2.DataSource = DSet4.DefaultView
    con.Close()

Now DataGRIDVIEW SHOW Like THIS.. FEEAMOUNT CALCULATE SOME WRONG... PLEASE TELL ME Where I DID WORNG CODE..Why FeeAmount Come like this..
Reg no ST_Name Amount_Paid FeeAmount FeeType Class Section

A600 AAAA 650 1500 TOTAL I A
A700 DDDDD 1400 2800 TOTAL I A
A900 EEEEE 950 1400 TOTAL I A

Posted 21-Jan-13 6:18am

Navas Khanj

Updated 21-Jan-13 6:29am

Tharaka MTR

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Grasshopper.iics · Accepted Answer · 2013-01-21T12:13:00

What you can do in the cases of compound queries is create a view in sql and fetch the data from view.

So you cat create a view like this:

create view V1 as (Select Fees_Transaction.Regno, Fees_Transaction.ST_Name, SUM(Fees_Transaction.Amount_Paid) as Amount_Paid, Fees_Transaction.Class, Fees_Transaction.Section, SUM(FeesMaster.FeeAmount) as FeeAmount  FROM Fees_Transaction LEFT OUTER JOIN FeesMaster ON (Fees_Transaction.Regno=FeesMaster.Regno AND and Fees_Transaction.FeeType=FeesMaster.FeeType) GROUP By (Fees_Transaction.Regno, Fees_Transaction.ST_Name, Fees_Transaction.Class, Fees_Transaction.Section))

test the result with a query. say

select * from V1.

It is easy to debug the problems in view rather than debugging them through code. Once your data is shown appropriately, select from view with a condition.