I have a sql database that contains several thousand records that need to be numbered sequentially from 1 through the end. I have tried several different ways and nothing seems to hit the database. I've included my attempts and would greatly appreciate any help.
- Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
-
Dim cmdSave As New SqlClient.SqlCommand
-
'Dim iRC As Integer
-
Dim dsD As DataSet
-
Dim adD As SqlClient.SqlDataAdapter
-
Dim rowD As DataRow
-
Dim i As Integer = 1
-
Dim sD As String = "select * from table"
-
Dim sU As String = ""
-
Dim dsMax As DataSet
-
Dim adMax As SqlClient.SqlDataAdapter
-
Dim sMax As String = "select max(recid) + 1 as maxnum from table"
-
-
dsD = New DataSet
-
adD = New SqlClient.SqlDataAdapter(sD, gdbRecordSet)
-
adD.Fill(dsD, "stuff")
-
-
dsMax = New DataSet
-
adMax = New SqlClient.SqlDataAdapter(sMax, gdbRecordSet)
-
adMax.Fill(dsMax, "max")
-
-
Dim maxnum As Double = dsMax.Tables("max").Rows(0)(0)
-
-
For Each rowD In dsD.Tables("stuff").Rows
-
'rowD.AcceptChanges()
-
'rowD("recid") = i
-
'rowD.SetModified()
-
rowD.BeginEdit()
-
rowD("recid") = i
-
rowD.EndEdit()
-
-
If dsD.HasChanges Then
-
dsD.AcceptChanges()
-
'adD.Update(dsD, "stuff")
-
'adD.Update(dsD.GetChanges())
-
End If
-
'sU = "update table set recid = '" & i & "' where recid > '" & i & "'
-
i = i + 1
-
'cmdSave.Connection = gdbRecordSet
-
'cmdSave.CommandText = sU
-
'cmdSave.CommandType = CommandType.Text
-
'iRC = cmdSave.ExecuteNonQuery
-
Next
-
End Sub
