[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Pass Parameter to Stored Procedure

Posted on 2013-06-10
3
Medium Priority
?
580 Views
Last Modified: 2013-06-10
I need to pass a parameter to a stored procedure from code.

This is the stored procedure:

ALTER PROCEDURE [dbo].[usp_getPriceChart]
      -- Add the parameters for the stored procedure here
AS
DECLARE      @SymbolID AS integer

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

SELECT QuoteDate, ClosePrice
      FROM StockHist
    WHERE SymbolID=@SymbolID AND QuoteDate <= GETDATE()
      AND QuoteDate >= DATEADD(day,-65,GETDATE())
    ORDER BY QuoteDate

      
      END

Here is the c# code:

Int16 intSymbolID = 6939;

 SqlCommand cmdGetChartData = new SqlCommand();
            cmdGetChartData.CommandType = CommandType.StoredProcedure;
            cmdGetChartData.CommandText = "usp_getPriceChart";

            // Add the input parameter and set its properties.
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@SymbolID";
            parameter.SqlDbType = SqlDbType.Int;
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = intSymbolID;
            cmdGetChartData.Parameters.Add(parameter);

            dscWatchListChart.SelectCommand = cmdGetChartData.CommandText;  

dscWatchListChart.DataBind();  // Data is not displayed.

Works when the stored procedure is hard coded.
SET @SymbolID = 6939

What should I change?

Thanks
0
Comment
Question by:Dovberman
  • 2
3 Comments
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 1200 total points
ID: 39234201
Hi,
please change the beginning of your stored procedure from
ALTER PROCEDURE [dbo].[usp_getPriceChart]
      -- Add the parameters for the stored procedure here
AS
DECLARE      @SymbolID AS integer

BEGIN

Open in new window

to
ALTER PROCEDURE [dbo].[usp_getPriceChart]
   @SymbolID AS integer
      -- Add the parameters for the stored procedure here
AS
BEGIN

Open in new window


You have to move the variable right after the procedure name.

HTH
Rainer
0
 

Author Comment

by:Dovberman
ID: 39234351
Thank you.
0
 

Author Closing Comment

by:Dovberman
ID: 39234352
Thank you.
0

Featured Post

Technology Partners: 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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

872 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