Solved

Primary Key in Access not loaded into Dataset

Posted on 2003-11-28
8
635 Views
Last Modified: 2013-12-25
Hi there,

Hoping you can help me with a Primary Key issue with VB .NET and Access. I've set up a simple Access database and defined a Primary Key for one of the columns. I've no problems connecting to Access and loading the table in question into a dataset however it seems to lose or not load any PK's. There's no complexity to the database its just one table with no relations [i'm getting stuff to work before things get convoluted].

As a consequence commandbuilder builds incorrect SQL commands as i get 'INSERT INTO' errors when trying to use update. As the DB is going to stay reasonably simple i'd like to keep commandbuider for its flexability.

I'm hoping that this has cropped up before and any advice or insights would be much appreciated. Many thanks in advance.

Cheers,
Dave.
0
Comment
Question by:HereticDave
8 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9838828
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9839543
listening
0
 

Author Comment

by:HereticDave
ID: 9842535
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
 
0
 

Author Comment

by:HereticDave
ID: 9844003
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
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 3

Expert Comment

by:SQLMaster
ID: 9849340
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
0
 

Author Comment

by:HereticDave
ID: 9876629
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.
0
 
LVL 1

Expert Comment

by:ayufans
ID: 10512164
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
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 10544700
PAQed, with points refunded (50)

CetusMOD
Community Support Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now