locked
dataadapter doesnt generate update insert commands RRS feed

  • Question


  • Hi all. Ive a problem with dataAdapter that can't generate delete and update commands with the wizard.

    First, i have a simple project where im trying to save data to a simple 3 fields table table (CostumerID, Costumer ,Address) , without keys. (access database)

    I add a new data source with the wizard that makes the connection, i select customer table and vb 2005 (express) creates the dataset.

    At dataset design view  i realize that update and delete commands arent generated. If i configure the adapter with the wizard and selects generete direct methods doesnt work. It leaves update and delete commands blank. ive tried to manually select (New) to create the update,delete parameters but the commandtext property remains empty. At this point i tried to run the configure wizard tool to generate the commands properly but nothing.

    I would appreciatte that someone tells me whats wrong with this. I would like to know how to save my data if this adapter's wizard dont work.

    Thanks.

    Friday, May 12, 2006 11:17 AM

Answers

  • Your original UPDATE statement is the one generated by the CommandBuilder or wizard to ensure optimistic concurrency. If you just use the primary key in the WHERE clause, you could overwrite changes made by another user since your originally fetched the data.

    When you say the database is not updated, are you getting an error, or do you just not see the updates in the MDB?

    If you are just not seeing the updates, the two most common things I see are:

    (1) Calling AcceptChanges before you call Update. AcceptChanges commit all changes and updates the DataTable's RowStates, so that when you then call Update, there's nothing to actually Update.

    (2) If you are using a WinForms application, a common problem is that if you add the MDB to your project, VS asks you if you want to copy the file into your project. If you say Yes, it then sets some properties such that it will copy the MDB every time you run the project. This means that you never see the changes unless you look at the right copy of the MDB between test runs. See these two forum threads for more info on that: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390672&SiteID=1, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=343901&SiteID=1

    If you are not running into either one of these issues, please post some code that shows how you are trying to update the data, and any errors you get.

    Thanks,
    Sarah

    Monday, May 15, 2006 8:57 PM

All replies

  • I am also having similar problems with getting generated methods to work.. but I think my problem is related to stored procedures.

    I think your issue is because you dont have a primary key set. They wont work unless you have the key set.

    On your dataset in design veiw, right click on the CustomerID field and set it as primary key.

    It should work then.

    Amit

    Friday, May 12, 2006 11:48 AM
  •  

          If you are using more than 1 table while setting your DataAdapter or tblXXXTableAdapter`s Select command in design-time QueryBuilder,  it fails to create update, delete and insert commands. First select fileds only from the table you need first then click next until you are finished with creating the DataAdapter, then open it´s properties and modify SelectCommand´s CommandText to include other tables. I think that will solve your issue.

    Friday, May 12, 2006 7:17 PM
  • Hi,

    I guess you must have to have a primary key in your table so that the Update and Delete statements will be automatically generated. Its much similar to using a command builder in your code.

    If the Insert, Update and Delete statements are quite simple, then I suggest manually supplying them in the adapter.

     

    cheers,

    Paul June A. Domag

    Saturday, May 13, 2006 12:55 AM
  •  

    Hi ppl thanks for the replies.

    Im not able to write data in one simple table with 1 primary key on one field, with no realtionships or underlying tables. . Im trying to do this before im on two or more.

    I set up the primary key on the database (not only the dataset) and works fine. All commands are generated. But now they dont update the database. It only works the select command.

    Dont know whats wrong. doesnt work commands generated by the wizard on a small table?.

    if i have to set commands manually. where do i find documentation on how to  manage parameters?

    Thanks

    Database

    CustomerId (Primary Key) Long
    Customer String
    NIF String

    Update commandtext

    UPDATE    Customers
    SET              CustomerID = ?, Customer = ?, NIF = ?
    WHERE     (CustomerID = ?) AND (? = 1) AND (Customer IS NULL) AND (? = 1) AND (NIF IS NULL) OR
                          (CustomerID = ?) AND (Customer = ?) AND (? = 1) AND (NIF IS NULL) OR
                          (CustomerID = ?) AND (? = 1) AND (Customer IS NULL) AND (NIF = ?) OR
                          (CustomerID = ?) AND (Customer = ?) AND (NIF = ?)

    Is it ok?

     

    Monday, May 15, 2006 4:27 PM
  • I would expect an update statement like so:

    UPDATE   Customers
    SET           Customer = ?, NIF = ?
    WHERE    CustomerID = ?

    In other words, #1 you don't want to change the primary key value with the update statement since this value will not change in an UPDATE.  Also #2 you only need the primary key value in the WHERE clause to select the one correct record.

    It looks like the wizard cannot detect that your database has a primary key.

    Monday, May 15, 2006 5:08 PM
  • Yes, post back with more details on what database system you are using, this will help.
    Monday, May 15, 2006 5:09 PM
  •  

    All the stuff im using is:

    Vb 2005 express
    Default configuration.

    Microsoft Access 2000 Database

    The mdb has only one Customers table. with the structure above. (1 key and two text fields more)

    I tried your update query that seems ok  to me. But at design time if i do a "verify SQL Syntax" it tells me "This command is not supported by this provider."

    The conn string is
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Factu.mdb

    I guess i have installed MDAC 2.8 but im going to ensure that.

    Some idea?

    Monday, May 15, 2006 6:00 PM
  • Your original UPDATE statement is the one generated by the CommandBuilder or wizard to ensure optimistic concurrency. If you just use the primary key in the WHERE clause, you could overwrite changes made by another user since your originally fetched the data.

    When you say the database is not updated, are you getting an error, or do you just not see the updates in the MDB?

    If you are just not seeing the updates, the two most common things I see are:

    (1) Calling AcceptChanges before you call Update. AcceptChanges commit all changes and updates the DataTable's RowStates, so that when you then call Update, there's nothing to actually Update.

    (2) If you are using a WinForms application, a common problem is that if you add the MDB to your project, VS asks you if you want to copy the file into your project. If you say Yes, it then sets some properties such that it will copy the MDB every time you run the project. This means that you never see the changes unless you look at the right copy of the MDB between test runs. See these two forum threads for more info on that: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390672&SiteID=1, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=343901&SiteID=1

    If you are not running into either one of these issues, please post some code that shows how you are trying to update the data, and any errors you get.

    Thanks,
    Sarah

    Monday, May 15, 2006 8:57 PM

  • Incredible thing, but true. The database updates fine by telling "no" that question.

    Thank you very much to all. ;)

    Tuesday, May 16, 2006 1:25 AM