Bulk Data update in a windows application using SQL update procedure

Question

I've created a windows application to display the data from SQL database to a gridview in a windows application. I used VB.net and SQL server to do that. While using where condition in the sql query I got the values which satisfies given data. Now I need to update the shown data. For that I used the sql query Update columnName = value where (the condition). the column name and value are got from the user using a textbox. When i used the query in Sql, it works fine and the value gets updated in database. But, When I run the same in VB code it doesn't gets updated in the windows application. I doesn't get it, and the assigned time for it runs beyond my watch time. Please, help me someone. Thanks in advance.Here I attach the code.

\ the code starts here \

Private Sub Btn_Edit_Click(sender As Object, e As EventArgs) Handles btn_Update.Click
        cmdupdate.Connection = connection
        connection.Open()
        'table.Rows.Clear()
        'table.Columns.Clear()

 cmdupdate.CommandText = "update tblWebaccessErrorTrack set @columnName = @editData where IssueDateTime between @frmdate and @todate and ErrorDetails like '%' + @errorDetails + '%' and  ClientIpaddress like '%'+ @IpAddress + '%' and ResponseStatus like '%'+ @responseStatus + '%' and TicketNo like '%' + @ticketNo + '%' select * from tblWebaccessErrorTrack"
        table.Clear()
        '"UPDATE tblWebaccessErrorTrack
        ' SET
        ' @columnName = @editData"
        '"sp_Updatedata"
        'Update tblWebaccessErrorTrack set TicketNo=202010 select * from tblWebaccessErrorTrack

        'cmdupdate.CommandType = CommandType.StoredProcedure
        cmdupdate.Parameters.Add("@frmdate", SqlDbType.DateTime).Value = frm_DateTimePicker.Text
        cmdupdate.Parameters.Add("@todate", SqlDbType.DateTime).Value = to_DateTimePicker.Text
        cmdupdate.Parameters.Add("@errorDetails", SqlDbType.NVarChar).Value = txtErrorDetails.Text
        cmdupdate.Parameters.Add("@IpAddress", SqlDbType.NVarChar).Value = txtClientIp.Text
        cmdupdate.Parameters.Add("@responseStatus", SqlDbType.NVarChar).Value = txtResponseStatus.Text
        cmdupdate.Parameters.Add("@ticketNo", SqlDbType.NVarChar).Value = txtTicketNo.Text
        cmdupdate.Parameters.Add("@columnName", SqlDbType.NVarChar).Value = Columnvalue '//column name
        cmdupdate.Parameters.Add("@editData", SqlDbType.NVarChar).Value = txtEditdata.Text '//Data we give

        adapter = New SqlDataAdapter(cmdupdate)

        adapter.Fill(table)
        adapter.Update(table)
        DataGridView1.DataSource = table
        'adapter.Fill(table)
        'table.Clear()


        'cmdupdate.CommandText = "select * from tblWebaccessErrorTrack" 'where IssueDateTime between @frmdate and @todate and ErrorDetails like '%' + @errorDetails + '%' and  ClientIpaddress like '%'+ @IpAddress + '%' and ResponseStatus like '%'+ @responseStatus + '%' and TicketNo like '%' + @ticketNo + '%'"
        'adapter = New SqlDataAdapter(cmdupdate)
        'adapter.Fill(table)
        'adapter.Update(table)
        'DataGridView1.DataSource = table
        ''adapter.Fill(table)
        ''cmdupdate.Parameters.Clear()
        'table.Clear()
        connection.Close()
        cmd.Parameters.Clear()

    End Sub`enter code here`

• the character(') present in front of some line represents commented line.

Answer 1

It will be easier if the code is split up into smaller pieces which each do one thing. It makes understanding and updating the code simpler. So, the methods to update a column and show the DGV would be separate.

For the SQL, please note that it is a bad idea to have just one SQL connection that gets re-used. There is a mechanism called connection pooling built in which makes the correct way of running a query a simple: create connection, use connection, dispose of the connection.

If you want to use a variable for the column name, then you have to concatenate it into the SQL query. This is open to SQL injection, so it is necessary to verify that a valid column name is being used before doing anything with it.

For the DateTimePickers, the value to use is the .Value, not the .Text, otherwise it could have problems with different Windows settings/locale.

So, this code is a starting point for getting it to work:

Private Function IsValidColumnName(colName As String) As Boolean
    'TODO: Put the valid column names in the array.
    Dim validColumnNames = {"Col1", "Col2", "ColWhatever"}
    Return validColumnNames.Any(Function(c) colName = c)

End Function

Private Sub UpdateDataInColumn(columnName As String, newData As String)
    If Not IsValidColumnName(columnName) Then
        Throw New Exception("Invalid column name [" & columnName & "]")
    End If

    Dim sql = "UPDATE tblWebaccessErrorTrack SET [" & columnName & "] & = @editData WHERE IssueDateTime BETWEEN @frmdate AND @todate AND ErrorDetails LIKE @errorDetails AND ClientIpaddress LIKE @IpAddress AND ResponseStatus LIKE @responseStatus AND TicketNo LIKE @ticketNo"

    Using conn As New SqlConnection("Your connection string"),
            cmdupdate As New SqlCommand(sql, conn)

        cmdupdate.Parameters.Add("@frmdate", SqlDbType.DateTime).Value = frm_DateTimePicker.Value
        cmdupdate.Parameters.Add("@todate", SqlDbType.DateTime).Value = to_DateTimePicker.Value
        cmdupdate.Parameters.Add("@errorDetails", SqlDbType.NVarChar).Value = "%" + txtErrorDetails.Text & "%"
        cmdupdate.Parameters.Add("@IpAddress", SqlDbType.NVarChar).Value = "%" + txtClientIp.Text & "%"
        cmdupdate.Parameters.Add("@responseStatus", SqlDbType.NVarChar).Value = "%" + txtResponseStatus.Text & "%"
        cmdupdate.Parameters.Add("@ticketNo", SqlDbType.NVarChar).Value = "%" + txtTicketNo.Text & "%"
        cmdupdate.Parameters.Add("@editData", SqlDbType.NVarChar).Value = newData '//Data we give

        Dim nChanged = cmdupdate.ExecuteNonQuery()

        If nChanged > 1 Then
            ' More than one row was updated. This is probably a Big Problem.
        End If

    End Using

End Sub

Private Sub ShowDataGridView()
    'TODO: write the ShowDataGridView() sub.
End Sub

Private Sub btn_Update_Click(sender As Object, e As EventArgs) Handles btn_Update.Click
    Dim colName = "Some method to select a column would go here."
    Dim newData = txtEditdata.Text

    UpdateDataInColumn(colName, newData)
    ShowDataGridView()

End Sub