Update a SQL Record

[tdspaul]

I am trying to update a sequence of records where the field WebSvc = No

Here is the code

cn.Open()

        da = New SqlDataAdapter("SELECT * FROM dbo.DurexBOL", cn)


        cmdBuilder = New SqlCommandBuilder(da)

        da.Fill(ds, "DurexBOL")

        Do While ds.Tables("DurexBOL").Select("WebSvc = 'No'")

            ds.Tables("DurexBOL").Rows(0)("WebSvc") = "Yes"

            da.Update(ds, "DurexBOL")

        Loop

        Console.WriteLine("City updated successfully")

        cn.Close()

        Console.ReadLine()

[ChrisPadgham]

Why don't you write an update SQL to update the table directly rather than bring it into a ds.

eg

UPDATE DurexBOL SET WebSvc=Yes WHERE WebSvc=No

below is the code from the vb help file for executing non-query SQL

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub

[tdspaul]

Why don't you write an update SQL to update the table directly rather than bring it into a ds.

eg

UPDATE DurexBOL SET WebSvc=Yes WHERE WebSvc=No

below is the code from the vb help file for executing non-query SQL

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub

The reason why is I have several records at a time a need to call up one at a time to send to a different table. So as I send the info to the other table I change the websvc from no to yes so I don't repeat. Does this make sense?

[kvprajapati]

.....
        Dim Rows() As DataRow = Ds.Tables("DurexBOL").Select("WebSvc = 'No'")

        For Each row As DataRow In Rows
            row("WebSvc") = "Yes"
        Next
        da.Update(Ds, "DurexBOL")