Filling a DataTable in C# using MySQL

Question

I'm attempting to fill a DataTable with results pulled from a MySQL database, however the DataTable, although it is initialised, doesn't populate. I wanted to use this DataTable to fill a ListView. Here's what I've got for the setting of the DataTable:

    public DataTable SelectCharacters(string loginName)
    {
        this.Initialise();
        string connection = "0.0.0.0";
        string query = "SELECT * FROM characters WHERE _SteamName = '" + loginName + "'";

        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            MySqlDataAdapter returnVal = new MySqlDataAdapter(query,connection);
            DataTable dt = new DataTable("CharacterInfo");
            returnVal.Fill(dt);
            this.CloseConnection();
            return dt;
        }
        else
        {
            this.CloseConnection();
            DataTable dt = new DataTable("CharacterInfo");
            return dt;            
        }
    }

And for the filling of the ListView, I've got:

 private void button1_Click(object sender, EventArgs e)
    {
        string searchCriteria = textBox1.Text;

        dt = characterDatabase.SelectCharacters(searchCriteria);
        MessageBox.Show(dt.ToString());

        listView1.View = View.Details;

        ListViewItem iItem;
        foreach (DataRow row in dt.Rows)
        {
            iItem = new ListViewItem();
            for (int i = 0; i < row.ItemArray.Length; i++)
            {
                if (i == 0)
                    iItem.Text = row.ItemArray[i].ToString();
                else
                    iItem.SubItems.Add(row.ItemArray[i].ToString());
            }
            listView1.Items.Add(iItem);
        }
    }

Is there something I'm missing? The MessageBox was included so I could see if it has populated, to no luck.

Thanks for any help you can give.

never seen this type of connection string string connection = "0.0.0.0"; — TalentTuner, Dec 24 '12 at 10:41

Ramgy Borja · Answer 1 · 2017-07-17T03:06:30.950

Check your connection string and instead of using

MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataAdapter returnVal = new MySqlDataAdapter(query,connection);
DataTable dt = new DataTable("CharacterInfo");
returnVal.Fill(dt);
this.CloseConnection();
return dt;

you can use this one

MySqlCommand cmd = new MySqlCommand(query, connection);
DataTable dt = new DataTable();
dt.load(cmd.ExecuteReader());
return dt;

Sylca · Accepted Answer · 2017-07-31T14:47:30.837

Well, I ... can't figure out what you have done here so I'll paste you my code with which I'm filling datagridview:

1) Connection should look something like this(if localhost is your server, else, IP adress of server machine):

string connection = @"server=localhost;uid=root;password=*******;database=*******;port=3306;charset=utf8";

2) Query is ok(it will return you something), but you shouldn't build SQL statements like that.. use parameters instead. See SQL injection.

3) Code:

void SelectAllFrom(string query, DataGridView dgv)
        {
            _dataTable.Clear();

            try
            {
                _conn = new MySqlConnection(connection);
                _conn.Open();
                _cmd = new MySqlCommand
                {
                    Connection = _conn,
                    CommandText = query
                };
                _cmd.ExecuteNonQuery();

                _da = new MySqlDataAdapter(_cmd);
                _da.Fill(_dataTable);

                _cb = new MySqlCommandBuilder(_da);

                dgv.DataSource = _dataTable;
                dgv.DataMember = _dataTable.TableName;
                dgv.AutoResizeColumns();

                _conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (_conn != null) _conn.Close();
            }
        }

So, every time I want to display some content of table in mysql database I call this method, pass query string and datagridview name to that method. and, that is it.

For your sake, compare this example with your and see what can you use from both of it. Maybe, listview is not the best thing for you, just saying ...

hope that this will help you a little bit.

Brilliant. Edited mine to be kind of similar to this - but gave it a return value with additional data. Thanks. — Warjekk, Dec 25 '12 at 12:17
Glad it helped you and also you didn't just copy/pasted code.:-) — Sylca, Dec 25 '12 at 12:33

score 0 · Answer 3 · answered Dec 24 '12 at 11:08

0

Debug your application and see if your sql statement/ connection string is correct and returns some value, also verify if your application is not throwing any exception.

answered Dec 24 '12 at 11:08

Himanshu Dhami

3
4

score 0 · Answer 4 · answered Dec 24 '12 at 11:31

0

Your connection string is invalid.

Set it as follows:

connection = "Server=myServer;Database=myDataBase;Uid=myUser;Pwd=myPassword;";

Refer: mysql connection strings

answered Dec 24 '12 at 11:31

nunespascal

17,584
2
43
46

The TRUE connection string was set above this - my fault for forgetting to include it – Warjekk Dec 25 '12 at 12:18

spajce · Answer 5 · 2012-12-24T12:07:04.643

0

Here the following why the codes would not work.

Connection
The connection of your mySQL is invalid
Query
I guess do you want to search in the table, try this query

string query = "SELECT * FROM characters WHERE _SteamName LIKE '" + loginName + "%'";

notice the LIKE and % this could help to list all the data.
for more details String Comparison Functions

edited Dec 24 '12 at 12:07

answered Dec 24 '12 at 11:54

spajce

7,044
5
29
44

Filling a DataTable in C# using MySQL

5 Answers5

Linked