Solved

Error configuring SQl Data Adapter

Posted on 2006-06-10
8
273 Views
Last Modified: 2010-04-23
Hi,

I get errors in the Insert, Update and Delete commands when using configuring the SqlDataAdapter using the configuration wizard.. the Adapter works well with every thing except when I try to save changes.
0
Comment
Question by:tariqanis2001
  • 4
  • 4
8 Comments
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
0
 

Author Comment

by:tariqanis2001
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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

0
 

Author Comment

by:tariqanis2001
Comment Utility
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..
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:tariqanis2001
Comment Utility
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
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
0
 

Author Comment

by:tariqanis2001
Comment Utility
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

0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
Comment Utility
Tariq

That particular error is, I think, usually connected with a field name in an SQL query being a "reserved word" in the database concerned.  What are the field names in the database table you are using for your tests?  If, as you said you were going to do, you tried to test it in Access, the list of reserved words for that can be found here

   http://support.microsoft.com/default.aspx?scid=kb;[LN];Q286335

I don't work much with SQL databases myself, so have not got a note of a source for reserved words in those.  But I'm sure a Google search, or a search or question on here - Experts Exchange - should throw up some references on that for you.

On the schema point, the only reason for my suggestion of using it was to try and get over the problem that you originally reported.  But it now looks as though that problem might have had a different cause - namely, reserved word/s - if so, we'll be able to drop that line (it won't do any good) and tackle the root cause.  

The simplest way of doing that is to change any field names that are causing problems in the database itself.  Would that be possible in your scenario?

As to the dataset/datatable question, a dataset is a collection of datatables.  If you have a number of datatables, a dataset is a convenient place to keep them all.  If you want to create DataRelations between different datatables then you have to use a dataset, because it is in the dataset that the collection of DataRelations is stored.  But, such cases apart, it is possible to work with free-standing datatables.

When you think about it, everything you do with your data you do at the level of the datatable.  So, when you use a dataadapter's .Fill method, you have to specify the datatable, not just the dataset.  Similarly with .Update.  And when you bind a control to a datasource you have to name not just the dataset but also the datatable.  In the binding code you quote

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

the datatable is identified by the "TableName" bit of "TableName.FirstName")

So - while there is nothing wrong with using datasets if you prefer - it is equally possible (and I often find it easier) to dispense with that extra level and just work directly with the datatable.  You've seen how that works with the .Fill method.  The equivalent code for binding - putting the .Add in ;-) - would be

   Me.txtFirstName.Databindings.Add("Text", datatable, "FirstName")

Roger
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

10 Experts available now in Live!

Get 1:1 Help Now