DataTable is not getting the data requested

Question

I ran the SQL Query in SQL Server Management Studio and it worked.

I get the following error in my WinForm C# application

The parameterized query '(@word1 text)SELECT distinct [database].[dbo].[tableName].[n' expects the parameter '@word1', which was not supplied.

Here is my code

    private void buttonRunQuery_Click(object sender, EventArgs e)
     {
         if (connection == null)
         {
             connection = ConnectionStateToSQLServer();
             SqlCommand command = new SqlCommand(null, connection);
             command = createSQLQuery(command);
             GetData(command);
         }
         else
         {
             SqlCommand command = new SqlCommand(null, connection);
             command = createSQLQuery(command);
             GetData(command);
         }
     }

    private SqlCommand createSQLQuery(SqlCommand command)
     {
         string[] allTheseWords;
         if (textBoxAllTheseWords.Text.Length > 0)
         {
             allTheseWords = textBoxAllTheseWords.Text.Split(' ');
             string SQLQuery = "SELECT distinct [database].[dbo].[customerTable].[name], [database].[dbo].[customerTable].[dos], [database].[dbo].[customerTable].[accountID], [database].[dbo].[reportTable].[customerID], [database].[dbo].[reportTable].[accountID], [database].[dbo].[reportTable].[fullreport] FROM [database].[dbo].[reportTable], [database].[dbo].[customerTable] WHERE ";
             int i = 1;
             foreach (string word in allTheseWords)
             {
                 var name = "@word" + (i++).ToString();
                 command.Parameters.Add(name, SqlDbType.Text);
                     //(name, SqlDbType.Text).Value = word;
                 SQLQuery = SQLQuery + String.Format(" [database].[dbo].[reportTable].[fullreport] LIKE {0} AND ", name);
             }
             SQLQuery = SQLQuery + " [database].[dbo].[customerTable].[accountID] = [database].[dbo].[reportTable].[accountID]";
             command.CommandText = SQLQuery;
         }
         MessageBox.Show(command.CommandText.ToString());
         return command;
     }

    public DataTable GetData(SqlCommand cmd)
     {
         //SqlConnection con = new SqlConnection(connString);
         //SqlCommand cmd = new SqlCommand(sqlcmdString, cn);
         SqlDataAdapter da = new SqlDataAdapter(cmd);
         connection.Open();
         DataTable dt = new DataTable();
         da.Fill(dt);
         connection.Close();
         return dt;
     }

The error is happening on da.Fill(dt)

Any suggestions would be helpful

Thank you

Cocoa Dev have you looked at my example from the other OP's accepted answer, you asked which was better to use DataTable or DataSet.. also why are you using @word + (i++) this is not how you should increment. also when adding cmd.Parameters user cmd.Parameters.AddWithValue(@Param,parmvar) instead — MethodMan, Feb 01 '13 at 17:04
is it necessary to include the `[database].[dbo]` before the `Field Name`? — spajce, Feb 01 '13 at 17:05
it won't hurt the query if its there is the connection string is setup correctly he could have just used `[dbo]` or `[table]` name this doesn't affect the issue that Cocoa Dev is experiencing — MethodMan, Feb 01 '13 at 17:07
thanks for the reply of my question Mr. @DJKRAZE, but i guess its too difficult debug if you have a large of.. or.. `join table` if we include the `[database].[dbo]` ..but it's just my opinion `:)` — spajce, Feb 01 '13 at 17:20
My connectionstring is string source = "Data Source=Server Address;Initial Catalog=Database Name;Integrated Security=SSPI;"; — Cocoa Dev, Feb 01 '13 at 17:34

score 1 · Answer 1 · answered Feb 01 '13 at 16:45

1

On var name = "@word" + (i++).ToString(); use just i, increment somewhere else.

answered Feb 01 '13 at 16:45

RandomUs1r

4,010
1
24
44

I am having a similar problem. Can you please explain it a little more? – software is fun Feb 01 '13 at 16:49
The I++ is necessary to make sure the Parameter name is unique. If you meant something else, I think @softwareisfun is right about a little more detail. – Cocoa Dev Feb 01 '13 at 16:51
{ var name = "@word" + (i).ToString(); command.Parameters.Add(name, SqlDbType.Text); //(name, SqlDbType.Text).Value = word; SQLQuery = SQLQuery + String.Format(" [database].[dbo].[reportTable].[fullreport] LIKE {0} AND ", name); i++ } – RandomUs1r Feb 01 '13 at 18:27
or you can set i to 0 up above, but think about how incrementation works, you're basically passing a 2 instead of a 1. – RandomUs1r Feb 01 '13 at 18:28

score 1 · Accepted Answer · answered Feb 01 '13 at 16:52

1

In your example, you have commented out the line where you set the value of the Parameter:

command.Parameters.Add(name, SqlDbType.Text);
//(name, SqlDbType.Text).Value = word;

If you do not set a value for a parameter, it is ignored (and won't exist). Change to this:

command.Parameters.AddWithValue(name, word);

For clarity, consider this quote:

The value to be added. Use DBNull.Value instead of null, to indicate a null value.

From here: SqlParameterCollection.AddWithValue Method

answered Feb 01 '13 at 16:52

Codesleuth

10,321
8
51
71

Thanks for the help! But Im still not getting any data back. I had the AddWithValue but commented it out because I saw an article that didn't use it. Thanks – Cocoa Dev Feb 01 '13 at 17:06
Cocoa Dev Parameters.Add has been partially deprecated it's actually better to use Parameters.AddWithValue() method let the Database Server handle the SqlDbType resolving you're not getting data back is something totally different. It would be nice to see a snippet of what the text / data looks like that you are trying to work on – MethodMan Feb 01 '13 at 17:09
There aer 6 fields of data. All are nvarchar except DOS which is a date field, accountID and customerID are both floats. – Cocoa Dev Feb 01 '13 at 17:33
I can't really help with the separate question regarding the lack of data. You would be better off closing this question (with an answer) then creating a new question detailing your data structure and the query that is failing (export it out, either in debugger or with SQL Profiler). – Codesleuth Feb 01 '13 at 18:37

DataTable is not getting the data requested

2 Answers2