Solved

select max SQL

Posted on 2006-10-26
15
607 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
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 92

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
Industry Leaders: 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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

740 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