We help IT Professionals succeed at work.

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

BozM
BozM asked
on
579 Views
Last Modified: 2013-11-26
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()
Comment
Watch Question

SELECT Max(<column_name>) as Expr1 FROM tblContacts

Author

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)
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()
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.