Click here to Skip to main content
14,936,944 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone. 
I am working with a small POS application , and after the payment data are stored into two tables. From the form data are stored into two tables from two group of data : 
1. Textboxes (general data like date, worker , total etc)-- saved in table 1
2. Datagridview (Product informations like code, name, qty , price etc) -- saved in table 

Data are being stored into each tables, for in table one there are much rows inserted as the datagridview hass rows. To be more clearer : if datagridview has 5 rows stored in tables 2, there are also 5 rows stored in table one with same data repeated from textboxes.

The code I used to insert is showed below:

        try
        {
        conn.Open();
         foreach (DataGridViewRow row in dtgartikuj.Rows)
         {
         if (!row.IsNewRow)
              {
               SqlCommand cmd = new SqlCommand("insertfaturimi", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@nrfatures", int.Parse(txtnrfatures.Text)));
        cmd.Parameters.Add(new SqlParameter("@klienti", cmbklienti.Text));
        cmd.Parameters.Add(new SqlParameter("@shenime", txtshenime.Text));
        cmd.Parameters.Add(new SqlParameter("@pagesa", faturimi));
        cmd.Parameters.Add(new SqlParameter("@nentotali", txttotali.Text));
        cmd.Parameters.Add(new SqlParameter("@zbritje", txtzbritja.Text));
        cmd.Parameters.Add(new SqlParameter("@totali", totali.Text));
        cmd.Parameters.Add(new SqlParameter("@vleratvsh", textBox1.Text));
        cmd.Parameters.Add(new SqlParameter("@nrartikujve", lblnumri.Text));
        cmd.Parameters.Add(new SqlParameter("@kasieri", lbluser.Text));
        cmd.Parameters.Add(new SqlParameter("@koha", DateTime.Now));
        cmd.Parameters.Add(new SqlParameter("@barkodi", row.Cells[0].Value));
        cmd.Parameters.Add(new SqlParameter("@emertimi", row.Cells[1].Value));
        cmd.Parameters.Add(new SqlParameter("@sasia", row.Cells[2].Value));
        cmd.Parameters.Add(new SqlParameter("@tvsh", row.Cells[3].Value));
        cmd.Parameters.Add(new SqlParameter("@cmimi", row.Cells[4].Value));
        cmd.Parameters.Add(new SqlParameter("@totalipcs", row.Cells[5].Value));
        cmd.Parameters.Add(new SqlParameter("@vlerapatvshpcs", row.Cells[6].Value));
        cmd.Parameters.Add(new SqlParameter("@vleraetvshpcs", row.Cells[7].Value));
        cmd.ExecuteNonQuery();
        }


                }
            }
            catch(Exception ex)
            {
                MessageBox.Show("Faturimi deshtoi" + ex.ToString());
            }
            finally
            {
                conn.Close();
                clear();
                button5.PerformClick();
            }
        }
     <pre lang="c#">try
        {
        conn.Open();
         foreach (DataGridViewRow row in dtgartikuj.Rows)
         {
         if (!row.IsNewRow)
              {
               SqlCommand cmd = new SqlCommand("insertfaturimi", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@nrfatures", int.Parse(txtnrfatures.Text)));
        cmd.Parameters.Add(new SqlParameter("@klienti", cmbklienti.Text));
        cmd.Parameters.Add(new SqlParameter("@shenime", txtshenime.Text));
        cmd.Parameters.Add(new SqlParameter("@pagesa", faturimi));
        cmd.Parameters.Add(new SqlParameter("@nentotali", txttotali.Text));
        cmd.Parameters.Add(new SqlParameter("@zbritje", txtzbritja.Text));
        cmd.Parameters.Add(new SqlParameter("@totali", totali.Text));
        cmd.Parameters.Add(new SqlParameter("@vleratvsh", textBox1.Text));
        cmd.Parameters.Add(new SqlParameter("@nrartikujve", lblnumri.Text));
        cmd.Parameters.Add(new SqlParameter("@kasieri", lbluser.Text));
        cmd.Parameters.Add(new SqlParameter("@koha", DateTime.Now));
        cmd.Parameters.Add(new SqlParameter("@barkodi", row.Cells[0].Value));
        cmd.Parameters.Add(new SqlParameter("@emertimi", row.Cells[1].Value));
        cmd.Parameters.Add(new SqlParameter("@sasia", row.Cells[2].Value));
        cmd.Parameters.Add(new SqlParameter("@tvsh", row.Cells[3].Value));
        cmd.Parameters.Add(new SqlParameter("@cmimi", row.Cells[4].Value));
        cmd.Parameters.Add(new SqlParameter("@totalipcs", row.Cells[5].Value));
        cmd.Parameters.Add(new SqlParameter("@vlerapatvshpcs", row.Cells[6].Value));
        cmd.Parameters.Add(new SqlParameter("@vleraetvshpcs", row.Cells[7].Value));
        cmd.ExecuteNonQuery();
        }


                }
            }
            catch(Exception ex)
            {
                MessageBox.Show("Faturimi deshtoi" + ex.ToString());
            }
            finally
            {
                conn.Close();
                clear();
                button5.PerformClick();
            }
        }

