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 enter image description here

Did you spell the table correctly? i.e. is the table "Data" and not "data" — CAMD_3441
– CAMD_3441, Commented Aug 29, 2018 at 1:57
"but keep on failing". That is a wholly inadequate explanation. You need to use your words and explain what actually happens. If there's an exception thrown, tell us that and provide the error message. Have you actually debugged you code? If not do so now. ALWAYS debug before posting here. Does the Read call return false on the first go? If so then that means that your query result is empty, plain and simple. That's not even failing. If you tipped up an empty bucket and nothing came out, would that be a failure? — jmcilhinney
– jmcilhinney, Commented Aug 29, 2018 at 2:06
I'm just following a youtube tutorial, and even i run the program there is no error message shown , how do i debug with no error??? and im only trying to do one thing at a time , is that not plain and simple enough ? "select * from tablename" is not plain and simple enough ?? — NDSAC
– NDSAC, Commented Aug 29, 2018 at 2:13

dj079 · Accepted Answer · 2018-08-29 10:10:14Z

2

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);
    }
}

edited Aug 29, 2018 at 10:10

answered Aug 29, 2018 at 3:33

dj079

1,3899 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

NDSAC Over a year ago

Yes, it exactly what i want I have add columns when i add the Listview and click into the little arrow at the top right corner , is that not the same?? And i have pasted your code already ,but it still not result . And its look a bit different

dj079 Over a year ago

You missed out setting the View property I believe.

NDSAC Over a year ago

I just need to add those listview1.Columns.clear();..... and listview!.columns.add("xx"); on top of my while(dr.Read()) right ??? And i have set those columns by clicking the top right arrow and view set as details, will that duplicated ??

dj079 Over a year ago

If you have added columns and have set view to details using visual properties by clicking top right arrow, you don’t need to do it again using code.

NDSAC Over a year ago

so will it be myconnection problem ? Is there anyway i can test it ? MySqlConnection("Data Source=localhost;Initial Catalog=rfiddb;uid=username;pwd=password;");

|

Kira Hao · Accepted Answer · 2018-08-29 02:13:32Z

0

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);
    }
}

answered Aug 29, 2018 at 2:13

Kira Hao

1,1372 gold badges11 silver badges17 bronze badges

2 Comments

NDSAC Over a year ago

Sorry i have no idea what this is, Im new to C# and just trying out a tutorial from youtube. Can you explain what this is or is there any web site i could learn about it ? Thanks

Kira Hao Over a year ago

this code actually rewrite what you have into using(), in C# we call it using statement. Normally for a connection establish. So in this code, credit from the answer post, it try to establish connection with data reader then read it. Once it done. the rdr will be dipose, mean remove from memory.

Collectives™ on Stack Overflow

C# SQL data populate to Listview fail

2 Answers 2

11 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related