Populate a datagridview with sql query results

Question

I'm trying to present query results, but I keep getting a blank data grid. It's like the data itself is not visible

Here is my code:

 private void Employee_Report_Load(object sender, EventArgs e)
 {
     string select = "SELECT * FROM tblEmployee";
     Connection c = new Connection();
     SqlDataAdapter dataAdapter = new SqlDataAdapter(select, c.con); //c.con is the connection string
     SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

     DataTable table = new DataTable();
     table.Locale = System.Globalization.CultureInfo.InvariantCulture;
     dataAdapter.Fill(table);
     bindingSource1.DataSource = table;

     dataGridView1.ReadOnly = true;        
     dataGridView1.DataSource = bindingSource1;
}

What's wrong with this code?

Bind the data in dataGridView – Dawood Ahmed Aug 07 '13 at 20:36 — Dawood Ahmed, Aug 07 '13 at 20:36

score 47 · Accepted Answer · edited Jan 11 '17 at 23:09

47

Here's your code fixed up. Next forget bindingsource

 var select = "SELECT * FROM tblEmployee";
 var c = new SqlConnection(yourConnectionString); // Your Connection String here
 var dataAdapter = new SqlDataAdapter(select, c); 

 var commandBuilder = new SqlCommandBuilder(dataAdapter);
 var ds = new DataSet();
 dataAdapter.Fill(ds);
 dataGridView1.ReadOnly = true; 
 dataGridView1.DataSource = ds.Tables[0];

edited Jan 11 '17 at 23:09

DamienG

6,575
27
43

answered Aug 07 '13 at 20:39

Don Thomas Boyle

3,055
3
32
54

7

You can "fill" a `DataTable` no problem. Don't need a `DataSet`. – banging Aug 07 '13 at 20:41
2

It does work but a `DataSet` is extra overhead for no good use. – banging Aug 07 '13 at 20:44
1

@DonThomasBoyle In the second sample code, the class that are used are `SqlServerCe` not `SqlServer`. It could be confusing. – Chris Aug 07 '13 at 20:53
It's like the data itself is not visible in my case – user2023203 Aug 07 '13 at 20:55

score 11 · Answer 2 · edited Jun 28 '18 at 14:04

String strConnection = Properties.Settings.Default.BooksConnectionString;
SqlConnection con = new SqlConnection(strConnection);

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "Select * from titles";
SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);

DataTable dtRecord = new DataTable();
sqlDataAdap.Fill(dtRecord);
dataGridView1.DataSource = dtRecord;

score 4 · Answer 3 · answered Aug 07 '13 at 20:36

4

You don't need bindingSource1

Just set dataGridView1.DataSource = table;

answered Aug 07 '13 at 20:36

banging

2,540
1
22
26

Do you see columns in the datagridview? If yes then your query is not returning any rows!! – banging Aug 07 '13 at 20:42
I checked my query in sql server 2008 and it does return all the data of employee table – user2023203 Aug 07 '13 at 20:48

score 2 · Answer 4 · answered Aug 07 '13 at 20:38

2

Try binding your DataGridView to the DefaultView of the DataTable:

dataGridView1.DataSource = table.DefaultView;

answered Aug 07 '13 at 20:38

Khan

17,904
5
47
59

Develop4Life · Answer 5 · 2020-01-02T18:17:10.523

This is suppose to be the safest and error pron query :

    public void Load_Data()
        {
            using (SqlConnection connection = new SqlConnection(DatabaseServices.connectionString)) //use your connection string here
            {
                var bindingSource = new BindingSource();
                string fetachSlidesRecentSQL = "select top (50) * from dbo.slides order by created_date desc";
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(fetachSlidesRecentSQL, connection))
                {
                    try
                    {
                       SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

                        DataTable table = new DataTable();
                        dataAdapter.Fill(table);
                        bindingSource.DataSource = table;
                        recent_slides_grd_view.ReadOnly = true;
                        recent_slides_grd_view.DataSource = bindingSource;
                    }
                    catch (SqlException ex)
                    {
                       MessageBox.Show(ex.Message.ToString(), "ERROR Loading");
                    }
                    finally
                    {
                        connection.Close();
                    }
                }

            }
        }

