Saving data from datagridview to sql c#

Question

Getting problem while trying to save rows from datagridview to a SQL Server table using a stored procedure.

This is the c# code:

SqlConnection con = new SqlConnection("server= localhost;Database = Vehiculum ;integrated Security = true");
con.Open();

SqlCommand cmd = new SqlCommand("inserproducts", con);
cmd.CommandType = CommandType.StoredProcedure;

  SqlConnection con = new SqlConnection("server= localhost;Database = Vehiculum ;integrated Security = true");
        con.Open();
        SqlCommand cmd = new SqlCommand("insertservisi", con);
        cmd.CommandType = CommandType.StoredProcedure;

        for (int i = 0; i < dtgservisimi.Rows.Count; i++)
        {

            cmd.Parameters.AddWithValue("@kategoria", dtgservisimi.Rows[i].Cells[0].Value);
            cmd.Parameters.AddWithValue("@servisimi", dtgservisimi.Rows[i].Cells[1].Value);
            cmd.Parameters.AddWithValue("@barkodi", dtgservisimi.Rows[i].Cells[2].Value);
            cmd.Parameters.AddWithValue("@emertimi", dtgservisimi.Rows[i].Cells[3].Value);
            cmd.Parameters.AddWithValue("@sasia", dtgservisimi.Rows[i].Cells[4].Value);
            cmd.Parameters.AddWithValue("@garancioni", dtgservisimi.Rows[i].Cells[5].Value);



            cmd.Parameters.AddWithValue("@emri", txtemri.Text);
            cmd.Parameters.AddWithValue("@mbiemri", txtmbiemri.Text);
            cmd.Parameters.AddWithValue("@telefoniI", txttelefoniI.Text);
            cmd.Parameters.AddWithValue("@telefoniII", txttelefoniII.Text);
            cmd.Parameters.AddWithValue("@adresa", txtadresa.Text);
            cmd.Parameters.AddWithValue("@komuna", cmbkomuna.Text);
            cmd.Parameters.AddWithValue("@prodhuesi", cmbprodhuesi.Text);
            cmd.Parameters.AddWithValue("@modeli", cmbmodeli.Text);
            cmd.Parameters.AddWithValue("@motorri", cmbmotori.Text);
            cmd.Parameters.AddWithValue("@shasia", txtshasia.Text);
            cmd.Parameters.AddWithValue("@tabela", txttabela.Text);
            cmd.Parameters.AddWithValue("@viti", txtviti.Text);
            cmd.Parameters.AddWithValue("@shenime", txtshenime.Text);
            cmd.Parameters.AddWithValue("@data", DateTime.Now);
            cmd.Parameters.AddWithValue("@punetori", lbluser.Text);

        }
        cmd.ExecuteNonQuery();

        con.Close();

And the stored procedure code:

USE [Vehiculum] GO

         ALTER procedure [dbo].[insertservisi]
         @emri varchar(50) = null,
         @mbiemri varchar (50) =null,
         @telefoniI varchar (50) = null,
         @telefoniII varchar (50) = null,
         @adresa varchar (100) = null,
         @komuna varchar (50) = null,
         @prodhuesi varchar (50) = null,
         @modeli varchar (50) = null,
         @motorri varchar (50) = null,
         @shasia varchar (50) = null,
         @tabela varchar (50) = null,
         @viti varchar (50) = null,
         @kategoria varchar (100) = null,
         @servisimi varchar (20) = null,
         @barkodi int  = null,
         @emertimi varchar (max) = null,
         @sasia int = null,
         @garancioni varchar(200) = null,
         @shenime varchar(max) = null,

         @data datetime = null,
         @punetori varchar(100) = null


         as


        declare @id int;

        INSERT INTO 



        Servisimi_info
        (Emri,
        Mbiemri,
        TelefoniI,
        TelefoniII,
        Adresa,
        Komuna,
        Prodhuesi,
        Modeli,
        Motorri,
        Shasia,
        Tabela,
        Viti,
        Data_servisimit,
        Punetori)
         VALUES (@emri,
        @mbiemri,
        @telefoniI,
        @telefoniII,
        @adresa,
        @komuna,
        @prodhuesi,
        @modeli,
        @motorri,
        @shasia,
        @tabela,
        @viti,
        @data,
        @punetori)
     set @id = scope_identity();


         INSERT INTO Servisimi_produkti
        (Kategoria,
        Servisimi,
        Barkodi,
        Emertimi,
        Sasia,
        Garancion,
        Shenime,
        Id_servisimi)
         VALUES 

         (@kategoria,

        @servisimi,
        @barkodi,
        @emertimi,
        @sasia,
        @garancioni,
        @shenime,
        @id)

But I get this error .

Procedure or function has too many arguments specified

I am trying to store data data in two tables. The second table has a foreign key connected to primary key(first table). Also datagridview has multiple rows that has to be stored in second table with the same id(from table 1)

What went wrong with my code? Appreciate your assistance.

You should NOT call the .AddWithValue() inside the loop - this way, you keep adding more and more parameters, with each loop - bad ïdea! — marc_s
– marc_s, Commented Dec 27, 2019 at 20:52
cmd.ExecuteNonQuery(); move that inside your loop... :) you'll have to clear the params again coming around to add more.... — Trevor
– Trevor, Commented Dec 27, 2019 at 20:55
Did you clear the params before adding them again.. you can re-use the command. — Trevor
– Trevor, Commented Dec 27, 2019 at 20:59

marc_s · Accepted Answer · 2019-12-27 20:59:31Z

5

I assume (not clear from your question) that you want to call this stored procedure several times - inside a loop.

In that case (and all the time, really), you should define your parameters outside the loop, once, and then just set their value inside the loop and call the stored procedure.

Something like:

SqlConnection con = new SqlConnection("server= localhost;Database = Vehiculum ;integrated Security = true");

SqlCommand cmd = new SqlCommand("insertservisi", con);
cmd.CommandType = CommandType.StoredProcedure;

// *DEFINE* the parameters - once, before the loop - in the same order they're defined in the stored procedure!
cmd.Parameters.Add("@emri", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@mbiemri", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@telefoniI", SqlDbType.VarChar, 50);
// and so forth, for all your parameters

// now open connection, loop
con.Open();

for (int i = 0; i < dtgservisimi.Rows.Count; i++)
{
    // set the parameter values
    cmd.Parameters["@emri"].Value = txtemri.Text;
    cmd.Parameters["@mbiemri".Value = txtmbiemri.Text;
    cmd.Parameters["@telefoniI".Value =  txttelefoniI.Text;
    // and so forth, for all your parameters

    // execute the procedure
    cmd.ExecuteNonQuery();
}

con.Close();

answered Dec 27, 2019 at 20:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Le Reacher Over a year ago

and for the datagridview what code to use. Because i cant refer like this: cmd.Parameters["@kategoria"].Value = dtgservisimi.Rows[i].Cells[0].Value);

marc_s Over a year ago

@LeReacher: why not? That's exactly how you would reference data from the datagridview.....

Le Reacher Over a year ago

this is working, but why is inserting two times the same data. I mean that the data are being stored in two rows with same data

marc_s Over a year ago

@LeReacher: is the original datagridview showing each row twice? Or do you happen to call the .ExecuteNonQuery() method twice in your code?

Le Reacher Over a year ago

Marc_s the datagridview has only one row, but when i go to database there are two rows with identical data on it! the .ExecuteNonQuery() is inside the block of insert statement at the end of code

Collectives™ on Stack Overflow

Saving data from datagridview to sql c#

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related