How to get the max value???

G

Gene

Hi, i'm a new vb user,
may i ask how to get the max value from the followin situation?

Dataset name: dataset11
Table name: Employee
Column name: StaffID

the syntax is???
txtMaxStaffID.text = dataset11.Employee.StaffIDColumn.????????
THX!!!
 
D

Dmitriy Lapshin [C# / .NET MVP]

Hi Gene,

You can possibly use DataTable's Select method, but I am not sure it
supports aggregate functions like MAX(). If it doesn't, you should iterate
on all the rows in the DataTable and find out the maximum value in this
column.

To the best of my knowledge, the DataColumn class itself does not provide
such functionality so there is nothing that could replace the "???????"
placeholder in your example.
 
G

Gene

thx!!!
I'll try on that

Dmitriy Lapshin said:
Hi Gene,

You can possibly use DataTable's Select method, but I am not sure it
supports aggregate functions like MAX(). If it doesn't, you should iterate
on all the rows in the DataTable and find out the maximum value in this
column.

To the best of my knowledge, the DataColumn class itself does not provide
such functionality so there is nothing that could replace the "???????"
placeholder in your example.

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://x-unity.miik.com.ua/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

Gene said:
Hi, i'm a new vb user,
may i ask how to get the max value from the followin situation?

Dataset name: dataset11
Table name: Employee
Column name: StaffID

the syntax is???
txtMaxStaffID.text = dataset11.Employee.StaffIDColumn.????????
THX!!!
Click to expand...
Click to expand...
 
J

Jeremy Cowles

Gene said:
Hi, i'm a new vb user,
may i ask how to get the max value from the followin situation?

Dataset name: dataset11
Table name: Employee
Column name: StaffID

the syntax is???
txtMaxStaffID.text = dataset11.Employee.StaffIDColumn.????????
Click to expand...

This question will be better answered by the ADO.NET group. However, I
*think* you can accomplish this two ways:

1) select an aggregate of the desired column in your actual SQL statement:

SELECT MAX( StaffID ) FROM [Employee]
GROUP BY {add your grouping fields here}

That will return 1 column, with the max values based on your grouping.

2) (not sure if this will work or not)Try using the MAX function in the
select query on the DataSet:

dataset11.Select("SELECT MAX(StaffID) FROM [EMPLOYEE]...")

This is the same as the previous method, but it all executes client side,
wich is much better in terms of server abuse. I think it returns either a
DataRowView, or a DataRow( ). If you are having problems with this method,
check the documentation on the DataSet.Select() method.


HTH,
Jeremy
 
C

Cor

Hi Gene,

I am curious, why do you not just use a dataview?

something as rough written
\\\
dv as new dataview(dataset11.employee)
dv.sort="StaffIdColumn, ASC"
txtMaxStaffId.text=dv(dv.count-1)("StaffID")
///


Cor
 
C

Cor

Hi Jerymy
dataset11.Select("SELECT MAX(StaffID) FROM [EMPLOYEE]...")
Click to expand...

Which version from VB?

:)) I did try something like this this this morning but could not find it

Cor
 
G

Gene

Thx, Jeremy
I have another question is how to do it using method?
I know sql statement,
I know how to use select * from table,
but how to use other functions like MAX in the sql statement using vb.net.
do you mind tell me more on this?


Jeremy Cowles said:
Gene said:
Hi, i'm a new vb user,
may i ask how to get the max value from the followin situation?

Dataset name: dataset11
Table name: Employee
Column name: StaffID

the syntax is???
txtMaxStaffID.text = dataset11.Employee.StaffIDColumn.????????
Click to expand...

This question will be better answered by the ADO.NET group. However, I
*think* you can accomplish this two ways:

1) select an aggregate of the desired column in your actual SQL statement:

SELECT MAX( StaffID ) FROM [Employee]
GROUP BY {add your grouping fields here}

That will return 1 column, with the max values based on your grouping.

2) (not sure if this will work or not)Try using the MAX function in the
select query on the DataSet:

dataset11.Select("SELECT MAX(StaffID) FROM [EMPLOYEE]...")

