We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

select max SQL

mSchmidt
mSchmidt asked
on
Medium Priority
667 Views
Last Modified: 2011-10-03
           Dim cmd As New Data.SqlServerCe.SqlCeCommand
            cmd.CommandText = "SELECT MAX(nr_spray) from spray_db"
            cmd.Connection = form1.ssceconn
            Dim rdr As Data.SqlServerCe.SqlCeDataReader
            rdr = cmd.ExecuteReader
            rdr.Read()
            Primary_row_nr = rdr.Item("nr_spray").ToString

i am trying to select the highest row in that, and getting out, what the nr was, but it says IndexOutOfRangeException.
what is wrong?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Hi mSchmidt,

Try:

SELECT TOP 1 nr_spray FROM spray_db GROUP BY nr_spray ORDER BY nr_spray DESC

Regards,

Patrick

Author

Commented:
hmm now there is a passing error whit the execute reader
anyideas?
Patrick, you  should not need the Group By clause in that string:

SELECT TOP 1 nr_spray FROM spray_db ORDER BY nr_spray DESC

Leon

Author

Commented:
hmm leon, still says the same thing

any other ideas??
Are you trying your SQL in Query Analyzer to see if it returns what you think it should before running the SQL in your VB program?
Hmm, you are using  SQL Server Mobile which may not support TOP or MAX. Try this:

SELECT nr_spray FROM spray_db ORDER BY nr_spray DESC

This will of course return all of them but you could just use the first one.
Well it does support  Max at least:

http://msdn2.microsoft.com/en-us/library/ms174438.aspx

Leon

Author

Commented:
leon the link help me, but
when i write this in the program says ""Primary_row_nr = rdr.Item("nr_spray").ToString"" that nr_spray is out of IndexOutOfRange
cmd.CommandText = "SELECT max (nr_spray) FROM spray_db"

but if i write this in the query analyzer
SELECT max (nr_spray) FROM spray_db
it tells me what i want, the number

so do you have any idea why?

According to http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.aspx try this:

            Dim cmd As New Data.SqlServerCe.SqlCeCommand
            cmd.CommandText = "SELECT MAX(nr_spray) from spray_db"
            cmd.Connection = form1.ssceconn
            Dim rdr As Data.SqlServerCe.SqlCeDataReader
            rdr = cmd.ExecuteReader
            rdr.Read()
            Dim Primary_row_nr As String = rdr.GetString(0)

Leon

Commented:
instead of ExecuteReader you should use ExecuteScalar
Commented:
I think the error is being thrown in this line:
Primary_row_nr = rdr.Item("nr_spray").ToString

This is because the column is not called nr_spray as you are accessing MAX(nr_spray)
You could also alias the column e.g.
"SELECT MAX(nr_spray) nr_spray from spray_db"

Then :
Primary_row_nr = rdr.Item("nr_spray").ToString
will work

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
hmm it says InvalidCastException about this line

Dim Primary_row_nr As String = rdr.GetString(0)

Author

Commented:
JR2003 your way worked, thx alot
Wait a minute the value you are returning is not a string so try:

Dim Primary_row_nr As Integer = rdr.GetInt32(0)

Leon

Author

Commented:
okey i will try that
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.