none
Link main form and datagridview when both main form and datagridview use stored procedures as source

    Question

  • I am creating a vb.net project in VS 2015.  I am very familiar with Access and subforms, and more proficient than most at VBA, so kind of stumbling through but more or less doing pretty well.

    I am using a MSSQL back end and trying to use only stored procedures rather than table references.

    The system currently consists of only 6 tables--3 Primary tables and 3 Cross tables linking each one to the other.

    The 2 tables I am working with are tblAccount and tblAccountUser (which also links to the tblUser table).  I also have stored procedures created for each table and each cross table.

    In the VS environment, I am able to create a form which works perfectly, as long as I use the actual tables.  I go to my dataset, select 'Details' in the dropdown next to the tblAccount table, and drag it into the form.  I then expand the tblAccount listing and the tblAccountUser appears as an option.  I drag it into the form and I have a working 'form/subform' relationship.

    When using sp's instead of tables, I get the main form working just fine by clicking 'Details' and dragging the sp onto the form.  But there is no reference to the AccountUser sp 'under' the Account sp, and when I drag the AccountUser sp onto the form, it works, but is not filtered to the AccountID on the main form.  The AccountUser sp takes the @AccountID parameter.

    I have looked for a way to manually link the AccountID text box to the AccountID in the datagridview, and don't see how. 

    Any help would be greatly appreciated.

    Thursday, April 6, 2017 9:27 PM

Answers

  • Hello,

    I don't have code samples that match what you are doing but do have advice, rather than use strong typed classes creating using the data wizards in Visual Studio in combination with stored procedures consider one of two alternate methods.

    Before getting into alternates, the approach you have taken under the covers is using DataTable objects within a DataSet which on top has your strong typed classes accessed via a TableAdapter DataSet and BindingSource.

    The first alternate method is to use SqlClient data provider and manually setup the relations. I have a simple master detail code sample for master-detail that shows how to setup relationships and display data in DataGridView controls and TextBox controls here. To push farther and not use DataGridView controls you can bind to controls such as TextBox e.g. txtFirstName.DataBinding.Add(....

    Another alternative is to rig up your data via Entity Framework where there are countless advantages but does not make is that much easier if you have never worked with Entity Framework before.

    Both Entity Framework and SqlClient data provider support Stored Procedures but most of the time a SP can causes issues down the road. 

    Both Entity Framework and SqlClient data provider supports all joins and cross table queries with much more control the using Visual Studio's data wizards.

    Wish I had a solution in regards to But there is no reference to the AccountUser sp 'under' the Account sp, and when I drag the AccountUser sp onto the form but there is not a method I'm aware of without writing custom code in the TableAdapter which really defeats the use of data wizards.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Thomas Thumb Friday, April 7, 2017 4:15 PM
    Thursday, April 6, 2017 10:44 PM
    Moderator

All replies

  • Hello,

    I don't have code samples that match what you are doing but do have advice, rather than use strong typed classes creating using the data wizards in Visual Studio in combination with stored procedures consider one of two alternate methods.

    Before getting into alternates, the approach you have taken under the covers is using DataTable objects within a DataSet which on top has your strong typed classes accessed via a TableAdapter DataSet and BindingSource.

    The first alternate method is to use SqlClient data provider and manually setup the relations. I have a simple master detail code sample for master-detail that shows how to setup relationships and display data in DataGridView controls and TextBox controls here. To push farther and not use DataGridView controls you can bind to controls such as TextBox e.g. txtFirstName.DataBinding.Add(....

    Another alternative is to rig up your data via Entity Framework where there are countless advantages but does not make is that much easier if you have never worked with Entity Framework before.

    Both Entity Framework and SqlClient data provider support Stored Procedures but most of the time a SP can causes issues down the road. 

    Both Entity Framework and SqlClient data provider supports all joins and cross table queries with much more control the using Visual Studio's data wizards.

    Wish I had a solution in regards to But there is no reference to the AccountUser sp 'under' the Account sp, and when I drag the AccountUser sp onto the form but there is not a method I'm aware of without writing custom code in the TableAdapter which really defeats the use of data wizards.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Thomas Thumb Friday, April 7, 2017 4:15 PM
    Thursday, April 6, 2017 10:44 PM
    Moderator
  • Thank you Karen.  I believe this is going to be just what I need.
    Friday, April 7, 2017 4:15 PM