Call a SQL stored procedure in ASP.NET with an output parameter

Question

I'm attempting to call my sql stored procedure which takes RaceDate as an input and returns Location as an OUTPUT. I'm not sure how to call my code in ASP.NET, this is what I have thus far.

DateTime RaceDate = Calendar1.SelectedDate;
    // string RaceDate = TxtBoxCalendar.Text;

    TxtBoxCalendar.ReadOnly = true;

    SqlConnection con = new SqlConnection();
    con.ConnectionString = ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ToString(); 

    con.Open();
    SqlCommand Command = new SqlCommand();

    Command.CommandType = System.Data.CommandType.StoredProcedure; 
    Command.CommandText = "CheckRaceCalendarDates";

    Command.Parameters.Add("@RaceDate", SqlDbType.DateTime, RaceDate);
    Command.Parameters.Add("@Location", SqlDbType.String).Direction = ParameterDirection.Output;

    Command.Parameters.Add("@Location",SqlDbType.String).Direction = ParameterDirection.Output;

    Command.ExecuteNonQuery();
    con.Close();

I think i may also run into a problem with datatypes. RaceDate is a date the user clicks through a calendar and has to be converted to a string however the SQL parameter RaceDate is of type date.

CREATE PROCEDURE [dbo].[CheckRaceCalendarDates]
@RaceDates DATE,
@Location NVARCHAR(50) OUTPUT
AS
IF EXISTS 
    (
SELECT 
    RaceCalendar.RaceDates,
    Locations.LocationName 
FROM 
    Locations
INNER JOIN RaceCalendar ON locations.LocationId = RaceCalendar.LocationId
WHERE 
    RaceCalendar.RaceDates = @RaceDates
    )

    BEGIN
SELECT 
    @Location = Locations.LocationName 
FROM 
    Locations
INNER JOIN RaceCalendar ON locations.LocationId = RaceCalendar.LocationId
WHERE 
    RaceCalendar.RaceDates = @RaceDates
    END

Use the Add method instead of AddWithValue and then specify the direction of the parameter. — Sean Lange, Mar 22 '19 at 14:08
Possible duplicate of [Using stored procedure output parameters in C#](https://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp)? — Thom A, Mar 22 '19 at 14:10
On a different note, if no rows are returned then `@Location` will have a value of `NULL`, which it would do if the query doesn't run. There's no need for the`IF` statement. — Thom A, Mar 22 '19 at 14:12
Why convert a date from a picker to a string, then back to a date for the SP? — HardCode, Mar 22 '19 at 14:15
Possible duplicate of [Using stored procedure output parameters in C#](https://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp) — hardkoded, Mar 22 '19 at 14:28
So how would you get the date from the calendar? Something like: DateTime RaceDate = Calendar1.SelectedDate;? — Greg, Mar 22 '19 at 14:30

mhmte · Accepted Answer · 2019-04-18T14:44:56.810

2

Your problem about using parameter name ; you have used @RaceDates on stored procedure but you try to use @RaceDate on code.. They should be same.

Also, you need to add second parameter to your code like this ;

Command.Parameters.Add("@Location",SqlDbType.String).Direction = ParameterDirection.Output;

And after cmd.ExeCuteNonQuery();

string location = Command.Parameters["@Location"].Value.ToString();

edited Apr 18 '19 at 14:44

answered Mar 22 '19 at 14:16

mhmte

44
3

Getting an error with datatypes: "Cannot convert from system.datetime to int". DateTime RaceDate = Calendar1.SelectedDate; Command.Parameters.Add("@RaceDates", SqlDbType.DateTime, RaceDate); – Greg Mar 22 '19 at 18:22
Where you use int type ? I could not see, use int anywhere your code, please check carefully – mhmte Mar 23 '19 at 11:38

Call a SQL stored procedure in ASP.NET with an output parameter

1 Answers1