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.InvalidOperation
- "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=
"Data Source= C:\Documents and Settings\Administrator\Des
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("Expr
myOLEDBConnection.Close()
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.InvalidOperation
- "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=
"Data Source= C:\Documents and Settings\Administrator\Des
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("Expr
myOLEDBConnection.Close()
SQL_SERVER_DBA
SELECT Max(<column_name>) as Expr1 FROM tblContacts
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)
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("Expr
else
'no records read
endif
strMaxContactID = myContactReader.Item("Expr
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")=objd
end if
objDR.close()
objconn.open()
objDR = objcmd.executereader()
if objDR.read() then
session("currentlog")=objd
end if
objDR.close()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Apologies for delay in getting back to you. Thanks for that. I