Error configuring SQl Data Adapter

This usually means that the database table does not have, or the dataadapter cannot recognise, a primary key.  This can happen with single tables that do not have a primary key or with Select statements for the dataadapter that include Joins.

Can you give a bit more detail?

Roger

Hi Roger,

The database contains only one table (tblCustomer) and the Primary key is set for the ID column. The errrors take place with when the Wizard tries to configure  Update and Delete with the following error given in each case:

Could not determine which columns uniquely identify the rows for "tblCustomer"

I can't make out why the wizard can't identify the primary key column.

I normally work with OleDb rather than SQL, but a method that I've used successfully to overcome the problem of the wizards not recognising a primary key when I know there is one is to forget the wizard (over which we as programmers have very little control) but still take advantage of the "automatic" creation of the a dataadapter's commands by using a commandbuilder.  Here's some code in which I recently did that

    Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Test\Questions.mdb"
    Dim sqlTopics As String = "SELECT * FROM Topics ORDER BY Topic_Number"
    '[...]
    Dim con As OleDbConnection
    Dim daTopics As OleDbDataAdapter
    '[...]
    Dim dtTopics As DataTable
    '[...]

    Public Sub GetTopicData()
        If con Is Nothing Then
            con = New OleDbConnection(constr)
        End If
        If dtTopics Is Nothing Then
            dtTopics = New DataTable
            daTopics = New OleDbDataAdapter(sqlTopics, con)
            daTopics.FillSchema(dtTopics, SchemaType.Source)
            Dim cb As New OleDbCommandBuilder(daTopics)
        End If
        daTopics.Fill(dtTopics)
    End Sub

It's the line

            daTopics.FillSchema(dtTopics, SchemaType.Source)

that makes the difference.  It forces the recognition of the primary key if one is declared.

You would obviously need to change the details in the above - changing the connection string and sql string and replacing OleDb objects with SQL equivalents and so on.  But can I suggest that you try this approach and see if it overcomes the problem?

Roger

Thanks Sancler,

I am going to try the code you recommended with an Access database first, if it works I'll try to adapt the code for SQL..

Hi Roger,

I keep getting the followin error when trying your code to access and Access database:

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

Please note that I have the Imports System.Data.OleDb at the top of the form

Try putting the offending code in a Try ... Catch block so that we can get more details of the error.  Using the code I gave it would be

    Public Sub GetTopicData()
        If con Is Nothing Then
            con = New OleDbConnection(constr)
        End If
        Try
            If dtTopics Is Nothing Then
                dtTopics = New DataTable
                daTopics = New OleDbDataAdapter(sqlTopics, con)
                daTopics.FillSchema(dtTopics, SchemaType.Source)
                Dim cb As New OleDbCommandBuilder(daTopics)
            End If
            daTopics.Fill(dtTopics)
        Catch ex As Exception
            MsgBox(ex.Message) 'or
            'Debug.WriteLine(ex.Message)
         End Try
    End Sub

But you will need to adjust that for your code.

Roger

Hi Roger,

Here is the error message generated after using the Try.. Catch block:

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).


In addition, I would appreciate it if you could explain the absence of a dataset in the code you used.
The concept of a schema is new to me. What I understood previously was that I needed a connection to the database (which is clearly present in your code), a data adapter (which is also present), and a dataset (which is not there). I see that you used the Fill method of the data adapter to fill the data table directly without going through the Dataset. Usually I would use a code like the following to fill the datatable:

DataAdapter.fill(Dataset, "table name")


In the absence of a dataset, I would also have trouble binding the textboxes to the database columns . I use the followin code to bind the text boxes on the form to the databse feilds:

Me.txtFirstName.Databindings("Text",DatasetName, "TableName.FirstName")

Finally, I would like to thank you for your patience.

Tariq