[RESOLVED] Update Table data

[MechEng]

I have a form, data table, and MS Database all tied together and working fine (using Bindingsource, table adaptor, currency manager, etc.). When the form fields are changed, the table is updated and then the database is updated. This works great.

What I would like to do is write directly to the table and not have to create a form textbox and bind it to a table field. Can someone provide an example of how to update data in an existing record in an existing table using only VB code? I am using VB2005.

Thanks in advance for your help.

[jmcilhinney]

Your already doing it. The data-binding is an independent part of the process that is not directly related to the data access. You are already retrieving data from the database into a DataTable and then saving the changes in that DataTable to the database. Those operations can still be done without any controls or data-binding. Here's what you're doing at the moment:

1. Get data.
2. Bind data.
3. User edits data.
4. Save data.

All you need to do is remove steps 2 and 3 and put in your own code to edit the data. That might look something like this:

myTable(0).MyColumn = someValue

That will set the "MyColumn" field of the first row to some value.

Note also that when you configure your TableAdapter you have the option of creating what are caled DBDirect methods. These are extra methods added to the TableAdapter class that allow you to delete, insert and update records in the database without actually retrieving data into a DataTable first and then saving it afterwards. That may or may not be more appropriate in your situation.

[MechEng]

Thanks for the quick response.

I get the following error: “Class 'System.Data.Dataset' cannot be indexed because it has no default property”

Here is the relevant code section:

1. Dim MyDataTable As Dataset
22. MyDataTable (1).GTD1 = 155

What is the proper way to dimension the data table, or is something wrong with the table definition itself?

Thanks

[jmcilhinney]

A DataTable and a DataSet are two different things. A dataSet is like a database and a dataTable is like a table.

If you have a typed DataSet then it has a property for each DataTable. You then index the DataTable to get a DataRow.

If you're using an untyped DataSet then you index its Tables collection to get the desired DataTable, then index the table's Rows collection to get a DataRow.

[MechEng]

I have tried dimensioning MyDataTable as DataTable with similar results:

1. Dim MyDataTable As DataTable
22. MyDataTable (1).GTD1 = 155

I get the following error: “Class 'System.Data.DataTable' cannot be indexed because it has no default property”

What is the proper way to dimension MyDataTable?

[MechEng]

I am sure this is something easy... I just can not seam to break through.

Here is the paired down code I am trying to get to work. Always with the same error message: “Class 'System.Data.DataTable' cannot be indexed because it has no default property”

The table "tbl_ScoreboardMatch" exists and has several rows of data in it.
The table has a column identified as the primary key.
The Column "GTD1" exists (this is not the primary key)

Private Sub TestWritetoTable(ByVal sender As System.Object, ByVal e As System.EventArgs)
 
        Dim Mytable As DataTable = Me.ScoreboardDataSet.Tables("tbl_ScoreboardMatch")
        Dim MyColumn As String = "GTD1"
 
        Mytable(0).MyColumn = 115
 
    End Sub

Any help is appriceated.

[jmcilhinney]

You aren't supposed to create a new DataTable. The idea is that you have an instance of a typed DataSet. That DataSet has a property for each DataTable it contains. You use a TableAdapter to Fill the corresponding DataTable in the DataSet, then you get that DataTable from the DataSet, then you get DataRow from the DataTable. A typed DataSet contains typed Datatables and typed DataRows, which is the whole point. Creating an untyped DataTable defeats the purpose of creating the Data Source in the first place.

Dim data As New MyTypedDataSet
Dim adapter As New MyTypedDataSetTableAdapters.MyTableTableAdapter
 
adapter.Fill(data.MyTable)
 
Dim row As MyTableRow = data.MyTable(0)
 
row.MyColumn = newValue
 
adapter.Update(data.MyTable)

[MechEng]

Thanks. I finally got to something close to that using:

MyDataSet.Tables("MyTable").Rows(RowNumber).Item(ColName) = NewVal

It is tough for me to break the methods (habits) learned in ACCESS2003 VBA. Thanks for your Help.

[jmcilhinney]

