Troubleshooting Data Access in Visual Studio
This topic lists some common issues that arise when working with data access in Visual Studio.
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.
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.
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 (http://support.microsoft.com).
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 (http://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.
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).
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.
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.
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).
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.