locked
unable to addnew or update or delete RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

     

    I am working with a Access 2000 database with a vb.net 2005 GUI.

    The access table I am working with is greater than 100 feilds which is causing me issues. I get a "query is to complex" error if I try to hit the save button after modifying a record. However If I hit the addnew record button it works if I hit save. But any modification to a previously created record and I get the query to complex error.

    I did some reading and found that the JET 4.0 (access) database I am using has a 99 feild update limit.

    I got around this error by making my sql query pull less feilds from the database (I pulled in two fields for testing purposes). This made the update command less complicated once I would hit save. Great I thought I solved it. The update record when I hit save would work without a problem.

    But now when I add a record to the access database by hitting the addnew button on the binding navigator and then making a change to blank record and hit the save button I get this error..."Update requires a valid InsertCommand when passed DataRow collection with new rows."

    Its nice that I can get both the update and insert commands to work individually but I need them to work together. The Access database table cannot change either because it is used by other programs.

    I am going to start making to forms one for editing and one for adding a new record. This is not the way I want to do it however any help would be great

    Here is my limited code..

    PublicClass Form2

    PrivateSub Form2_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

    'TODO: This line of code loads data into the 'DataSet1.BrokerPayout' table. You can move, or remove it, as needed.

    Me.BrokerPayoutTableAdapter_DATA1.Fill(Me.DataSet1.BrokerPayout)

    EndSub

    PrivateSub SaveToolStripButton_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles SaveToolStripButton.Click

    Me.Validate()

    Me.BrokerPayoutBindingSource_DATA1.EndEdit()

    Me.BrokerPayoutTableAdapter_DATA1.Update(Me.DataSet1.BrokerPayout)

    EndSub

    EndClass

    Monday, February 11, 2008 4:59 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Adel,

    Based on your post, when you use datasource wizard to fetch data from access database the ide doesn't generate insertcommand or updatecommand for your use. 

    When we fetch data from multi table, ide can't generate insertcommand, updatecommand and deletecommand. In this scenario you fetch data from a single table. Based on my experience, I think that your question relates your table doesn't have primary key. Could you confirm this? When we use ide to generate commands to update data to database, ide needs the primarykey to properly generate delete, update , insert statement to save data. One workaround is that  you need to set a field(or several fields) as primary key. Hope this helps.

    Best regards,
    Riquel
    Friday, February 15, 2008 4:27 AM
    Moderator