Click here to Skip to main content
15,029,206 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi guys. Everything is working fine but when i input my excel file to my datagrid view and i update it to my database, it is only saving one row to my database and not the other 2. Why not?

images:
http://postimg.org/image/oj2zqbeyb/[^]
http://postimg.org/image/4jy6mv6at/[^]

C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Login
{
    public partial class EmployeeRota : Form
    {
        string con = "Data Source=dqq5ndqef2.database.windows.net;Initial Catalog=Login;Integrated Security=False;User ID=richardjacobs97;Password=*******;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
        SqlCommandBuilder scb;
        DataTable dt;

        public EmployeeRota()
        {
            InitializeComponent();
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                this.textBox1.Text = openFileDialog1.FileName;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string PathCpnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";";
            OleDbConnection conn = new OleDbConnection(PathCpnn);

            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + textBox2.Text + "$]", conn);
            DataTable dt = new DataTable();
            myDataAdapter.Fill(dt);
            dataGridView1.DataSource = dt;
            myDataAdapter.Update(dt);
        }

        private void EmployeeRota_Load(object sender, EventArgs e)
        {
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string connectionString = con;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("INSERT INTO Rota (Id, Name, DateWorking) Values (@Id, @Name, @Date)");
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                            cmd.Parameters.AddWithValue("@Id", dataGridView1.Rows[i].Cells["Id"].Value);
                            cmd.Parameters.AddWithValue("@Name", dataGridView1.Rows[i].Cells["Name"].Value);
                            cmd.Parameters.AddWithValue("@Date", dataGridView1.Rows[i].Cells["DateWorking"].Value);
                            dt = new DataTable();
                            sda.Fill(dt);
                            dataGridView1.DataSource = dt;
                        }
                    }
                }
            }
        }
Posted

Just move following section to the outside of the loop. It is refreshing the grid after 1st save operation and thus inserting only 1st row.
C#
dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;

Something like-
C#
SqlCommand cmd = new SqlCommand("INSERT INTO Rota (Id, Name, DateWorking) Values (@Id, @Name, @Date)",connection);
cmd.CommandType = CommandType.Text;
connection.Open();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {
       //Insert data
       cmd.Parameters.AddWithValue("@Id", dataGridView1.Rows[i].Cells["Id"].Value);
       cmd.Parameters.AddWithValue("@Name", dataGridView1.Rows[i].Cells["Name"].Value);
       cmd.Parameters.AddWithValue("@Date", dataGridView1.Rows[i].Cells["DateWorking"].Value);
       cmd.ExecuteNonQuery();
       cmd.Parameters.Clear()
     }

cmd.Dispose();
connection.Close();

//bind update data to grid
cmd = new SqlCommand("SELECT * FROM Rota"); //replace with actual query of yours
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;


Hope, it helps :)
   
v2
Comments
Member 12161319 23-Nov-15 12:54pm
   
Done that now i get this error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: The variable name '@Id' has already been declared. Variable names must be unique within a query batch or stored procedure.

any sugguestions?
Suvendu Shekhar Giri 23-Nov-15 14:15pm
   
Check the updated code.
Use this loop for insert data it will help

C#
  foreach (DataGridViewRow di in dataGridView1.Rows)
{
  write your insert query here or pass parameters of Sp.....

}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900