Microsoft KB Archive/827993

= Visual Studio .NET Server Explorer generates parameters of incorrect data types =

Article ID: 827993

Article Last Modified on 5/2/2006

-

APPLIES TO


 * Microsoft Visual Studio .NET 2003 Enterprise Architect
 * Microsoft Visual Studio .NET 2003 Enterprise Developer
 * Microsoft Visual Studio .NET 2003 Professional Edition
 * Microsoft Visual Studio .NET 2003 Academic Edition
 * Microsoft Visual Studio .NET 2002 Enterprise Architect
 * Microsoft Visual Studio .NET 2002 Enterprise Developer
 * Microsoft Visual Studio .NET 2002 Professional Edition
 * Microsoft Visual Studio .NET 2002 Academic Edition
 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0

-



SYMPTOMS
Microsoft Visual Studio .NET Server Explorer generates SqlCommand objects that contain SqlParameter fields with incorrect data types when one of the following scenarios is true:
 * You connect to the Microsoft SQL Server database by using Visual Studio .NET Server Explorer and then you drag a database table that contains columns where the data type is text or ntext to the form designer.
 * You connect to the SQL Server database by using Visual Studio .NET Server Explorer and then you drag a stored procedure that contains parameters where the data type is text or ntext to the form designer.

Note This problem also occurs for columns or parameters where the data type is image, smallmoney, smalldatetime, or binary.



CAUSE
Visual Studio .NET Server Explorer uses an OLE DB provider to connect to a computer that is running SQL Server. The OLE DB provider does not distinguish between varchar columns and text columns of a data table. Therefore, the size of the parameters that are generated by Server Explorer is correct, but the data type is not correct.



RESOLUTION
To resolve the problem, manually change the data type of the parameters to text or to ntext in the generated code as appropriate for the data type on the computer that is running SQL Server.

Note If you do not manually change the data type of your text and ntext parameters in the generated code, you may receive the error that is mentioned in the following article:

827366 &quot;Invalid Buffer Received from Client&quot; Error Message in SQL Server Log When You Use SQL Server .NET Provider Classes



STATUS
This behavior is by design.



Steps to Reproduce the Behavior
  Start SQL Query Analyzer (or SQL Server Enterprise Manager), and then create a new stored procedure in the SQL Northwind sample database as follows: USE Northwind Go CREATE proc TestProc @ntextParam ntext, @textParam text as return 1 Go  Start Microsoft Visual Studio .NET. On the File menu, point to New, and then click Project. Click Visual Basic Projects or Visual C# Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is created. On the View menu, click Server Explorer. In Server Explorer, right-click Data Connections, and then click Add Connection.</li> In the Data Link Properties dialog box, type your local SQL Server name in the Select or enter a server name text box.</li> Type your local SQL Server user name in the User name box, and then type your password in the Password text box.</li> In the Select the database on the server box, click Northwind, and then click OK.</li> In Server Explorer, expand your local SQL Server data connection, and then expand Stored Procedures.</li> In Server Explorer, drag the TestProc stored procedure from Stored Procedures to Form1.</li> On the View menu, click Code.</li> In the code editor, expand Windows Form Designer generated code.

In the SqlCommand1 object, you see that the data type of the ntextParam parameter is NVarCharand the data type of the textParam parameter is VarChar.</li></ol>

<div class="references_section">