This is the same as the previous method, but it all executes client side,
wich is much better in terms of server abuse. I think it returns either a
DataRowView, or a DataRow( ). If you are having problems with this method,
check the documentation on the DataSet.Select() method.


HTH,
Jeremy
Click to expand...
 
G

Gene

it's method "1"

Gene said:
Thx, Jeremy
I have another question is how to do it using method?
I know sql statement,
I know how to use select * from table,
but how to use other functions like MAX in the sql statement using vb.net.
do you mind tell me more on this?


Jeremy Cowles said:
Gene said:
Hi, i'm a new vb user,
may i ask how to get the max value from the followin situation?

Dataset name: dataset11
Table name: Employee
Column name: StaffID

the syntax is???
txtMaxStaffID.text = dataset11.Employee.StaffIDColumn.????????
Click to expand...

This question will be better answered by the ADO.NET group. However, I
*think* you can accomplish this two ways:

1) select an aggregate of the desired column in your actual SQL statement:

SELECT MAX( StaffID ) FROM [Employee]
GROUP BY {add your grouping fields here}

That will return 1 column, with the max values based on your grouping.

2) (not sure if this will work or not)Try using the MAX function in the
select query on the DataSet:

dataset11.Select("SELECT MAX(StaffID) FROM [EMPLOYEE]...")

This is the same as the previous method, but it all executes client side,
wich is much better in terms of server abuse. I think it returns either a
DataRowView, or a DataRow( ). If you are having problems with this method,
check the documentation on the DataSet.Select() method.


HTH,
Jeremy
Click to expand...
Click to expand...
 
G

Gene

thx Cor,
it seems to be a good suggestion to use dataview,
but I have never use it,
I'll try it, THX!!!
 
J

Jay B. Harlow [MVP - Outlook]

Gene,
In addition to the other suggestions I find the easiest way is to use the
DataTable.Compute function.

txtMaxStaffID.text = dataset11.Employee.Compute("Max(StaffID)", Nothing)

The second parameter is a filter, incase you want to limit the rows that are
applied to the aggregate function.

David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press
covers the DataTable.Compute function along with every thing else in
ADO.NET!

Hope this helps
Jay
 
C

cfx9850gb

Joined
Jun 3, 2008
Messages
1
Reaction score
0
Gene said:
Hi, i'm a new vb user,
may i ask how to get the max value from the followin situation?

Dataset name: dataset11
Table name: Employee
Column name: StaffID

the syntax is???
txtMaxStaffID.text = dataset11.Employee.StaffIDColumn.????????
THX!!!
Click to expand...

if about dataTable this i wish help you
Code: 
[size=2][color=#0000ff]
private[/color][/size][size=2] [/size][size=2][color=#0000ff]int[/color][/size][size=2] MaxvalueRecord()

{

[/size][size=2][color=#0000ff]int[/color][/size][size=2] maxvalue=0;

[/size][size=2][color=#0000ff]for[/color][/size][size=2]([/size][size=2][color=#0000ff]int[/color][/size][size=2] i=0;i<dataTableRecord.Rows.Count;i++)

{

[/size][size=2][color=#008000]//if have max value 

[/color][/size][size=2][/size][size=2][color=#0000ff]if[/color][/size][size=2](maxvalue<[/size][size=2][color=#0000ff]int[/color][/size][size=2].Parse(dataTableRecord.Rows[i]["ID"].ToString()))

{

maxvalue=[/size][size=2][color=#0000ff]int[/color][/size][size=2].Parse(dataTableRecord.Rows[i]["ID"].ToString())+1;

}

}

[/size][size=2][color=#0000ff]return[/color][/size][size=2] maxvalue;

}

[/size]
 
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CheckedListBox and TabControl problem 3
Displaying n:m relation without showing the in-between table. 3
Binding ComboBox 3
Retrieving info out of a MDB 7
DataGrid Trouble / Help 2
Datagrid with checkbox and SQL 2
Databinding 1
Databind a PictureBox to a SQL Server... 17

Top