Click here to Skip to main content
15,881,852 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
i have 2 tables let A & B 

i have to copy data from TblA to TblB using a sql sp in C#.

data's view in datagrid view & check box attached for selecting rows.

But when i am executing query all data of tblA copying to tblB 

but i want only selected row need to copy not all.


What I have tried:

SP
---
ALTER procedure [dbo].[sptblStockTransfer]

AS
Begin

insert into tblAssetMaster(SerialNumber,AssetType,HDDSize,RAMSize,MonitorType,AssetMake,AssetModel,InvoiceNumber,AssetStatus,WorkingStatus,InvoiceDate,Remarks)
select SerialNumber,AssetType,HDDSize,RAMSize,MonitorType,AssetMake,AssetModel,InvoiceNumber,AssetStatus,WorkingStatus,InvoiceDate,Remarks from tblStockinWard ;

End


 VS Code
------
 private void button1_Click(object sender, EventArgs e)
        {
            int inserted = 0;
            for (int i = 0; i <= dataGridView1.RowCount - 1; i++)
            {
                if (Convert.ToBoolean(dataGridView1.Rows[i].Cells["chk"].Value) == true)
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("sptblStockTransfer", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                inserted++;
            }
            if (inserted > 0)
            {
                MessageBox.Show(string.Format("{0} records inserted.", inserted), "Message");
            }
        }
Posted
Updated 19-Jul-20 7:30am
Comments
[no name] 19-Jul-20 13:15pm    
Think about to spend a parameter to your SP which tells the SP which data should be copied ;)
Satyabrata Samantaray 19-Jul-20 13:30pm    
hello Sir, can you please suggest me which parameter need to define in SP
[no name] 19-Jul-20 13:39pm    

insert into tblAssetMaster(...)
select ...from tblStockinWard ;

copies all rows from "tblStockinWard". So you need to pass a parameter to SP which restricts copy to a unique value of "tblStockinWard". This allows you to do something like this in SP. Pseudo Code:

insert into tblAssetMaster(...)
select ... from tblStockinWard
WHERE tblStockinWard.Id= paramValue;


I used tblStockinWard.Id but only you know which field of tblStockinWard is the unique idenitfier/primary key
Satyabrata Samantaray 19-Jul-20 14:12pm    
i made below changes in SP
------------------------
ALTER procedure [dbo].[sptblStockTransfer]
@SerialNumber varchar (25)

AS
Begin

insert into tblAssetMaster(SerialNumber,AssetType,HDDSize,RAMSize,MonitorType,AssetMake,AssetModel,InvoiceNumber,AssetStatus,WorkingStatus,InvoiceDate,Remarks)
select SerialNumber,AssetType,HDDSize,RAMSize,MonitorType,AssetMake,AssetModel,InvoiceNumber,AssetStatus,WorkingStatus,InvoiceDate,Remarks from tblStockinWard WHERE tblStockinWard.SerialNumber=@SerialNumber;

End
[no name] 19-Jul-20 14:22pm    
And now, did it solved your issue?

Btw: Next problem you probably will face (depending how tblAssetMaster is defined) is when already a same record exists there.

1 solution

Quote:
data's view in datagrid view & check box attached for selecting rows.
But when i am executing query all data of tblA copying to tblB
but i want only selected row need to copy not all.

Look at your code, how do you tell the procedure which record is to be copied ?
[Update]
Quote:
I am New here & little bit wired , can you please suggest which parameter need to pass

What do you display in datagrid ?.
How do you link a row in datagrid with the record it display ?
Assuming SerialNumber is the Id or record, Do you display it in DataGrid ? Do you keep track of which row in Datagrid is linked to record Id ?
On you check a Datagrid row is checked, you need to get the record Id and then call the stored procedure with the Id as parameter.
 
Share this answer
 
v2
Comments
Satyabrata Samantaray 19-Jul-20 13:38pm    
I am New here & little bit wired , can you please suggest which parameter need to pass
Satyabrata Samantaray 19-Jul-20 13:55pm    
*https://ibb.co/8dbnCSX
*Datagrid is linked
Patrice T 19-Jul-20 14:07pm    
Use Improve question to update your question.
So that everyone can pay attention to this information.

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