Why is my SqlParameter not being recognised?

Question

I am getting this error:

System.Data.SqlClient.SqlException: 'The parameterized query '(@inputKarakterSoort varchar(8000))SELECT TOP 2 * FROM Karakter ' expects the parameter '@inputKarakterSoort', which was not supplied.

But I think I have given the parameterized query.

My DAL:

public class SortedKarakterContext : ISortedKarakterContext
{
        public string conn { get; set; }

        public SortedKarakterContext()
        {
            this.conn = "MYCONNECTIONSTRING";
        }

        private SqlConnection GetConnection()
        {
            return new SqlConnection(conn);
        }

        public IEnumerable<KarakterDTO> GetSortedKarakters(string inputKarakterSoort)//User input was not supplied?? Hoe veranderen
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                var command = new SqlCommand("SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = @inputKarakterSoort ORDER BY NEWID();", connection);
                command.Parameters.Add("@inputKarakterSoort", SqlDbType.VarChar).Value = inputKarakterSoort;
                var reader = command.ExecuteReader();
                var sortedKarakters = new List<KarakterDTO>();

                while (reader.Read())
                {
                    var karakter = new KarakterDTO
                    {
                        KarakterId = (int)reader["KarakterId"],
                        KarakterSoort = reader["KarakterSoort"]?.ToString(),
                        KarakterNaam = reader["KarakterNaam"]?.ToString()
                    };

                    sortedKarakters.Add(karakter);
                }

                return sortedKarakters;
            }
        }
    }
}

I thought I did that at this line:

   command.Parameters.Add("@inputKarakterSoort", SqlDbType.VarChar).Value = inputKarakterSoort;

I am sure that inputKaraktersoort has a value because when I put a breakpoint there it says it has the right value.

The error is on this line:

var reader = command.ExecuteReader();

Can anyone help me?

As requested the value of the command and the inputKarakterSoort:

When I type this:

 inputKarakterSoort = "Defensive"

above: connection.Open()

It does work, but I want my parameter in my query.

When you say inputKaraktersoort has a value, do you mean it is not null? (As you would get this error if inputKaraktersoort was null rather than DBNull.Value). — sgmoore, Nov 10 '20 at 16:37
Have you tried using `AddWithValue()` method instead of just `Add()`? — Jamshaid K., Nov 10 '20 at 16:37
Then there are high chances that the value is being supplied null. Can you debug and post the screenshot of the parameters property of `command` object? — Jamshaid K., Nov 10 '20 at 16:40
@sgmoore It is a string that says: "Defensive". When I type this above connection.Open() --> inputKarakterSoort = "Defensive" it does work, but I want my parameter in my query. — , Nov 10 '20 at 16:41
@JamshaidKamran That is terrible suggestion. Don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/). — SMor, Nov 10 '20 at 16:44
@SMor, I would disagree with that. This query is fairly simple query and it won't cause any problems. Also, if my Table is modified multiple times for example from int to decimal or to bit column, it will be easier as the changes get implement straight away. Not as evil the article portrays it. You might have different use cases. — Jamshaid K., Nov 10 '20 at 16:49
@LardinoisJ, It seems your command object is null itself. It seems you have a break point before the command object is initialized. — Jamshaid K., Nov 10 '20 at 16:52
@JamshaidKamran How is the command object null? The user is passing a null to the parameter that is causing the exception. — LarsTech, Nov 10 '20 at 16:55
If you want to search for null, then change your line to be `command.Parameters.Add("@inputKarakterSoort", SqlDbType.VarChar).Value = inputKarakterSoort ?? DBNull.Value` — sgmoore, Nov 10 '20 at 16:57
@JamshaidKamran How can command be null than? I never set it to null. — , Nov 10 '20 at 16:57
@LardinoisJ Ignore that. You are getting some bad advice here regarding AddWithValue and the command object being null. You are calling your function with a null, so fix it at the source, or do something about a null parameter being passed. — LarsTech, Nov 10 '20 at 17:00
@LarsTech What do you mean with fix it at the source. I am positive that there are no null values in my database — , Nov 10 '20 at 17:02
@sgmoore When I do that I get the following error: Operator '??' cannot be applied to operands of type 'string' and 'BBNull' — , Nov 10 '20 at 17:03
@LarsTech I never suggested anything like that. If you look closely at his question you will see the debugged `command` object as null in the screenshot. — Jamshaid K., Nov 10 '20 at 17:04
@LardinoisJ Somewhere in your code, you are calling `var blah = GetSortedKarakters(thisisnull);` where your variable thisisnull is ... null. — LarsTech, Nov 10 '20 at 17:04
@JamshaidKamran The command object being null in that image has nothing to do with the user's exception. It is very clearly is being initialized in the code presented. — LarsTech, Nov 10 '20 at 17:05
@LarsTech Yes. I wanted him to provide a screenshot when the parameters has added. Now, What my comment pointed was, he has a breakpoint before that point that is why the command is being shown as null. — Jamshaid K., Nov 10 '20 at 17:06
Sorry. Needs to be `if (inputKarakterSoort == null) command.Parameters.Add("@inputKarakterSoort", SqlDbType.VarChar).Value = DBNull.Value; else command.Parameters.Add("@inputKarakterSoort", SqlDbType.VarChar).Value = inputKarakterSoort; ` — sgmoore, Nov 10 '20 at 17:07
Try this: command.Parameters.Add(new SqlParameter("@inputKarakterSoort",inputKarakterSoort)); — Nahid, Nov 11 '20 at 07:12

score 0 · Accepted Answer · edited Nov 11 '20 at 08:53

I tried the comment of sgmoore and implemented it. It works now.

This is my DAL now:

public class SortedKarakterContext : ISortedKarakterContext
{
        public string conn { get; set; }

        public SortedKarakterContext()
        {
            this.conn = "MYCONNECTIONSTRING";
        }

        private SqlConnection GetConnection()
        {
            return new SqlConnection(conn);
        }

        public IEnumerable<KarakterDTO> GetSortedKarakters(string inputKarakterSoort)
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                var command = new SqlCommand("SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = @inputKarakterSoort ORDER BY NEWID();", connection);
                if (inputKarakterSoort == null)         
                     command.Parameters.Add("@inputKarakterSoort", SqlDbType.VarChar).Value = DBNull.Value;     
                else        
                     command.Parameters.Add("@inputKarakterSoort", SqlDbType.VarChar).Value = inputKarakterSoort;
                var reader = command.ExecuteReader();
                var sortedKarakters = new List<KarakterDTO>();

                while (reader.Read())
                {
                    var karakter = new KarakterDTO
                    {
                        KarakterId = (int)reader["KarakterId"],
                        KarakterSoort = reader["KarakterSoort"]?.ToString(),
                        KarakterNaam = reader["KarakterNaam"]?.ToString()
                    };

                    sortedKarakters.Add(karakter);
                }

                return sortedKarakters;
            }
        }
    }
}

score -2 · Answer 2 · answered Nov 11 '20 at 08:56

-2

instead use

var command = new SqlCommand($"SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = {inputKarakterSoort} ORDER BY NEWID();", connection);

answered Nov 11 '20 at 08:56

Chavda11

1

1

No, definitely not. That introduces a SQL injection attack vulnerability. Using parameterized SQL is the way to go. – Jon Skeet Nov 11 '20 at 08:58
I agree with @Jon Skeet since it is a source of SqlInjection – Ismail Diari Nov 11 '20 at 13:31

Why is my SqlParameter not being recognised?

2 Answers2