Update a specific column of dataset's table data to SQL database

Question

I have a gridview loaded with data from my database and a gridview swapping function which after exchanging data between rows with a column call "Priority", I want to save these changes back to my database.

Only the "Priority" column value will be change, how do I update only that Column of my dataset table to my SQL database? Please advice, thanks!

Code:

Protected Sub Gridviewselectbus_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)

        If e.CommandName = "Up" Then

            Dim index As Int16 = Convert.ToInt16(e.CommandArgument)
            If index = 0 Or index = 1 Then
                Exit Sub
            End If

            Dim objCampaignManagementTable As New CampaignManagementBLL
            Dim ds As DataSet = objCampaignManagementTable.SelectCampaignManagementTableListing()

            Dim dtr As DataRow = ds.Tables(0).Rows(index - 1)

            Dim dtrSwap As DataRow = ds.Tables(0).Rows(index - 2)
            Dim dc As DataColumn = ds.Tables(0).Columns(6)

            'Dim value As Int16 = Convert.ToInt16(dt.Rows(index)("Priority"))
            Dim value As Int16 = CType((dtr)(dc), Short)
            Dim temp1 As Int16 = value

            'Increases the selected row's priority
            dtr(dc) = value - 1

            'Decreases the priority of the row that is on top of the selected row by assigning 
            'the original selected row value to it.
            dtrSwap(dc) = value

            ds.Tables(0).DefaultView.Sort = "Priority"
            ds.Tables(0).AcceptChanges()
            dtNew = ds.Tables(0).Copy()
            uigvList.DataSource = ds.Tables(0)
            uigvList.DataBind()
            ds.Tables(0).AcceptChanges()

            For i As Integer = 0 To uigvList.Rows.Count - 1
                dtNew.Rows(i)("Code") = uigvList.Rows(i).Cells(1).Text
                dtNew.Rows(i)("Name") = uigvList.Rows(i).Cells(2).Text
                dtNew.Rows(i)("Type") = uigvList.Rows(i).Cells(3).Text
                dtNew.Rows(i)("ActiveDateFrom") = uigvList.Rows(i).Cells(4).Text
                dtNew.Rows(i)("ActiveDateTo") = uigvList.Rows(i).Cells(5).Text
                dtNew.Rows(i)("Priority") = uigvList.Rows(i).Cells(6).Text
            Next

          ' Update database
End if

  If e.CommandName = "down" Then

' Down code here

End Sub

Answer 1

take a look at this code this only updates particular column and then it updates sql server database also ..

i hope it will helps you..

Note : catDA Means Dataadapter....and this is only example...how to update .....

catDA.UpdateCommand = new OdbcCommand("UPDATE Categories SET CategoryName = ? " +
                                      "WHERE CategoryID = ?" , nwindConn);

catDA.UpdateCommand.Parameters.Add("@CategoryName", OdbcType.VarChar, 15, "CategoryName");

OdbcParameter workParm = catDA.UpdateCommand.Parameters.Add("@CategoryID", OdbcType.Int);
workParm.SourceColumn = "CategoryID";
workParm.SourceVersion = DataRowVersion.Original;

DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");    

DataRow cRow = catDS.Tables["Categories"].Rows[0];

cRow["CategoryName"] = "New Category";

DataRow[] modRows = catDS.Tables["Categories"].Select(null, null, DataViewRowState.ModifiedCurrent);
catDA.Update(modRows);