Why SqlParameter always set parameter is null?

Question

using (SqlCommand cmd = new SqlCommand())
{
    DataTable dt = new DataTable();
    cmd.CommandText = p;
    cmd.CommandType = CommandType.Text;
    SqlConnection con = new SqlConnection("--");
    cmd.Connection = con;
    cmd.Connection.Open();
    foreach (var parameter in filters)
    {
        var type = parameter.Value.GetType();
        var param = new SqlParameter(parameter.Id, parameter.Value);
        param.Direction = ParameterDirection.Input;
        param.Value = parameter.Value;

        cmd.Parameters.Add(param);
    }
    dt.Load(cmd.ExecuteReader());
    cmd.Connection.Close();
    return dt;
}

Here is my code. Variable "p" is my sqlQuery string. Variable "filters" is my parameter list. For example: parameter.Id = "@offerId" (as string) and parameter.Value = 1230 (as Integer) Also my query is like that : "select * from Offers where ID = @offerID and IsActive = @isActive"

when pass into cmd.ExecuteReader(), in IntelliTrace shows my query like that:

--The data may be truncated and may not represent the query that was run on the server
USE [DB];

GO

--Type and value data was not available for the following variables. Their values have been set to defaults.
DECLARE @offerID AS SQL_VARIANT;
DECLARE @isActive AS SQL_VARIANT;
SET @offerID = NULL;
SET @isActive = NULL;

select  *  from Offers where  ID = @offerID and IsActive = @isActive

We tried lots of method for set. But always variables set null.

var param = new SqlParameter(parameter.Id, parameter.Value);, should it be var param = new SqlParameter(parameter.Id, /*your column data type, int or etc, SqlDbType.Something*/); — ah_hau
– ah_hau, Commented Sep 5, 2014 at 8:59
could you show the contents (obfuscated, if required) of your filters collection? — decPL
– decPL, Commented Sep 5, 2014 at 9:09
how about cmd.Parameters.AddWithValue(parameter.Id, parameter.Value); or cmd.Parameters.Add(parameter.Id, parameter.Value); directly without new variable declared ? — ah_hau
– ah_hau, Commented Sep 5, 2014 at 9:10
also, put a breakpoint on your parameter to make sure it has the correct name(@offerID) and value as expected — ah_hau
– ah_hau, Commented Sep 5, 2014 at 9:11

Wiktor Kopec · Accepted Answer · 2014-09-07 20:05:10Z

1

IntelliTrace currently only supports this kind of type information for log files from MMA scenarios. In your scenario, the type information from SqlParameter isn't collected; as a result all the variables in the query default to SQL_VARIANT with null values.

answered Sep 7, 2014 at 20:05

Wiktor Kopec

261 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

andrei.ciprian · Accepted Answer · 2014-09-07 20:24:22Z

Using your code on Visual Studio 2010 and SQL Server 2008 I tried to reproduce your scenario (or at least what I thought your scenario was as you weren't very specific). I created these tables: Q25682067 using int for the offer id and bit for the 'isActive' field; and Q25682067_Offers with sql_variant for each field, as suggested by your post.

CREATE TABLE [Q25682067]([ID] [int] NOT NULL, [IsActive] [bit] NOT NULL) ON [PRIMARY]
CREATE TABLE [Q25682067_Offers]([ID] [sql_variant] NOT NULL,[IsActive] [sql_variant] NOT NULL ) ON [PRIMARY]

Data pairs (1,false) and (1,true) added to each table. Now considering your filters are something like:

var filters = new Parameter[] { 
    new Parameter() {Id="@offerID ", Value=1},
    new Parameter() {Id="@isActive", Value=false}
};

where a Parameter might very swiftly be (without any consideration to OOP practices):

internal class Parameter
{
   public string Id;
   public object Value;
}

Now, this populates the data table:

cmd.CommandText = "select * from Q25682067 where ID = @offerID and IsActive = @isActive";

this does not:

cmd.CommandText = "select * from Q25682067_Offers where ID = @offerID and IsActive = @isActive";

Using the SqlParameter constructor like that binds your parameters to SqlDbType.Int and SqlDbType.Bit instead of SqlDbType.Variant, which seems to be your weapon of choice. That's why your code works with the first table definition, and not the second.

Collectives™ on Stack Overflow

Why SqlParameter always set parameter is null?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related