• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

select max SQL

           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
mSchmidt
Asked:
mSchmidt
  • 6
  • 5
  • 2
  • +2
1 Solution
 
Patrick MatthewsCommented:
Hi mSchmidt,

Try:

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

Regards,

Patrick
0
 
mSchmidtAuthor Commented:
hmm now there is a passing error whit the execute reader
anyideas?
0
 
leonstrykerCommented:
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
Technology Partners: 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!

 
mSchmidtAuthor Commented:
hmm leon, still says the same thing

any other ideas??
0
 
dancebertCommented:
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
 
leonstrykerCommented:
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
 
leonstrykerCommented:
Well it does support  Max at least:

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

Leon
0
 
mSchmidtAuthor 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?

0
 
leonstrykerCommented:
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
 
JR2003Commented:
instead of ExecuteReader you should use ExecuteScalar
0
 
JR2003Commented:
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
 
mSchmidtAuthor Commented:
hmm it says InvalidCastException about this line

Dim Primary_row_nr As String = rdr.GetString(0)
0
 
mSchmidtAuthor Commented:
JR2003 your way worked, thx alot
0
 
leonstrykerCommented:
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
 
mSchmidtAuthor Commented:
okey i will try that
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now