im trying to read data from microsft sql then insert to Mysql but im getting an error stating "Invalid attempt to read when no data is present"

Question

SqlCommand reportCommand = new SqlCommand("select row_date as RowDate, min(extension) as Extension, logid as AgentId, sum(ti_stafftime) as StaffedTime, sum(ti_availtime) as AvailableTime, sum(ti_auxtime) as AuxTime, sum(auxoutcalls) as AuxOutCalls, sum(auxouttime) as AuxOutTime,sum(acdcalls) as ACDCalls, sum(acdtime) as ACDTime, sum(acwoutcalls) as ACWOutCalls, sum(acwtime) as ACWTime, sum(holdtime) as HoldTime, sum(ringtime) as RingTime, sum(noansredir) as RONACalls from NakheelCMS_CC..DAGENT with (nolock) where row_date >= CAST(DATEPART(YEAR, GETDATE()) AS CHAR(4)) + '-' + right('00' + rtrim(DATEPART(MONTH, GETDATE())), 2) + '-01' group by row_date, logid order by logid, row_date", reportConnection);


        using(SqlDataReader reader = reportCommand.ExecuteReader())
        {
            if (reader.HasRows) {
                query_dashboardDB = "insert into agent_daily_stats (date, login_id, extension, staffed_time, avail_time, acd_time, acd_calls, aux_time, aux_outcalls, aux_outtime, acw_time, acw_outcalls, ring_time, hold_time, rona_calls) VALUES ('" + String.Format("{0}", reader["RowDate"]) + "','" + String.Format("{0}", reader["AgentId"]) + "','" + String.Format("{0}", reader["Extension"]) + "','" + String.Format("{0}", reader["StaffedTime"]) + "','" + String.Format("{0}", reader["AvailableTime"]) + "','" + String.Format("{0}", reader["AuxTime"]) + "','" + String.Format("{0}", reader["AuxOutCalls"]) + "','" + String.Format("{0}", reader["AuxOutTime"]) + "','" + String.Format("{0}", reader["ACDCalls"]) + "','" + String.Format("{0}", reader["ACDTime"]) + "','" + String.Format("{0}", reader["ACWOutCalls"]) + "','" + String.Format("{0}", reader["ACWTime"]) + "','" + String.Format("{0}", reader["HoldTime"]) + "','" + String.Format("{0}", reader["RingTime"]) + "','" + String.Format("{0}", reader["RONACalls"]) + "')";
                try {
                    dashboardCommand.CommandText = query_dashboardDB;
                    dashboardCommand.ExecuteNonQuery();
                } catch (Exception sqlEx) {
                    logMe.Error("Failed to update agent_daily_stats.", sqlEx);
                }
            } else {
                logMe.Error("no rows found");
            }
        }

The query_dashboardDb is getting no return values from sqlcommand even though it is being executed under reader.HasRows

Answer 1

As I suggested in the comments, I'd avoid data readers for this anyway, making the question of an issue with data readers moot. If you use data adapters then your code might look something like this:

Dim table As New DataTable

Using sourceAdapter As New SqlDataAdapter("SELECT Column1, Columnn2 FROM Table1", "connection string here") With {.AcceptChangesDuringFill = False}
    If sourceAdapter.Fill(table) = 0 Then
        'No data found.
    Else
        Using destinationConnection As New MySqlConnection("connection string here"),
              destinationCommand As New MySqlCommand("INSERT INTO Table1 (Column1, Column2) VALUES (@Column1, @Column2)", destinationConnection),
              destinationAdapter As New MySqlDataAdapter With {.InsertCommand = destinationCommand}
            With destinationCommand.Parameters
                .Add("@Column1", MySqlDbType.VarChar, 50, "Column1")
                .Add("@Column2", MySqlDbType.VarChar, 50, "Column2")
            End With

            destinationAdapter.Update(table)
        End Using
    End If
End Using

By default, Fill will implicitly call AcceptChanges on the DataTable after populating it, changing all the RowStates of the DataRows from Added to Unchanged. By setting AcceptChangesDuringFill to False, you prevent that happening, meaning that all the rows are ready to be inserted.