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(@aa); cmd.Parameters.AddWithValue(@bb); cmd.Parameters.AddWithValue(@cc); cmd.Parameters.AddWithValue(@dd); cmd.Parameters.AddWithValue(@eeee); cmd.Parameters.AddWithValue(@ff); cmd.Parameters.AddWithValue(@gg); cmd.Parameters.AddWithValue(@hh); cmd.Parameters.AddWithValue(@ii); cmd.Parameters.AddWithValue(@jj); cmd.Parameters.AddWithValue(@kk); cmd.Parameters.AddWithValue(@ll); cmd.Parameters.AddWithValue(@mm); cmd.Parameters.AddWithValue(@nn); cmd.Parameters.AddWithValue(@oo); //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 (usernamepassword) 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