Microsoft KB Archive/316323

= FIX: You cannot insert empty string into Memo, Text, nText, or Blob columns =

Article ID: 316323

Article Last Modified on 5/13/2007

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q316323



This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.OleDb



SYMPTOMS
When you try to insert an empty string into Memo, Text, nText or Blob columns by using the OLE DB .NET data provider, you receive the following exception:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

If you handle this exception within a try-catch block, you receive the following information:

System.InvalidOperationException

System.Data.OleDb.OleDbException: Multiple-Step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.



RESOLUTION
To work around this problem:
 * For strings, insert a space that has a length greater than zero (such as &quot; &quot;), instead of using an empty string.
 * If the database has an Allow Nulls setting, select Allow Nulls for the columns in the database, and treat nulls as empty strings.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected in Microsoft ADO.NET (included with the .NET Framework 1.1), and Microsoft Visual Basic .NET (2003).



Steps to Set Up the Database
 Create a table &quot;Test&quot; in your Microsoft Access database. Add the following two fields to the database:

&quot;Fld1&quot; Data Type = &quot;Text&quot;

&quot;Fld2&quot; Data Type = &quot;Memo&quot;, Required=&quot;Yes&quot;, AllowZeroLength=&quot;Yes&quot;

 Make Fld1 the primary key. Save the changes and close the database.

Steps to Reproduce the Problem
 Start Visual Studio .NET.</li> Create a new Microsoft Windows application in Visual Basic .NET.</li> Make sure that your project contains a reference to the System.Data namespace.</li> Place a command button on Form1. Change the Name property of the button to &quot;btnTest&quot; and the Text property to &quot;Test&quot;.</li>  Use the Imports statement on the System and System.Data namespaces, so that you do not have to qualify declarations in those namespaces later in your code. Add the following to the General Declarations section of Form1, as follows: Imports System Imports System.Data.OleDb </li>  Paste the following code in the code window, after the region &quot;Windows Form Designer generated code&quot;: Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click Try Dim myConnString As String = _ &quot;Provider = Microsoft.Jet.Oledb.4.0;Data Source=c:\nwind1.mdb;&quot; Dim mySql As String = &quot;SELECT * FROM Test&quot; Dim ds As New DataSet Dim conn As New OleDbConnection(myConnString) conn.Open

Dim da As New OleDbDataAdapter(mySql, conn) da.MissingSchemaAction = MissingSchemaAction.AddWithKey da.Fill(ds, &quot;Test&quot;)

Dim dr As DataRow dr = ds.Tables(&quot;Test&quot;).NewRow dr(&quot;Fld1&quot;) = &quot;1111&quot; dr(&quot;Fld2&quot;) = &quot;&quot; ds.Tables(0).Rows.Add(dr)

Dim cb As New OleDbCommandBuilder(da) da.Update(ds, &quot;Test&quot;)

conn.Close Catch ex As Exception MessageBox.Show(ex.ToString) End Try End Sub </li> Modify the connection string (myConnString) as appropriate for your environment.</li> Save your project. On the Debug menu, click Start to run your project.</li> Click Test.

Notice that an exception is generated when you attempt to update.</li></ol>

<div class="references_section">