search database table for value and put it in a datatable

Question

I have a textbox and a search button i trying to search file names in a database table and display them in a datatable...

private void GetSearchResults(string machineID, string searchFileName)
{
    DataTable dt = new DataTable();
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = ConfigurationManager.ConnectionStrings["SumooHAgentDBConnectionString"].ConnectionString;
    connection.Open();
    SqlCommand sqlCmd = new SqlCommand("SELECT FileID, BuFileName FROM BackedUpFiles WHERE BuFileName Like '%@searchFileName%' AND MachineID=@machineID", connection);
    SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

    sqlCmd.Parameters.AddWithValue("@machineID", machineID);

    sqlCmd.Parameters.AddWithValue("@searchFileName", searchFileName);

    sqlDa.Fill(dt);
}

everything is working fine except that the searchFileName is not working in the query...

I tried putting just a value to check like

SELECT FileID, BuFileName FROM BackedUpFiles WHERE BuFileName Like '%b%' AND MachineID=@machineID

and i got the file values...

any suggestions..??

Answer 1

You have your variable inside of a string, which is telling the database to search for the literal string '@searchFileName'. To use the value of the variable, try this

'%' + @searchFileName + '%'

Answer 2

Here's what you need:

private void GetSearchResults(string machineID, string searchFileName)
{
    DataTable dt = new DataTable();
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = ConfigurationManager.ConnectionStrings["SumooHAgentDBConnectionString"].ConnectionString;
    connection.Open();
    SqlCommand sqlCmd = new SqlCommand("SELECT FileID, BuFileName FROM BackedUpFiles WHERE BuFileName Like @searchFileName AND MachineID=@machineID", connection);
    SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

    sqlCmd.Parameters.AddWithValue("@machineID", machineID);

    sqlCmd.Parameters.AddWithValue("@searchFileName", String.Format("%{0}%",searchFileName);

    sqlDa.Fill(dt);
}

I've run into this before. For some reason, it doesn't know how to parse out the parameters if it's in quotes. I guess it thinks it's a literal.

Answer 3

try this one in your select

SELECT FileID, BuFileName FROM BackedUpFiles where REGEXP_LIKE(upper(BuFileName ),UPPER(@searchFileName)) AND MachineID=@machineID;