Knowledge Base

DOC: Error in SqlCommandBuilder Code Sample

Article ID: 316758

Article Last Modified on 2/19/2007

APPLIES TO

Microsoft .NET Framework 1.1 Service Pack 1
Microsoft ADO.NET 1.1

This article was previously published under Q316758

This article refers to the following Microsoft .NET Framework Class Library namespace:

System.Data.SqlClient

SUMMARY

When you execute the code in the MSDN documentation for the SqlCommandBuilder class, you may receive the following exception:

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

Additional information: Table doesn't have a primary key.

MORE INFORMATION

To obtain additional information about the DataSet object, such as the primary key information, use one of the following methods:

Call the DataAdapter.FillSchema method to obtain extended schema information.
Set DataAdapter.MissingSchemaAction to MissingSchemaAction.AddWithKey before you call the Fill method.

For additional information about when to use the FillSchema method and the MissingSchemaAction property, click the article number below to view the article in the Microsoft Knowledge Base:

310128 INFO: When to Use FillSchema and MissingSchemaAction with the DataAdapter in ADO.NET

Code Sample for Visual Basic .NET

Public Function SelectSqlSrvRows(myDataSet As DataSet, myConnection As String, mySelectQuery As String, myTableName As String) As DataSet
    Dim myConn As New SqlConnection(myConnection)
    Dim myDataAdapter As New SqlDataAdapter()
    myDataAdapter.SelectCommand = New SqlCommand(mySelectQuery, myConn)
    Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(myDataAdapter)

    myConn.Open()
    Dim custDS As DataSet = New DataSet

    'Use one of the following two methods to fix the problem.
    'Using MissingSchemaAction 
    myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    'Using FillSchema
    'custDS.FillSchema(ds, SchemaType.Source, "Customers") 

    myDataAdapter.Fill(custDS, "Customers")

    ' Code to modify data in DataSet here 

    ' Without the SqlCommandBuilder this line would fail.
    myDataAdapter.Update(custDS, "Customers")

    myConn.Close()
End Function 'SelectSqlSrvRows

Code Sample for C# .NET

public DataSet SelectSqlSrvRows(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName) {
    SqlConnection myConn = new SqlConnection(myConnection);
    SqlDataAdapter myDataAdapter = new SqlDataAdapter();
    myDataAdapter.SelectCommand = new SqlCommand(mySelectQuery, myConn);
    SqlCommandBuilder custCB = new SqlCommandBuilder(myDataAdapter);

    myConn.Open();

    DataSet custDS = new DataSet();
    
    //Use one of the following two methods to fix the problem.
    //Using MissingSchemaAction 
    myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    //Using FillSchema
    //custDS.FillSchema(ds, SchemaType.Source, "Customers");

    myDataAdapter.Fill(custDS, "Customers");

    //Code to modify data in DataSet here

    //Without the SqlCommandBuilder this line would fail
    myDataAdapter.Update(custDS, "Customers");

    myConn.Close();

    return custDS;
 }

REFERENCES

For more information about the SqlCommandBuilder class, see the Microsoft .NET Framework Software Documentation Kit (SDK) documentation, or visit the following MSDN Online Web sites:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandBuilderClassTopic.asp

For more information, see the following topics in the Microsoft Visual Studio .NET Help documentation:

DataAdapter.Fill Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataCommonDataAdapterClassFillTopic.asp

DataAdapter.FillSchema Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataCommonDataAdapterClassFillSchemaTopic.asp

DataAdapter.MissingSchemaAction Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataCommonDataAdapterClassMissingSchemaActionTopic.asp

MissingSchemaAction Enumeration
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataMissingSchemaActionClassTopic.asp

Loading DataSet Schema Information from XML
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconloadingdatasetschemainformationfromxml.asp

Keywords: kbbug kbdocerr kbnofix kbsqlclient kbsystemdata KB316758

Microsoft KB Archive/316758

Contents