Difference between revisions of "Microsoft KB Archive/101522"

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
 
Line 59: Line 59:
  
 
On the Update method for the Dynaset, the following SQL code is generated by Jet Engine used by both Microsoft Access and Visual Basic version 3.0:
 
On the Update method for the Dynaset, the following SQL code is generated by Jet Engine used by both Microsoft Access and Visual Basic version 3.0:
<pre class="codesample">  Insert into Customer (Name, City) values (&quot;bob&quot;, NULL)
+
<pre class="codesample">  Insert into Customer (Name, City) values ("bob", NULL)
 
                 </pre>
 
                 </pre>
For example, look at the schema definition shown in the More Information section below. If the table definition is as in A, the Insert fails because it is an attempt to insert NULL into a non-null column. If the table definition is as in B, the Insert command inserts &quot;bob&quot; and Null into the table -- bypassing the default of &quot;Seattle&quot; for City<br />
+
For example, look at the schema definition shown in the More Information section below. If the table definition is as in A, the Insert fails because it is an attempt to insert NULL into a non-null column. If the table definition is as in B, the Insert command inserts "bob" and Null into the table -- bypassing the default of "Seattle" for City<br />
 
<br />
 
<br />
 
To correct the problem, the Jet Engine should construct the SQL Statement to enforce defaults:
 
To correct the problem, the Jet Engine should construct the SQL Statement to enforce defaults:
<pre class="codesample">  Insert into Customer (Name) values (&quot;bob&quot;)
+
<pre class="codesample">  Insert into Customer (Name) values ("bob")
 
                 </pre>
 
                 </pre>
This would correctly insert &quot;bob&quot; and &quot;Seattle&quot; into the Customer table.
+
This would correctly insert "bob" and "Seattle" into the Customer table.
  
 
</div>
 
</div>
Line 94: Line 94:
  
 
   Create Unique Index Customer_ndx on Customer(name)
 
   Create Unique Index Customer_ndx on Customer(name)
   Create Default city_default  as &quot;Seattle&quot;
+
   Create Default city_default  as "Seattle"
 
   sp_bindefault city_default, 'table.city'
 
   sp_bindefault city_default, 'table.city'
  
 
// VB Code to insert a new row into SQL Server
 
// VB Code to insert a new row into SQL Server
 
   Dim DS as Dynaset
 
   Dim DS as Dynaset
   DS = DB.Createdynaset (&quot;Customer&quot;)
+
   DS = DB.Createdynaset ("Customer")
 
   DS.AddNew
 
   DS.AddNew
   DS(&quot;Name&quot;) = &quot;bob&quot;
+
   DS("Name") = "bob"
 
// No code to set the value for 'City'
 
// No code to set the value for 'City'
 
   DS.Update
 
   DS.Update
Line 109: Line 109:
 
<pre class="codesample">  Column 'Name' in table 'Customer' may not be NULL.
 
<pre class="codesample">  Column 'Name' in table 'Customer' may not be NULL.
 
                 </pre>
 
                 </pre>
If the table definition for Customer is as in B, the row is inserted into SQL Server, but the default has been bypassed. The values &quot;bob&quot; and Null are inserted into the table
+
If the table definition for Customer is as in B, the row is inserted into SQL Server, but the default has been bypassed. The values "bob" and Null are inserted into the table
  
 
</div>
 
</div>

Latest revision as of 09:21, 20 July 2020

Article ID: 101522

Article Last Modified on 10/28/2003



APPLIES TO

  • Microsoft Visual Basic 3.0 Professional Edition
  • Microsoft Visual Basic 3.0 Professional Edition



This article was previously published under Q101522

SYMPTOMS

When inserting a row into a SQL Data Source using dynasets, you may see one of the following behaviors:

  • The row is not inserted due to a NON-NULL integrity conflict.
  • The row is inserted but the default for a column is bypassed.

The behavior depends on the table definition (can it be made NULL or not) for the default-bound column. If default(s) exist on the table in SQL Server and the dynaset column corresponding to the default-bound column is not given a value before the insert, one the behaviors listed above will occur:

CAUSE

On the Update method for the Dynaset, the following SQL code is generated by Jet Engine used by both Microsoft Access and Visual Basic version 3.0:

   Insert into Customer (Name, City) values ("bob", NULL)
                

For example, look at the schema definition shown in the More Information section below. If the table definition is as in A, the Insert fails because it is an attempt to insert NULL into a non-null column. If the table definition is as in B, the Insert command inserts "bob" and Null into the table -- bypassing the default of "Seattle" for City

To correct the problem, the Jet Engine should construct the SQL Statement to enforce defaults:

   Insert into Customer (Name) values ("bob")
                

This would correctly insert "bob" and "Seattle" into the Customer table.

STATUS

Microsoft has confirmed this to be a bug in Visual Basic version 3.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Example to Reproduce Problem

The following example demonstrates this incorrect behavior:

// SQL Server schema definition

// A) City is defined 'non-nullable' for behavior (1) to manifest
   Create table Customer
   (Name char(30) not null , City char(30) not null)

// B) City is defined 'nullable' for behavior (2) to manifest
   Create table Customer
   (Name char(30) not null , City char(30) null)

   Create Unique Index Customer_ndx on Customer(name)
   Create Default city_default  as "Seattle"
   sp_bindefault city_default, 'table.city'

// VB Code to insert a new row into SQL Server
   Dim DS as Dynaset
   DS = DB.Createdynaset ("Customer")
   DS.AddNew
   DS("Name") = "bob"
// No code to set the value for 'City'
   DS.Update
   DS.Close
                

If the table definition for Customer is as in A, an attempt to insert a new row into SQL Server fails with the following message from SQL Server:

   Column 'Name' in table 'Customer' may not be NULL.
                

If the table definition for Customer is as in B, the row is inserted into SQL Server, but the default has been bypassed. The values "bob" and Null are inserted into the table


Additional query words: buglist3.00 3.00 Access JET default update

Keywords: kbbug KB101522