3 Replies - 3425 Views - Last Post: 03 November 2014 - 01:35 PM Rate Topic: -----

#1 Datagram   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 06-December 13

Searching DB for value in textbox (WPF)

Posted 03 November 2014 - 12:50 PM

Hi, I'm looking for some help with a WPF window I'm working on. I have a window that has a textbox to enter a name to search a database table for, and when the search button is clicked, the ID for that username will be returned to a separate textbox. The code I've written atm doesn't seem to be working, but it looks fine to me. Here's what I've got;

        private void btn_SearchUsers_Click(object sender, RoutedEventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(txtb_SearchName.Text))
            {
                SqlConnection sqlCon = new SqlConnection(conStr);
                string name = txtb_SearchName.Text.Trim();
                string sqlQuery = @"SELECT ID
                                    FROM tblUsers
                                    WHERE name=" + name;
                SqlDataAdapter da = new SqlDataAdapter(sqlQuery, sqlCon);
                DataTable dt = new DataTable();
                da.Fill(dt);
                
                if (dt.Rows.Count == 0)
                {
                    MessageBox.Show("There's no data found in that table.", "Empty table.",
                        MessageBoxButton.OK, MessageBoxImage.Exclamation);
                }
                else
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr[1] + "" == name)
                        {
                            txtb_IDResult.Text = dr[0] + "";
                            break;
                        }
                    }
                }
            }
            else
            {
                MessageBox.Show("Please enter a name to search for.", "Empty textbox.", 
                    MessageBoxButton.OK, MessageBoxImage.Exclamation);
            }
        }



So, I have the value entered into the textbox to be searched for stored in a variable, I'm selecting the ID from the table when the name in the variable is found, storing the result in a DataTable, and then in my foreach loop, if I find the name (the name column being index 1 in the table), I set set the ID result textbox to equal the ID for that name (the ID column being index 0 in the table). I think the foreach part is what's throwing me off. Maybe the column stuff? My Users table is like;

ID Name
1 John
2 Roger
3 Mike

Thanks for anyone who can help.

Is This A Good Question/Topic? 0
  • +

Replies To: Searching DB for value in textbox (WPF)

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,820
  • Joined: 12-June 08

Re: Searching DB for value in textbox (WPF)

Posted 03 November 2014 - 12:59 PM

First I would advocate a try/catch around anything connecting to, or using, a database.

Next I would look at this, and think about using a parameter on line 09.
07	        string sqlQuery = @"SELECT ID
08	                            FROM tblUsers
09	                            WHERE name=" + name;
10	        SqlDataAdapter da = new SqlDataAdapter(sqlQuery, sqlCon);



Then I would think about that select statement. Say your variable 'name' was the value "foo".

That means you have:
SELECT ID FROM tblUsers WHERE name=foo

which is way different from, say...
SELECT ID FROM tblUsers WHERE name='foo'


The first is interpreted as 'foo' being a column name. The second is 'foo' is a string value.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • Entwickler
  • member icon

Reputation: 6604
  • View blog
  • Posts: 26,911
  • Joined: 12-December 12

Re: Searching DB for value in textbox (WPF)

Posted 03 November 2014 - 01:06 PM

Quote

The code I've written atm doesn't seem to be working

Are there any errors?

In the SQL statement the name variable needs to be surrounded with apostrophes, as does any string value. However, you should be using parameterized queries. My VB.NET Snippet demonstrates the basic process of supplying parameters, and includes a C# version of the code.

After this, you are only returning a single column, so there is no dr[1] only dr[0]. You also don't need to search for name again (in the foreach loop) because you have already included this filter in the WHERE clause.
if (!string.IsNullOrWhiteSpace(txtb_SearchName.Text))

You should also be referring to properties of the Window, not directly to GUI elements. This is how WPF is designed to work.
Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6535
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Searching DB for value in textbox (WPF)

Posted 03 November 2014 - 01:35 PM

This really comes down to "How do I do basic functions in a database?" and the part about search criteria being from a textbox, or the fact that its a WPF window really doesn't play a part, as the search of the database is completely independent from the source of what it is you are looking for.

I would strongly urge you to take a break from your program and WORK the tutorials from FAQ 32 below. An hour or two learning the basics of database interaction will save you 10 times that amount of time in frustration.

An hour spent on the debugging tutorials would go a long way as well. It would keep you from wondering what's happening and let you do better than "It doesn't work."


TOP most asked:
What does this error message mean?
FAQ 2: How do I debug
FAQ 6: How do I make Class1/Form1 talk to Class2/Form2


FAQ (Frequently Asked Questions - Updated July 2014
Spoiler

This post has been edited by tlhIn`toq: 03 November 2014 - 01:36 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1