try
{
conn.Open();
foreach (DataGridViewRow row in dtgartikuj.Rows)
{
if (!row.IsNewRow)
{
SqlCommand cmd = new SqlCommand("insertfaturimi", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@nrfatures", int.Parse(txtnrfatures.Text)));
cmd.Parameters.Add(new SqlParameter("@klienti", cmbklienti.Text));
cmd.Parameters.Add(new SqlParameter("@shenime", txtshenime.Text));
cmd.Parameters.Add(new SqlParameter("@pagesa", faturimi));
cmd.Parameters.Add(new SqlParameter("@nentotali", txttotali.Text));
cmd.Parameters.Add(new SqlParameter("@zbritje", txtzbritja.Text));
cmd.Parameters.Add(new SqlParameter("@totali", totali.Text));
cmd.Parameters.Add(new SqlParameter("@vleratvsh", textBox1.Text));
cmd.Parameters.Add(new SqlParameter("@nrartikujve", lblnumri.Text));
cmd.Parameters.Add(new SqlParameter("@kasieri", lbluser.Text));
cmd.Parameters.Add(new SqlParameter("@koha", DateTime.Now));
cmd.Parameters.Add(new SqlParameter("@barkodi", row.Cells[0].Value));
cmd.Parameters.Add(new SqlParameter("@emertimi", row.Cells[1].Value));
cmd.Parameters.Add(new SqlParameter("@sasia", row.Cells[2].Value));
cmd.Parameters.Add(new SqlParameter("@tvsh", row.Cells[3].Value));
cmd.Parameters.Add(new SqlParameter("@cmimi", row.Cells[4].Value));
cmd.Parameters.Add(new SqlParameter("@totalipcs", row.Cells[5].Value));
cmd.Parameters.Add(new SqlParameter("@vlerapatvshpcs", row.Cells[6].Value));
cmd.Parameters.Add(new SqlParameter("@vleraetvshpcs", row.Cells[7].Value));
cmd.ExecuteNonQuery();
}


}
}
catch(Exception ex)
{
MessageBox.Show("Faturimi deshtoi" + ex.ToString());
}
finally
{
conn.Close();
clear();
button5.PerformClick();
}
}


And stored procedures I used is showed below:

ALTER procedure [dbo].[insertfaturimi]
@nrfatures int,
@barkodi int,
@emertimi varchar (max),
@sasia int,
@tvsh float,
@cmimi float,
@totalipcs float,
@vlerapatvshpcs float,
@vleraetvshpcs float,
@klienti varchar(100),
@pagesa varchar (200),
@shenime varchar (200),
@nrartikujve int,
@vleratvsh float,
@nentotali float,
@zbritje float,
@totali float,
@kasieri varchar(50),
@koha datetime


as
begin

insert into tbl_faturimi(ID_FATURES, Klienti,Shenime,Pagesa,Nentotali,Zbritja,Totali,VleraTVSH,NR_artikujve,Kasieri, Data) values
(@nrfatures,@klienti,@shenime,@pagesa,@nentotali,@zbritje,@totali,@vleratvsh,@nrartikujve,@kasieri,@koha)

insert into tblfaturimi_details (NR_FATURES,Barkodi,Emertimi,Cmimi,Sasia,TVSH,Totali,VleraeTVSH,VleraPaTVSH)values
(@nrfatures,@barkodi,@emertimi,@cmimi,@sasia,@tvsh,@totalipcs,@vleraetvshpcs,@vlerapatvshpcs)

