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