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)
Try
Me.Label1.Text = iSchool
Me.Label2.Text = iProgram
Dim ds As New DataSet()
ds = New DataSet
'If Me.ddType.SelectedValue = "Status" Then
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 & "'"
onyxConnection.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter(strsql, onyxConnection)
da.Fill(ds)
'Bind DataGrid from DataView
'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"
da.Dispose()
'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"
Dim dreader As SqlClient.SqlDataReader
Dim sqlcmd As SqlCommand
sqlcmd = New SqlCommand(strsql, onyxConnection)
dreader = sqlcmd.ExecuteReader()
dreader.Read()
While dreader.Read()
Me.Label3.Text = dreader.Item(3).ToString
End While
Dim ds1 As New DataSet()
ds1 = New DataSet
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"
da = New SqlDataAdapter(strsql, onyxConnection)
'load second table
dreader.Close()
da.Fill(ds1)
da.Dispose()
ds.Merge(ds1)
Me.SessionDateGrid.DataSource = ds.Tables(0)
Me.SessionDateGrid.DataBind()
Catch ex As Exception
Dim a, b
a = ex.ToString
b = a.ToString
Finally
onyxConnection.Close()
End Try
End Sub
Well it’s more than two tables.I have two sql queries and they are different datasets:ds and ds1. I am trying to merge the results of each dataset into one grid.
I’ve never used .net but am I understanding your question ok:
You have your tables and you have your queries. You want to combine the result sets from the queries into one for processing by .net as one overall result set (data set). Can you show:
A CREATE TABLE for the tables involved
The current queries you have that you want to combine the result sets for
A short explaination of what info (the combined results(data) set) your trying to get from the tables involved
The queries are shown in the first post in the thread. I have tested both in SQL and both have results but not for the grid. I can get results from the first query to show in the grid but not the second query. Each query is going to a different dataset as I mentioned before. That is where I believe I am having trouble merging the two together I have looked online and have not gotten much of anything except the code I posted in this thread and that doesn’t seem to work. I am trying to see if anyone can help me with my code to see what it is I am doing wrong.
If I’m reading this correct (I’ve not worked with .net or MS SQL before), your second query that your sending to the MsSQL server:
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
What errors if any are being returned by the MsSQL server? It may be syntax specific to MsSQL but the part of the query:
Count(1) gives me a count of of 26 record. Using count(*) if I remember either gives me an error or no results. I don’t have SQL in front of me at the moment.
If you want to merge to DataSets/DataTables, they will need to have the same columns of data. You cannot insert 2 values into table of 1 column for eg. You can put 1 value into 3 3 column dataset tho, but you would have to do it manually and add empty strings for the remainder columns