Solved

Variables in a view

Posted on 2004-08-28
3
210 Views
Last Modified: 2010-04-23
I need to select records for a Crystal Report.  I thought the best way was to create a view - i need to select records based on their shipment number - my code is below and the question follows:
--------------
DECLARE @ship char(5);
SET              @ship = '1012';
                          SELECT     dbo.tblShipments.id AS ShipNum, dbo.tblShipments.Destination AS Destination, dbo.tblShipments.Date AS ShipDate,
                                                  dbo.tblShipmentData.palletid AS Pallet, dbo.tblShipmentData.tubcode AS Product, dbo.tblShipmentData.qty AS qty,
                                                  dbo.tblShipmentData.dateprod AS DateProd
                           FROM         dbo.tblShipments RIGHT OUTER JOIN
                                                  dbo.tblShipmentData ON dbo.tblShipments.id = dbo.tblShipmentData.shipnum
                           WHERE     (dbo.tblShipments.id = @ship)
---------------------
Now, how, in VB or Chrystal, so i pass the current @ship value to the view????
0
Comment
Question by:Dan Flood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 11922412
you wouldnt do that ...

you would either use a stored procedure and pass a parameter or use ...

view ...
                          SELECT     dbo.tblShipments.id AS ShipNum, dbo.tblShipments.Destination AS Destination, dbo.tblShipments.Date AS ShipDate,
                                                  dbo.tblShipmentData.palletid AS Pallet, dbo.tblShipmentData.tubcode AS Product, dbo.tblShipmentData.qty AS qty,
                                                  dbo.tblShipmentData.dateprod AS DateProd
                           FROM         dbo.tblShipments RIGHT OUTER JOIN
                                                  dbo.tblShipmentData ON dbo.tblShipments.id = dbo.tblShipmentData.shipnum

SELECT * from YourViewName where ShipNum = '1012'
0
 

Author Comment

by:Dan Flood
ID: 11922710
oh!
Thank you - I didn't understand how to use views.
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11923052
no worries ...

Let me know if you require any further help.

Cheers,

Greg
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question