Trying to read remove table from dataset - ADO.NET 1

[Fred48]

Hi,

I am in the process of converting from ActiveX to ADO.NET and I ran into a samll problem. I have a function that reads throught a dataset and looks for a table and if found then deletes it.

I am not familiar with ADO.NET but I found an article on the Interent but id did not show how to open a dataset. Below is my "new" code. The problem I have is "how do I open the data set to read it".

Dim cnn As OleDbConnection
Dim dSet As DataSet
Dim tableCol As DataTableCollection

cnn = New OleDbConnection(PrepareDBConnectStr(AppDir & TKWINS_MDB))
cnn.Open()

tableCol = dSet.Tables
If tableCol.Contains("tblTempPrint") Then
dSet.Tables.Remove("tblTempPrint")
End If

cnn.Close()
cnn = Nothing

Any help would be greatly appreciative!

Thanks!!

 

[RiverGuy]

First of all, you should understand how the different components in ADO.Net work and what they are used for.

DataSets are objects stored in memory of your .Net application and contain DataTables. DataTables contain collections of DataColumns and DataRows. DataSets and DataTables have nothing to do with databases by themselves.

You can leverage DataAdapters to populate DataTables and DataSets with data from databases or other datasources, as well as reconcile changes back to the datasource. Think of the DataAdapter as the intelligent link between DataSets and databases. DataSets and databases are repositories of data, and the DataAdapter decides (with help from your coding) how to move data back and forth between the two repositories. The DataAdapter uses Commands to help perform data operations against the database.

That being said, if you remove a DataTable from a DataSet's collection of DataTables -- then you have removed it from memory in your application. You haven't removed it from the database. To remove the table from the database, you need to submit a "DROP TABLE tblTempPrint" query to the database. You can do this with an OleDBConnection and an OleDBCommand.

 

[Fred48]

Thanks for the information. I changed my code to "DROP TABLE' and it worked. Also, thanks for the small write up on ADO.NET.

 

[RiverGuy]

No problem. Good luck with your project.

 

[Fred48]

Hi,

When using the OleDbDataReader to read thorough a table and I need to update a datacolumn I am getting a "Read-Only" message when I try to do the following:

cmd2 = New OleDbCommand("SELECT * FROM tblTempPrint", cnn)
drreader2 = cmd2.ExecuteReader

While drreader2.read
drreader2("Save_Ind") = "Y"


The "Read-Only" error message is from the above statement. Do I need to set an option somewhere to allow me to update the field?

ALso, can you recommend a good book for VB.Net with ADO.Net.

Thanks again!!

 

[RiverGuy]

DataReaders are Read-Only and Forward-Only. ADO.Net is very different than classic ADO. To update data through ADO.Net, you need to update DataRows of DataTables, and configure DataAdapters to facilitate Inserts, Updates and Deletes to the database. So only use a DataReader if you either:

A.) Want read-only data
-or-
B.) Want to grab read-only data and create your own custom solution to update the database.

I'm not sure about a book. The latest VB.Net book I have is from 2002/2003, so it would be too outdated to suggest. Usually I find the Wrox books are pretty good. You might want to looks at some of the reviews for their books on Amazon.

 

[Fred48]

Hi,

After sending the email I was reading where the DataReader is just for that, reading.

I will check out the Wrox books.

Thanks again for your help.