Click here to Skip to main content
14,692,047 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone.
I am trying to save data into two tables. Table 1 will handle generic data( date, teller, total) and table 2 will handle details data( like pname, pqty, pprice).
I am trying to save these data into two tables with same ID. For moment I am storing data in this way:



But the main problem from below code is that the bill number( NR_F) is stored via code, not automatically, which it is not a great option( due to clonflict data on dba)
I used the ID field( autonumber, identiy(1,1)). but the id isn't the same. As much product i insert , as much ID will be provided( and where i want to call these data,i cant call by id due to large count of id inserted).
The main question is how do i insert only one row in table 1(date, total, teller), and multiple rows in table 2 (product details), (related to each other by bill number(NR_F))

What I have tried:

{
           conn.Open();


           foreach (DataGridViewRow row in dtgprofatura.Rows)
           {
               if (!row.IsNewRow)
               {
                   SqlCommand cmd = new SqlCommand("insertprofatura", conn);
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.Clear();
                   cmd.Parameters.Add(new SqlParameter("@shenime", txtshenimi.Text));
                   cmd.Parameters.Add(new SqlParameter("@data", DateTime.Now));
                   cmd.Parameters.Add(new SqlParameter("@kasieri", lbluser.Text));
                   cmd.Parameters.Add(new SqlParameter("@NR_F", int.Parse(lblidfatura.Text)));
                   cmd.Parameters.Add(new SqlParameter("@emri_sh", lblshitesiemri.Text));
                   cmd.Parameters.Add(new SqlParameter("@niptsh", int.Parse(lblshitesinipt.Text)));
                   cmd.Parameters.Add(new SqlParameter("@adresash", lbladresashitesi.Text));
                   cmd.Parameters.Add(new SqlParameter("@telefonish", lblshitesitelefon.Text));
                   cmd.Parameters.Add(new SqlParameter("@emri_b", cmbbleresi.Text));
                   cmd.Parameters.Add(new SqlParameter("@niptb", lblbleresinipt.Text));
                   cmd.Parameters.Add(new SqlParameter("@adresab", lblbleresiadresa.Text));
                   cmd.Parameters.Add(new SqlParameter("@telefonib", lblbleresitelefoni.Text));
                   cmd.Parameters.Add(new SqlParameter("@nentotali", lblnentotali.Text));
                   cmd.Parameters.Add(new SqlParameter("@vleratvsh", lblvleratvsh.Text));
                   cmd.Parameters.Add(new SqlParameter("@zbritja", txtzbritja.Text));
                   cmd.Parameters.Add(new SqlParameter("@totali", lbltotali.Text));
                   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[3].Value));
                   cmd.Parameters.Add(new SqlParameter("@cmimi", row.Cells[2].Value));
                   cmd.Parameters.Add(new SqlParameter("@totaliPCS", row.Cells[5].Value));
                   cmd.Parameters.Add(new SqlParameter("@tvsh", row.Cells[4].Value));
                   cmd.Parameters.Add(new SqlParameter("@vleratvshpcs", row.Cells[7].Value));
                   cmd.Parameters.Add(new SqlParameter("@patvshpcs", row.Cells[6].Value));
                   cmd.ExecuteNonQuery();

               }
           }
       }
       catch (Exception ex)
       {
           MessageBox.Show("Procedimi i profatures deshtoi " + ex.ToString());
       }
       finally
       {
           conn.Close();
           clear();
           kRIJOToolStripMenuItem.PerformClick();
       }
