Solved

Pass Parameter to Stored Procedure

Posted on 2013-06-10
3
526 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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error on link 14 40
Code works but it's slow 24 45
How useful is the free version of Selenium? 3 16
jquery progress bar 3 0
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now