CODEWITHSUNDEEP
c#sql-server.net-4.0
Gideon
Gideon

Reputation: 1507

Multiple usage of a parameter in a SQLCommand C#

I need to ask something again. I'm a PHP developer for two years. Previously, I got my hands with Java for a year and C# for at least some couple of months before Java. I'm in the process of relearning C#. The following C# string declaration is from a tested SQL script that determines if a reservation entry (from the [RESERVATION] table) overlaps with an existing Pending reservation (filtered by the [status] as [r] where clause)

string query = "select [r].[id], [first_name], [middle_name], [surname], [extension], [reservation_time_from], [reservation_time_to] " +
    "from [RESERVATION] as [r] " +
        "join [CUSTOMER] as [c] on [r].[customer_id] = [c].[id] " +
    "where [reservation_date] = @reservation_date and " +
        "( @reservation_time_from between [reservation_time_from] and [reservation_time_to] or " +
            "@reservation_time_to between [reservation_time_from] and [reservation_time_to] " +
        ") or " +
        "( [reservation_time_from] between @reservation_time_from and @reservation_time_to " +
            "or [reservation_time_to] between @reservation_time_from and @reservation_time_to " +
        ") and " +
        "[status] = 'Pending' " +
    "order by [transaction_date] asc";

I attached the parameters using this method:

command.Parameters.AddWithValue();

Now when I perform the query using the command.ExecuteReader(), it seems that the query does not fetch overlap schedules of [RESERVATION]. I got a hunch that it has something to do with the source of the data: that is a DateTimePicker object; since the column type of the columns are date only, but I'm not quite sure. I attached the values of those time picker using this code (that is a parameter for a time column):

command.Parameters.AddWithValue("@reservation_time_from", SqlDbType.Date).Value = ((DateTime)param[value]).TimeOfDay;

Can someone assist me? Note though that some parameters (such as @reservation_time_from) occurred at least twice on the query string. Thanks for the support.

EDIT:

These is how I attached parameters (sorry the previous example is wrongly pasted, Tee hee):

command.Parameters.AddWithValue("@reservation_time_from", SqlDbType.Time).Value = ((DateTime)param['reservation_time_from']).TimeOfDay;
command.Parameters.AddWithValue("@reservation_time_to", SqlDbType.Time).Value = ((DateTime)param['reservation_time_to']).TimeOfDay;
command.Parameters.AddWithValue("@reservation_date_from", SqlDbType.Date).Value = ((DateTime)param['reservation_date_from']).Date;
command.Parameters.AddWithValue("@reservation_date_to", SqlDbType.Date).Value = ((DateTime)param['reservation_date_to']).Date;

Upvotes: 0

Views: 943

Answers (2)

stuartd
stuartd

Reputation: 73243

AddWithValue takes the value as the second parameter, and so is interpreting the SqlDbType as the parameter value. You will need to use Add instead of AddWithValue:

command.Parameters.Add("@reservation_time_from", SqlDbType.Time)
                  .Value = ((DateTime)param[value]).TimeOfDay;

From the documentation:

AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value.

Edit: what you're doing only works because the SqlCommand is correctly assigning the SqlDbType based on the value provided in the Value set and the SqlDbType is irrelevant: run this code to see:

var cmd = new SqlCommand();
var param = cmd.Parameters.AddWithValue("@date_from", SqlDbType.Date);
Console.WriteLine(param.ParameterName);
Console.WriteLine(param.SqlDbType);
Console.WriteLine(param.Value.GetType());

Console.WriteLine();

param.Value = DateTime.Today;
Console.WriteLine(param.ParameterName);
Console.WriteLine(param.SqlDbType);
Console.WriteLine(param.Value.GetType());

This outputs:

@date_from
Int
System.Data.SqlDbType

@date_from
DateTime
System.DateTime

Upvotes: 1

Gideon
Gideon

Reputation: 1507

Sorry my bad, it seems that I've slightly overlooked the mapping of sql type on some of the parameters:

command.Parameters.AddWithValue("@reservation_date_from", SqlDbType.Date).Value = ((DateTime)param['reservation_date_from']).Date;
command.Parameters.AddWithValue("@reservation_date_to", SqlDbType.Date).Value = ((DateTime)param['reservation_date_to']).Date;

Instead of using SqlDbType.Date, I must use SqlDbType.DateTime. I overlooked that the sql type of the columns [reservation_date_from] and [reservation_date_to] are both DateTime. Now the overlap trap works like a charm. Sorry for the silly mistake...

Upvotes: 0

Related Questions