Querying a dataset

[JC]

I have created a data adapter (CheckVacationHrsDataAdapter1) and generated a dataset (CheckHrsVacDataSet1) in the form designer. What I would like to do is query this dataset, find the sum of TotalVacationHrs per EmployeeID, and put this sum in a text box. How do I load my dataset at runtime in order to query it? Here is my query...please help

"SELECT SUM(TotalVacationHrs
FROM PYCheckHistor
WHERE (Employee = '" & EmployeeID & "')

My data adapter is mapped to the table PYCheckHistory

[Cor Ligthert]

Hi JC,

I think this is what you are looking for

http://msdn.microsoft.com/library/de...mputetopic.asp

I hope it was the answer?

Cor

[Cor Ligthert]

Hi JC,

VacHrsThisYr.Text =
CheckHrsVacDataSet1.Tables("PYCheckHistory").Compu te("Sum(TotalVacationHrs)"
, "Employee = 'ARBUCKLEP'").toString

With other names of course, does work for me

No name problems or case sensitive problems (This kind of statements is case
sensetive)

Cor

[Cor Ligthert]

Hi JC,

I tried it with this sample, can you try it also, the value is 1200

I hope it is with you the same?

Cor
\\\
Dim dt As New DataTable
Dim dc As New DataColumn("OHM")
dc.DataType = GetType(System.Int32)
Dim dd As New DataColumn("OHH")
dd.DataType = GetType(System.Int32)
dt.Columns.Add(dc)
dt.Columns.Add(dd)
For i As Integer = 0 To 11
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = 100
dt.Rows(i)(1) = 1
Next
Me.TextBox1.Text = dt.Compute("Sum(OHM)", _
"OHH = 1").ToString
///

[JC]

Cor, could it be that my datatable is not populated with the dataset??

[Cor Ligthert]

JC

That you can test with by instance

messagebox.show(CheckHrsVacDataSet1.Tables("PYChec kHistory").rows.count.tost
ring)

Cor

[Cor Ligthert]

Hi JC,

Have a look at this,
http://msdn.microsoft.com/library/de...ClassTopic.asp

When you use OleDb just change SQL in OleDb

I hope this helps?

Cor

[Cor Ligthert]

Hi JC,

If you have created a dataadapter using the designer and generated the
dataset with that, than you need only that fill with the right dataadapter
and datasetname (strong one)

I give it with a non strongly typed dataset also, however than your program
becomes fast a mesh. This should be enough when you made all with the
designer.

dap1.Fill(das1) ' the names you where using
VacHrsThisYr.Text = das1.Compute("Sum(TotalVacationHrs)", "Employee =
'ARBUCKLEP'").ToString

However when you first want to try from the outside

Dim cnn1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;
Data Source=J:\TCMW\Database\TCMW.mdb")

Dim dap1 As New OleDb.OleDbDataAdapter("SELECT Employee, CheckDate,
TotalVacationHrs from PYCheckHistory", cnn1)
Dim das1 As New DataSet
dap1.Fill(das1)
MessageBox.ShowRows.Count.ToString)

VacHrsThisYr.Text =(das1.Tables(0).Compute("Sum(TotalVacationHrs)",
"Employee = 'ARBUCKLEP'").ToString

I thought that this should work ( I typed it in here in this message and
mostly I make than typos)

Cor

[Cor Ligthert]

Hi JC,

Do you mean something as?

VacHrsThisYr.Text = myTable.Compute("Sum(TotalVacationHrs)", "((EMPLOYEE =
'" & "EmployeeID') AND (CheckDate >" & " 'DateOfHire'))").ToString

Cor