none
VS2010 generated CRUD code and Mysql RRS feed

  • Question

  • Hi all,

    been having issues with tableadapter usage with Mysql as backend DB.

    Using Mysql 3.6.8 connector library

    VS2010, C# winforms

    I have 3 tableadapters (not linked via data designer code or anything).

    1 is a parent other two are children.

    children can insert, update data to the underlying DB - no problems wont be deleting anything but hidding them via a boolean column (true display false don't select and copy / delete those entries to a archive manually).

    the parent is another matter....

    Note With the children i did have this same problem but got it solved by i think fluking it (don't understand why it failed so many times only to work).

    what did I do?   I kept going into a loop from data designer to compiling the app and checking if data got written to the mysql DB backend,

    In  data designer form, I right click on the table click on advanced button, click refresh table checkbox, save or close on that popup, click next, wait for the select, update, insert statements to be generated then click finish.

    In one of these loops I hit pay dirt and so have left them as they are.

    the select statement for these two adapters is SELECT * from TABLE where fieldname = @PARAM1 I use this command to retreive the record(s).

    this.tableadapter.Fill(this.dataset.TABLE, textBox1.text) this being the customer code;

    To save changes or new entries i do this.tableadapter.Update(this.dataset.TABLE);

    So with the parent I have a FILL/GETDATA pair and a FILLBY/GETDATABY pair.

    the FILL/GETDATA is a select * from table statement, I then loop through and load a combobox with valid names.

    the FILLBY/GETDATABY pair is a selective call based on the name selected in the combobox (select * from table where fieldname = comboBox1.text).

    PROBLEM....

    I cannot get any changes made flow through to the DB Table (I  view the changes via the Mysql workbench app).

    I am wanting to use the VS2010 generated code if possible (saving me time but after two weeks of hassles am wondering if this is a good way to go).

    Any all help gratefully accepted.

    I have searched the net unable to find anything that would allude how to over come this particular problem.

    I have also added but then removed a Update SQL statement as i couldn't get that to work (I've got like 29 fields databound controls so don't really want to do all the coding myself when it's already done via VS2010).

    Dave.

    Tuesday, January 10, 2012 12:28 AM

Answers

  • Well I've posted a reply to the person delegated to my "bug" and he hasn't repied to my last post - Sigh.

    It appears that because I'm using 2 queries in the one adapter and one query is a parameterised one (find it doesn't matter which order they are put) I am encountering this error.

    I believe that if i had hand crafted the code there would be no issues but because I'm using Auto Generated code I've hit a glitch with how things work and so I'm now coming back to look at this matter and will try this monkey patch...

    1. Create a 2nd TableAdapter and dataset.

    2. Remove 1 query and put it into the 2nd TableAdapter.

    3. see if this approach allows me to save data via the parameterised query.

    Failing that I'll have to hand craft the code to do this.

     

    Very sad that the Mysql connector doesn't act as it should considering all mysql docs i've come across points me to the MSDN web pages.

    Have regetable marked this as answered but in fact it isn't as of the closure of this matter.

     

    Dave.

     


    Hopeless is thee who cannot ask for help.
    • Marked as answer by dglnz Friday, February 3, 2012 6:01 AM
    Friday, February 3, 2012 6:01 AM

