CODEWITHSUNDEEP
sql-servervb.netstored-proceduresdatagridviewinsert-into
mohamed soliman
mohamed soliman

Reputation: 21

When I try to save data from table to another by datagridview

I have a datagridview read data from (t1) and I want to save data into another table (t2) with a procedure

create procedure [dbo].[saving_tasks]
    @task1 nvarchar(50),
    @task2 nvarchar(50),
    @task3 nvarchar(50)
as 
begin
    insert into t2 (task1, task2, task3)
    values (@task1, @task2, @task3)
end

enter image description here

And using this code to saving data from (t1. col1) checked rows to (t2.task1 and t2.task2 and t2.task3) 

Dim comm As New SqlCommand
    comm.Connection = sqlconn
    comm.CommandText = "saving_task"
    comm.CommandType = CommandType.StoredProcedure

    Dim i As Integer
    For i = 0 To DGV1.Rows.Count - 1
        If DGV1.Rows(i).Cells(0).Value = True Then

            comm.Parameters.AddWithValue("task1", DGV1.Rows(i).Cells(1).Value)
            comm.Parameters.AddWithValue("task2", DGV1.Rows(i).Cells(1).Value)
            comm.Parameters.AddWithValue("task3", DGV1.Rows(i).Cells(1).Value)
            'Else'

        End If

    Next
    sqlconn.Open()
    comm.ExecuteNonQuery()
    sqlconn.Close()
End Sub

And when I try to save I get error on the line comm.ExecuteNonQuery()

Upvotes: 0

Views: 55

Answers (1)

Steve
Steve

Reputation: 216342

The main error in your query is the fact that you call ExecuteNonQuery only after the end of the loop. This executes the command only one time and not for every row, so the main fix is to move the ExecuteNonQuery inside the loop.

However, if you try to reuse the same command for every row in your grid, you need to avoid adding the same parameter to the same command. You need to call 

 comm.Parameters.Clear()

before adding the same parameters in the loop, but you can also define the parameters before entering the loop and just change the value at each loop

Dim comm As New SqlCommand
comm.Connection = sqlconn
comm.CommandText = "saving_task"
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("@task1", SqlDbType.NVarChar, 50)        
comm.Parameters.Add("@task2", SqlDbType.NVarChar, 50)    
comm.Parameters.Add("@task3", SqlDbType.NVarChar, 50)    
sqlconn.Open()
Dim i As Integer
For i = 0 To DGV1.Rows.Count - 1
    If DGV1.Rows(i).Cells(0).Value = True Then
        comm.Parameters("@task1").Value = DGV1.Rows(i).Cells(1).Value
        comm.Parameters("@task2").Value = DGV1.Rows(i).Cells(2).Value
        comm.Parameters("@task3").Value = DGV1.Rows(i).Cells(3).Value
        comm.ExecuteNonQuery()
    End If
Next
sqlconn.Close()

(Notice that I suppose you have a typo in the index of the cell to save)

Upvotes: 1

Related Questions