Home > DeveloperSection > Articles > HOW TO ACCESS DATA FROM SQL DATABASE IN GRIDVIEW

HOW TO ACCESS DATA FROM SQL DATABASE IN GRIDVIEW


ADO.Net ADO.Net 
Ratings:
0 Comment(s)
 7627  View(s)
Rate this:

HOW TO ACCESS DATA FROM DATABASE IN GRIDVIEW

In this project we have to Access data from SQL in datagridview control and populating data in another form by selecting fields in the datagridview as well as updating records.

First step is to open a new Project and name it as “DataGridView_Application”, Next step is to create a Form that contains DataGridView control as shown below.

 

GridView

As soon as we will populate data from the database in Gridview while selecting of any fields in the gridview another form display as here we named it as “NewForm”.  To show data in another from we have to create another form as “New Form” with four Fields as textbox and two Update Buttons; as shown below.

Form

 

code for accept a data from the database and display a record in the data gridview as soon as page load so we have to write a code under form_load area as shown below.

 

  private void Update_Load(object sender, EventArgs e)

        {

//create a connection and pass connection string along with parameters.

        SqlConnection con = new SqlConnection("server=abc-PC1\\sqlexpress; uid=sa; password=sa; database=Mind");

          

            con.Open();//connection open

//creating dataadapter object and executing query inside it.

            SqlDataAdapter sda = new SqlDataAdapter("select * from Employee",                con);

//craeting datatable object as dt

            DataTable dt = new DataTable();

            sda.Fill(dt);//fill data in dataadapter

            dataGridView1.DataSource = dt;

//setting datasource of datagridview to object of datatable

 

        }

On double click of selected record from datagridview we are going to populate record to our “NewForm” Please check blow lines of code.

private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)

        {

            int id = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value);

            NewForm nf = new NewForm(id);//creating Object of NewForm and passing the “id”

            nf.ShowDialog();//show new form

        }

 

Next step is code area of the “newform” and declare id global.

 

Code

public partial class NewForm : Form

    {

        SqlConnection con;//global declaration

        SqlDataReader rdr;//global declaration

        SqlDataReader rdr1;//global declaration

        int nID;      

        public NewForm(int ID) //constructor is passing            

        {

            InitializeComponent();

            con = new SqlConnection("server=abc-PC1\\sqlexpress; uid=sa; password=sa; database=Mind");

            nID = ID;

        }

 

 

 

 

private void NewForm_Load(object sender, EventArgs e)

        {

            con.Open();

//creating sqlcommand object and Passing query

            SqlCommand cmd = new SqlCommand("select * from Employee where Employee_id=" + nID, con);

//execting data reader

rdr = cmd.ExecuteReader();

rdr.Read();//reading datareader

string a = rdr[0].ToString();

txtID.Text = a;

txtName.Text = rdr[1].ToString();

txtAddress.Text = rdr[2].ToString();

txtPhoneno.Text = rdr[3].ToString();

txtage.Text = rdr[4].ToString();

rdr.Close();//close the reader

}

As soon as we will populate data from the database in a datagridview we have to update the various records which is displaying on the newForm with the help of Update Button and also conferming with the help of MessageBox “wether do you want to update the record or not.

Code for this  is shown below.

private void btbUpdate_Click(object sender, EventArgs e)

        {

            try

            {

//message box shows wether do you want to update the record or not

                if (MessageBox.Show("Do you want to update record?", "update", MessageBoxButtons.YesNo) == DialogResult.Yes)

                {

//query for updating record ina sqlcommand

                    SqlCommand cmd = new SqlCommand("UPDATE employee SET Employee_id='" + txtID.Text + "',Employee_name='" + txtName.Text + "',Employee_address='" + txtAddress.Text + "',Employee_phoneno='" + txtPhoneno.Text + "',Employee_age='" + txtage.Text + "'where Employee_id='" + nID + "'", con);

                    rdr1 = cmd.ExecuteReader();

                    con.Close();

                    this.Close();

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

 

 

Output shows below as soon as page load data populates from the database in a gridview as shown below in the output.

 

data from Database in GridView

 

As soon as we click any of the fields newform will display as shown below along with their records in the DataGrid.

Data populated in form on GridView click

 

As you click update button it will ask a confirmation dialog box whether you want to update the record or not if u have pressed yes then records updated as shown below with the help of arrow.

update record


Don't want to miss updates? Please click the below button!

Follow MindStick