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.
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.
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.
As soon as we click any of the fields newform will display as shown below along
with their records in the DataGrid.
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.
|