-1

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
Greg
  • 476
  • 9
  • 23
  • 2
    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
  • https://stackoverflow.com/a/11293220/1507566 – Tab Alleman Mar 22 '19 at 14:14
  • 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
  • I have a problem with my data types, see my changes. – Greg Mar 22 '19 at 14:50

1 Answers1

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();
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