8 Replies - 546 Views - Last Post: 08 July 2013 - 09:57 AM

#1 Quinncunx  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 03-March 12

How to populate a data grid using a JOIN query

Posted 08 July 2013 - 07:05 AM

I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.

So I have 2 tables for products in the system
products
+-----------+------------+
|productid  |productname |
|Int        |varchar(50) |
+-----------+------------+


productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int      |Int       |Text        |Money |Int   |
|         |FK_From_  |            |      |      |
|         |productid_|            |      |      |
|         |products  |            |      |      |
+---------+----------+------------+------+------+


One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.

CREATE VIEW [dbo].[v_stock]
	AS SELECT        tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock
	FROM			 tab_productdetails INNER JOIN
                         tab_products ON tab_productdetails.productid = tab_products.productid


The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".

I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.

Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.

Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: How to populate a data grid using a JOIN query

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8910
  • View blog
  • Posts: 33,420
  • Joined: 12-June 08

Re: How to populate a data grid using a JOIN query

Posted 08 July 2013 - 07:20 AM

OMS?

Quote

The problem I am having is then returning the data from this query into a data grid,

.. a data grid in some programming language like VB.NET, Javascript, ASP.NEt (etc), or just in MSSQL?
Was This Post Helpful? 0
  • +
  • -

#3 Quinncunx  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 03-March 12

Re: How to populate a data grid using a JOIN query

Posted 08 July 2013 - 07:36 AM

View Postmodi123_1, on 08 July 2013 - 07:20 AM, said:

OMS?

Quote

The problem I am having is then returning the data from this query into a data grid,

.. a data grid in some programming language like VB.NET, Javascript, ASP.NEt (etc), or just in MSSQL?


Sorry - OMS order management system

Data grid in VB.NET
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8910
  • View blog
  • Posts: 33,420
  • Joined: 12-June 08

Re: How to populate a data grid using a JOIN query

Posted 08 July 2013 - 08:18 AM

... so this is a VB.NET question about loading data from a databsae into your app, or is there a specific MSSQL issue I am not reading?
Was This Post Helpful? 0
  • +
  • -

#5 Quinncunx  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 03-March 12

Re: How to populate a data grid using a JOIN query

Posted 08 July 2013 - 08:59 AM

No - I know how to fill a data grid using VB - Why is using the above query not populating the data grid. I suspect that it is because the table that the grid and query are bound to do not contain all the columns that are returned.
Was This Post Helpful? 0
  • +
  • -

#6 Quinncunx  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 03-March 12

Re: How to populate a data grid using a JOIN query

Posted 08 July 2013 - 09:05 AM

Addendum:
I am not sure if this is the right method to solve this. But I created a new TableAdapter that was bound to my stored procedure. Then got the data grid to load the data from that query.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8910
  • View blog
  • Posts: 33,420
  • Joined: 12-June 08

Re: How to populate a data grid using a JOIN query

Posted 08 July 2013 - 09:12 AM

... so this is a VB.NET issue then?
Was This Post Helpful? 0
  • +
  • -

#8 Quinncunx  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 03-March 12

Re: How to populate a data grid using a JOIN query

Posted 08 July 2013 - 09:45 AM

I don't see how it is specific to VB.NET when the solution was to add another table adapter to the SQL database. But heh, your the moderator so you decide.
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8910
  • View blog
  • Posts: 33,420
  • Joined: 12-June 08

Re: How to populate a data grid using a JOIN query

Posted 08 July 2013 - 09:57 AM

Tableadapters are client (read: not part of the DB) objects used to facilitate the data between your VB.NET code and the database. I mean - you are not mucking around in MSSQL Management Studio with a table adapter, right?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1