[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

select max SQL

Posted on 2006-10-26
15
Medium Priority
?
614 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?
0
Comment
Question by:mSchmidt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 17813925
Hi mSchmidt,

Try:

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

Regards,

Patrick
0
 

Author Comment

by:mSchmidt
ID: 17813979
hmm now there is a passing error whit the execute reader
anyideas?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17814603
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mSchmidt
ID: 17814645
hmm leon, still says the same thing

any other ideas??
0
 
LVL 9

Expert Comment

by:dancebert
ID: 17814756
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?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17814815
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.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17814835
Well it does support  Max at least:

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

Leon
0
 

Author Comment

by:mSchmidt
ID: 17815018
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?

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17815045
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
0
 
LVL 18

Expert Comment

by:JR2003
ID: 17815098
instead of ExecuteReader you should use ExecuteScalar
0
 
LVL 18

Accepted Solution

by:
JR2003 earned 2000 total points
ID: 17815114
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
0
 

Author Comment

by:mSchmidt
ID: 17815160
hmm it says InvalidCastException about this line

Dim Primary_row_nr As String = rdr.GetString(0)
0
 

Author Comment

by:mSchmidt
ID: 17815183
JR2003 your way worked, thx alot
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17815194
Wait a minute the value you are returning is not a string so try:

Dim Primary_row_nr As Integer = rdr.GetInt32(0)

Leon
0
 

Author Comment

by:mSchmidt
ID: 17815334
okey i will try that
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question