and the SP used for insertation:
ALTER procedure [dbo].[insertprofatura]
        @shenime varchar(max),
        @data datetime,
        @kasieri varchar(50),
        @NR_F int,
        @emri_sh varchar(50),
        @niptsh varchar(50),
        @adresash varchar(100),
        @telefonish varchar(50),
        @emri_b varchar(50),
        @niptb varchar(50),
        @adresab varchar(100),
        @telefonib varchar(50),
        @nentotali float,
        @zbritja float,
        @vleratvsh float,
        @totali float,
        @barkodi int,
        @emertimi varchar(200),
        @sasia int, 
        @cmimi float,
        @totaliPCS float,
        @tvsh float,
        @vleratvshpcs float,
        @patvshpcs float

        as

        insert into tblprofatura         (NR_F,Shenime,Data,Kasieri,Emri_sh,NIPT_sh,Adresa_sh,Telefoni_sh,Emri_b,NIPT_b,Adresa_b,Telefoni_b,Nentot        ali,Zbritja,VleraTVSH,Totali)
			        values(@NR_F,@shenime,@data,@kasieri,@emri_sh,@niptsh,@adresash,@telefonish,@emri_b,@niptb,@adresab,@telefonib,@nentotali,@zbritja,@vleratvsh,@totali)
        insert into tblproofatura_details(NR_F,Barkodi,Emertimi,Cmimi,Sasia,TVSH,Totali,PaTVSHpcs,VleraTVSHpcs)
        values (@NR_F,@barkodi,@emertimi,@cmimi,@sasia,@tvsh,@totaliPCS,@patvshpcs,@vleratvsh)
Posted
Updated 28-Oct-20 2:03am
v3

1 solution

Insert into your tblprofatura first then capture the generated ID using SCOPE_IDENTITY[^].
Then insert the detail information into tblproofatura_details with the captured id

Here is a simplified example:
declare @master table (masterId int identity(1,1), MasterSummary nvarchar(20));
declare @lastId int;
insert into @master (MasterSummary) values ('Testing1')
set @lastId = SCOPE_IDENTITY();

declare @detail table(myId int identity(1,1), fk_to_master int, myDetail nvarchar(255))
insert into @detail (fk_to_master, myDetail) values (@lastId, 'Test details 1');
insert into @detail (fk_to_master, myDetail) values (@lastId, 'Test details 2');
select * from @master
select * from @detail
which gives the results
masterId	mastersummary
1		Testing1
and
myId	fk_to_master	myDetail
1		1		Test details 1
2		1		Test details 2
   
v2
Comments
Member 14947303 28-Oct-20 7:18am
   
thank you @CHill60
CHill60 28-Oct-20 7:41am
   
My pleasure
Member 14947303 28-Oct-20 11:28am
   
I am using now this SP code,

ALTER procedure [dbo].[insertimi]

@shenime varchar(max),
@data datetime,
@kasieri varchar(50),
@emri_sh varchar(50),
@niptsh varchar(50),
@adresash varchar(100),
@telefonish varchar(50),
@emri_b varchar(50),
@niptb varchar(50),
@adresab varchar(100),
@telefonib varchar(50),
@nentotali float,
@zbritja float,
@vleratvsh float,
@totali float,
@barkodi int,
@emertimi varchar(200),
@sasia int,
@cmimi float,
@totaliPCS float,
@tvsh float,
@vleratvshpcs float,
@patvshpcs float
as


declare @lastId int;
insert into tblprofatura (Shenime,Data,Kasieri,Emri_sh,NIPT_sh,Adresa_sh,Telefoni_sh,Emri_b,NIPT_b,Adresa_b,Telefoni_b,Nentotali,Zbritja,VleraTVSH,Totali)
values(@shenime,@data,@kasieri,@emri_sh,@niptsh,@adresash,@telefonish,@emri_b,@niptb,@adresab,@telefonib,@nentotali,@zbritja,@vleratvsh,@totali)
set @lastId = SCOPE_IDENTITY();


insert into tblproofatura_details(NR_F,Barkodi,Emertimi,Cmimi,Sasia,TVSH,Totali,PaTVSHpcs,VleraTVSHpcs)
values (@lastId,@barkodi,@emertimi,@cmimi,@sasia,@tvsh,@totaliPCS,@patvshpcs,@vleratvsh)

But now inn my parent table for example are being inserted three rows with different ID, and in my child table none row is being inserted. I want in my parent table one row with generic data to be inserted and ni my child table as many rows needed related to parent ID
CHill60 28-Oct-20 11:47am
   
Looking at your C# code you are calling the SP for every row in dtgprofatura - perhaps you should insert the data you want into tblprofatura with one SP which returns the ID and then in the loop call an SP that writes the detailed rows.

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