ADO.NET with Visual Basic .NET

Posted August 1, 2003

Chat Date: July 1, 2003

Please note: Portions of this transcript have been edited for clarity

Chat Participants:

• David Sceppa, Microsoft data access development team and author of "Microsoft ADO.NET" from Microsoft Press
• Jayaprakasam, Support Engineer in Webdata.NET team
• Hussein Abuthuraya, Support professional with VB and VB.NET WebData Team
• Michael Pizzo, Architect
• Jason Cooke, Programmer/writer for Visual Basic User Education

Moderator: Jason (Microsoft)
Let's get started with our chat on "ADO.NET with Visual Basic .NET". I'll ask our Microsoft participants to introduce themselves.

Host: David (Microsoft)
My name is David Sceppa. I work with the Microsoft data access development team and I'm also the author of "Microsoft ADO.NET" from Microsoft Press.

Host: JP (Microsoft)
I am Jayaprakasam working as a Support Engineer in Webdata.NET team

Host: Hussein (Microsoft)
My name is Hussein Abuthuraya. I'm a support professional with VB and VB.NET WebData Team.

Host: David (Microsoft)
Q: One side is preaching, Unbind! Unbin! Unbind! the other side is saying: Bind your Data! Bind your Data! What's the Final Word on this from MS?

A: Data binding is a powerful feature that can help you build your user interface quickly and easily. It's much improved since previous versions, which may explain the message from that first side. Because data binding handles a lot of the interaction between the user and the data automatically for you, you do give up some control. The more control you require, the more code you have to write and the less helpful data binding becomes. Go with what's right for you.

Host: Mike (Microsoft)
Q: I have several datareaders how do I open the connection one time execute the readers then close the connection? When I try this I get an error connection needs to be closed before executing the next reader.

A: Microsoft SQL Server only supports a single active result per connection. That means that you must read all of the results and close a datareader before you can execute another command on the same connection. Since connection pooling is built into the SQL Server Managed Provider (SqlClient), you can simply create a connection, execute your query, and then close the connection. When you close the connection, by default, it goes back into a pool so that it is quick to use and release connections in this manner. An easy way to make sure a connection is closed and returned to the pool when you close a DataReader is to specify CommandBehavior.CloseConnection as part of ExecuteReader(). This causes the connection to be closed (returned to the pool) when you close the DataReader.

Host: Mike (Microsoft)
Q: The thing is I’m connecting to an AS400... I am able to open the connection 1 time and create multiple data adapters and fill a dataset..if I could use readers it would be faster

A: Which Managed Provider are you using? The OLE DB Managed Provider and ODBC Managed providers, both of which provide AS400 connectivity, both implement connection pooling, so you should be able to get pooling for both of those. Note that the DataAdapters can't get around a single-connection-per result limitation either. If you open the connection explicitly they will use the connection and return the same error if two adapters are trying to fill the dataset at the same time (i.e., on different threads). If you don't open the connection explicitly, the DataAdapter will open the connection, use it, and then close it (return to the pool).

Host: JP (Microsoft)
Q: how to develop a chat program like this using Dot Net?

A: Check for user samples in gotdotnet.com

Host: Hussein (Microsoft)
Q: Just some more information related to my original question: I made a webservice that connects to an access database and originally it had two functions one to update the database and one to load the database to the dataset. The problem was that access remembers how many rows have been entered into the database and regardless of how many there are now it uses the number entered as the primary key of the newest row. What I ended up doing was inserting a blank row with another member function and loading the dataset after this but it seemed wrong to do it this way with another function of the webservice sorry. So it is ok to add another empty row to the database and then use it? Thank you for your time. I had read that article already but did not apply(or could not get it to work)

A: Please see article Q310350 https://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B310350

And 815629 https://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B815629 for a step-by-step sample for retrieving auto number field from access

Host: Mike (Microsoft)
Q: What is the best way to make Autonumber Primary Key value that Microsoft Access gives a database to match the Key value of a Dataset?

A: For filling the DataSet, that's easy; just include it in the select list. I suspect your question has to do with adding a row to the DataSet and having it get the Autonumber value from Access. This is a much harder problem, of course, since rows are inserted to the DataSet without any interaction with the Database. There are various techniques for working with an Autonumber type primary key column. Most involve either "reserving" a set of numbers (for example, by inserting "blank" rows into the database, getting their IDs, and using them) or using temporary IDs on the client and then updating them with the values inserted by the server. By far the easiest solution is to use some other value as a primary key, such as a GUID, which can be created on each of multiple different clients, and guaranteed not to clash when inserting into the database.

Host: David (Microsoft)
Q: About the option GENERATE SQL statements to refresh the dataset how does it work

A: There are scenarios where you want to retrieve server-generated values (defaults, timestamps, auto-increment values, etc.) after a successful insert or update. The DataAdapter Configuration Wizard will include logic to automatically retrieve the contents of the row after a successful insert or update if you have this option checked and you're working with a database that supports batch queries that return rows. With this option checked, your InsertCommand's CommandText would look like: INSERT INTO MyTable (...) VALUES (...); SELECT ... FROM MyTable WHERE KeyCol = ...

Host: Mike (Microsoft)
Q: Angel: If the referenced articles didn't help, you'll probably have to try other news groups or product support directly.

A: I don't know much about System.Arithmetic, so I can't be much help myself. These chats are intended for discussion of a specific topic, not general product support.

Host: Jason (Microsoft)
Well, our time is up. Thanks everyone for participating in this unusual chat on "ADO.NET with Visual Studio .NET".

Host: David (Microsoft)
Thank you, everyone, for your time and for your questions.

Host: Mike (Microsoft)
Sorry we couldn't be more help; best of luck!

Top of Page