How to display data in gridview from MS Access?

Question

I want to display information of user stored in a MS Access database. The user enters his userid and on clicking a button following function is called. But no data is being displayed. What am I doing wrong ?

System.Data.OleDb.OleDbConnection con;
System.Data.OleDb.OleDbDataAdapter da;

protected void Button1_Click(object sender, EventArgs e)
{        
    con = new System.Data.OleDb.OleDbConnection();
    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" 
       + "Data Source=C:\\Users\\sam\\Desktop\\mydb.mdb";
    con.Open();
    string sql = "SELECT * From Leave where userid="+Textbox1.Text;
    da = new System.Data.OleDb.OleDbDataAdapter(sql, con);
    DataTable t = new DataTable();
    da.Fill(t);
    GridView1.DataSource = t;
    con.Close();
}

Answer 1

First off, please, please please don't concatenate your WHERE parameters in your SQL. Use Parameters. Second, Add a "using System.Data.OleDb" statement at the top of your module, so that you are not having to type things like:

System.Data.OleDb.OleDbDataAdapter

Over and over again.

Try the following code. Personally, when I have to work with data tables and such, I prefer to avoid all the DataAdapter nonsense, and keep it as simple as possible.

Note in the code below:

1. the "using" blocks. These place the variables created within them inside their own scope, and take care of disposal and such for you.

2. I used an OleDb Parameter instead of concatenating criteria. This is a much safer way to do things, and creates much cleaner and more readable code as well, especially in cases where you have several criteria in your WHERE clause.

3. I assume your UserID input is a string, since you are grabbing the value from a Textbox. If it is in fact an int value (such as an auto-incrementing id in MS Access) you will need to use an int data type instead. You may have to mess with it a little. When you are still figuring this stuff out, it can be a bit painful. However, using parameters increases security and maintainability.

Once you have obtained a data table as the return from the MyUsers method, you should be able to simply set the data source of your Gridview. If you have difficulties still, do as Steve suggests and check the Autogenerate columns property in the designer, or set it in code.

1. Not that I have moved the connection string to the project Properties/Settings. You should find this in the solution designer. Place your connection string there, in one spot, and you can obtain it from anywhere in your code. If you later change the connection string (such as moving your Db to another computer, server share, etc) you need only change it in one place.

SAMPLE CODE:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;  // put this here, and stop writing long namespaces inline

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        private void button1_Click(object sender, EventArgs e)
        {
            // Where possible, move code out of specific event handlers
            // into methods which can be re-used from other client code. 

            // Here, I pulled the actual data access out into separate methods, 
            // and simply call it from the event handler:
            this.LoadGridView(textBox1.Text);
        }


        private void LoadGridView(string UserID)
        {
            // Now we can load the gridview from other places in our
            // code if needed:
            this.dataGridView1.DataSource = this.MyUsers(UserID);
        }


        private DataTable MyUsers(string UserID)
        {
            var dt = new DataTable();

            // Use a SQL Paramenter instead of concatenating criteria:
            string SQL = "SELECT * FROM Leave WHERE userid = @UserID";


            // The "using" statement limits the scope of the connection and command variables, and handles disposal
            // of resources. Also note, the connection string is obtained from the project properties file:
            using(OleDbConnection cn = new OleDbConnection(Properties.Settings.Default.MyConnectionString))
            {
                using (var cmd = new OleDbCommand(SQL, cn))
                {
                    // For simpler things, you can use the "AddWithValue" method to initialize a new parameter, 
                    // add it to the Parameters collection of the OleDBCommand object, and set the value:
                    cmd.Parameters.AddWithValue("@UserID", UserID);

                    // Get in, get out, get done:
                    cn.Open();
                    dt.Load(cmd.ExecuteReader());
                    cn.Close();
                }
            }

            return dt;
        }
    }
}

Hope that helps. It's not how everyone might do it, but I have found it provides maximum flexibility, when you must work with MS Access.

Answer 2

You need to call GridView1.DataBind()

 GridView1.DataSource = t;
 GridView1.DataBind();

Just a side-note, it is good practice to wrap your connection with using

using(con = new System.Data.OleDb.OleDbConnection())
{
   con = new System.Data.OleDb.OleDbConnection();
   con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" 
   + "Data Source=C:\\Users\\sam\\Desktop\\mydb.mdb";
   con.Open();
   ...
   ...
}

This ensures your connection is properly disposed after use

Answer 3

You should use bind function:

 protected void Button1_Click(object sender, EventArgs e)
    {        
       con = new System.Data.OleDb.OleDbConnection();
       con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" 
       + "Data Source=C:\\Users\\sam\\Desktop\\mydb.mdb";
       con.Open();
       string sql = "SELECT * From Leave where userid="+Textbox1.Text;
       da = new System.Data.OleDb.OleDbDataAdapter(sql, con);
       DataTable t = new DataTable();
       da.Fill(t);
       GridView1.DataSource = t;
       GridView1.DataBind();
       con.Close();
    }