How to store multiple records in SQL Server using DataGridView

Question

I am trying to insert 5 records into a SQL Server database table from a DataGridView using C #.

From my code, it takes the input of several records, but only inserts the first record into the database. Can someone help me save 5 records in the database with the click of a save button?

Here is my code:

DataSet ds = new DataSet(); SqlConnection cs = new SqlConnection(@"Data Source=DELL-PC;Initial Catalog=Image_DB;Integrated Security=True"); SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(); BindingSource Input = new BindingSource(); DataView dview = new DataView(); private void Form1_Load(object sender, EventArgs e) { //create a DataGridView Image Column DataGridViewImageColumn dgvImage = new DataGridViewImageColumn(); //set a header test to DataGridView Image Column dgvImage.HeaderText = "Images"; dgvImage.ImageLayout = DataGridViewImageCellLayout.Stretch; DataGridViewTextBoxColumn dgvId = new DataGridViewTextBoxColumn(); dgvId.HeaderText = "ID"; DataGridViewTextBoxColumn dgvName = new DataGridViewTextBoxColumn(); dgvName.HeaderText = "Name"; dataGridView1.Columns.Add(dgvId); dataGridView1.Columns.Add(dgvName); dataGridView1.Columns.Add(dgvImage); dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; dataGridView1.RowTemplate.Height = 120; dataGridView1.AllowUserToAddRows = false; } // button add data to dataGridView // insert image from pictureBox to dataGridView private void btn_Add_Click(object sender, EventArgs e) { MemoryStream ms = new MemoryStream(); pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat); byte[] img = ms.ToArray(); dataGridView1.Rows.Add(txt_UserID.Text, txt_Name.Text, img); } // browse image in pictureBox1 Click private void pictureBox1_Click(object sender, EventArgs e) { OpenFileDialog opf = new OpenFileDialog(); opf.Filter = "Choose Image(*.jpg; *.png; *.gif)|*.jpg; *.png; *.gif"; if (opf.ShowDialog() == DialogResult.OK) { pictureBox1.Image = Image.FromFile(opf.FileName); } } private void btn_Save_Click(object sender, EventArgs e) { for (int i = 5; i < dataGridView1.Rows.Count; i++) { string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string insert_sql = "INSERT INTO Input(UserID, UserName, PassImage) VALUES ('" + col1 + "','" + col2 + "','" + col3 + "')"; this.getcom(insert_sql); } MessageBox.Show("Record Added"); } public SqlConnection GetSqlConnection() //connection function { string str_sqlcon = "Data Source=DELL-PC;Initial Catalog=Image_DB;Integrated Security=True"; SqlConnection mycon = new SqlConnection(str_sqlcon); mycon.Open(); return mycon; } public void getcom(string sqlstr) //function for adding rows { SqlConnection sqlcon = this.GetSqlConnection(); // Watch out same string type as GetSQLConnection function SqlCommand sqlcom = new SqlCommand(sqlstr, sqlcon); sqlcom.ExecuteNonQuery(); sqlcom.Dispose(); sqlcon.Close(); sqlcon.Dispose(); } 

Answer 1

The problem is this loop for the loop , you are not using i in the for loop.

better try this.

 for (int i = 0; i < dataGridView1.Rows.Count; i++) { string col1 = dataGridView1.Rows[i].Cells[0].Value.ToString(); string col2 = dataGridView1.Rows[i].Cells[1].Value.ToString(); string col3 = dataGridView1.Rows[i].Cells[2].Value.ToString(); string insert_sql = "INSERT INTO Input(UserID, UserName, PassImage) VALUES ('" + col1 + "','" + col2 + "','" + col3 + "')"; this.getcom(insert_sql); } 

change the logic of the code if necessary.

Answer 2

 for (int i = 5; i < dataGridView1.Rows.Count; i++) 

change this to for (int i = 0; i < dataGridView1.Rows.Count; i++)

You assign 5 to i , so after one insert it will exit the loop, because i will become 6 .

also include "i" when specifying a datagridview..else row, it always points to one row and inserts 5 rows, but with the same value