Take the 2-minute tour ×
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 ";
        SQLQuery = SQLQuery + " skullbase.dbo.patients.ACC2 = skullbase.dbo.fullreport.ACC";
        command.CommandText = SQLQuery;
    return command;

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

allTheseWords = textBoxAllTheseWords.Text.Split(' ');
share|improve this question
Cocoa Dev, where you are using or assigningthe value of word? change the command.Parameters.Add with command.Parameters.AddWithValues(@word, "variable word"); –  DJ KRAZE 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 stackoverflow.com/questions/242822/… –  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.. –  DJ KRAZE Jan 28 '13 at 18:19
the values are from a textbox. allTheseWords = textBoxAllTheseWords.Text.Split(' '); –  Cocoa Dev Jan 28 '13 at 18:29
add comment

closed as not constructive by marc_s, Soner Gönül, DJ KRAZE, Mario, hohner Jan 28 '13 at 22:14


2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
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
add comment

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.

share|improve this answer
I updated the code so that the parameters are different but it still doesn't work –  Cocoa Dev Jan 28 '13 at 20:46
add comment
