join dataset into gridview

napii21

Member
Joined
Oct 27, 2011
Messages
18
Programming Experience
3-5
hi all,

i have 2 datasets from the results of 2 queries.

1st query :

Dim strSQL As String = "SELECT TblAuditTrail.TransID, TblTrans.TransItem AS summary, COUNT(TblAuditTrail.TransID) AS total FROM TblAuditTrail INNER JOIN TblTrans ON TblAuditTrail.TransID = TblTrans.TransID WHERE (TblAuditTrail.DateCreated BETWEEN '2012-10-11 00:00:00.000' AND '2012-10-18 00:00:00.000')GROUP BY TblAuditTrail.TransID, TblTrans.TransItem"


Dim sqlcmd As New SqlCommand(strSQL, sqlConn)
Dim da As New SqlDataAdapter(sqlcmd)
Dim ds As New DataSet()
da.Fill(ds, "total")

result :

ID | summary | total
-----------------------
1 a 88
2 b 5
3 c 2
4 d 4
5 e 2
6 f 6
7 g 43
8 h 61



2nd query:

Dim strSQL1 As String = "SELECT TblAuditTrail.TransID, TblTrans.TransItem AS summary, COUNT(TblAuditTrail.TransID) AS totalDate1 FROM TblAuditTrail INNER JOIN TblTrans ON TblAuditTrail.TransID = TblTrans.TransID WHERE (TblAuditTrail.DateCreated BETWEEN '2012-10-11 00:00:00.000' AND '2012-10-12 00:00:00.000') GROUP BY TblAuditTrail.TransID, TblTrans.TransItem"


Dim sqlcmd1 As New SqlCommand(strSQL1, sqlConn)
Dim da1 As New SqlDataAdapter(sqlcmd1)
'Dim ds As New DataSet()
da.Fill(ds, "date1")



result :

ID | summary | total
-----------------------
2 b 5
3 c 2
6 f 6
8 h 61

the question is how can i combine these datasets into 1 gridview?
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
Hi,

Since the structure of the two queries is the same you just need to fill the same DataTable with the results of both queries. Doing it this way also means that you do not need a DataSet. i.e:-

VB.NET:
Dim sqlcmd As New SqlCommand(strSQL, sqlConn)
Dim da As New SqlDataAdapter(sqlcmd)
Dim dt As New DataTable()
da.Fill(dt)
 
Dim sqlcmd1 As New SqlCommand(strSQL1, sqlConn)
Dim da1 As New SqlDataAdapter(sqlcmd1)
da1.Fill(dt)

(note:- you had an error in your last fill statement since you called da.fill instead of da1.fill. This is why you must always use descriptive names for your variables instead of the likes of da1, da2 etc etc...)

Now all you need to do is set the DataSource of the DataGridView to the one DataTable.

Hope that helps.

Cheers,

Ian
 

napii21

Member
Joined
Oct 27, 2011
Messages
18
Programming Experience
3-5
thanks IanRyder,

i tried your code and this is the result:
1.jpg


but i want the result look like this:
2.jpg

do you have any idea how i can do that?
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
Hi,

There are two ways that I can see to do what you want but, to begin with, you need to define what the relationship is between the two queries that you have posted. Based on the example results in Post No.1 I cannot see an obvious relationship but based on your desired result there has to be one somewhere. Once done, you could do either of the following:-

1) Create the two queries that you are using in your Database as Views and then create a third View to join the first two views together via your relationship and then return a single table with the joined information to VS for the DataGridView.

2) Do the same sort of thing in VS by creating two Tables within a DataSet and then add a relationship between the two tables. You can then use a FOR loop, or LINQ, to iterate the Parent rows, get the children, and then build a third composite table containing the joined information. You would then bind this third table to the DataGridView.

Hope that helps.

Cheers,

Ian
 

napii21

Member
Joined
Oct 27, 2011
Messages
18
Programming Experience
3-5
thanks Ian,

for your 1st suggestion, i know it is possible. but there are many steps to do and i want to decrease load time. maybe i will take the 1st suggestion if there are no other way.
so i am considering to use linq and i tried a few code. i have post my code using linq here.

i hope you can help me.
thanks again Ian.
 
Top Bottom