DataGridView doesn't show any data from SQL Server datasource

Question

I've been practicing with ADO.NET and SQL Server in a Windows Forms application, but I can't get table data into a DataGridView on the press of a button.

There are no errors and I make server connection checking. I have corresponding database and table name with some data in it.

Any ideas what I am doing wrong?

Here is code from the button:

    private void button1_Click(object sender, EventArgs e)
    {
        string ConnectionString = "Server=DESKTOP-FV268LU;Database=ado_database;Integrated Security=true";

        SqlConnection myConnection = new SqlConnection();
        myConnection.ConnectionString = ConnectionString;
        myConnection.Open();

        if (myConnection.State == ConnectionState.Open)
            label1.Text = "YES!";
        else if (myConnection.State != ConnectionState.Open)
            label1.Text = "Nope!!";

        string sql = "SELECT * FROM Main";

        SqlDataAdapter myAdapter = new SqlDataAdapter(sql, myConnection);

        DataSet myDataSet = new DataSet("Main");
        myAdapter.Fill(myDataSet, "Main");

        dataGridView1.AutoGenerateColumns = true;
        dataGridView1.DataSource = myDataSet.DefaultViewManager;
        dataGridView1.Refresh();
    }

Answer 1

How about this?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;
using System.Data.SqlClient;

namespace WindowsFormsApplication6
{
    public partial class Form1 : Form
    {


        private DataTable table;
        private DAL dal;
        protected void Form_Load(object sender, EventArgs e)

        {
            dal = new DAL();
            table = dal.GetData();
            dataGridView1.DataSource = table;
        }

        public Form1()
        {
            InitializeComponent();
        }

private void button5_Click(object sender, EventArgs e)
        {
            dal.UpdateData(table);
        }

        class DAL //data access layer
        {
            string connString = @"Server=EXCEL-PC\EXCELDEVELOPER;Database=AdventureWorksLT2012;Trusted_Connection=True;";
            SqlDataAdapter da;
            SqlCommandBuilder builder;
            DataTable table;
            SqlConnection conn;
            public DataTable GetData()
            {
                table = new DataTable("dataGridView1");
                conn = new SqlConnection(connString);
                da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(@"SELECT * FROM [SalesLT].[Product]", conn);
                builder = new SqlCommandBuilder(da);
                da.Fill(table);
                return table;
            }
            public void UpdateData(DataTable table)
            {
                if (da != null && builder != null)
                {
                    da.Update(table);
                }
            }
        }

    }
}

Here is another option for you to consider.

private void button6_Click(object sender, EventArgs e)

        {
            SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = "Server=EXCEL-PC\\EXCELDEVELOPER;Database=AdventureWorksLT2012;Trusted_Connection=True;";
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter();

            for (int i = 0; i <= dataGridView1.Rows.Count - 2; i++)
            {

                String insertData = "INSERT INTO Import_List(Fname, Lname, Age) VALUES (@Fname, @Lname, @Age)";
                SqlCommand cmd = new SqlCommand(insertData, con);
                cmd.Parameters.AddWithValue("@Fname", dataGridView1.Rows[i].Cells[0].Value);
                cmd.Parameters.AddWithValue("@Lname", dataGridView1.Rows[i].Cells[1].Value);
                cmd.Parameters.AddWithValue("@Age", dataGridView1.Rows[i].Cells[2].Value);
                da.InsertCommand = cmd;
                cmd.ExecuteNonQuery();
            }

            con.Close();
        }

Answer 2

You need to call after setting the DataSource

dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = myDataSet.DefaultViewManager;
dataGridView1.DataBind();

EDIT

  dataGridView1.DataSource = myDataSet.Tables[0];
  dataGridView1.AutoGenerateColumns = true;