SqlCommand parameters are not substituting correctly

Question

private SqlCommand createSQLQuery(SqlCommand command)
{
    string[] allTheseWords;
    if (textBoxAllTheseWords.Text.Length > 0)
    {
        allTheseWords = textBoxAllTheseWords.Text.Split(' ');
        string SQLQuery = "SELECT distinct [skullbase].[dbo].[patients].[name], [skullbase].[dbo].[patients].[dos], [skullbase].[dbo].[patients].[ACC2], [SKULLbase].[dbo].[fullreport].[mrn1], [SKULLbase].[dbo].[fullreport].[ACC], [skullbase].[dbo].[fullreport].[fullreport] FROM [skullbase].[dbo].[fullreport], [skullbase].[dbo].[patients] WHERE ";
        int i = 1;
        foreach (string word in allTheseWords)
        {
            command.Parameters.Add("@word" + i.ToString(), SqlDbType.Text).Value = word;
            SQLQuery = SQLQuery + " [skullbase].[dbo].[fullreport].[fullreport] LIKE @word" + i.ToString() + " AND ";
            i++;
        }
        SQLQuery = SQLQuery + " skullbase.dbo.patients.ACC2 = skullbase.dbo.fullreport.ACC";
        command.CommandText = SQLQuery;
    }
    MessageBox.Show(command.CommandText.ToString());
    return command;
}

The above is my query. The word "word" is not being substituted for the actual value.

allTheseWords = textBoxAllTheseWords.Text.Split(' ');

Cocoa Dev, where you are using or assigningthe value of `word`? change the command.Parameters.Add with `command.Parameters.AddWithValues(@word, "variable word");` — MethodMan, Jan 28 '13 at 18:13
I'm pretty sure you have to set the `CommandText` of the `SqlCommand` prior to adding parameters. Also, this is going to create an invalid query anyway... after you exit your loop and append the next string, you're going to have `... AND AND skullbase.dbo.patients.Acc2 = ...` — Dave Zych, Jan 28 '13 at 18:17
Check out for how you can use better if you have to add some conditions to `Where` clause http://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause — Soner Gönül, Jan 28 '13 at 18:18
I am interested in knowing what values are stored in the `string[] allTheseWords;` this is not how you build a Parameterized query by the way.. — MethodMan, Jan 28 '13 at 18:19
the values are from a textbox. allTheseWords = textBoxAllTheseWords.Text.Split(' '); — Cocoa Dev, Jan 28 '13 at 18:29

score 4 · Accepted Answer · edited Jan 28 '13 at 18:20

4

For starters, when you quote your parameter reference in your SQL CommandText (e.g. ...[fullreport] = '@word'...) you are actually just using the literal value '@word'. It is not being interpreted as a parameterized query. To do that you would just use ...[fullreport] = @word...)

Secondly, I do not -think- you can assign multiple parameters with the same parameter name as you are doing in the loop. Each parameter you add should have a unique name.

edited Jan 28 '13 at 18:20

Soner Gönül

97,193
102
206
364

answered Jan 28 '13 at 18:17

maelstrom

1,111
8
7

how do I add single quotes around the data – Cocoa Dev Jan 28 '13 at 19:35
I removed the single quotes and same issue. It's not being interpreted as the actual value of word – Cocoa Dev Jan 28 '13 at 19:37
Wait...did you modify the code to use LIKE instead of equality? Also, if you are looping through a list of words and adding criterea that [fullreport] is LIKE (or EQUAL) to the words in the list, then you are potentially going to cause the WHERE to evaluate to false because it most likely won't be LIKE or EQUAL to all words..unless you are using LIKE and embedding wildcards in your words. At this point I'm a little confused about the goal. – maelstrom Jan 28 '13 at 21:04

score 2 · Answer 2 · answered Jan 28 '13 at 18:16

2

You're using the same parameter name for each word. You should be using a different name for each one. You might consider appending an index or something else like that to make it a unique parameter name.

answered Jan 28 '13 at 18:16

itsme86

19,266
4
41
57

I updated the code so that the parameters are different but it still doesn't work – Cocoa Dev Jan 28 '13 at 20:46

SqlCommand parameters are not substituting correctly

2 Answers2