468,491 Members | 2,024 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,491 developers. It's quick & easy.

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
Nov 20 '05 #1
8 4023
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
Nov 20 '05 #2
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
Nov 20 '05 #3
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
///
Nov 20 '05 #4
JC
Cor, could it be that my datatable is not populated with the dataset??
Nov 20 '05 #5
JC

That you can test with by instance

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

Cor
Nov 20 '05 #6
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
Nov 20 '05 #7
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
Nov 20 '05 #8
Hi JC,

Do you mean something as?

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

Cor
Nov 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Dan Koster | last post: by
3 posts views Thread by C. Hughes | last post: by
2 posts views Thread by Abhishek Srivastava | last post: by
3 posts views Thread by MDB | last post: by
1 post views Thread by Michel Moreno | last post: by
6 posts views Thread by Fred | last post: by
1 post views Thread by sharmaajay | last post: by
5 posts views Thread by sql_er | last post: by
2 posts views Thread by Peter Savas | last post: by
2 posts views Thread by Steven Cheng [MSFT] | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by theflame83 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.