How fill multiple row through datagridview to database?

Question

can any one give example for save datagridview data in database (sql server). by loop or any other way?

Vikas Ahlawat · Answer

Thanks for help me my problem is solved , error is generating because i m not giving the column name i was using MessageBox.Show(dataGridView1.Rows[1].Cells[1].Value.ToString()); but need MessageBox.Show(dataGridView1.Rows[1].Cells["Name"].Value.ToString());

Nilanka Dharmadasa · Answer

A null check will solve this problem. Paste your code here. Then I can tell you the exact reason for this and give you the solution.

Vikas Ahlawat · Answer

actually i have fill the name , class.. cloumns of datagridview with some database table and it displayed properly, But when i want to show these cells values in messagebox it shows same null values error , why? MessageBox.Show(dataGridView1.Rows[1].Cells[1].Value.ToString()); But it gives error: Object reference not set to an instance of an object. why?

Nilanka Dharmadasa · Answer

Hi Vikas and Kritan, If you use 'Value' property, you will notice that, sometimes it returns false eventhough the checkbox is checked. Because this 'Value' contains only the 'committed' value. This is not similar to our expected value always. Kritan, you can refer to this if you like. http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridviewcell.editedformattedvalue.aspx So Vikas, Do not use 'value' property. Instead use 'DataGridViewCell . EditedFormattedValue ' as I have given in my code. foreach(DataGridViewRow r in dataGridView1.Rows) { DataGridViewCheckBoxCell chk3 = (DataGridViewCheckBoxCell)r.Cells[3]; if (Convert.ToInt32(chk3. EditedFormattedValue ) == 1) { //Check box is checked. // Write your code here. } else { //Checkbox is not checked. } } If my answer helps you please accept my answer. Thanks

Kirtan Patel · Answer

Lets take Example if you want to check whether cell[10] if row 3 is checked or not then bool Checked = Convert.ToBoolean(dataGridview1.Rows[2].Cells[9].value); if(Checked == true) { //Check Box Checked } else { // No Checked }

Nilanka Dharmadasa · Answer

Hi Vikas, To check whether the check box is checked or not you can use my code. Here, I have written the code to check cell 3. You can use the same code for all the other check boxes. DataGridViewCheckBoxCell chk3 = (DataGridViewCheckBoxCell)r.Cells[3]; if (Convert.ToInt32(chk3.EditedFormattedValue) == 1) { //This checks whether the check box is checked. // Write your code here. } If this answer helps you please accept my answer. :) Thanks

Vikas Ahlawat · Answer

Hi Kirtan MY this problem is solved, by string a = r.Cells[0].Value == DBNull.Value ? r.Cells[0].Value.ToString() :""; string b = r.Cells[1].Value == DBNull.Value ? r.Cells[1].Value.ToString() : ""; string c = r.Cells[2].Value == DBNull.Value ? r.Cells[2].Value.ToString():""; string d = r.Cells[3].Value == DBNull.Value ? r.Cells[3].Value.ToString():""; string ee = r.Cells[4].Value == DBNull.Value ?r.Cells[4].Value.ToString():""; problem was that i was passing null values. ya i m using checkboxs cell[3]......13 are check boxes i want to fill present or absent according to these check boxes so how can i check that the checkbox is checked or not?

Kirtan Patel · Answer

is there any other type of cell is there at cell[0] mean checkbox etc ?? ???

Vikas Ahlawat · Answer

againg same error : Object reference not set to an instance of an object. error occure when assigning string a= r.cell[0].value.tostring(); private void button1_Click(object sender, EventArgs e) { con.Open(); DataSet ds = new DataSet(); foreach(DataGridViewRow r in dataGridView1.Rows) { string a = r.Cells[0].Value.ToString(); string b = r.Cells[1].Value.ToString(); string c = r.Cells[2].Value.ToString(); string d = r.Cells[3].Value.ToString(); string ee = r.Cells[4].Value.ToString(); string f = r.Cells[5].Value.ToString(); string g = r.Cells[6].Value.ToString(); string h = r.Cells[7].Value.ToString(); string i = r.Cells[8].Value.ToString(); string j = r.Cells[9].Value.ToString(); string k = r.Cells[10].Value.ToString(); string l = r.Cells[11].Value.ToString(); string m = r.Cells[12].Value.ToString(); string n = r.Cells[13].Value.ToString(); string o = r.Cells[14].Value.ToString(); SqlCommand cmd = new SqlCommand("insert into Student_Attendance_Record values(@a,@b,@c,@d,@ee,@f,@g,@h,@i,@j,@k,@l,@m,@n,@o", con); cmd.Parameters.AddWithValue("@a",a); cmd.Parameters.AddWithValue("@b",b); cmd.Parameters.AddWithValue("@c",c); cmd.Parameters.AddWithValue("@d",d); cmd.Parameters.AddWithValue("@ee",ee); cmd.Parameters.AddWithValue("@f",f); cmd.Parameters.AddWithValue("@g",g); cmd.Parameters.AddWithValue("@h",h); cmd.Parameters.AddWithValue("@i",i); cmd.Parameters.AddWithValue("@j",j); cmd.Parameters.AddWithValue("@k",k); cmd.Parameters.AddWithValue("@l",l); cmd.Parameters.AddWithValue("@m",m); cmd.Parameters.AddWithValue("@n",n); cmd.Parameters.AddWithValue("@o",o); //SqlCommand cmd=new SqlCommand("insert into Student_Attendance_Record values('"+r.Cells[0].Value.ToString()+"','"+r.Cells[1].Value.ToString()+"','"+r.Cells[2].Value.ToString()+"','"+r.Cells[3].Value.ToString()+"','"+r.Cells[4].Value.ToString()+"','"+r.Cells[5].Value.ToString()+"','"+r.Cells[6].Value.ToString()+"','"+r.Cells[7].Value.ToString()+"','"+r.Cells[8].Value.ToString()+"','"+r.Cells[9].Value.ToString()+"','"+r.Cells[10].Value.ToString()+"','"+r.Cells[11].Value.ToString()+"','"+r.Cells[12].Value.ToString()+"','"+r.Cells[13].Value.ToString()+"','"+r.Cells[14].Value.ToString()+"'",con); cmd.ExecuteNonQuery(); } con.Close(); }

Vikas Ahlawat · Answer

private void button1_Click(object sender, EventArgs e) { con.Open(); DataSet ds = new DataSet(); foreach(DataGridViewRow r in dataGridView1.Rows) { SqlCommand cmd=new SqlCommand("insert into Student_Attendance_Record values('"+r.Cells[0].Value.ToString()+"','"+r.Cells[1].Value.ToString()+"','"+r.Cells[2].Value.ToString()+"','"+r.Cells[3].Value.ToString()+"','"+r.Cells[4].Value.ToString()+"','"+r.Cells[5].Value.ToString()+"','"+r.Cells[6].Value.ToString()+"','"+r.Cells[7].Value.ToString()+"','"+r.Cells[8].Value.ToString()+"','"+r.Cells[9].Value.ToString()+"','"+r.Cells[10].Value.ToString()+"','"+r.Cells[11].Value.ToString()+"','"+r.Cells[12].Value.ToString()+"','"+r.Cells[13].Value.ToString()+"','"+r.Cells[14].Value.ToString()+"'",con); cmd.ExecuteNonQuery(); } con.Close(); } this is also giveing error

Vikas Ahlawat · Answer

in the code which give error is working when i replace " + dataGridView1.Rows[i].Cells[5].Value.ToString() + " with some simple string like values('abc','aaa',........

Vikas Ahlawat · Answer

But the below code working private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e) { Retrive_Data rd = new Retrive_Data(); SqlConnection con = new SqlConnection("Data Source =.\SQLEXPRESS;Initial Catalog = schoolmgt; password=; Integrated Security = true;"); con.Open(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("Retrive_S_AD1_DTL_Individual", con); da.SelectCommand.CommandType = CommandType.StoredProcedure; int i = 0; for (i = 0; i { SqlDataAdapter da1 = new SqlDataAdapter("Update Student_Exams_Marks_Detail set Paper1='" + dataGridView1.Rows[i].Cells[5].Value.ToString() + "', Paper2='" + dataGridView1.Rows[i].Cells[6].Value.ToString() + "',Paper3='" + dataGridView1.Rows[i].Cells[7].Value.ToString() + "',Paper4='" + dataGridView1.Rows[i].Cells[8].Value.ToString() + "',Paper5='" + dataGridView1.Rows[i].Cells[9].Value.ToString() + "',Paper6='" + dataGridView1.Rows[i].Cells[10].Value.ToString() + "',Paper7='" + dataGridView1.Rows[i].Cells[11].Value.ToString() + "',Paper8='" + dataGridView1.Rows[i].Cells[12].Value.ToString() + "',Paper9='" + dataGridView1.Rows[i].Cells[13].Value.ToString() + "',Paper10='" + dataGridView1.Rows[i].Cells[14].Value.ToString() + "' Where Regno=" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "and Exam='" + dataGridView1.Rows[i].Cells[4].Value.ToString() + "'", con); da1.Fill(ds); } con.Close(); //dataGridView1.DataSource = rd.Retrive_S_Exams_Marks_Detail(comboBox1.Text, comboBox2.Text, comboBox3.Text).Tables["Marks"]; } So why this working ?

Kirtan Patel · Answer

Friend, you should not write code like this way :) when you Insert Data in Database you dont need data Adapter ... just Execute insert query as i Shown in Example :) All the Best :)

Vikas Ahlawat · Answer

i m using following code con.Open(); DataSet ds = new DataSet(); for (int i = 0; i { SqlDataAdapter da0 = new SqlDataAdapter("insert into Student_Attendance_Record values('" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[2].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[3].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[4].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[5].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[6].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[7].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[8].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[9].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[10].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[11].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[12].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[13].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[14].Value.ToString() + "',)", con); da0.Fill(ds); } con.Close(); but it give error:-Object reference not set to an instance of an object. why?

Kirtan Patel · Answer

Hi friend, Here is Method how to do code to solve this type of problem for example we have dataGridView Filled with two columns ( username ,password) and also having Table Users with column (username,password) in database now lets seee how to fill dataGridView data in database . friend if it helps you please check " Do you like this answer " :) foreach ( DataGridViewRow r in dataGridView1.Rows) { string username = r.Cells[0].Value.ToString(); string password = r.Cells[1].Value.ToString(); //Connectt to Database //Code Insert Both values into database table SqlConnection con = new SqlConnection ( "ConnectionString" ); con.Open(); SqlCommand comm = new SqlCommand ( "insert into users values(@username,@password)" , con); comm.Parameters.AddWithValue( "@username" , username); comm.Parameters.AddWithValue( "@password" , password); comm.ExecuteNonQuery(); con.Close(); }

Vikas Ahlawat · Answer

You tell me about fill datagridview to database table . But i want to update database table through datagridview data.

Ibrahim Afane · Answer

Hello, I think all what you have to do is to bind data table to data grid view... you can find below code sample: private void Form1_Load ( object sender, System.EventArgs e ){ string connString = "server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI" ; string sql = @ "select * from employee" ; SqlConnection conn = new SqlConnection ( connString ) ; SqlDataAdapter da = new SqlDataAdapter ( sql, conn ) ; DataSet ds = new DataSet () ; da.Fill ( ds, "customers" ) ; // Bind the data table to the data grid dataGrid1.SetDataBinding ( ds, "customers" ) ; and you can refer to this link : http://msdn.microsoft.com/en-us/library/fbk67b6z.aspx Thanks,

How fill multiple row through datagridview to database?

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (17)