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

kiko yang

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes15y ago
Accepted answer
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()
kiko yang15y ago
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.