Searching a Record From Database using C#

Submitted by joken on Tuesday, April 15, 2014 - 15:46.
This tutorial is a continuation of our last topic called “How to Update and Delete Record in the Database using C#”. At this time we will be dealing with searching a Specific Record from the Database. To start with, we need to add another label, button and a textbox, then arranges it like as shown below. a1 First we will set the visibility of the label and textbox for searching to false, so that when the application load it will not be visible to the user. Second on the “Search Record” button, add the following code: The code below will take effect when the search record button is clicked then it will show the label and a textbox that will allow the user to accept inputs for searching of a specific record.
  1. lblsearch.Show();
  2. txtsearch.Show();
  3. btnsearch.Enabled = false;
At this time we will add code for textbox.To do this, double click the textbox and add the following code: The code below will take effect when the text inside the textbox is changing because we are using the textchanged event of the textbox.
  1. dt = new DataTable();
  2. string sql = "Select * from tbluseraccounts WHERE username LIKE '%" + txtsearch.Text + "%'";
  3. OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
  4. da.Fill(dt);
  5. dataGridView1.DataSource = dt;
After adding this code, you can test it by pressing the “F5” or the start button. Take note! You can modify the SQL Query using the Name or the Username of the user during searching of record. And here’s all the code used in this application.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Windows.Forms;
  9. using System.Data.OleDb;
  10.  
  11. namespace WindowsFormsApplication1
  12. {
  13.     public partial class Form1 : Form
  14.     {
  15.         //declare new variable named dt as New Datatable
  16.         DataTable dt = new DataTable();
  17.         //this line of code used to connect to the server and locate the database (usermgt.mdb)
  18.         static string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + Application .StartupPath + "/usermgt.mdb";
  19.         OleDbConnection conn = new OleDbConnection(connection);
  20.        
  21.         public Form1()
  22.         {
  23.            
  24.             InitializeComponent();// calling the function
  25.         }
  26.  
  27.         private void Form1_Load(object sender, EventArgs e)
  28.         {
  29.            
  30.         }
  31.         private void loadrecord()
  32.         {
  33.             dt = new DataTable();
  34.             string sql = "Select * from tbluseraccounts";
  35.             OleDbDataAdapter da = new OleDbDataAdapter(sql , conn);
  36.             da.Fill(dt);
  37.             dataGridView1.DataSource = dt;
  38.         }
  39.  
  40.         private void button1_Click(object sender, EventArgs e)
  41.         {
  42.             loadrecord();
  43.         }
  44.  
  45.         private void btninsert_Click(object sender, EventArgs e)
  46.         {
  47.            
  48.             OleDbCommand cmd = new OleDbCommand();
  49.             //set our SQL Insert INTO statement
  50.             string sqlInsert = "INSERT INTO tbluseraccounts ( username, userusername, userpassword, usertype ) VALUES('" + txtname.Text + "','" + txtuser.Text + "','" + txtpass.Text + "','" + txttype.Text  + "')";
  51.             try
  52.             {
  53.                    
  54.                 //open the connection
  55.                 conn.Open();
  56.                 //set the connection
  57.                 cmd.Connection = conn;
  58.                 //get the SQL statement to be executed
  59.                 cmd.CommandText = sqlInsert;
  60.                 //execute the query
  61.                 cmd.ExecuteNonQuery();
  62.                 //display a message
  63.                 MessageBox.Show("New Record Added!....");
  64.                 //close the connection
  65.                 conn.Close();
  66.                                                
  67.             }
  68.             catch (Exception ex)
  69.             {
  70.                 //this will display some error message if something
  71.                 //went wrong to our code above during execution
  72.                 MessageBox.Show(ex.ToString());
  73.             }
  74.             //we call the loadrecord() function after adding a new record
  75.             loadrecord();
  76.  
  77.  
  78.         }
  79.  
  80.  
  81.  
  82.         private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
  83.         {
  84.             int i = e.RowIndex;
  85.             DataGridViewRow r = dataGridView1.Rows[i];
  86.  
  87.             int id = Convert.ToInt32(r.Cells[0].Value);
  88.             txtuserID.Text = r.Cells[0].Value + "";
  89.  
  90.         }
  91.  
  92.         private void btnupdate_Click(object sender, EventArgs e)
  93.         {
  94.             OleDbCommand cmd = new OleDbCommand();
  95.             //set our SQL Insert INTO statement
  96.             string sqlUpdate = "UPDATE tbluseraccounts set username ='" + txtname.Text  + "' , userusername = '" + txtuser .Text  + "', userpassword = '" + txtpass.Text + "', usertype= '" + txttype .Text  + "' where userID = " + txtuserID .Text  + "";
  97.             try
  98.             {
  99.  
  100.                 //open the connection
  101.                 conn.Open();
  102.                 //set the connection
  103.                 cmd.Connection = conn;
  104.                 //get the SQL statement to be executed
  105.                 cmd.CommandText = sqlUpdate;
  106.                 //execute the query
  107.                 cmd.ExecuteNonQuery();
  108.                 //display a message
  109.                 MessageBox.Show("Record Updated!....");
  110.                 //close the connection
  111.                 conn.Close();
  112.  
  113.             }
  114.             catch (Exception ex)
  115.             {
  116.                 //this will display some error message if something
  117.                 //went wrong to our code above during execution
  118.                 MessageBox.Show(ex.ToString());
  119.             }
  120.             //we call the loadrecord() function after adding a new record
  121.             loadrecord();
  122.         }
  123.  
  124.         private void btndelteRecord_Click(object sender, EventArgs e)
  125.         {
  126.             OleDbCommand cmd = new OleDbCommand();
  127.             //set our SQL DELETE statement
  128.             string sqlUpdate = "Delete * from tbluseraccounts where userID= " + txtuserID.Text + "";
  129.             try
  130.             {
  131.  
  132.                 //open the connection
  133.                 conn.Open();
  134.                 //set the connection
  135.                 cmd.Connection = conn;
  136.                 //get the SQL statement to be executed
  137.                 cmd.CommandText = sqlUpdate;
  138.                 //execute the query
  139.                 cmd.ExecuteNonQuery();
  140.                 //display a message
  141.                 MessageBox.Show("Record Deleted!....");
  142.                 //close the connection
  143.                 conn.Close();
  144.  
  145.             }
  146.             catch (Exception ex)
  147.             {
  148.                 //this will display some error message if something
  149.                 //went wrong to our code above during execution
  150.                 MessageBox.Show(ex.ToString());
  151.             }
  152.             //we call the loadrecord() function after adding a new record
  153.             loadrecord();
  154.         }
  155.  
  156.         private void txtsearch_TextChanged(object sender, EventArgs e)
  157.         {
  158.             dt = new DataTable();
  159.             string sql = "Select * from tbluseraccounts WHERE username LIKE '%" + txtsearch.Text + "%'";
  160.             OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
  161.             da.Fill(dt);
  162.             dataGridView1.DataSource = dt;
  163.  
  164.         }
  165.  
  166.         private void btnsearch_Click(object sender, EventArgs e)
  167.         {
  168.             lblsearch.Show();
  169.             txtsearch.Show();
  170.             btnsearch.Enabled = false;
  171.         }
  172.  
  173.        
  174.  
  175.  
  176.     }
  177. }
Tags
Search in the Database using C#
Textchanged
c#
Microsoft Access Database in C#

Add new comment

About text formats
  • 3670 views