Solved

select max SQL

Posted on 2006-10-26
15
604 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now