Primary Key in Access not loaded into Dataset

Hey HereticDave!

  Your indexing is not lost or stolen, it is just implied.
  What is the commandbuilder building that is incorrect for this table or query.
  What is the insert into error you are getting.

  Post your sql, and an explanation of the fields and All indexes for the affected tables.

regards
Jack

listening

Hi Jack and mlmcc!

My apologies that its taken me so long to reply i didn't expect such a speedy response! Many thanks.

I'm running a small simple test database in Access there are two columns 'CustomerID' and 'Number', CustomerID is a Primary Key and defined as Text and Number as a Number with a default value of 0 and 'Auto' for decimal places.

When update_db is called the DB_Adapter.Update throws an OleDb.OleDbException with this message: "Syntax error in INSERT INTO statement."

Here's the automatically generated sql from commandbuilder

INSERT INTO Testtable( CustomerID , Number ) VALUES ( ? , ? )

DELETE FROM  Testtable WHERE ( (CustomerID = ?) AND ((? IS NULL AND Number IS NULL) OR (Number = ?)) )

UPDATE Testtable SET CustomerID = ? , Number = ? WHERE ( (CustomerID = ?) AND ((? IS NULL AND Number IS NULL) OR (Number = ?)) )

I have a form with a datagrid and a single button that when selected calls the update [button2] function of my DB class. The above error is given. I had no idea why this was happening as my database had CustomerID declared as a Primary key. My realization came when i tried to implement getRow and i used the test table.rows.contains(Pri_Key) to search for a specific key which failed saying that there was none. Hence if you take a look at getRow its a bit of a mess where i've generated a DataColumn and set it as the Primary Key. Once this has been done getRow works and returns teh right values. I'm assuming i can do the same for my Update function however in both my ADO.NET books this isn't necessary and the dataset should get the Keys itself. I hope this helps and you can see where i've gone wrong, thanks in advance :)

Following is the VB .NET code i'm using if it helps. First is the Form code then the database class that i've created containing update_db and getRow functions.

Dim CCF_Connection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\WINNT\Profiles\Administrator\My Documents\Visual Studio Projects\Lease\test.mdb; Persist Security Info=False"

    Dim CCF_SQL As String = "SELECT * FROM testtable"
    Dim CCF_Table As String = "testtable"
    Dim CCF_DB As New DB(CCF_Connection, CCF_SQL, CCF_Table)



    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Dim str1 As String
     
        str1 = "SELECT * FROM Testtable"

        CCF_DB.Display_DataGrid(DataGrid1, str1, "testtable")

        DataGrid1.DataSource = CCF_DB.DB_Set
        DataGrid1.DataMember = "testtable"
        DataGrid1.ReadOnly = True

        'test getrow
        Dim row As DataRow

        row = CCF_DB.getRow("testtable", "Dave")

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Try

            CCF_DB.Update_DB("testtable")

        Catch err As Exception
            MsgBox(err.Message, MsgBoxStyle.Exclamation, "Update Error!")
        End Try


    End Sub



-=-=-=-=--=
The DB class.....


Public Class DB
    'Database Class

    ''''''''''''''''' Variable Declarations '''''''''''''''''
    Private DB_Connection As OleDb.OleDbConnection              'Connection Variable
    Private DB_Command As OleDb.OleDbCommand                    'Command Variable
    Private DB_DataAdapter As OleDb.OleDbDataAdapter            'DataAdapter for DB
    Private DB_DataReader As OleDb.OleDbDataReader              'DataReader for DB

    Public DB_Set As Data.DataSet                              'DataSet to store info
    Private DB_CmdBuilder As OleDb.OleDbCommandBuilder          'Command Builder to construct INSERT, UPDATE & DELETE commands :)
    Private DB_Connection_Attained As Boolean                   'Set to True if a connection has been made successfully

    Private strConn As String                                   'Connection String
    Private strSQL As String                                    'Opening SQL statement to fill the DataSet
    Private strTable As String                                  'Table to open from DB ie Vehicles :)


    Sub New(ByVal conn As String, ByVal sql As String, ByVal table As String)
        'Constructor
        MyBase.new()

        'Setup Connection string and SQL statement
        strConn = conn
        strSQL = sql
        strTable = table

        DB_Connection_Attained = False

        'Call Database Setup sub
        Setup_DB()

    End Sub

    Sub Dispose()
        'Destructor

    End Sub

    ''''''''''''''''' Methods '''''''''''''''''

    Private Sub Setup_DB()
        'Sets up Database and fills the dataset: DB_Set

        DB_Connection = New OleDb.OleDbConnection(strConn)
        DB_Command = New OleDb.OleDbCommand(strSQL, DB_Connection)

        DB_DataAdapter = New OleDb.OleDbDataAdapter(DB_Command)
        DB_CmdBuilder = New OleDb.OleDbCommandBuilder(DB_DataAdapter)

        'Create Dataset
        DB_Set = New DataSet()

        Try
            'open the connection
            DB_Connection.Open()
            DB_DataAdapter.Fill(DB_Set, strTable)
            DB_Connection.Close()
            DB_Connection_Attained = True
        Finally
            'Close the connection if still open/problems
            If DB_Connection.State = ConnectionState.Open Then
                DB_Connection.Close()
            End If
        End Try

    End Sub

    Public Sub Refresh_DS(ByVal sql As String)
        'Refreshes the dataset with a new query as defined by sql string

        strSQL = sql        'Set new query string

        Setup_DB()          'Setup DataSet with new information

    End Sub

