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
– TalentTuner, Commented Dec 24, 2012 at 10:41

Ramgy Borja · Accepted Answer · 2017-07-17 03:06:30Z

7

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;

edited Jul 17, 2017 at 3:06

answered Jul 17, 2017 at 2:56

Ramgy Borja

2,4682 gold badges24 silver badges41 bronze badges

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

Comments

Sylca · Accepted Answer · 2017-07-31 14:47:30Z

4

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.

edited Jul 31, 2017 at 14:47

answered Dec 24, 2012 at 11:33

Sylca

2,5554 gold badges34 silver badges52 bronze badges

2 Comments

Warjekk Over a year ago

Brilliant. Edited mine to be kind of similar to this - but gave it a return value with additional data. Thanks.

Sylca Over a year ago

Glad it helped you and also you didn't just copy/pasted code.:-)

Himanshu Dhami · Accepted Answer · 2012-12-24 11:08:13Z

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, 2012 at 11:08

Himanshu Dhami

34 bronze badges

Comments

nunespascal · Accepted Answer · 2012-12-24 11:31:56Z

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, 2012 at 11:31

nunespascal

17.7k2 gold badges45 silver badges46 bronze badges

1 Comment

Warjekk Over a year ago

The TRUE connection string was set above this - my fault for forgetting to include it

spajce · Accepted Answer · 2012-12-24 12:07:04Z

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, 2012 at 12:07

answered Dec 24, 2012 at 11:54

spajce

7,0905 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

Filling a DataTable in C# using MySQL

5 Answers 5

Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related