Solved

Pass Parameter to Stored Procedure

Posted on 2013-06-10
3
552 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
[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 44

Accepted Solution

by:
Rainer Jeschor earned 300 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…

739 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