Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Populate DataGridView with MySql Query

Posted on 2010-01-11
6
Medium Priority
?
1,137 Views
Last Modified: 2013-12-17
I have a simple query that I need to bind to a datagrid for viewing. I have seen several tutorials but not many using MySql. I need to be able to bind this using my query method below. Can someone help me out here. I could figure it out eventually but really don't want to waste much time on this.

The query method I use works, but how do I bind the results to the datadridview control.

Thanks
private void SetHistoryListView()
        {
            clsMySqlODBCConnection MySqlCnn = new clsMySqlODBCConnection();
            string strQry = null;
            int tserialnumber = 0;
            //
            strQry = "Select * FROM HIstory WHERE SerialNumber = " + tserialnumber + "";
            MySqlCnn.MySQLQuery(strQry);
            while (MySqlCnn.OdbcDR.Read())
            {
                datagridView.DataSource = 1;
            }
        }

Open in new window

0
Comment
Question by:SaltyDawg
  • 3
  • 2
5 Comments
 
LVL 30

Accepted Solution

by:
anarki_jimbel earned 2000 total points
ID: 26288551
In some sense does not matter what DB you use. You need to fill some Datatable object and set is as a datasource for the datagridview. datagridView.DataSource = 1; does not make sense really.

Check on Internet how to get results to a dataadapter, e.g.
0
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 26288578
0
 
LVL 1

Author Comment

by:SaltyDawg
ID: 26293001
This is the Connection Class I use throughout my project. The examples I find have a difference connection method. I would like to be able to use my own class to bind the datagridview, but I am not sure how to make the connection with datagridView.DataSource.
datagridView.DataSource  = ?

datagridView.DataSource = MySqlCnn.MySQLQuery(strQry);
This does not error but I can see this is not correct.

    class clsMySqlODBCConnection
    {
        public int QueryType = 0;
        public System.Data.Odbc.OdbcConnection OdbcCon;
        public System.Data.Odbc.OdbcCommand OdbcCom;
        public System.Data.Odbc.OdbcDataReader OdbcDR;
        public string ConStr;
        private string MySqlServer = "linux02", MySqlPort = "", MySqlDatabase = "EbtronSystems", MySqlUser = "jimmy", MySqlUserPass = "ebtron", MySqlOption = "3";

        public clsMySqlODBCConnection()
        {
            ConStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + MySqlServer + ";PORT=" + MySqlPort + ";DATABASE=" + MySqlDatabase + ";UID=" + MySqlUser + ";PWD=" + MySqlUserPass + ";OPTION=" + MySqlOption + "";
            OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);

            try
            {
                //txtLog.AppendText("Openning connection...\r\n");
                if (OdbcCon.State == ConnectionState.Closed)
                {
                    OdbcCon.Open();
                }
                //txtLog.AppendText("Connection opened\r\n");
                //MessageBox.Show("Connection opened\r\n");
            }
            catch (System.Data.Odbc.OdbcException Ex)
            {
                //txtLog.AppendText(Ex.Message + "\r\n");
                //MessageBox.Show("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        public void MySqlDisconnection()
        {
            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCon.Close();
                //MessageBox.Show("Connected Closed\r\n");
            }
        }

        public void ShowTables()
        {
            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW TABLES", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                //txtLog.AppendText("Tables inside " + txtDatabase.Text + ":\r\n");
                while (OdbcDR.Read())
                {
                    //txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }
            }
        }

        public string[] MySQLQuery(string qry, int fldidx)
        {
            List<string> list = new List<string>();

            string tStr = null;     // the following is not needed: = Convert.ToString(null);
            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand(qry, OdbcCon);
                if (fldidx >= 0)
                {
                    OdbcDR = OdbcCom.ExecuteReader();
                    while (OdbcDR.Read())
                    {
                        tStr = Convert.ToString(OdbcDR[fldidx]);
                        list.Add(tStr);
                    }
                }
                else
                {
                    OdbcCom.ExecuteNonQuery();
                    QueryType = 1;
                }
            }
            return list.ToArray();
        }

        public IDataReader MySQLQuery(string qry)
        {
            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand(qry, OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
            }
            return OdbcDR;
        }

        public void MySQLNONQuery(string qry)
        {
            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand(qry, OdbcCon);
                OdbcCom.ExecuteNonQuery();
                QueryType = 1;
            }
        }

        public object InitializeCnnObject()
        {
            // Create and initilize a recordset to represent the User Table
            clsMySqlODBCConnection OpenConnection = new clsMySqlODBCConnection();
            return OpenConnection;
        }

    }

Open in new window

0
 
LVL 1

Author Comment

by:SaltyDawg
ID: 26293038
Also how is MySqlDataAdapter used?
0
 
LVL 1

Author Comment

by:SaltyDawg
ID: 26398321
How do I set MySqlDataAdapter ?
 
Thanks
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question