score 0 · Answer 6 · answered Nov 01 '16 at 21:55

0

You may get a blank data grid if you set the data Source to a Dataset that you added to the form but is not being used. Set this to None if you are programatically setting your dataSource based on the above codes.

answered Nov 01 '16 at 21:55

Damion Willi

11
1

score 0 · Answer 7 · edited Sep 15 '17 at 15:15

You may try this sample, and always check your Connection String, you can use this example with or with out bindingsource you can load the data to datagridview.

private void Employee_Report_Load(object sender, EventArgs e)
{
        var table = new DataTable();

        var connection = "ConnectionString";

        using (var con = new SqlConnection { ConnectionString = connection })
        {
            using (var command = new SqlCommand { Connection = con })
            {

                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }

                con.Open();

                try
                {
                    command.CommandText = @"SELECT * FROM tblEmployee";
                    table.Load(command.ExecuteReader());

                    bindingSource1.DataSource = table;

                    dataGridView1.ReadOnly = true;
                    dataGridView1.DataSource = bindingSource1;

                }
                catch(SqlException ex)
                {
                    MessageBox.Show(ex.Message + " sql query error.");
                }

            }

        }

 }

score 0 · Answer 8 · answered Nov 30 '17 at 12:06

0

you have to add the property Tables to the DataGridView Data Source

 dataGridView1.DataSource = table.Tables[0];

answered Nov 30 '17 at 12:06

Mohammed Elrayh

1
2

There is already the accepted answer, that provides as well way more information than you do. This here is quite redundant. – L. Guthardt Nov 30 '17 at 12:25

score 0 · Answer 9 · answered Jan 17 '19 at 06:19

if you are using mysql this code you can use.

string con = "SERVER=localhost; user id=root; password=; database=databasename";
    private void loaddata()
{
MySqlConnection connect = new MySqlConnection(con);
connect.Open();
try
{
MySqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "SELECT * FROM DATA1";
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
datagrid.DataSource = dt;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}

score 0 · Answer 10 · answered Feb 01 '21 at 16:48

Years late but here's the simplest for others in case.

String connectionString = @"Data Source=LOCALHOST;Initial Catalog=DB;Integrated Security=true";

SqlConnection cnn = new SqlConnection(connectionString);
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM tblEmployee;", cnn);

DataTable data = new DataTable();
sda.Fill(data);

DataGridView1.DataSource = data;

Using DataSet is not necessary and DataTable should be good enough. SQLCommandBuilder is unnecessary either.

score 0 · Answer 11 · edited Oct 10 '21 at 12:32

I think this professional way to Write from start, but you can use this code with MySQL bout I think they both are the same:

1/

using System.Data; AND using MySql.Data.MySqlClient;

2/

MySqlConnection con = new MySqlConnection("datasource=172.16.2.104;port=3306;server=localhost;database=DB_Name=root;password=DB_Password;sslmode=none;charset=utf8;");
        MySqlCommand cmd = new MySqlCommand();

3/

public void SetCommand(string SQL)
{
    cmd.Connection = con;
    cmd.CommandText = SQL;

}
private void FillGrid()
{
    SetCommand("SELECT * FROM `transport_db`ORDER BY `id` DESC LIMIT 15");
    DataTable tbl = new DataTable();
    tbl.Load(cmd.ExecuteReader());
    dataGridView1.DataSource = tbl;
}

ozanmut · Answer 12 · 2022-11-22T11:20:00.840

for oracle:

        var connString = new ConfigurationBuilder().AddJsonFile("AppSettings.json").Build()["ConnectionString"];
        OracleConnection connection = new OracleConnection();
        connection.ConnectionString = connString;
        connection.Open();          
        var dataAdapter = new OracleDataAdapter("SELECT * FROM TABLE", connection);
        var dataSet = new DataSet();
        dataAdapter.Fill(dataSet);

Populate a datagridview with sql query results

12 Answers12

Linked