Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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
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
Edited 21-Jan-13 6:29am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
Navas Khanj at 24-Jan-13 16:02pm
   
Thanks

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 448
1 Sergey Alexandrovich Kryukov 425
2 Abhinav S 408
3 sanket saxena 285
4 Tadit Dash 160
0 Sergey Alexandrovich Kryukov 8,482
1 OriginalGriff 4,830
2 Peter Leow 3,794
3 Maciej Los 3,515
4 Er. Puneet Goel 3,107


Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 21 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid