Reputation: 9541
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
Upvotes: 0
Views: 369
Reputation: 10541
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
Upvotes: 1
Reputation: 4190
On var name = "@word" + (i++).ToString(); use just i, increment somewhere else.
Upvotes: 1