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:
{
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)
But the main problem from above 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)). ONE -TO-MANY
Code edited :
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
