Returning results of max function from MS access table with VB.net

Hi,
 I have a simple MS access database with a list of contacts - each of which has a sequential increasing
unique contact ID number. When I run a query with the SQL string below, I get a nice table with one column (entitled Expr1) with one row (containing the max value for strContactID) .
I want to take that value and use later in my VB code. However, when I run the following VB.net code I get an error message which says
"NoSystem.InvalidOperationException was unhandled"
  - "No data exists for the row/column."
I'd love some guidance on what I'm doing wrong.
Thanks.



         Dim myOLEDBConnection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
                        "Data Source= C:\Documents and Settings\Administrator\Desktop\Contacts.mdb")

        Dim mySQL As String = "SELECT Max(strContactID) as Expr1 FROM tblContacts"


        myOLEDBConnection.Open()
        Dim myCommand As New OleDbCommand(mySQL, myOLEDBConnection)
        Dim myContactReader As OleDbDataReader = myCommand.ExecuteReader()
        Dim strMaxContactID As String

        strMaxContactID = myContactReader.Item("Expr1")

        myOLEDBConnection.Close()
BozMAsked:
Who is Participating?
 
SQL_SERVER_DBAConnect With a Mentor Commented:
while mycontactreader.read()
        strMaxContactID = myContactReader.Item(0)
end while
0
 
SQL_SERVER_DBACommented:
SELECT Max(<column_name>) as Expr1 FROM tblContacts
0
 
BozMAuthor Commented:
Hi - thanks for your comment.
As I said in my question, the SQL works fine on its own (i.e. when I use it in an Access query) - strContactID is the name of the column. My problem is in the VB.net code ( I think)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
UnifiedISCommented:
if mycontactreader.read
        strMaxContactID = myContactReader.Item("Expr1")
else
    'no records read
endif
0
 
Bradley HaynesCommented:
Use this object to test for return of data. This will handle the 'Exception was unhandled' error.
objconn.open()
objDR = objcmd.executereader()

if objDR.read() then
session("currentlog")=objdr("clientid")
end if


objDR.close()
0
 
BozMAuthor Commented:
Apologies for delay in getting back to you. Thanks for that. I
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.