0

I’m using Visual Studio with DevExpress Winforms in conjunction with Microsoft SQL Server Manager, and I’m currently trying to write a stored procedure that will activate on a button click, copying the focused row to a different table.

I’ve looked into it and haven’t been able to find a solution that does what I need it to. I would prefer not to use checkboxes if possible, but am open to it.

My current code within the stored procedure is:

CREATE PROCEDURE [dbo].[procedurename]
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT * FROM tbl_b)
        INSERT INTO tbl_b (column names here)
            SELECT DISTINCT (column names here)
            FROM tbl_a
END

I’ve been able to successfully call this procedure when testing the program with the button, and it functions mostly as intended, moving the data from one table to the other with no duplicates, however it moves the entire table when I only want to move a focused row.

I’m using gridView for this, and I’ve tried solutions that use DataGridView that don’t seem to work.

This is a desktop based program.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
neoneclectica
  • 5
  • 6
  • 2
    it is very nice to hear that "This is a desktop based program.", but in case you need " I only want to move a focused row" you should inform the stored procedure about the focused row, e.g. add SELECT DISTINCT (column names here) FROM tbl_a where the_focused_row=..... – Sergey Jun 25 '21 at 12:46

1 Answers1

1

You need a parameter in your stored procedure that holds an identification of the row you want to copy

CREATE PROCEDURE [dbo].[procedurename] (@ID int)
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT * FROM tbl_b)
      INSERT INTO tbl_b (column names here)
        SELECT DISTINCT (column names here)
        FROM tbl_a
        where tbl_A.PrimaryKeyColumn = @ID
END

In the Click event of your button you could then do something like this

var id = myGridView.GetRowCellValue(myGridView.FocusedRowHandle, "YourPrimaryKeyName");

and now you can use this id variable to pass to your stored procedure

However, you do have another problem in your stored procedure

    IF NOT EXISTS (SELECT * FROM tbl_b)

This check will never allow you to do your insert once there is 1 or more rows in that table.

I think you need something like

    IF NOT EXISTS (SELECT 1 FROM tbl_b where tbl_b.PrimaryKey = @ID)
GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • Thank you, this is really helpful. However I am having an issue where I continue to get an SQL exception where it says I’m not passing in a parameter, when I’ve followed the method to pass the parameter in. Do you have any suggestions? – neoneclectica Jun 25 '21 at 13:46
  • Without more information I can only guess. I suggest you create a new question on SO and put the code of the procedure and the code how you call the procedure there. Also put the exact error message there – GuidoG Jun 25 '21 at 14:42
  • it says I have reached an apparent question limit even though I have only asked one question today, do you know any workarounds for this? – neoneclectica Jun 25 '21 at 15:27
  • Can you edit your question to include the code in which you call your stored procedure and set the parameter? – Brendon Jun 28 '21 at 13:07