Public Sub Display_DataGrid(ByRef datagrid As DataGrid, ByVal sql As String, ByVal table As String)
        'Puts Field contents specified by sql string

        'Reload dataset with new string
        Refresh_DS(sql)

        'set bindings
        datagrid.DataMember = table
        datagrid.DataSource = Me.DB_Set

    End Sub

    Public Function getRow(ByVal TableName As String, ByVal Pri_Key As String)
        'Returns a specified row from the dataset

        'Declarations
        Dim index As Integer
        Dim table As DataTable
        Dim row As DataRow

        Dim pk(1) As DataColumn
        Dim pkII() As DataColumn

        pk(0) = New DataColumn()
        pk(0) = DB_Set.Tables.Item(DB_Set.Tables.IndexOf(TableName)).Columns.Item("CustomerID")

        DB_Set.Tables.Item(DB_Set.Tables.IndexOf(TableName)).PrimaryKey() = pk
        pkII = DB_Set.Tables.Item(DB_Set.Tables.IndexOf(TableName)).PrimaryKey()
        MsgBox(pkII(0).ColumnName, MsgBoxStyle.Exclamation, "Primary Key column Name")


        index = DB_Set.Tables.IndexOf(TableName)
        table = DB_Set.Tables.Item(index)

        If table.Rows.Contains(Pri_Key) Then
            row = table.Rows.Find(Pri_Key)
        End If


        '**Change eventually, but will do for now***
        If row Is Nothing Then
            MsgBox("No Row with that Key exists", MsgBoxStyle.Exclamation, "Primary Key Error")
        End If

        MsgBox(row.Item("customerid"), MsgBoxStyle.Exclamation, "Test result of Make from Row")
        Return row


    End Function

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class
 

Hello again, i've realized that i've forgotten the Update_DB function in the DB class - many apologies:

Public Sub Update_DB(ByVal UpdateTableName As String)
        'Updates the data base with the current DB_Set

        Try
            'open the connection
            DB_Connection.Open()                            'Open connection to Database

            MsgBox(strSQL, MsgBoxStyle.Information, "strsql")

            DB_CmdBuilder.RefreshSchema()                   'Refresh commands just in case

            DB_DataAdapter.Update(DB_Set, UpdateTableName)  'Update the DB


            DB_Set = New DataSet()
            DB_DataAdapter.Fill(DB_Set, UpdateTableName)    'Get new values
            strTable = UpdateTableName

            DB_Connection.Close()                           'Close connection to Database

        Catch e As OleDb.OleDbException
            'Attempt to enter a null value in the DB [probably a bunch of other stuff to but this'll do :)]
            Debug.Write(DB_CmdBuilder.GetInsertCommand.CommandText.ToString)
            Debug.Write(DB_CmdBuilder.GetDeleteCommand.CommandText.ToString)
            Debug.Write(DB_CmdBuilder.GetUpdateCommand.CommandText.ToString)
            Debug.Write(e.Message)

            Dim Err As New Exception("An attempt to store a null value into the database has been made. Please make sure every field has an entry.")
            Throw Err

        Catch e As DBConcurrencyException
            'Attempt to update and target row was deleted or has been changed since first read
            Debug.Write(e.Message)
            Debug.Write(e.Source.ToString)

            Dim Err As New Exception("The Database has since been updated by another user. Your changes will NOT be implemented as the will compromise theirs. As such, your current version of the database will be updated and you may re-implement your changes.")
            'This should be dealt with however calling function thinks best....
            'Refresh_DS should probably be called with a new SQL string and get the user to re-implement changes.

            Throw Err

        Finally
            'Close the connection if still open/problems
            If DB_Connection.State = ConnectionState.Open Then
                DB_Connection.Close()
            End If

        End Try

    End Sub

Hi Dave,

You have to set the 'MissingSchemaAction' property of DataAdapter to 'AddWithKey' before calling Fill method.

That will retieve constraints information and add in DataSet

Cheers

Hi SQLMaster,

Sorry its been a couple of days, another project demanded my attention for a bit. I've tried your advice and unfortunately i get the same errors :( Here is what i have done:

Private Sub Setup_DB()
        'Sets up Database and fills the dataset: DB_Set

        DB_Connection = New OleDb.OleDbConnection(strConn)
        DB_Command = New OleDb.OleDbCommand(strSQL, DB_Connection)

        DB_DataAdapter = New OleDb.OleDbDataAdapter(DB_Command)
        DB_CmdBuilder = New OleDb.OleDbCommandBuilder(DB_DataAdapter)

        'Create Dataset
        DB_Set = New DataSet()

        '******CHANGE IMPLEMENTED HERE************
        DB_DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        Try
            'open the connection
            DB_Connection.Open()
            DB_DataAdapter.Fill(DB_Set, strTable)
            DB_Connection.Close()
            DB_Connection_Attained = True
        Finally
            'Close the connection if still open/problems
            If DB_Connection.State = ConnectionState.Open Then
                DB_Connection.Close()
            End If
        End Try

    End Sub

I've also thrown it into the try statement incase the connection had to be open without success.

Thanks again SQLMaster, any further help or suggestions would be much appreciated!

Dave.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- PAQ'd and points refunded

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer