Steps to reproduce this problem:1. Create a table on the target SQL Server using the following statement:
create table test (c1 nvarchar(max))
2. Create a Microsoft Visual Basic.Net Console Application that contains the following code:
Note: Before you run the code, replace the
DBServer and
DBName variables with the correct names of your computer that is running SQL Server and your database.
Imports System.Data.SqlClient
Imports System.Text
Module Module1
Dim instance As SqlException
Sub Main()
Dim cnStr As String
Dim cmdStr As String
Dim retVal As String
Dim errMsg As New StringBuilder()
Dim DBServer As String
Dim DBName As String
DBServer = "
SQLServerName"
DBName = "
DatabaseName "
'get parameter.size from the console input.
Console.WriteLine("Specify the size for SqlParameter.Size. Specifying between 4001 and 8000, SqlClient may throw the exception.")
Console.WriteLine()
Console.WriteLine("After input, press Enter")
Console.WriteLine()
retVal = Console.ReadLine()
Console.WriteLine("SqlParameter.Size is specified: " + retVal)
'connect to SQL Server
cnStr = "Data Source =" + DBServer + ";Integrated Security=True;Initial Catalog=" + DBName
cmdStr = "insert into test values (@ParameterName)"
Using connection As New SqlConnection(cnStr)
Dim command As New SqlCommand(cmdStr, connection)
Dim param As SqlParameter = New SqlParameter
param = command.CreateParameter
With param
.ParameterName = "ParameterName"
.DbType = DbType.String
'Uncomment the next line for implemnting Workaround #2 that is discussed above.
'.SqlDbType = SqlDbType.NText
.Size = retVal
End With
param.Value = "TestValue"
command.Parameters.Add(param)
Try
command.Connection.Open()
Dim iRowsAffected As Integer = command.ExecuteNonQuery()
If (iRowsAffected > 0) Then
Console.WriteLine("{0} rows affected.", iRowsAffected)
connection.Close()
End If
Console.Write("done")
Catch ex As SqlException
Dim i As Integer
For i = 0 To ex.Errors.Count - 1
errMsg.Append("Index #" & i.ToString() & ControlChars.NewLine _
& "Message: " & ex.Errors(i).Message & ControlChars.NewLine _
& "LineNumber: " & ex.Errors(i).LineNumber & ControlChars.NewLine _
& "Source: " & ex.Errors(i).Source & ControlChars.NewLine _
& "Procedure: " & ex.Errors(i).Procedure & ControlChars.NewLine)
Next i
Console.WriteLine(errMsg.ToString())
connection.Close()
End Try
End Using
End Sub
End Module
3. Build and then run the Visual Basic.Net Console Application. You receive the error messages that are mentioned in the "Symptoms" section when you specify a value that is between 4001 and 8000 for SqlParameter.Size. If you specify a value that is outside of that range or -1, the application does not report an error message.
References:Modifying Large-Value (max) Data (ADO.NET)