All replies

  • Hi dglnz,

    Welcome to MSDN Forum.

    After reading your post, do you mean the CRUD code which VS auto-generated couldn't update your database? If I understand correctly, please post the code which VS auto-generated, your table schema and the code you manually write to update your database here. This is, so I can help you more effectively.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 11, 2012 8:48 AM
    Moderator
  • Hi Allen,

    Thanks for the reply,

    I am going to have a look at the customer TableAdapter just in case their is something different the properties to the Detail TableAdapters that might affect the updating, So if I don't find anything different their what would be the most appropraite way of posting the code?

    Zip, plain txt, pastiebin site??

    regrds,

    Dave


    Hopeless is thee who cannot ask for help.
    Wednesday, January 11, 2012 7:55 PM
  • Hi dglnz,

    If you want to see the T-SQL that TableAdapter generated, you can get it from porperties window.

    Please look at the .xsd file, left-click your TableAdapter,

    and then, look at your properties window,

    By the way, I used SQL Server2008R2, it works well. Based on the database is 3rd party, if it couldn't help you to solve the issue, I suggest you to post in the MySQL Forum. This is, so you may get more help.

    Best Regards

     


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, January 12, 2012 7:39 AM
    Moderator
  • Hi Allen,

    Yes I've got a posting on this in the MYSQL forums with no help at all so far.

    below is a visual display of what i have for the cust_data TableAdapter.

     

    As you can see i have 2 sets of SQL code.

    1 is a "select * from table"

    2 is a "select * from table where cust_name = @param1"

    The Delete, update, Insert Auto-Generated code is done I believe on the 1st SQL Statement, and from my observations isn't "hooked" what so ever to the 2nd (But i'd have thought they both would be connected to those Auto-Generated calls as I've not found anything that implies otherwise).

    I've just thought maybe if I swap the two around as below (same SQL statements just a positional switch that it may just work).

    So as you can see (well I hope) I have got Auto Generated select, Insert, Update & delete statements associated with my cust_dataTableAdapter, it has 2 SQL statements AND that when i do a search using FILL/GETDATA pair passing in the customer name I get a Dataset back.

    Now if I where to add a new or edit a value will it be seen in the underlying TABLE?

    But alas the changes don't go through.


     

    private void Form1_KeyDown(object sender, KeyEventArgs e)
    {
        if (beenin && e.KeyData == Keys.F3)
        {
            comboBox1.Items.Add(textBox29.Text);
            textBox29.SendToBack();
            textBox29.Visible = false;
            this.fourstar_gdsystemDataSet.cust_data.AcceptChanges();
            this.cust_dataTableAdapter.Update(
                this.fourstar_gdsystemDataSet.cust_data);
            beenin = false;
        }
    }
    

     


    Code above I write changes to the customer record back to the DB

    The code is also for Updating & Insertion(as the code block below can be used for just the same thing or have I missed something).

     

    if (dataGridView1.EndEdit())
        this.cust_barcodesTableAdapter.Update(
            this.fourstar_gdsystemDataSet.cust_barcodes);
    
    if (dataGridView2.EndEdit())
        this.cust_dispatchTableAdapter.Update(
            this.fourstar_gdsystemDataSet.cust_dispatch);
    
    

     


    Code above is what I use to Save changes to the Slave tables.

    Rgds,

     

    Dave.

     


    Hopeless is thee who cannot ask for help.
    • Edited by dglnz Thursday, January 12, 2012 10:57 AM
    Thursday, January 12, 2012 9:54 AM
  • Hi dglnz,

    ==>"Now if I where to add a new or edit a value will it be seen in the underlying TABLE?"

    ==>"But alas the changes don't go through."

    Do you mean you can't update the records? Based on the code you post, before calling update method, you called AcceptChanges method, this method set all of the row state as unchanged, so the database couldn't be updated.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Friday, January 13, 2012 7:34 AM
    Moderator
  • Correct.

     

    the code below (snipped from my previous code DOES update the connected underlying DB tables NOW.

    if (dataGridView1.EndEdit())
        this.cust_barcodesTableAdapter.Update(
            this.fourstar_gdsystemDataSet.cust_barcodes);
    
    if (dataGridView2.EndEdit())
        this.cust_dispatchTableAdapter.Update(
            this.fourstar_gdsystemDataSet.cust_dispatch);

    But I did have a time where it didn't, and it was a concurrancy error 0 of 1 rows affected issue.

     

    it doesn't matter if i remove that the 1st line of code (snipped from previous post) from *my* testing the data would not be seen in the underlying table via a 3rd party (Msql workbench or from within views accessible from inside VS).

            this.fourstar_gdsystemDataSet.cust_data.AcceptChanges();
            this.cust_dataTableAdapter.Update(
                this.fourstar_gdsystemDataSet.cust_data);

     

    It's worth noting i decided to post a Bug report to MYSQL about this error and have been asked to submit code so I've created a simple app accessing a Mysql DB & table and will be posting this to them.

     

    Maybe it's down to me not understanding the where's and why's but then it's strange to me that no one else has had this type of issue.

    I do know LOTS of people say not use VS generated tableadapters but to create you're own etc etc and to do the plumbing work to link things up yourself.

    any further ideas?

     

     


    Hopeless is thee who cannot ask for help.
    Saturday, January 14, 2012 9:18 AM
  • Hi dglnz,

    => But I did have a time where it didn't, and it was a concurrancy error 0 of 1 rows affected issue.

    What's the meaning of this?

    If concurrancy error was thrown when you update the database, I suggest you to create your own update command, it may more suitable.

    Here's the steps

    1. Open the DataSet in the designer.
    2. Click on the adapter portion of the relevant table.
    3. The properties window will show the UpdateCommand and DeleteCommand for this adopter.
    4. Expand Update and Delete commands by clicking the + sign on their left. This would show the CommandText property for these commands.
    5. Click on the value entered against CommandText. It will show you the SQL statement for that command. Edit its WHERE clause and remove all other columns except the columns included in your primary key.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 16, 2012 7:47 AM
    Moderator
  • Well is an image of the SQL that was generated by VS2010 and the properties window.

    Now As you reqeusted I removed all text after so that it now has .... "WHERE (id = @Original_id)".

    Recompiled the app and tested it, again no new data was saved to the DB after puting in new values.

    Also you may notice that the primary column id isn't present in the sample image above but I have since included it and re ran the program (incase that's the problem) but still no data is being saved to the backend Table.

    I have created a simply name address program that demonstrates my problem (that of not getting a new entry saved) how would i post this zipped up solution?

    To me this is very deflating as I see how the Auto generated code can speed up development but I've spent a LONG time dealing with a problem that's not very well explained or covered and see this as poor (even if I'm at fault for not understanding or that the DB isn't a MS product) I would have thought someone would've encountered this type of issue before now in the many forums on the net.

    Regards,

    Dave

     


    Hopeless is thee who cannot ask for help.
    Tuesday, January 17, 2012 7:57 AM
  • Hi dglnz,

    Please send the .zip file to v-alll@microsoft.com , I will test the project and help you to find the root cause.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, January 17, 2012 8:02 AM
    Moderator
  • I have just this morning received a reply from Mysql (raised a bug issue on this matter)

    here is the reply from my posting of the project their.

     

    [17 Jan 13:24] Bogdan Degtyariov
    I have done some more debugging and found a weird thing:
    
    after modifying the data row I see changes inside the dataset, but the RowState remains
    "Unchanged":
    
    ((System.Data.DataRow)(this.custData.custnames[0])).RowState is "Unchanged"
    
    That is why base DbDataAdapter.Update() method does nothing, although it is actually
    called from MySQL code (dataadapter.cs: lines 168-191):
    
    protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping)
    {
    
      List<MySqlConnection> connectionsOpened = new List<MySqlConnection>();
      try
      {
        // Open connections for insert/update/update commands, if 
        // connections are closed.
        foreach(DataRow row in dataRows)
        {
          OpenConnectionIfClosed(row.RowState, connectionsOpened);
        }
    
        int ret = base.Update(dataRows, tableMapping);
        return ret;
      }
      finally 
      {
        foreach(MySqlConnection c in connectionsOpened)
          c.Close();
      }
    }

     

    So it appears that it is to do with the Mysql connector dot net Library.

    I can still post the project to the above mentioned site _but_ I just find it strange that no one has encountered my problem before now.

    I shall follow through with the Mysql side (and with you're consent leave this thread Open until I get it resolved then post the result here more for others who may follow with similar issue than anything else).

     

    Appreciate all you're time and effort.

     

    rgds,

     

    Dave.


    Hopeless is thee who cannot ask for help.
    Tuesday, January 17, 2012 6:28 PM
  • Hi dglnz,

    I'm glad to hear that you are willing to post the solution here. I hope you can solve the issue soon.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 18, 2012 2:00 AM
    Moderator
  • Well I've posted a reply to the person delegated to my "bug" and he hasn't repied to my last post - Sigh.

    It appears that because I'm using 2 queries in the one adapter and one query is a parameterised one (find it doesn't matter which order they are put) I am encountering this error.

    I believe that if i had hand crafted the code there would be no issues but because I'm using Auto Generated code I've hit a glitch with how things work and so I'm now coming back to look at this matter and will try this monkey patch...

    1. Create a 2nd TableAdapter and dataset.

    2. Remove 1 query and put it into the 2nd TableAdapter.

    3. see if this approach allows me to save data via the parameterised query.

    Failing that I'll have to hand craft the code to do this.

     

    Very sad that the Mysql connector doesn't act as it should considering all mysql docs i've come across points me to the MSDN web pages.

    Have regetable marked this as answered but in fact it isn't as of the closure of this matter.

     

    Dave.

     


    Hopeless is thee who cannot ask for help.
    • Marked as answer by dglnz Friday, February 3, 2012 6:01 AM
    Friday, February 3, 2012 6:01 AM