how to populate mysql union query by using dataset in VB.Net?

Hi all, regarding to the subject. I'm using MySQL and VB.NET. I was unable to output the value as it always return with empty value but i can get the value by using mysql console. I'm using the below query and codes:

Dim strSQL as string
Dim objRS As DataSet
Dim objConn As MySqlConnection
objDataAdapter = New MySqlDataAdapter

strSQL = "SELECT MAX(memberID) as max_member_id FROM ((SELECT memberID FROM A) UNION (SELECT memberID FROM B))";
objDataAdapter.SelectCommand = New MySqlCommand(strSQL, objConn)
objDataAdapter.Fill(objRS)

Anyone has the idea how to do that? Thanks a lot.

You don't appear to have either created a MySqlConnection object or opened it.

But, rather than use a MySqlDataAdapter, I'd see if you can get the value using MySqlCommand.ExecuteScalar:

Dim strSQL as string

Dim objConn As New MySqlConnection(connectionString)

strSQL = "SELECT MAX(memberID) as max_member_id FROM ((SELECT memberID FROM A) UNION (SELECT memberID FROM B))"

Dim command As New MySqlCommand(strSQL, objConn)

command.Connection.Open()

Dim maxMemberId As Integer = CInt(command.ExecuteNonScalar()) ' assuming its an integer

objConn.Close()

Hi Vulpes. Yes, I forgot to include the open connection into this question as I wrote it separately with the query.  Thanks a lot for the suggestion. I just tested the ExecuteScalar method and it works like a charm.