1

İ want pass some values also include datetime's. I am trying this way but i am getting this error "Error converting data type nvarchar to datetime2."

This is my function;

public string InsertRezervation(int GarageId, int MemberId, string StartDate, string EndDate, bool isFinish)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            int rowsAffected = 0;

            string format = "yyyyMMdd";
            DateTime startDate = new DateTime(2014, 12, 18);
            DateTime endDate = new DateTime(2014, 12, 25);
            if (StartDate != null && EndDate != null)
            {
                startDate = DateTime.ParseExact(StartDate, format, CultureInfo.InvariantCulture);
                endDate = DateTime.ParseExact(EndDate, format, CultureInfo.InvariantCulture);
            }
            connection = new SqlConnection(conn);
            connection.Open();
            command = new SqlCommand("Parking.Rezervation", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@PGarageId", System.Data.SqlDbType.NVarChar).Value = GarageId;
            command.Parameters.Add("@PMemberId", System.Data.SqlDbType.NVarChar).Value = MemberId;
            command.Parameters.Add("@PStartDate", System.Data.SqlDbType.NVarChar).Value = startDate;
            command.Parameters.Add("@PEndDate", System.Data.SqlDbType.NVarChar).Value = endDate;
            command.Parameters.Add("@PIsFinish", System.Data.SqlDbType.NVarChar).Value = isFinish;
            command.CommandText = "Parking.Rezervation";
            rowsAffected = command.ExecuteNonQuery();
            connection.Close();
            return string.Format("You entered: {0}", rowsAffected.ToString());

        }

And this is interface;

[OperationContract]
        [WebInvoke(Method = "GET", BodyStyle = WebMessageBodyStyle.WrappedRequest, RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json, 
        UriTemplate = "/InsertRezervation?GarageId={GarageId}&MemberId={MemberId}&StartDate={StartDate}&EndDate={EndDate}&isFinish={isFinish}")]
        string InsertRezervation(int GarageId, int MemberId, string StartDate, string EndDate, bool isFinish);

How can i fix this problem.

mesopotamia
  • 393
  • 1
  • 5
  • 19
  • Could you add the code of the stored procedure?. You pass paramenters of NVARCHAR type and if the stored procedure expects different types then you get the error. – Steve Dec 24 '15 at 10:18
  • Just guessing, but most likely you intended to pass 'startDate' and 'endDate' as datetime2 parameters - but you are actually passing them as NVarChar types: 'command.Parameters.Add("@PStartDate", System.Data.SqlDbType.NVarChar).Value = startDate;'. I suggest changing NVarChar to DateTime2 there ;) – Jan de Vos Dec 24 '15 at 10:30

1 Answers1

0

You are passing your DateTime values with SqlDbType.NVarChar type. Change them to SqlDbType.DateTime2 like;

command.Parameters.Add("@PStartDate", SqlDbType.DateTime2).Value = startDate;
command.Parameters.Add("@PEndDate", SqlDbType.DateTime2).Value = endDate;

Also change for GarageId and MemberId to SqlDbType.Int and for isFinish to SqlDbType.Bit if they fits your column types. Don't use NVarChar for all of them.

Also use using statement to dispose your connection and command automatically instead of calling Close method manually.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364