[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-08
6
Medium Priority
?
555 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()
0
Comment
Question by:BozM
6 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20036532
SELECT Max(<column_name>) as Expr1 FROM tblContacts
0
 

Author Comment

by:BozM
ID: 20036564
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
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20036582
if mycontactreader.read
        strMaxContactID = myContactReader.Item("Expr1")
else
    'no records read
endif
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 8

Expert Comment

by:Bradley Haynes
ID: 20036659
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
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 2000 total points
ID: 20036990
while mycontactreader.read()
        strMaxContactID = myContactReader.Item(0)
end while
0
 

Author Comment

by:BozM
ID: 20064154
Apologies for delay in getting back to you. Thanks for that. I
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

868 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