Unable to retrieve data from database

Question

Hello everyone, I'm connecting to a SQL Server CE database on a WinForms application using Visual Studio 2010. What I'm trying to do is get the data that is in a textbox and see if it is in the abbrev column in my abbreviations table. If it is, then I want to get the data that is in the descriptions column for that entry and put it into a string. Below is what I have so far and I can't seem to figure out what I'm doing wrong. Any suggestions?

if (subjectiveCheckBox.Checked)
            {
                sb.Append(qLabel.Text);

                SqlCeConnection connection = new SqlCeConnection(@"Data Source=keywordDB.sdf");
                SqlCeCommand command = new SqlCeCommand("SELECT abbrev FROM abbreviations WHERE abbrev = '" + qTextBox1.Text + "'", connection);
                SqlCeCommand cmd = new SqlCeCommand("SELECT description FROM abbreviations WHERE abbrev = '" + qTextBox1.Text + "'", connection);
                SqlCeDataReader reader;

                try
                {
                    connection.Open();
                    reader = command.ExecuteReader();
                    reader.Read();
                    abbrevQueryResult = reader.ToString();

                    if (abbrevQueryResult == qTextBox1.Text)
                    {
                        reader = cmd.ExecuteReader();
                        reader.Read();
                        diagnosis = reader.ToString();
     
                    }
                }

                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }

                sb.Append(" " + diagnosis);
            }

Answer 1

                    abbrevQueryResult = reader.ToString();

Hello,

You convert the complete "reader" object to string, not the selected column value. Try it this way to get the value of the first column = index 0:

abbrevQueryResult = reader[0].ToString();

or

abbrevQueryResult = reader.GetSqlString(0).ToString();

---

Olaf Helper
Blog Xing

Answer 2

Thanks, I'll try that this afternoon when I get home and let you know how it works.

---

-- Tyler Hughes

Answer 3

Hi,

There are a couple of things you should do to improve and fix your code:

1st. Do not concatenate the text filter with the SELECT command. SQL Server Compact is not subject to "SQL Injection" because it can't process more than one command at a time, but if the user types a single quote on the text filter, your code will break. Instead use a SQL Parameter

2nd. You don't need two commands, the second one is enough, so it returns directly the description associated to that abbreviation. You can use a DataReader with a .Read() command find if it returns any rows, or if you're sure it will return at maximum one single row, use a ExecuteScalar command instead and check if it returns null. Also take into account that the way you're doing you are forcing an exact match, the LIKE operator is more flexible.

I could retype your code to give you a 'no-brain' solution to your issue, but I'm sure you'll learn a lot from looking at the clues I've provided you.

---

Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - http://www.moving2u.pt

Answer 4

1st.   I'm not familiar with SQL Parameter. This is my first project where I've had to do something like this. All my experience with SQL is with SQL only queries. I've never had to do a SQL query and compare it to something I've done in another language. Do you have any tutorials to explain what you're referring to?

2nd.   How's this?

if (subjectiveCheckBox.Checked)
            {
                sb.Append(qLabel.Text);

                SqlCeConnection connection = new SqlCeConnection(@"Data Source=keywordDB.sdf");
                SqlCeCommand cmd = new SqlCeCommand("SELECT description FROM abbreviations WHERE abbrev LIKE '" + qTextBox1.Text + "'", connection);
                SqlCeDataReader reader;

                try
                {
                    connection.Open();

                    reader = cmd.ExecuteReader();
                    reader.Read();
                    diagnosis = reader.ToString();
                }

                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }

                if (diagnosis == qTextBox1.Text)
                {
                    sb.Append(" " + diagnosis);
                }
            }

---

-- Tyler Hughes

Answer 5

      diagnosis = reader.ToString();

Tyler, you still convert the complete reader object to string, this won't give you the required result (and makes no sense); see my first post.

---

Olaf Helper
Blog Xing

Answer 6

That's because I posted my code before I had tried out what you said. My code was in response to Alberto Silva's reply. 

Since the code above all I've done is this:

diagnosis = reader[0].ToString();

The reader object returns exactly what I want it to but for some reason it's not adding it to my StringBuilder.

---

-- Tyler Hughes

Answer 7

but for some reason it's not adding it to my StringBuilder

Hello Tyler,

String compare is case sensitive, so if there is a small difference in upper/lower cases, then your compare false and the string won't be added. You should change the compare to lower case on both side like

if (diagnosis.ToLowerInvariant() == qTextBox1.Text.ToLowerInvariant())
   {
       sb.Append(" " + diagnosis);
   }

---

Olaf Helper
Blog Xing

Answer 8

Hi Olaf,

I'm at work and have not had a chance yet to remote into my computer at home and try the method you mentioned above. However, while eating lunch today I realized something. Even while comparing the 2 strings in lower case it still will not produce the wanted result, because the diagnosis is the description column and the text in qTextBox is the abbreviation. Therefore, they will always be different.

---

-- Tyler Hughes

Answer 9

I was able to fix the problem by doing this:

if (subjectiveCheckBox.Checked)
            {
                sb.Append(qLabel.Text);

                SqlCeConnection connection = new SqlCeConnection(@"Data Source=keywordDB.sdf");
                SqlCeCommand cmd = new SqlCeCommand("SELECT abbrev, description FROM abbreviations WHERE abbrev LIKE '" + qTextBox1.Text + "'", connection);
                SqlCeDataReader reader;

                try
                {
                    connection.Open();

                    reader = cmd.ExecuteReader();
                    reader.Read();
                    abbrevQueryResult = reader[0].ToString();
                    diagnosis = reader[1].ToString();
                }

                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }

                if (abbrevQueryResult.ToLowerInvariant() == qTextBox1.Text.ToLowerInvariant())
                {
                    sb.Append(" " + diagnosis);
                }
            }

I made the query request both the abbreviation and the description. Then I used abbrevQueryResult to get the database abbreviation and got the user's input and compared them in lowercase to add the diagnosis to the StringBuilder. It worked when I did that. Now I just need to figure out how I'm going to set it up to see if there is more than one abbreviation entered into the textbox.

---

-- Tyler Hughes