end
SQL
ALTER procedure [dbo].[insertfaturimi]
        @nrfatures int,
        @barkodi int,
        @emertimi varchar (max),
        @sasia int,
        @tvsh float,
        @cmimi float,
        @totalipcs float,
        @vlerapatvshpcs float,
        @vleraetvshpcs float,
        @klienti varchar(100),
        @pagesa varchar (200),
        @shenime varchar (200),
        @nrartikujve int,
        @vleratvsh float,
        @nentotali float,
        @zbritje float,
        @totali float,
        @kasieri varchar(50),
        @koha datetime


        as 
        begin

        insert into tbl_faturimi(ID_FATURES, Klienti,Shenime,Pagesa,Nentotali,Zbritja,Totali,VleraTVSH,NR_artikujve,Kasieri, Data) values
        (@nrfatures,@klienti,@shenime,@pagesa,@nentotali,@zbritje,@totali,@vleratvsh,@nrartikujve,@kasieri,@koha)

        insert into tblfaturimi_details (NR_FATURES,Barkodi,Emertimi,Cmimi,Sasia,TVSH,Totali,VleraeTVSH,VleraPaTVSH)values
        (@nrfatures,@barkodi,@emertimi,@cmimi,@sasia,@tvsh,@totalipcs,@vleraetvshpcs,@vlerapatvshpcs)
									
         end

ALTER procedure [dbo].[insertfaturimi]
@nrfatures int,
@barkodi int,
@emertimi varchar (max),
@sasia int,
@tvsh float,
@cmimi float,
@totalipcs float,
@vlerapatvshpcs float,
@vleraetvshpcs float,
@klienti varchar(100),
@pagesa varchar (200),
@shenime varchar (200),
@nrartikujve int,
@vleratvsh float,
@nentotali float,
@zbritje float,
@totali float,
@kasieri varchar(50),
@koha datetime


as
begin

insert into tbl_faturimi(ID_FATURES, Klienti,Shenime,Pagesa,Nentotali,Zbritja,Totali,VleraTVSH,NR_artikujve,Kasieri, Data) values
(@nrfatures,@klienti,@shenime,@pagesa,@nentotali,@zbritje,@totali,@vleratvsh,@nrartikujve,@kasieri,@koha)

insert into tblfaturimi_details (NR_FATURES,Barkodi,Emertimi,Cmimi,Sasia,TVSH,Totali,VleraeTVSH,VleraPaTVSH)values
(@nrfatures,@barkodi,@emertimi,@cmimi,@sasia,@tvsh,@totalipcs,@vleraetvshpcs,@vlerapatvshpcs)

end



I think the error is from the loop , because inside the loop is also the textbox included not just the datagridview. I tried to put the code that insert the textboxes outside the loop , but i get this error:**“Procedure or function has too many arguments specified”**
Could some one tell what to change in code.
Thank you!!!

What I have tried:

The code I tried its inside the question
Posted
Updated 8-Apr-20 4:25am
v2
Comments
CHill60 8-Apr-20 10:05am
   
Why are you using cmd.Parameters.AddWithValue for some of the parameters but cmd.Parameters.Add(new SqlParameter( for others? AddWithValue I think is the recommended approach
Jörgen Andersson 8-Apr-20 15:18pm
   
No, AddWithValue is NOT the recommended approach, more info on why here: https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/
tldr; AddWithValue might assume the wrong type if it's making the wrong guess from the value which may or may not create an error.
CHill60 9-Apr-20 3:51am
   
Trust me to get that completely the wrong way around!
Fortunately the OP must have read your comment and went the right way.
Thanks for the correction.
Jörgen Andersson 9-Apr-20 4:06am
   
No worries.

I don't have a problem understanding why people want to use a simpler to use method, what I don't understand is why MS doesn't fix it. (Or actually remove it)
[no name] 8-Apr-20 10:18am
   
I changed the code , but still the same problem as mentioned above
Jörgen Andersson 9-Apr-20 4:19am
   
What error do you get when you DON'T insert the textboxes outside the loop?
[no name] 9-Apr-20 7:03am
   
@Jörgen Andersson, - Procedure or function has too many arguments specified

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