I have two datasets called ds and ds1 that I want to merge into a grid. I get data from the first dataset to display in the grid but the second dataset where I want to display a count I don't get any data from it. I am trying a ds.Merge(ds1). here is the code i have for the sub. What am I doing incorrectly? I need some assistance please
Public Sub LoadSessionDateGrid(ByVal iSchool As Integer, ByVal iProgram As Integer)
2. Try
3. Me.Label1.Text = iSchool
4. Me.Label2.Text = iProgram
5. Dim ds As New DataSet()
6. ds = New DataSet
7. 'If Me.ddType.SelectedValue = "Status" Then
8. strsql = "select session_id as SessionId,school_id as SchoolId,program_id as ProgramId,convert(nvarchar(10),session_start_date,101) as SessionstartDate, session_class_size as SessionClassSize from dbo.cnSchoolProgramSessions with (nolock) where record_status = 1 and school_id = '" & iSchool & "' and program_id = '" & iProgram & "'"
9.
10. onyxConnection.Open()
11.
12. Dim da As SqlDataAdapter = New SqlDataAdapter(strsql, onyxConnection)
13. da.Fill(ds)
14. 'Bind DataGrid from DataView
15. 'strsql = "select z.iIndividualId,z.vchFirstName,z.vchLastName,z.vchEmailAddress,z.vchAddress1,z.vchCity,z.chRegionCode,dbo.cnfnFormatPostCode(z.vchPostCode) as PostCode,dbo.cnfnFormatUSPhone(z.vchPhoneNumber) as PhoneNumber,convert(nvarchar(10),cpd.dtDate1,101) as SessionstartDate from individual z with (nolock) inner join customerProduct cp with (nolock) on z.iIndividualId = cp.iOwnerId and z.tiRecordStatus = cp.tiRecordStatus and z.iSiteId = cp.iSiteId inner join customerProductDetail cpd with (nolock) on cp.iProductId = cpd.iProductId and cp.iSiteId = cpd.iSiteId and cp.tiRecordStatus = cpd.tiRecordStatus where z.tiRecordStatus = 1 and z.iSiteId = 1 and cpd.dtDate1 is not null and convert(nvarchar(10),cpd.dtDate1,101) = '" & sSessionDate & "' order by cpd.dtDate1"
16. da.Dispose()
17. 'strSql = "select contact.*, Convert(varchar(10),contacthistory.logdate,110) as logdate, contacthistory.note, contactstatus.statusdesc, contactcategory.categorytext from contact join contacthistory on contact.contactid = contacthistory.contactid join contactstatus on contact.statusid = contactstatus.statusid join contactcategory on contact.categoryid =contactcategory.categoryid where contactoruserid = '" & userid & "' order by newmsg desc,contacthistory.contactid"
18. Dim dreader As SqlClient.SqlDataReader
19. Dim sqlcmd As SqlCommand
20. sqlcmd = New SqlCommand(strsql, onyxConnection)
21.
22. dreader = sqlcmd.ExecuteReader()
23.
24. dreader.Read()
25.
26. While dreader.Read()
27. Me.Label3.Text = dreader.Item(3).ToString
28. End While
29. Dim ds1 As New DataSet()
30. ds1 = New DataSet
31. strsql = "select count(1) as count from individual z with (nolock) inner join customerProduct cp with (nolock) on z.iIndividualId = cp.iOwnerId And z.tiRecordStatus = cp.tiRecordStatus and z.iSiteId = cp.iSiteId inner join customerProductDetail cpd with (nolock) on cp.iProductId = cpd.iProductId and cp.iSiteId = cpd.iSiteId and cp.tiRecordStatus = cpd.tiRecordStatus inner join cnSchoolProgramSessions cn with (nolock) on cpd.dtDate1 = cn.session_start_date where z.tiRecordStatus = 1 and z.iSiteId = 1 and cpd.dtDate1 is not null and convert(nvarchar(10), cpd.dtDate1,101) = '" & Me.Label3.Text & "' group by cpd.dtDate1 order by cpd.dtDate1"
32. da = New SqlDataAdapter(strsql, onyxConnection)
33. 'load second table
34. dreader.Close()
35. da.Fill(ds1)
36. da.Dispose()
37. ds.Merge(ds1)
38. Me.SessionDateGrid.DataSource = ds.Tables(0)
39. Me.SessionDateGrid.DataBind()
40.
41. Catch ex As Exception
42. Dim a, b
43. a = ex.ToString
44. b = a.ToString
45.
46. Finally
47. onyxConnection.Close()
48. End Try
49. End Sub