SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Fill data grid with data from a JOIN query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dhatsah
Starting Member

United Kingdom
1 Posts

Posted - 07/08/2013 :  09:40:40  Show Profile  Reply with Quote
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.

Mar
Starting Member

44 Posts

Posted - 07/16/2013 :  08:46:51  Show Profile  Reply with Quote
It sounds like the stored procedure is not returning productname. So why not include it? Your specs say this is required.

It is not wise to make a temp table to patch up a problem. This will hurt performance, add an additional layer for allowing bugs to creep in without providing any benefit.

Sometimes views are created (or stored procedures) to limit access to a table. You can keep the table secure and give the user access to a view (or stored procedure) but in practice usually you use one or the other, not both. Every additional layer you add increases cpu cycles and is an additional place for things to break down. So every layer should provide some benefit that will counter the liability.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000