Click here to Skip to main content
15,069,838 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.
Satyabrata Samantaray 19-Jul-20 14:27pm
   
no not resolved. i didn't understand what i will write after serial number ?
con.Open();
SqlCommand cmd = new SqlCommand("sptblStockTransfer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SerialNumber" ,---------);
cmd.ExecuteNonQuery();
con.Close();
[no name] 19-Jul-20 14:31pm
   
Something like
cmd.Parameters.AddWithValue("@SerialNumber", dataGridView1.Rows[i].Cells["SerialNumber"].Value);
Satyabrata Samantaray 19-Jul-20 14:41pm
   
Thank You Sir. my issue got resolved. Thank you once again.
[no name] 19-Jul-20 14:48pm
   
You are welcome, at your service Sir

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.
   
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900