Updating Field in DataSet

[Gary]

I have a date field in a Dataset. I need to change the
date field for display purposes so I would like to loop
through the dataset and modify the field before I bind it
to a grid. Here is the code I have so far:

--------------------------------------------------
Dim cmdstring As String = "Select [date], [time],
endtime, [desc], con_id from Notes where con_id = '" &
Label3.Text & "'"
Dim da As New SqlClient.SqlDataAdapter

Dim ds As New DataSet
Dim drCurrent As System.Data.DataRow

Dim daNotes1 As New SqlClient.SqlDataAdapter
(cmdstring, Me.SqlConnection2)
daNotes1.Fill(ds, "Notes")

Dim RowLoopIndex As Integer
ListBox1.Items.Clear()
ListBox2.Items.Clear()

'
' I would like to modify the field here and then
add the item to
' a listbox so I can see the results of each
field I modified.
'
For RowLoopIndex = 0 To (ds.Tables
("Notes").Rows.Count - 1)
ListBox1.Items.Add(ds.Tables("Notes").Rows
(RowLoopIndex).Item(0))
Next

Me.DG_Notes.DataSource = ds
Me.DG_Notes.DataMember = "Notes"

----------------------------------------------------

My question is how can I change the item and have it
update the table? When I bind to the Datagrid at the
end, I would like to see the results.

Any help and code examples, placement would be greately
appreciated.

Thanks,

Gary

 

[William Ryan eMVP]

Gary:

If you are only changing it for display purposes, I'd see if you could do it
with one of the formatters in the DataGridTableStyle ->DataGridColumnStyle

That notwithstanging.... date is the 0th column in the table so you could
add this in your for loop

ds.Tables("Notes").Rows(RowLoopIndex)(0) = WhateverValue

You could substitute the name instead of the 0 but that's a lot slower..if
you want to use a name based lookup, create an enum where Date for instance
is the value and have it correspond to 0 then you 'll have the best of both
worlds (Bill Vaughn's Idea, not mine).

Now, and trust me on this, Don't use Reserved words as field names. SQL
Server will let you do it, access will blow up but it's a terrible practice.
Either you or someone else will forget to escape it sometime and you'll
waste time tracking down the problem. You have three reserved words there
and even though you can use the [], I've found a few situations where even
that didn't help....it's a lot of potential headache.

Nonetheless, as to your original question, if you use the method I reference
above, you'll change those values (once again, assuming date is the 0th
column) in the datatable so when you bind the grid, those changes will be
reflected. However, the Rowstate of each of those rows will change too, so
when you call dataadapter.update, each one of those rows is going to head
back to the db with your changes in place. Then, the next time you do a
select, you'll append or whatever to them b/c the changes were made last
time. Unless this is a one time thing, or you'll never re-reference them,
you probably don't want to do this (or I guess if you aren't going to call
update or your update command undoes the formatting) Nonetheless, the
Rowstate is going to be changed so the dataAdapter will try to update those
rows if you do an update. If you can't live with this, create an expression
column http://www.knowdotnet.com/articles/advancedrowfilter.html that is the
formatted Date Field or whatever, and then apply a table style setting the
'real' date fields width to 0. This way you won't be able to see the real
data field, but the user will see the formatted one (one of the benefits of
the expression column too is that once you change anything in the datatable,
it's reflected immediately whereas if you do it with a SQL Statement it
won't refresh b/c its pulled over as an absolute value).

I can't emphasize this enough...if you don't want the data changed in the
db, instead of iterating through the set, create an expression column and
use a DataGridtablestyle. If you do want your changes submitted, then go
ahead and use the loop. Just remember that the next tim eyou do a Select
query, you may not want to reformat the stuff.

HTH,

Bill

I have a date field in a Dataset. I need to change the
date field for display purposes so I would like to loop
through the dataset and modify the field before I bind it
to a grid. Here is the code I have so far:

--------------------------------------------------
Dim cmdstring As String = "Select [date], [time],
endtime, [desc], con_id from Notes where con_id = '" &
Label3.Text & "'"
Dim da As New SqlClient.SqlDataAdapter

Dim ds As New DataSet
Dim drCurrent As System.Data.DataRow

Dim daNotes1 As New SqlClient.SqlDataAdapter
(cmdstring, Me.SqlConnection2)
daNotes1.Fill(ds, "Notes")

Dim RowLoopIndex As Integer
ListBox1.Items.Clear()
ListBox2.Items.Clear()

'
' I would like to modify the field here and then
add the item to
' a listbox so I can see the results of each
field I modified.
'
For RowLoopIndex = 0 To (ds.Tables
("Notes").Rows.Count - 1)
ListBox1.Items.Add(ds.Tables("Notes").Rows
(RowLoopIndex).Item(0))
Next

Me.DG_Notes.DataSource = ds
Me.DG_Notes.DataMember = "Notes"

----------------------------------------------------

My question is how can I change the item and have it
update the table? When I bind to the Datagrid at the
end, I would like to see the results.

Any help and code examples, placement would be greately
appreciated.

Thanks,

Gary