none
Merge Dataset Issue RRS feed

  • Question

  •  Hi All, I have an issue that I haven't been able to debug for several days now. I am merging an imported Excel spreadsheet to a Dataset Table;

     ImportedDS.Merge(Me.DUDataSet.TableCount, True, MissingSchemaAction.Add)

    It worked then it stopped working. The Two data sources have the correct content, 4 columns each with identical column names. The database Table definition and the Dataset have the first column set as a Primary Key.

    Basically the code executes (in Debug Start New Instance) but nothing is merged - no exception is thrown.Setting MissingSchemaAction to AddwithKey or Ignore has no impact. If I change MissingSchemaAction.Add to MissingSchemaAction.Error I get a message dialog  - ArgumentException was unhandled "Target DataSet missing definition for TableCount".   I can't find any information regarding what the messages means - can anyone give me a pointer please.

    Regards,

    Ian

    • Moved by Tina-Shi Tuesday, March 24, 2015 5:17 AM the issue is related to the dataset
    Monday, March 23, 2015 10:30 AM

Answers

  • The connection string should look like the code below.   Make sure "HDR=YES".  Does the table start in row one of the excel worksheet?

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
    Extended Properties="Excel 12.0 Xml;HDR=YES";
    
    
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";


    jdweng

    Wednesday, March 25, 2015 10:05 PM

All replies

  • What version of excel (file extension)?  What is the connection string?  Does the excel worksheet have row 1 as headers and data immediately on consecutive rows?

    Usually this type error indicates the query isn't returning any data.  Have you tried to open the worksheet to make sure there is still data?  Are you include a '$' at the end of the worksheet name?  Does the worksheet name match the name in your query?


    jdweng

    Monday, March 23, 2015 4:38 PM
  • Hi webrat11,

    Thank you for posting in MSND forum.

    Since this issue is related to the Dataset, so we will move this case to this dataset forum:https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataset ,  you will get better support.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 24, 2015 5:16 AM
  • Hi Ian,

    The error message sounds, to me, like your DataSet doesn't have a property for TableCount, which should be a DataTable. Judging from the way you're using it, I'd say that your DUDataSet is a Typed DataSet.

    Just for the heck of it, try it like this (the non-typed syntax) and see if that makes a difference:

    ImportedDS.Merge(Me.DUDataSet.Tables("TableCount"), True, MissingSchemaAction.Add)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, March 24, 2015 3:13 PM
  • @Bonnie,

    For Excel?

    But maybe is it because not the Table is used but the DataSet and does the OP not know that a DataSet is a binder around a DataTable which represents the excel sheet.


    Success
    Cor

    Tuesday, March 24, 2015 5:24 PM
  • Yeah, Cor ... I think I got it backwards. What he really should be doing is this:

    ImportedDS.Tables[0].Merge(Me.DUDataSet.TableCount, True, MissingSchemaAction.Add)

    What threw me off is that Ian said it originally worked and then it stopped. In any case ... Ian, try that and see if it works.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, March 25, 2015 3:19 PM
  • Hi all,

    Thanks for the feedback, just getting back to this one as I have another issue of which more later probably though I suspect they may be linked.

    Jdweng, I am importing .xls, the ImportedDS DataSet “collects” all the Excel data – I can see it in Debug. The Excel has header with column titles identical to the Table. The dataset is loaded up regardless of headings in the excel sheet. Data is inconsecutive rows and it loads into ImportedDS  ...  just can’t merge it into the Table. $ is included. The merge command is the issue I suspect not the Imported.

    Bonnie, your suggestions results in “Property access must assign to the property or use its value”. I believe my dataset is a Typed DataSet.

    Cor, I do get that the DataSet is a cached representation of the Table … I presume my access has to be though the DataSet though? I load the required data from the Database Table into the Dataset and work on that, save changes back to Database when done. Have I got this wrong?

    When I work the Database through Gridview driven by DuDataSet1 I can successfully Add, List, Select etc. it's just that when I try to access the Data programmatically (this relates to my other issue) “nothing” happens.

    The other issue is that the following doesn't work, it doesn't work for other non date row values either.

    Me.TableCountTableAdapter.FillByTop(Me.DuDataSet1.TableCount)
    Dim Value1 As Date = Date(DuDataSet1.TableCount.Rows(0).Item(0))
    CountDateLast = Value1

    I think I am getting the top row of the Table which is cached into the Dataset. I then read the first field Row 1, Column 1 which is a date and assign it to the variable CountDateLast.

    Debug shows that the target cell has the correct value in the cell (01/02/2015) but CountDateLast is 0" (#12:00:00 AM) because the variable sets itself to the default instead..because it doesn't like it. I have also tried Getby rather than Fill but no difference.

    Growing realisation that I might not know what I am doing. LOL.

    Any ideas please?

    Thanks, Ian

    Wednesday, March 25, 2015 4:42 PM

  • Growing realisation that I might not know what I am doing. LOL.

    Any ideas please?

    Thanks, Ian

    Then I mostly start with a new test project for myself and bring it back to the essentials. If it is then working like I wise. I copy the knowledge in my project.


    Success
    Cor

    Wednesday, March 25, 2015 8:26 PM
  • The connection string should look like the code below.   Make sure "HDR=YES".  Does the table start in row one of the excel worksheet?

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
    Extended Properties="Excel 12.0 Xml;HDR=YES";
    
    
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";


    jdweng

    Wednesday, March 25, 2015 10:05 PM
  • Ian, I am an idiot ... well, maybe not an idiot, but certainly more knowledgeable of C# than of VB. I used [ ] when I should have used ( ) in my last suggestion to you. It should have been this:

    ImportedDS.Tables(0).Merge(Me.DUDataSet.TableCount, True, MissingSchemaAction.Add)



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com




    • Edited by BonnieBMVP Thursday, March 26, 2015 2:19 PM formatting
    Thursday, March 26, 2015 2:16 PM
  • Thanks jdweng, Your strings have helped.

    regards, Ian

    Sunday, March 29, 2015 5:22 PM
  • Thanks jdweng, Your strings have helped.


    Ian --- does that mean that your connection string was wrong all along? I thought you had been getting data OK ....

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, March 29, 2015 8:36 PM
  • Bonnie,

    Webrat11 has made a new question. 

    It shows he is mixing a strongly typed dataset with an non st one and is then using the non st part of the st dataset.

    I've replied about that in his new question.


    Success
    Cor

    Tuesday, March 31, 2015 7:56 AM