How to get the max value???

  • Thread starter Thread starter Gene
  • Start date Start date
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!!!
 
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.
 
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...
 
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
 
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
 
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
 
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...
 
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...
 
thx Cor,
it seems to be a good suggestion to use dataview,
but I have never use it,
I'll try it, THX!!!
 
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
 
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:
Back
Top