Using loop to edit values in database

19 May 2010 2:55 PM

gbubb

Hi,

I would like to loop through my database and edit a value. For some reason
the last value of the FOR loop is being saved. What should happen is that
the ZonePos field should be filled with value 1 to how many records in the
table. Currently lets say there are 5 records in the table, the value 5 is
saved in all the rows.

Here is my code:

Dim dSet As DataSet
        Dim dRow As DataRow
        Dim dTable As DataTable
        Dim sqlCmd As SqlCommand
        Dim sqlConn As SqlConnection
        Dim sqlDapter As SqlDataAdapter

        sqlConn = New SqlConnection("Data
Source=server;AttachDbFilename=xxxx; Integrated Security=true")
        sqlDapter = New SqlDataAdapter
        sqlCmd = New SqlCommand("SELECT * FROM Weighing", sqlConn)
        sqlDapter.SelectCommand = sqlCmd

        dSet = New DataSet
        sqlDapter.Fill(dSet, "Weighing")

        dTable = New DataTable
        dTable = dSet.Tables("Weighing")

        Dim i As Integer
        i = 1
        dRow = dTable.Rows(i)

        For i = 1 To dTable.Rows.Count - 1
            dRow = dTable.Rows(i)
            dRow.BeginEdit()
            dRow("ZonePos") = i
            dRow.EndEdit()

            sqlCmd = New SqlCommand("Update Weighing Set ZonePos = " & i & "",
sqlConn)
            sqlDapter.UpdateCommand = sqlCmd
            sqlDapter.Update(dSet.Tables("Weighing"))
        Next

19 May 2010 3:50 PM

Armin Zingler

Am 19.05.2010 16:55, schrieb gbubb:
Show quoteHide quote

> Hi,
>
> I would like to loop through my database and edit a value. For some reason
> the last value of the FOR loop is being saved. What should happen is that
> the ZonePos field should be filled with value 1 to how many records in the
> table. Currently lets say there are 5 records in the table, the value 5 is
> saved in all the rows.
>
> Here is my code:
>
> Dim dSet As DataSet
>         Dim dRow As DataRow
>         Dim dTable As DataTable
>         Dim sqlCmd As SqlCommand
>         Dim sqlConn As SqlConnection
>         Dim sqlDapter As SqlDataAdapter
>
>         sqlConn = New SqlConnection("Data
> Source=server;AttachDbFilename=xxxx; Integrated Security=true")
>         sqlDapter = New SqlDataAdapter
>         sqlCmd = New SqlCommand("SELECT * FROM Weighing", sqlConn)
>         sqlDapter.SelectCommand = sqlCmd
>
>         dSet = New DataSet
>         sqlDapter.Fill(dSet, "Weighing")
>
>         dTable = New DataTable
>         dTable = dSet.Tables("Weighing")

Here you're overwriting the dTable just created in the line before.

>         Dim i As Integer
>         i = 1
>         dRow = dTable.Rows(i)

What's the purpose of the 3 lines above?

> For i = 1 To dTable.Rows.Count - 1

The index of the row is zero-based. => for i=0 to ..

>             dRow = dTable.Rows(i)
>             dRow.BeginEdit()
>             dRow("ZonePos") = i
>             dRow.EndEdit()
>
>             sqlCmd = New SqlCommand("Update Weighing Set ZonePos = " & i & "",
> sqlConn)

Without a Where clause, all records are updated. The constraint must be the
primary key of the table.

>             sqlDapter.UpdateCommand = sqlCmd
>             sqlDapter.Update(dSet.Tables("Weighing"))
>         Next
>

It's strongly recommended to use the SqlCommand's parameters property.

Anyway, without a specific sort order with the SELECT statement,
the ZonePos field is just any number without a meaning. If you're
trying to create a PK here, you should consider adding an autoincrement
field in the database instead. Otherwise you must specify
it in the update statement.

Above all, if you're using a DataTable, you can just change all
datarows, then call the DataAdapter's Update method _once_ only.
So, withouth knowing the meaning of the ZonePos field and the
existence of a PK, the shorter solution can be:

      Dim dSet As DataSet
      Dim dRow As DataRow
      Dim dTable As DataTable
      Dim sqlCmd As SqlCommand
      Dim sqlConn As SqlConnection
      Dim sqlDapter As SqlDataAdapter

      sqlConn = New SqlConnection("DataSource=server;AttachDbFilename=xxxx; Integrated Security=true")
      sqlDapter = New SqlDataAdapter
      sqlCmd = New SqlCommand("SELECT * FROM Weighing", sqlConn)
      sqlDapter.SelectCommand = sqlCmd

      dSet = New DataSet
      sqlDapter.Fill(dSet, "Weighing")

      dTable = dSet.Tables("Weighing")

      For i as integer = 0 To dTable.Rows.Count - 1
         dRow = dTable.Rows(i)
         dRow("ZonePos") = i
      Next

      sqlDapter.Update(dTable)

--
Armin

19 May 2010 5:48 PM

gbubb

Hi,

Thank you for the response. I'm not trying to create a PK with the ZonePos
field.
I tried running your code but receive the below error:

"Update requires a valid UpdateCommand when passed DataRow collection with
modified rows."

The select statement can be changed to
"SELECT * FROM Weighing ORDER BY Zone ASC, Score DESC"

Below is a table of what I'm trying to do (after the above Select):
Zone, Name, Qty, Weight and Score are being entered into the db on a form.
When I'm done with that I would like to compute a Competition Position. BUT,
I need to compute the posistion in the Zone first. So each Zone has a 1 - x
positions. THEN, each zone's 1st place, will form the competition postions 1,
2, 3 (I entered them in now for clarity). The highest Score of the three
1st places will get the 1st CompPos. The second highest score will get 2nd.
The highest score of the 2 ZonePositions will get 4th etc etc. After CompPos
have been calculated I need to calculate a Merrit Score which will count down
from 50 to 10.

Zone Name       Qty       Weight     Score      ZonePos     CompPos
Merrit
1    John       20             20          400               1               2
49
1    Will        10             10          200               2               5
46
1    Harold     15             15          300               3               8
43
2    Hendry    15             15          300               1               3
48
2    Peter     12             12          132               2               6
45
2    Richard    5               5          100               3               9
42
3    Andrew    40             40          800               1               1
50
3    Gary     40             30          700               2               4
47
3    Scott     30             30          600               3               7
44

I hope this makes sense. I'm open for any suggestions on how to do it better
or easier. I'm relative new to VB.net and know that it SHOULD be relative
easy to do, but trying it in a new language is something completely different

:-)

Thanks again

Regards

Armin Zingler wrote:
Show quoteHide quote

>Am 19.05.2010 16:55, schrieb gbubb:
>> Hi,
>>
>[quoted text clipped - 24 lines]
>>         dTable = New DataTable
>>         dTable = dSet.Tables("Weighing")
>
>Here you're overwriting the dTable just created in the line before.
>
>>         Dim i As Integer
>>         i = 1
>>         dRow = dTable.Rows(i)
>
>What's the purpose of the 3 lines above?
>
>>         For i = 1 To dTable.Rows.Count - 1
>
>The index of the row is zero-based. => for i=0 to ..
>
>>             dRow = dTable.Rows(i)
>>             dRow.BeginEdit()
>[quoted text clipped - 3 lines]
>>             sqlCmd = New SqlCommand("Update Weighing Set ZonePos = " & i & "",
>> sqlConn)
>
>Without a Where clause, all records are updated. The constraint must be the
>primary key of the table.
>
>>             sqlDapter.UpdateCommand = sqlCmd
>>             sqlDapter.Update(dSet.Tables("Weighing"))
>>         Next
>
>It's strongly recommended to use the SqlCommand's parameters property.
>
>Anyway, without a specific sort order with the SELECT statement,
>the ZonePos field is just any number without a meaning. If you're
>trying to create a PK here, you should consider adding an autoincrement
>field in the database instead. Otherwise you must specify
>it in the update statement.
>
>Above all, if you're using a DataTable, you can just change all
>datarows, then call the DataAdapter's Update method _once_ only.
>So, withouth knowing the meaning of the ZonePos field and the
>existence of a PK, the shorter solution can be:
>
>      Dim dSet As DataSet
>      Dim dRow As DataRow
>      Dim dTable As DataTable
>      Dim sqlCmd As SqlCommand
>      Dim sqlConn As SqlConnection
>      Dim sqlDapter As SqlDataAdapter
>
>      sqlConn = New SqlConnection("DataSource=server;AttachDbFilename=xxxx; Integrated Security=true")
>      sqlDapter = New SqlDataAdapter
>      sqlCmd = New SqlCommand("SELECT * FROM Weighing", sqlConn)
>      sqlDapter.SelectCommand = sqlCmd
>
>      dSet = New DataSet
>      sqlDapter.Fill(dSet, "Weighing")
>
>      dTable = dSet.Tables("Weighing")
>
>      For i as integer = 0 To dTable.Rows.Count - 1
>         dRow = dTable.Rows(i)
>         dRow("ZonePos") = i
>      Next
>
>      sqlDapter.Update(dTable)
>

19 May 2010 7:38 PM

Armin Zingler

Am 19.05.2010 19:48, schrieb gbubb:

> Hi,
>
> Thank you for the response. I'm not trying to create a PK with the ZonePos
> field.
> I tried running your code but receive the below error:
>
> "Update requires a valid UpdateCommand when passed DataRow collection with
> modified rows."

Sorry, forget that one:

      Dim cb As SqlCommandBuilder

      '...

      sqlDapter.SelectCommand = sqlCmd

      cb = New SqlCommandBuilder(sqlDapter)

The SqlCommandBuilder automatically creates the UpdateCommand as soon
as the SqlDataAdapter needs it for updating the database.

> The select statement can be changed to
> "SELECT * FROM Weighing ORDER BY Zone ASC, Score DESC"
>
> Below is a table of what I'm trying to do (after the above Select):
> Zone, Name, Qty, Weight and Score are being entered into the db on a form.
> When I'm done with that I would like to compute a Competition Position. BUT,
> I need to compute the posistion in the Zone first. So each Zone has a 1 - x
> positions. THEN, each zone's 1st place, will form the competition postions 1,
> 2, 3 (I entered them in now for clarity). The highest Score of the three
> 1st places will get the 1st CompPos. The second highest score will get 2nd.
> The highest score of the 2 ZonePositions will get 4th etc etc. After CompPos
> have been calculated I need to calculate a Merrit Score which will count down
> from 50 to 10.

Is that solved with the ORDER BY clause you've added? (otherwise I have to
read it once more. ;-) )

--
Armin