Link to home
Create AccountLog in
Avatar of BozM
BozM

asked on

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()
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

SELECT Max(<column_name>) as Expr1 FROM tblContacts
Avatar of BozM
BozM

ASKER

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)
if mycontactreader.read
        strMaxContactID = myContactReader.Item("Expr1")
else
    'no records read
endif
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()
ASKER CERTIFIED SOLUTION
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of BozM

ASKER

Apologies for delay in getting back to you. Thanks for that. I