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.
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
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.
To resolve this problem, do not call the Prepare
method until after you add the parameters.
Steps to Reproduce the Behavior
The sample code to follow uses the Region table of the Northwind
- Start Microsoft Visual Studio .NET.
- Create a new Visual Basic Windows Application project.
Form1 is added to the project by default.
- Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does
- Place a Button control on Form1. Change the Name property of the button to btnTest, and
change the Text property to Test.
- Use the Imports statement on the System, System.Data.OleDb, and System.Data.SqlClient 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:
- Add the following code to the Code window after the
"Windows Form Designer generated code" region.
Note You must change User ID <username>
and password =<strong password> to the correct values before you run this
code. Make sure that User ID has the appropriate permissions to perform this
operation on the database.
Private Sub btnTest_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnTest.Click
Dim myConnString As String = _
"User ID=<username>;password=<strong password>;Initial Catalog=Northwind;Data Source=myServer"
Dim id As Integer = 25
Dim desc As String = "myFirstRegion"
Dim rConn As SqlConnection = New SqlConnection(myConnString)
Dim command As SqlCommand = 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.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.Parameters(0).Value = id
command.Parameters(1).Value = desc
- Modify the connection string (myConnString) as appropriate for your environment.
- Save your project. On the Debug menu, click Start to run your project.
- 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.
- 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
For more information on ADO.NET objects and syntax, refer
to the following Microsoft .NET Framework Software Development Kit (SDK)
Article ID: 310368 - Last Review: February 28, 2014 - Revision: 2.8
- Microsoft ADO.NET 2.0
- Microsoft ADO.NET (included with the .NET Framework)
- Microsoft Visual Basic .NET 2002 Standard Edition
- Microsoft Visual Basic .NET 2003 Standard Edition
- Microsoft Visual Basic 2005
- Microsoft SQL Server 7.0 Standard Edition
|kbnosurvey kbarchive kbtshoot kberrmsg kbnofix kbprb kbsqlclient kbsystemdata KB310368|