Error message occurs in SQL Server 7.0 when you call the Command.Prepare method before you add parameters by using Visual C# .NET: "An unhandled exception of type"

Article ID: 311348 - View products that this article applies to.
This article was previously published under Q311348

For a Microsoft Visual Basic .NET version of this article, see 310368
(http://support.microsoft.com/kb/310368/ )
.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data.SqlClient
  • System.Data.OleDb
Expand all | Collapse all

On This Page

SYMPTOMS

When you create a parameterized command against Microsoft SQL Server 7.0, if you call the Prepare method before you add parameters to the command, you receive the following error message:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll.
Additional information: System error.
This problem does not occur in SQL Server 2000.
Back to the top | Give Feedback

CAUSE

This problem occurs in SQL Server 7.0 because, by design, you cannot run the Prepare method before you add parameters. This applies to most database systems.

SQL Server 2000 does not generate the above-mentioned exception because it does not run Prepare until the first command is executed. This optimization prevents the overhead of Prepare if no commands are subsequently executed.
Back to the top | Give Feedback

RESOLUTION

To resolve this problem, do not call the Prepare method until after you add the parameters.
Back to the top | Give Feedback

MORE INFORMATION

Steps to reproduce the behavior

The sample code to follow uses the Region table of the Northwind sample database.
  1. Start Microsoft Visual Studio .NET.
  2. Create a new Windows Application project in Visual C# .NET. Form1 is added to the project by default.
  3. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
  4. Place a Button control on Form1. Change the Name property of the button to btnTest, and change the Text property to Test.
  5. Use the using statement on the required namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the General Declarations section of Form1:
    using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
  6. Add the following code in the Click event for the button (Test).

    Note You will need to change User ID <username> and password =<strong password> to the correct values before you run this code. Be sure that User ID has the appropriate permissions to perform this operation on the database. 
        String myConnString = "User ID=<username>;password=<strong password>;Initial Catalog=northwind;
                           Data Source=myServer";
    int id = 25;
    string desc = "myFirstRegion";
    SqlConnection rConn = new SqlConnection(myConnString);
    rConn.Open();
    SqlCommand command  = new SqlCommand("", rConn);
        
    command.CommandText = "insert into Region (RegionID, RegionDescription) 
                           values (@id, @desc)";
    //SQL Server 7.0 throws an exception here.
    //Comment the following line to resolve this problem against SQL Server 7.0.
    command.Prepare();
    command.Parameters.Add("@id", SqlDbType.Int,4);
    command.Parameters.Add("@desc", SqlDbType.Char,50);
    //You can call Prepare after you set up commandtext and parameters.
    command.Prepare();
    command.Parameters[0].Value = id;
    command.Parameters[1].Value = desc;
    command.ExecuteNonQuery();
    MessageBox.Show("Updated Successfully");
  7. Modify the Connection string (myConnString) as appropriate for your environment.
  8. Save your project. On the Debug menu, click Start to run your project.
  9. Click Test. If you are connected to a SQL Server 7.0 database, the code generates the above-mentioned exception.

    If you are connected to a SQL Server 2000 database, the code runs properly, and the "Updated Successfully" message box appears.
  10. To resolve this problem against SQL Server 7.0, comment out the call to command.Prepare that precedes the code to add the parameters, and then run the project again.
Back to the top | Give Feedback

REFERENCES

For more information on ADO.NET objects and syntax, refer to the following Microsoft .NET Framework Software Development Kit (SDK) documentation:
Accessing Data with ADO.NET
http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx
(http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx)
Back to the top | Give Feedback

Properties

Article ID: 311348 - Last Review: May 13, 2007 - Revision: 3.8
APPLIES TO
  • Microsoft ADO.NET 2.0
  • Microsoft ADO.NET 1.0
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
  • Microsoft Visual C# 2005
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbtshoot kberrmsg kbprb kbsqlclient kbsystemdata KB311348
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top