Like Tree1Likes
  • 1 Post By alionas

C# Search in database

This is a discussion on C# Search in database within the C# Programming forums, part of the General Programming Boards category; hello guys i'm doing some kind of work with database it's called phone book, where u can add delete search ...

  1. #1
    Registered User
    Join Date
    Nov 2010
    Posts
    20

    C# Search in database

    hello guys i'm doing some kind of work with database it's called phone book, where u can add delete search for a friend an I stuck by searching,
    i can do search by ID number, it works with it. but when i want to do by entering friend name, it's gives my error. here is the code searching by name. so if someone could help me how to do a search by a friend First name, it would be great .

    Code:
           private void button1_Click(object sender, EventArgs e)
            {
                try
                {
    
                    SqlConnection con;
                    con = new System.Data.SqlClient.SqlConnection();
                    con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\PsychO\\My Documents\\Visual Studio 2010\\Projects\\savarankiskas2\\savarankiskas2\\FriendsContact.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
                    DataTable dt = new DataTable();
                    SqlDataAdapter SDA = new SqlDataAdapter("SELECT * FROM Friends where FirstName = " + char.parse(textBox1.Text), con);
                    con.Open();
                    SDA.Fill(dt);
                    dataGridView1.DataSource = dt;
                    con.Close();
                   
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
    Rute Ara˙jo likes this.

  2. #2
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,139
    You don't need char.Parse. You need to put the content of textBox1.Text in quotes in your SQL-string:

    Code:
    string.Format("SELECT * FROM Friends where FirstName = '{0}' ", textBox1.Text)
    Please note that this is very susceptible to sql injection attacks, better use bind parameters once you got it running.
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

  3. #3
    Registered User
    Join Date
    Nov 2010
    Posts
    20
    hmm. i don't get a little bit. can you correct my code?

  4. #4
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,139
    Code:
    SqlDataAdapter SDA = new SqlDataAdapter("SELECT * FROM Friends where FirstName = " + char.parse(textBox1.Text), con);
    This line is wrong. The first parameter needs to be a string that looks like this:

    Code:
    SELECT * FROM FRIENDS WHERE FIRSTNAME = 'Bob'
    Obviously, Bob is your input. Build a string that looks like this and pass it to the adapter:

    Code:
    string sql = "SELECT * FROM FRIENDS WHERE FIRSTNAME = 'Bob' ";
    
    SqlDataAdapter SDA = new SqlDataAdapter(sql, con);
    Now you obviously need to get textBox1.Text in there or you will only select Bob forever. Use th line from my first post to format you sql string according to your input.

    Databases are advanced stuff. Have a look at string formatting and SQL first.
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

  5. #5
    Registered User
    Join Date
    Nov 2010
    Posts
    20
    i done it just needed to add this

    string sql = "SELECT * FROM Friends where FirstName like '" + textBox1.Text+"'";

    and it worked ^^. thx for your help
    Last edited by alionas; 11-15-2011 at 07:54 AM.

  6. #6
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,139
    Okay, it's working, that's great. Please have a look at the concept of SQL injections and it's remedy, bind variables. Once you got your program working, try to change your formatted SQL strings to SQL commands and use the Parameters collection. It's way easier, too. Try to input " D'Artagnan " in your textbox and see what happens to your program.
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

  7. #7
    Super Moderator VirtualAce's Avatar
    Join Date
    Aug 2001
    Posts
    9,596
    You may also want to check LINQ which could make your task even simpler.

  8. #8
    Registered User
    Join Date
    Dec 2011
    Posts
    7
    SqlCommand cmd = new SqlCommand("select * from STOCK WHERE Item_ID=" + txtItemID.Text, conn);
    What would happen if I put "; DROP DATABASE;" in txtItemID?

  9. #9
    Registered User
    Join Date
    Sep 2011
    Posts
    71
    here's an idea:

    Code:
            static void Main(string[] args)
            {
                string con = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\PsychO\My Documents\Visual Studio 2010\Projects\savarankiskas2\savarankiskas2\FriendsContact.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
                string cmd = "SELECT * FROM Friends WHERE FirstName LIKE @name + '%'";
                DataTable dt = new DataTable();
    
                   using (SqlDataAdapter a = new SqlDataAdapter())
                {
                    try
                    {
                        a.SelectCommand = new SqlCommand();
                        a.SelectCommand.Connection = new SqlConnection(con);
                        a.SelectCommand.CommandType = CommandType.Text;
                        a.SelectCommand.CommandText = cmd;
                        a.SelectCommand.Parameters.AddWithValue("name", textBox1.Text);
    
                        a.Fill(dt);
    
                        dataGridView1.DataSource = dt;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                    }
                }
                Console.Read();
            }
    I'm not sure if the AddWithValue method of the SqlParameterCollection class will help prevent SQL injection attacks. would it? or is it better to use the Add method and specify the SQL type, etc?
    Last edited by y99q; 12-10-2011 at 10:30 AM.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Advanced Search -> Search Multiple Content Types
    By phantomotap in forum Tech Board
    Replies: 2
    Last Post: 05-21-2011, 07:28 AM
  2. Difference Between A Linear Search And Binary Search
    By ImBack92 in forum C Programming
    Replies: 4
    Last Post: 05-12-2011, 08:47 AM
  3. c++ database search
    By mr_empty in forum Windows Programming
    Replies: 5
    Last Post: 12-23-2007, 11:15 AM
  4. Database Search Algorithm
    By Krupux in forum C Programming
    Replies: 1
    Last Post: 08-28-2003, 09:57 PM
  5. Replies: 1
    Last Post: 10-09-2001, 10:20 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21