If you're using a TableAdapter as you say then you are using a typed DataSet, with typed DataTables and typed DataRows. You are treating them as untyped in that code, which defeats the purpose of creating a typed DataSet in the first place. While what you posted will work, it should be:

MyDataSet.MyTable(RowNumber).ColName = NewVal

The whole point of typed DataSets is that you don't have to use strings to identify tables and columns. The dataSet is customised to your specific data, so the DataSet has a property for each DataTable and each row has a property for each DataColumn.

[MechEng]

First- thanks for taking the time to continue this thread. In reading through the forum, I see that you have been repeating this message with regular consistency… I assume that means it is important. I get it. I do not however get the meaning of your message regarding “Typed DataSet” –vs- “UnTyped DataSet”. I have read thru more than half of Rod Stephens’ Visual Basic 2005 Programmer’s Guide and there is no mention in this book about “Typed" or "Un-Typed" DataSets. (As a side comment I have not really gotten so much from this book anyway).

So, I know this is off topic, but can you (will you) recommend a reference book that will help me with VB programming? I learn best from examples.

Back on topic: I tried the DataSet reference as you suggested and found that I can better explain what it is that I am having trouble with. If I use:

MyDataSet.MyTable(RowNumber).ColName = NewVal

Then I will have to hard code every reference to the dataset and for my program that will take several hundred lines of code. If I can point to DataRow DataColumn property with a code built string (or to a code built column number) then my code reduces to about 50 lines. It seams more efficient. But this gets me back to doing what I think you are indicating is not the best method.

MyDataSet.MyTable(RowNumber).item(str_ColName) = NewVal

The location of where the data needs to be written in the DataSet is dependant on several independent variables. Is there a way to stick with “Typed DataSets” and use a variable for the DataRow DataColumn property?

PS- If from my question it sounds like I just do not get it… there is probably a reason for that. But I am trying.

Thanks for your help.

[jmcilhinney]

I can't recommend any books I'm afraid because I've never really used any. Here's a bit of explanation on typed and untyped DataSets though.

A DataSet is basically an in-code representation of a database although it doesn't necessarily have to correspond directly to any specific database schema. A DataSet has two primary properties of interest: Tables and Relations. Tables is a collection of DataTables and Relations is a collection of DataRelations, thus a DataSet contains DataTables and DataRelations just as a database contains tables and relationships.

A DataTable has two primary properties of interest too: Columns and Rows. Columns is a collection of DataColumns and Rows is a collection of DataRows. DataColumns describe the data, e.g. its type, while DataRows contain the data itself.

In an untyped DataSet, which is an instance of the DataSet class, you have to index the Tables collection by number or name to get a DataTable. You also have to index a DataRow by number or name to get the data in a certain column. This means that you get no Intellisense, you might enter an invalid index, plus all data is returned via Object references and requires casting.

A typed DataSet is a custom built class that inherits the DataSet class and adds extra properties and methods customised specifically for your data. First of all it a property for each DataTable. You therefore get Intellisense so writing code is quicker and safer, as you can't specify an invalid table name. Each DataTable is also typed, so they inherit the DataTable class and add functionality customised to your data. You can then index the table itself, rather than its Rows collection, to get a DataRow. Again, the rows are typed too. They inherit the DataRow class and add functionality cutomised for your data. They have a property for each field, so again you get Intellisense. They also have methods for testing for and setting null values if the columns support it. each property returns the data as its native type too, so no casting is required.

Note that all these custom types are still derived from the standard types, so you can still access a typed DataSet in exactly the same way as you do an untyped DataSet if you want. There are times when this is desirable, but in the vast majority of cases you should be taking advantage of the features the typed DataSet provides. If you're finding that using an untyped DataSet takes more work then chances are you aren't doing it properly.

[MechEng]

Wow... Lots to chew on.

I believe I understand the academic difference of Typed and UnTyped DataSets as well as the benefits of using Typed DataSets in my programming. Really. But I am unclear on how to implement this in practice. Like I said, I learn best from examples. So… I will go look for an example showing the programming structure using Typed DataSets. Thanks for putting me on this path.