I have a stored procedure that updates a row in a table. I'm using C# to pass all the parameters into the stored procedure like this:
public int editFestival(String festId, String festNaam, String festLocatie, String festDatum,
String festDuur, String festEindDatum, String festUrl)
{
sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["project"].ConnectionString);
sqlCommand = new SqlCommand("EditFestival", sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new SqlParameter("@festId", Convert.ToInt32(festId)));
sqlCommand.Parameters.Add(new SqlParameter("@festNaam", festNaam));
sqlCommand.Parameters.Add(new SqlParameter("@festLocatie", festLocatie));
sqlCommand.Parameters.Add(new SqlParameter("@festDatum", Convert.ToDateTime(festDatum)));
//Also tried -> , SqlDbType.date)).Value = festDatum
sqlCommand.Parameters.Add(new SqlParameter("@festDuur", Convert.ToInt32(festDuur)));
sqlCommand.Parameters.Add(new SqlParameter("@festEindDatum", Convert.ToDateTime(festEindDatum)));
sqlCommand.Parameters.Add(new SqlParameter("@festUrl", festUrl));
sqlConnection.Open();
sqlTransaction = sqlConnection.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
rows = sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
return rows;
}
Although the String festDatum (which means festDate in english) is in a correct format (yyyy-MM-dd) it's not accepted by the stored procedure.
When I execute the stored procedure with my own given variables, this is what it generates:
USE [groep2_festivals]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[EditFestival]
@festId = 1,
@festNaam = N'Rock Werchter',
@festLocatie = N'Werchter - Belge',
@festDatum = 2013-07-01, --This is where it says that there's a syntax error near '-'
@festDuur = 4,
@festEindDatum = 2013-07-04,
@festUrl = N'www.rockwerchter.be'
SELECT 'Return Value' = @return_value
GO
When I manually surround both dates with single quotes it works fine. What am I doing wrong? Or would it be as simple as surrounding the parameter inside the update statement with single quotes?
EDIT: This is the original stored procedure (should have posted this earlier)
USE [groep2_festivals]
GO
/****** Object: StoredProcedure [dbo].[EditFestival] Script Date: 10/05/2013 12:44:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Robbie Vercammen
-- Create date: 10/05/2013
-- Description: Veranderd de gegevens van een bepaald festival
-- =============================================
CREATE PROCEDURE [dbo].[EditFestival]
(
@festId int,
@festNaam nvarchar(255),
@festLocatie nvarchar(255),
@festDatum date,
@festDuur int,
@festEindDatum date,
@festUrl nvarchar(255)
)
AS
BEGIN
SET NOCOUNT ON;
UPDATE festivals
SET fest_naam = @festNaam, fest_locatie = @festLocatie, fest_datum = @festDatum,
fest_duur = @festDuur, fest_einddatum = @festEindDatum, fest_url = @festUrl
WHERE fest_id = @festid
END
EDIT: What I'm trying I execute the stored procedure from within management studio

The generated code is already posted above ;) When executing that it just says
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '-'.
2013-07-01is not a date literal. Use'2013-07-01'instead. This is not an issue with the parameterized version (with dates). This assuming you are using the query window to run the query.Convert.ToDateTime(festDatum)really give you a valid object of typeDateTime?@FestDataumand@FestEindDatumarguments to the stored procedure? I suspect the stored procedure might be taking in string arguments and converting them into dates in the code.'2013-07-01',01-Jul-2013,#2013-07-01#. This is SSMS trying to convert the string to a date and failing.