Click here to Skip to main content
14,775,067 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I am trying to do an insertion or update(based on if product exist on db)


IN case if count ==0, data should be inserted
Else if count ==1 data should be update (where qty is provided)

The update of qty for data that exist is being executed fine( count==1 case), but new product isn't being inserted if data doesn't exist( count==0 case)
What is wrong with code inside count ==1 block code

Thanks to everyone

What I have tried:

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

                        SqlCommand cmdkontrolla = new SqlCommand("barkodicheck", conn);
                        cmdkontrolla.CommandType = CommandType.StoredProcedure;
                        cmdkontrolla.Parameters.AddWithValue("@barkodi", Convert.ToString(dtgartikulli.Rows[i].Cells[0].Value));
                        SqlDataAdapter adapt = new SqlDataAdapter(cmdkontrolla);
                        DataSet ds = new DataSet();
                        adapt.Fill(ds);
                        conn.Close();
                        int count = ds.Tables[0].Rows.Count;
                        //nese është barazi me 1, shfaqe dritaren pasuese
                        if (count == 0)
                        {
                            SqlCommand cmdinsertimi = new SqlCommand("insertprodukti", conn);
                            cmdinsertimi.CommandType = CommandType.StoredProcedure;
                            SqlParameter ibarkodi = cmdinsertimi.Parameters.Add("@barkodi", SqlDbType.Int);
                            SqlParameter iemertimi = cmdinsertimi.Parameters.Add("@emertimi", SqlDbType.VarChar, 200);
                            SqlParameter isasia = cmdinsertimi.Parameters.Add("@sasia", SqlDbType.Int);
                            SqlParameter istoku = cmdinsertimi.Parameters.Add("@stoku", SqlDbType.Int);
                            SqlParameter icmimi = cmdinsertimi.Parameters.Add("@cmimi", SqlDbType.Float);
                            SqlParameter icmimif = cmdinsertimi.Parameters.Add("@cmimif", SqlDbType.Float);
                            SqlParameter ifitimi = cmdinsertimi.Parameters.Add("@fitimi", SqlDbType.Float);
                            SqlParameter itvsh = cmdinsertimi.Parameters.Add("@tvsh", SqlDbType.Float);
                            SqlParameter ikategoria = cmdinsertimi.Parameters.Add("@kategoria", SqlDbType.VarChar, 100);
                            SqlParameter idata = cmdinsertimi.Parameters.Add("@data", SqlDbType.DateTime);
                            SqlParameter ishtuarnga = cmdinsertimi.Parameters.Add("@shtuarnga", SqlDbType.VarChar, 100);
                            SqlParameter injesia = cmdinsertimi.Parameters.Add("@njesia", SqlDbType.VarChar, 50);
                            foreach (DataGridViewRow row in dtgartikulli.Rows)
                            {
                                if (!row.IsNewRow)
                                {

                                    ibarkodi.Value = row.Cells[0].Value;
                                    iemertimi.Value = row.Cells[1].Value;
                                    isasia.Value = "1";
                                    istoku.Value = row.Cells[3].Value;
                                    icmimi.Value = row.Cells[2].Value;
                                    icmimif.Value = row.Cells[4].Value;
                                    ifitimi.Value = row.Cells[5].Value;
                                    itvsh.Value = row.Cells[7].Value;
                                    ikategoria.Value = row.Cells[6].Value;
                                    idata.Value = DateTime.Now;
                                    ishtuarnga.Value = lbluseri.Text;
                                    injesia.Value = row.Cells[9].Value;
                                    cmdinsertimi.ExecuteNonQuery();


                                }
                            }


                        }
                        else if (count == 1)
                        {
                            for (int j = 0; j < dtgartikulli.Rows.Count; j++)
                            {

                                SqlCommand cmdeditimi = new SqlCommand("updatestokublerja", conn);
                                cmdeditimi.CommandType = CommandType.StoredProcedure;
                                cmdeditimi.Parameters.AddWithValue("@barkodi", Convert.ToString(dtgartikulli.Rows[j].Cells[0].Value));
                                cmdeditimi.Parameters.AddWithValue("@sasi", Convert.ToString(dtgartikulli.Rows[j].Cells[3].Value));
                                conn.Open();
                                cmdeditimi.ExecuteNonQuery();
                                conn.Close();

                            }
                        }
                        clear();
                        conn.Close();
                        dtgartikulli.DataSource = null;
                        dtgartikulli.Refresh();
                    }
                }
            }
        }

First i am checking if that barcode does exist(                    cmdkontrolla.Parameters.AddWithValue("@barkodi", Convert.ToString(dtgartikulli.Rows[i].Cells[0].Value));
)


SP CODE:

        ALTER procedure [dbo].[barkodicheck]
        @barkodi int

        as

        select Barkodi from tabela_produktet where
        Barkodi = @barkodi


        ALTER procedure [dbo].[updatestokublerja]
        @barkodi int,
        @sasi int

        as

        update tabela_produktet set Stoku = Stoku + @sasi
       where
       Barkodi = @barkodi
Posted
Updated 4-Nov-20 23:31pm
Comments
Gerry Schmitz 3-Nov-20 8:20am
   
You just "fire and forget". No status or error handling. No try ... catch. As far as we know, it works.
Member 14947303 4-Nov-20 5:09am
   
there are the try, catch and finally, but I didn't put these in question, because it limited me. Inside the try is the upper code, and in the catch is the exception ex error, and in the finally is the clear and close connection

But the whole code isnt; wroking as i said in the upper question
Member 14947303 4-Nov-20 6:16am
   
I tested the code. When there are rows only in count==0 case(the insertion process is done perfectly), but when there are multiple rows in both cases( count==0 and count=1), the insertion process isn't done, only the update process is done
Gerry Schmitz 4-Nov-20 10:50am
   
You say the insert doesn't work, but the update does.
1) How did you get a record to "update" if you can't insert.
2) If the insert is not working, why are showing only the SP's that work?
Member 14947303 4-Nov-20 12:49pm
   
You sir didn't understand the question or to be more clearly you didn't read the entire question. The question was and is:
First when I click the button save, the data on datagridview is goint to be checked if it exist in sql. If it exist (count ==1) the use update SP( update the old qty+ new qty), if data does not exist (count==0) then insert the data from datagridview.

1 solution

You might be interested in this article:
Please stop using this UPSERT anti-pattern - SQLPerformance.com[^]
   

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