C# SQL data populate to Listview fail

Question

I'm new to C# and trying to show data from Mysql database to ListView, but keep on failing.

by these coding i didnt get any error but no data showing at the listview when i run the program.

Please advice and help, thanks

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace sql_to_listview
{
public partial class Form1 : Form
{
    MySqlConnection cn = new MySqlConnection("Data Source=localhost;Initial 
    Catalog=rfiddb;uid=username;pwd=password;");
    MySqlCommand cmd = new MySqlCommand();
    MySqlDataReader dr;

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        cn.Open();
        cmd.CommandText = "select * from data";
        cmd.Connection = cn;
        dr = cmd.ExecuteReader();

        while(dr.Read())
        {
            ListViewItem lv = new ListViewItem(dr[0].ToString());
            lv.SubItems.Add(dr[1].ToString());
            lv.SubItems.Add(dr[2].ToString());
            lv.SubItems.Add(dr[3].ToString());
            listView1.Items.Add(lv);
        }
        cn.Close();
    }
  }
}

Here is the data table looks like

Answer 1

I believe this is what you want to achieve.

If yes, you are required to set ListView's View property to Details and add Columns to the ListView.

Here is the code you should use:

    listView1.Columns.Clear(); // Clear previously added columns
    listView1.Items.Clear(); // Clear previously populated items
    listView1.View = View.Details; // Set View property

    // Set Columns
    listView1.Columns.Add("Id"); 
    listView1.Columns.Add("Name");
    listView1.Columns.Add("Number");
    listView1.Columns.Add("Date");

    while(dr.Read())
    {
        ListViewItem lv = new ListViewItem(dr[0].ToString());
        lv.SubItems.Add(dr[1].ToString());
        lv.SubItems.Add(dr[2].ToString());
        lv.SubItems.Add(dr[3].ToString());
        listView1.Items.Add(lv);
    }

Update after looking at the code

Replace your form_load method with this one. Once you understand what has to be done, replace the sample data with MySql code.

protected override void OnLoad(EventArgs e)
{
    base.OnLoad(e);
    //cn.Open();  
    //cmd.CommandText = "select * from data";
    //cmd.Connection = cn;
    //dr = cmd.ExecuteReader();

    //listView1.Columns.Clear(); // Clear previously added columns
    //listView1.Items.Clear(); // Clear previously populated items
    //listView1.View = View.Details; // Set View property

    //// Set Columns
    //listView1.Columns.Add("Id");
    //listView1.Columns.Add("Name");           
    //listView1.Columns.Add("Number");
    //listView1.Columns.Add("Date");

    //while (dr.Read())
    //{
    //    ListViewItem lv = new ListViewItem(dr[0].ToString());
    //    lv.SubItems.Add(dr[1].ToString());
    //    lv.SubItems.Add(dr[2].ToString());
    //    lv.SubItems.Add(dr[3].ToString());
    //    listView1.Items.Add(lv);
    //}
    //cn.Close();

    List<List<string>> data = new List<List<string>>();
    var row = new List<string>();
    row.Add("1");
    row.Add("Name");
    row.Add("111");
    row.Add(DateTime.Now.AddDays(-2).ToString());
    data.Add(row);

    row = new List<string>();
    row.Add("2");
    row.Add("Name");
    row.Add("222");
    row.Add(DateTime.Now.AddDays(-2).AddMinutes(-5).ToString());
    data.Add(row);

    row = new List<string>();
    row.Add("3");
    row.Add("Name");
    row.Add("333");
    row.Add(DateTime.Now.AddDays(-2).AddMinutes(-10).ToString());
    data.Add(row);

    //lv = new ListView();
    listView1.Columns.Clear();
    listView1.Items.Clear();
    listView1.View = View.Details;
    listView1.Columns.Add("Id");
    listView1.Columns.Add("Name2");
    listView1.Columns.Add("Number");
    listView1.Columns.Add("Date");
    foreach (var dr in data)
    {
        ListViewItem lv = new ListViewItem(dr[0].ToString());
        lv.SubItems.Add(dr[1].ToString());
        lv.SubItems.Add(dr[2].ToString());
        lv.SubItems.Add(dr[3].ToString());
        listView1.Items.Add(lv);
    }
}

Answer 2

By any chance, do you try something like this before ?

https://stackoverflow.com/a/4018147/5209563

using(SqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        var myString = rdr.GetString(0); //The 0 stands for "the 0'th column", so the first column of the result.
        // Do somthing with this rows string, for example to put them in to a list
        listDeclaredElsewhere.Add(myString);
    }
}