Troubleshooting Data Access in Visual Studio

Article
11/16/2012

This topic lists some common issues that arise when working with data access in Visual Studio.

Cannot Access a Database at Run Time that I Can Access at Design Time

This situation is most likely the result of the user name and password passed at run time being denied access to the database. Resolution depends on the authentication and authorization methods being used in the database, as well as the connection string.

A possible cause of this problem is:

Selecting the Use a specific username and password option and not selecting the Allow saving password option when completing the Add/Modify Connection Dialog Box (General). In this scenario you are prompted for a username and password when attempting to connect to the data source at design time. For run-time connectivity, you need to incorporate functionality into your application to pass the proper username and password to the data source.

Things to check:

Verify that the username and password that your application is using have proper permissions to access the data source.
Verify that the user has the correct permission settings in the database.
Verify that there is network connectivity between the computer running the application and the data source.

Incorrect or Missing Output Parameters Are Returned When Executing a SQL Command

This situation most likely results from an incorrect setting of the parameter's Direction property. The parameter's Direction property is set to a value defined in the ParameterDirection Enumeration.

Verify that the parameter's Direction property is set to a value that that can receive data (Output for output parameters).
Verify that the parameter's data type is the same as the data type of the expected return value.

Note

Output parameters are returned at the end of the data stream, so if you are using the DataReader object (for example, SqlDataReader), you must close it or read to the end of the data before the output parameters are visible.

For more information on output parameters not being returned, as well as a code sample, search for Q308051 on the Microsoft Product Support Services Web site (https://support.microsoft.com).

Getting the 'The .NET Data SQL Provider (System.Data.SqlClient) requires Microsoft Data Access Components (MDAC) version 2.6 or later' Error

The Microsoft Windows Software Development Kit (SDK) and the .NET Framework redistributable package do not include the MDAC installation. All .NET Framework applications that use data-access functionality require MDAC 2.6 or later (MDAC 2.8 SP1 is recommended). The latest version of MDAC is available as a download from the Microsoft Web site (https://www.microsoft.com).

Because Visual Studio installs MDAC by default, this error is most likely to occur when deploying to a computer that does not have Visual Studio installed.

When you deploy your application, you can have the setup check the version of MDAC on the computer being deployed to. For more information, see How to: Add a Launch Condition for Microsoft Data Access Components.

Getting an Unexpected Exception When Attempting to Commit or Roll Back a Transaction

When an error at the data source causes a transaction to be immediately rolled back, one of the following errors may be raised in your application:

"The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION"
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"

Although this behavior is by design, it is always best to call methods that access external data sources (such as the Commit and Rollback methods) from within a Try...Catch statement. For more information, see How to: Use the Try/Catch Block to Catch Exceptions (Visual Basic), or try-catch (C# Reference).

Getting an InvalidOperationException When Attempting to Remove Tables from a Dataset

When a dataset is bound to an XmlDataDocument, calling the Clear method throws the following exception:

System.InvalidOperationException: Cannot add or remove tables from the DataSet once the DataSet is mapped to a loaded XML document.

Clearing the data from a dataset bound to a XmlDataDocument object requires navigating the table and removing the individual DataRow objects. For more information on removing data rows from a data table, see How to: Delete Rows in a DataTable and DataRowCollection.RemoveAt Method.

The Caption Labels are Not Correct when Dragging Items from the Data Sources Window

When dragging items from the Data Sources window, column names are modified based on a default regular expression. If the column name contains a lowercase character followed by an uppercase character, a space is added to split the two words. Additionally, any underscores in the column name are replaced with spaces. For more information on controlling this behavior, see How to: Customize How Visual Studio Creates Captions for Data-bound Controls.

Fetching and Displaying Data Seems Slow

If your project uses a BindingSource Component try setting the RaiseListChangedEvents to False. This will suppress ListChanged events from occurring on the list and can increase performance on large datasets.

After Installation on Windows 2000, the Application Fails with a Warning That MDAC 2.8 Is Required

Any application that references the System.Data namespace requires Microsoft Data Access Components (MDAC) version 2.8 or later versions. In most cases, the file is already installed as part of the operating system. On computers that run Windows 2000 with Service Pack 3 and earlier, you may have to install the component with your application. You can do so by adding the component to the bootstrapper package and downloading the file from Microsoft during installation. For more information, see Deploying Prerequisites (Visual Studio).

Database-Generated Values on Insert or Update Are Returning NULL for LINQ to SQL Classes

LINQ to SQL handles database-generated values automatically for identity (auto-increment), rowguidcol (database-generated GUID), and timestamp columns. Database-generated values in other column types will unexpectedly result in a null value. To return the database-generated values, you should manually set IsDbGenerated to true and AutoSync to one of the following: Always, OnInsert, or OnUpdate.

Share via