Creating a SELECT Query Based on Textbox Data

[John L DeVito]

I'm building a simple windows form app which stores all of my DVD's (pet project, just for learning; I'm sure there are plenty already out there). I'm able to add items into the database (azure SQL Server) perfectly, even leaving some or most textboxes blank (the title column is setup as NOT NULL in my table) but I can't get it to retrieve any data. Here is my OnClick method:

private void searchButton_Click(object sender, EventArgs e)
{
string qString = @"SELECT Title, Director, Genre, ReleaseYear, Length, NumberofDisks, Description FROM Base WHERE Title = '" + titleTextbox.Text + "' AND Director = '" + directorTextbox.Text + "' AND Genre = '" + genreCombobox.GetItemText(genreCombobox.SelectedItem) + "' AND ReleaseYear = '" + yearCombobox.GetItemText(yearCombobox.SelectedItem) + "' AND Length = '" + lengthTextbox.Text + "' AND NumberOfDisks = '" + numberOfDisksTextbox.Text + "' AND Description = '" + descriptionTextbox.Text + "';";

string cString = @"Server=MyAzureServer,MyPortNumber;Database=MyDatabase;User ID=me@MyAzureServer;Password=MyPassword;Trusted\_Connection=False;Encrypt=True;Connection Timeout=30;";

DataTable dTable = new DataTable();

SqlConnection sConnection = new SqlConnection(cString);

sConnection.Open();

using(SqlCommand sCommand = new SqlCommand(qString, sConnection))
    {
    	try
	{
		SqlDataReader sReader = sCommand.ExecuteReader();
		dTable.Load(sReader);
	}
	catch(SqlException sEx)
	{
		MessageBox.Show(sEx.Message);
	}

	resultsDataGridView.DataSource = dTable;
}

	sConnection.Close();

}

and here is my table:

CREATE TABLE [dbo].[Base]
(
[MediaID] [int] IDENTITY(1,1) PRIMARY KEY,
[Title] [nvarchar](50) NOT NULL,
[Director] [nvarchar](50) NULL,
[Genre] [nvarchar](50) NULL,
[ReleaseYear] [date] NULL,
[Length] [time] NULL,
[NumberOfDisks] [int] NULL,
[Description] [nvarchar](200) NULL
)

I absolutely realize this is open to SQL injection. My plan is to just 'get it working' and then I will go back over it and and change to parameterized queries (I have to read up on how to use them). The program compiles and runs fine. When I click the search button with all or any of the textboxes filled, it returns no results in the datagridview. The white grid shows up but it is empty. I can query the database directly and get the results I'm looking for, but not programatically. Do I need to build the query using logic? Since almost all of my columns accept

[PIEBALDconsult]

Please learn to use parameterized statements and NEVER use concatenation to provide values, especially values from the user. That will likely solve your problem.

[Wendelius]

You said that you know that the code is vulnerable to SQL injections but that is not the only reason why to use parameters. Another thing is that they help you with conversions. Consider for example dates and the date format or numbers. Using a correct format is difficult when the data is in text format. Few other reasons why the query might not return any data

• There are a lot of conditions. Since they are defined in the WHERE clause you cannot leave any of them empty and expect that the condition would be ignored. If a text box is empty the comparison will still take place
• The operator for all conditions is AND so all the the conditions you have specified must be satisfied by a single row
• You use equality in all comparisons. For example description is compared to the value from the text box using equality. This means that the text must match exactly and completely. This prbably isn't what you're after. Perhaps LIKE comparison?

[Dave Kreskowiak]

Yeah, it'll help you to "get it working" if you just went to parameterized queries to begin with to help you avoid problems such as these and also makes it much easier to debug when you do run into problems. Your WHERE clause is screwing you over somehow. Rewrite to help you debug the problem and make sure the parameters you're passing in are expected and correct.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

[F ES Sitecore]

If you leave one of your boxes open, let's say length, then your sql includes WHERE length = '' so you'll only get records where the length is empty. If you want to ignore a field if it is empty then your SQL needs to look like WHERE (@field1 IS NULL OR field1 = @field1) AND (@field2 IS NULL OR field2 = @field2) @field1 and @field2 are your input params, and field1 and field2 are the matching columns in your database. In order for this to work it'll be easier if you switch to params first. So if you want to ignore an element from the query then set it's param to dbnull.value if the associated textbox is empty.

[Richard Deeming]

John L. DeVito wrote:

My plan is to just 'get it working' and then I will go back over it and and change to parameterized queries

That's a very bad plan. There's a good chance that you'll miss something, or forget to do it, or run out of time, or lose interest and move on to the next project... :) Plus, as others have pointed out, using string concatenation to build your query will introduce new problems that you'll have to fix, which wouldn't be the case if you used properly parameterized queries. Parameterized queries aren't particularly hard, particularly as you're using ADO.NET and SQL Server:

private void searchButton_Click(object sender, EventArgs e)
{
// TODO: This should probably be in the configuration file:
const string ConnectionString = @"Server=MyAzureServer,MyPortNumber;Database=MyDatabase;User ID=me@MyAzureServer;Password=MyPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;";

const string Query = @"SELECT 
Title, 
Director, 
Genre, 
ReleaseYear, 
Length, 
NumberofDisks, 
Description 

FROM
Base
WHERE
(NullIf(@Title, '') Is Null Or Title = @Title)
AND
(NullIf(@Director, '') Is Null Or Director = @Director)
AND
(NullIf(@Genre, '') Is Null Or Genre = @Genre)
AND
(NullIf(@ReleaseYear, '') Is Null Or ReleaseYear = @ReleaseYear)
AND
(NullIf(@Length, '') Is Null Or Length = @Length)
AND
(NullIf(@NumberOfDisks, '') Is Null Or NumberOfDisks = @NumberOfDisks)
AND
(NullIf(@Description, '') Is Null Or Description = @Description)
;";

DataTable dTable = new DataTable();

using (SqlConnection connection = new SqlConnection(ConnectionString))
using (SqlCommand command = new SqlCommand(Query, connection))
{
    command.Parameters.AddWithValue("@Title", titleTextbox.Text);
    command.Parameters.AddWithValue("@Director", directorTextbox.Text);
    command.Parameters.AddWithValue("@Genre", genreCombobox.GetItemText(genreCombobox.SelectedItem));
    command.Parameters.AddWithValue("@ReleaseYear", yearCombobox.GetItemText(yearCombobox.SelectedItem));
    command.Parameters.AddWithValue("@Length", lengthTextbox.Text);
    command.Parameters.AddWithValue("@NumberOfDisks", numberOfDisksTextbox.Text);
    command.Parameters.AddWithValue("@Description", descri