MySqlDataReader not returning data

Question

As part of a project to import data into wordpress via screen scraping I've a database table of old and new URL's stored in a MySQL database. In the example below the ExecuteReader command doesn't appear to be returning any data (-1 rows effected), I've ran the SQL via workbench and that returns data, so it's not the SQL or data in the database.

At other times within the code I've called ExecuteNonQuery() and ExecuteScalar() both without issue (so it isn't the connection string).

Any ideas what to try next?

    Dim SQL As String
    Dim conn As MySqlConnection = New MySqlConnection(_CONNECTIONSTRING)

    SQL = "SELECT OrgURL, NewURL FROM `wp_user`.`tbl_linkdata`"

    Try
        conn.Open()
        Dim cmd As MySqlCommand = New MySqlCommand(SQL, conn)
        Dim dr As MySqlDataReader = cmd.ExecuteReader()
        While (dr.Read)
            LinkHashMap.Add(dr.GetString(0), dr.GetString(1))
        End While
        Console.ForegroundColor = ConsoleColor.Cyan
        Console.WriteLine("The Hash map contains " + dr.RecordsAffected + " rows")
        dr.Close()

    Catch ex As Exception
        Console.ForegroundColor = ConsoleColor.Red
        Console.WriteLine("Exception loading the hashtable : " + ex.Message)
    Finally
        conn.Dispose()
    End Try

[`DataReader.RecordsAffected`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.recordsaffected.aspx) always returns -1 for a SELECT command. What does `LinkHashMap.Count` return? **Edit** For `MySqlDataReader` it is the same: http://www.devart.com/dotconnect/mysql/docs/Devart.Data.MySql~Devart.Data.MySql.MySqlDataReader~RecordsAffected.html (_"The number of rows changed, inserted, or deleted. -1 for SELECT statements"_) — Tim Schmelter, Oct 24 '14 at 11:27
I believe that under the hood it call's "mysql_affected_rows" according to http://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html "-1 indicates that the query returned an error or that", but as I say I've checked the SQL via workbench. — , Oct 24 '14 at 11:29
@ScottHerbert: how do you want to add something to it without initializing it first? A `NullReferenceException` should have happened. — Tim Schmelter, Oct 24 '14 at 11:32
I'm not a MySQL guy but is the select supposed to have schema and table in quotation marks `wp_user`.`tbl_linkdata`? — Iztoksson, Oct 24 '14 at 11:36
@TimSchmelter Doh! that was it.... If you make this an answer I'll accept it. Thanks — , Oct 24 '14 at 12:33

score 0 · Accepted Answer · answered Oct 24 '14 at 12:40

DataReader.RecordsAffected always returns -1 for a SELECT command. What does LinkHashMap.Count return? In MySqlDataReader it is the same:

"The number of rows changed, inserted, or deleted. -1 for SELECT statements"

If you want to count the number of records you can use LinkHashMap.Count.

You: "LinkHashMap is "Nothing" "

How do you want to add something to it without initializing it first? A NullReferenceException should have happened. So initialize the dictionary (or whatever it is) first via constructor:

Dim LinkHashMap As New Dictionary(Of String, String)
While (dr.Read)
    LinkHashMap.Add(dr.GetString(0), dr.GetString(1))
End While

MySqlDataReader not returning data

1 Answers1