Insert Multiple rows into gridview from database in asp.net c#

Question

User-367318540 posted

i want to insert rows from DB to gridview ,

I have two table PackMaster and PackDetail ,PackMaster table id is link with PackDetail table ,below is my data,

i will insert first ID 1111 with all Child table record(Pack Detail table) and then i will insert 1112 ID record into gridview 

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);

CREATE TABLE #PackMaster(OID INT,Entrydate DATETIME);
CREATE TABLE #PackDetail(DID INT,ODI INt,BID INT,CodeItem INT,QTY int);
   
INSERT INTO #ItemMasterFile VALUES
  (1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);
 
 INSERT INTO #Probale VALUES
(1001,1,1,001,100,'01-05-2019',null),
(1002,1,1,001,100,'01-06-2019',null),
(1003,3,1,001,200,'02-07-2019',null),
(1004,11,1,002,200,'03-08-2019',null),
(1005,10,1,002,200,'08-08-2019',null),
(1006,1,1,003,200,'08-08-2019',null),
(1007,1,1,003,200,'08-08-2019',null);

 Insert into #PackMaster values
 (1111,'01-05-2019'),
 (1112,'01-05-2019');

  Insert into #PackDetail values
 (1,1111,1001,1,1),
 (2,1111,1002,1,1),
(3,1111,1003,3,1),
(4,1111,1004,11,1),
 (5,1112,1005,10,1),
 (6,1112,1006,1,1),
(7,1112,1007,1,1),
(8,1112,1008,11,1);

Answer 1

User288213138 posted

Hi akhterr,

i will insert first ID 1111 with all Child table record(Pack Detail table) and then i will insert 1112 ID record into gridview 

According to your description, I couldn’t understand your requirement clearly.

What's the difference between getting the data with ID=1111 first and then getting the data with ID=1112 and getting the data with the whole table?

Do you mean you want to insert the data into gridview of the PackDetail table where ID =1111 or 1112?

If this is your requirement, you can use "select * from #ItemMasterFile" directly.

If I misunderstand your requirement, please post more details information about your requirement.

PackMaster table id is link with PackDetail table

If OID is the foreign key of PackMaster, then you need to set ODI as the primary key in PackDetail.

Best regards,

Sam

Answer 2

User-367318540 posted

Hi sam,

Do you mean you want to insert the data into gridview of the PackDetail table where ID =1111 or 1112?

Yes i want to add record into gridview where ID =1111 and then ID 1112 ,but note that 1111 record will remain in gridview and 1112 ID record will add below in gridview.

If OID is the foreign key of PackMaster, then you need to set ODI as the primary key in PackDetail.

yes OID is FK in PackDetail ,i will call data from DB on the behalf of OID then insert into gridivew.

Answer 3

User288213138 posted

Hi akhterr,

akhterr

but note that 1111 record will remain in gridview and 1112 ID record will add below in gridview.

I'm sorry, I can't understand what you mean. Can you describe your requirements in more detail?

Will the data in your GridView change with the data in the database?

Best regards,

Sam

Answer 4

User-367318540 posted

Hi Sam,

please below data will make you clear.

OID	Name	QTY
1111	A	1
1111	B	1
1111	C	1
1111	D	1
1111	E	1
1111	F	1
1111	G	1
1112	A	1
1112	B	1
1112	C	1
1112	D	1
1112	E	1

Answer 5

User288213138 posted

Hi akhterr,

OID	Name	QTY
1111	A	1
1111	B	1
1111	C	1
1111	D	1
1111	E	1
1111	F	1
1111	G	1
1112	A	1
1112	B	1
1112	C	1
1112	D	1
1112	E	1

Can you tell me how did you get the data of this table?

What is the relationship between these 4 tables?

I found that I can get the ODI (1111) in the PackDetail table by OID (111) in the PackMaster table, then get CodeItem in Probale table according to CodeItem in PackDetail table,

Finally, according to the CodeItem in the Probale table to get the Descriptionitem in the ItemMasterFile table. But does not match the data in your table.

Best regards,

Sam

Answer 6

User-367318540 posted

Hi Sam,

i just give you format to make you understand

Answer 7

User288213138 posted

Hi akhterr,

I still don't know the relationship between your 4 tables, but if you want to get the data above, you can try using left join () method.

The code:

Stored Procedure:

CREATE PROCEDURE  selectdata
AS
BEGIN
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #PackDetail(DID INT,ODI INt,BID INT,CodeItem INT,QTY int);
INSERT INTO #ItemMasterFile VALUES
  (1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);
Insert into #PackDetail values
 (1,1111,1001,1,1),
 (2,1111,1002,1,1),
(3,1111,1003,3,1),
(4,1111,1004,11,1),
 (5,1112,1005,10,1),
 (6,1112,1006,1,1),
(7,1112,1007,1,1),
(8,1112,1008,11,1);
select p.ODI,i.Descriptionitem as Name,p.QTY from #PackDetail p left join #ItemMasterFile i on p.CodeItem=i.CodeItem
END
GO

<asp:GridView ID="GridView1" runat="server"></asp:GridView>
protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constring))
                {
                    using (SqlCommand cmd = new SqlCommand("selectdata", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        con.Open();
                        IDataReader idr = cmd.ExecuteReader();
                        GridView1.DataSource = idr;
                        GridView1.DataBind();
                    }
                }

            }
        }

The result:

Best regards,

Sam

Answer 8

User-1716253493 posted

I guess you need to use insert into select command.

insert into tbl1 (a,b,c) select a+1,b,c from tbl1

or if you want include spesific id value

insert into tbl1 (a,b,c) select @param1,b,c from tbl1

you can use scope_identity() to get the new id

https://www.w3schools.com/sql/sql_insert_into_select.asp

https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql

Answer 9

User-367318540 posted

Thanks Sam alot