How to Save Record in Database using C#

Submitted by joken on Tuesday, April 15, 2014 - 13:24.
This tutorial is a continuation of our last topic called “Step by Step Connect Access Database in C#”. At this time, we’re going to focus on how to save record in Microsoft Access using C#. To start with, open our last project called “usermanagement” since we will be dealing with saving a record, we need to add another control such as Button, Labels and Textboxes. Then we will redesign our and it should look like as shown below. a1 At this time, we will add a functionality our “Save Record” button by adding some pieces of code. And here’s the following code. 
  1. OleDbCommand cmd = new OleDbCommand(); 
  2. //set our SQL Insert INTO statement
  3. string sqlInsert = "INSERT INTO tbluseraccounts ( username, userusername, userpassword, usertype ) VALUES('" + txtname.Text + "','" + txtuser.Text + "','" + txtpass.Text + "','" + txttype.Text  + "')";
  4. try 
  5. {
  6.  
  7.     //open the connection
  8.     conn.Open();
  9.     //set the connection
  10.     cmd.Connection = conn;
  11.     //get the SQL statement to be executed
  12.     cmd.CommandText = sqlInsert;
  13.     //execute the query
  14.     cmd.ExecuteNonQuery();
  15.     //display a message
  16.     MessageBox.Show("New Record Added!....");
  17.     //close the connection
  18.     conn.Close();
  19.  
  20. }
  21. catch (Exception ex)
  22. {
  23.     //this will display some error message if something 
  24.     //went wrong to our code above during execution
  25.     MessageBox.Show(ex.ToString());
  26. }
  27. //we call the loadrecord() function after adding a new record
  28. loadrecord();
As you have observe, we have a new OLE DB object added called “OleDbCommand”, this object it represent an SQL statement or stored procedure to execute against a data source. This time we will test the new added code, to do this, run the application by pressing “F5” function or simply click the start button. Then when the form loads, try to encode the following textbox provided. Example: a2 Next, you can now press the “Save Record” button. After you click the button you can observe that the new added record is listed in the datagridview. Meaning you have successfully inserted a record in the database. The output will look like as shown below. a3 And here’s all the codes 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.             string sql = "Select * from tbluseraccounts";
  34.             OleDbDataAdapter da = new OleDbDataAdapter(sql , conn);
  35.             da.Fill(dt);
  36.             dataGridView1.DataSource = dt; 
  37.         }
  38.  
  39.         private void button1_Click(object sender, EventArgs e)
  40.         {
  41.             loadrecord(); 
  42.         }
  43.  
  44.         private void btninsert_Click(object sender, EventArgs e)
  45.         {
  46.  
  47.             OleDbCommand cmd = new OleDbCommand(); 
  48.             //set our SQL Insert INTO statement
  49.             string sqlInsert = "INSERT INTO tbluseraccounts ( username, userusername, userpassword, usertype ) VALUES('" + txtname.Text + "','" + txtuser.Text + "','" + txtpass.Text + "','" + txttype.Text  + "')";
  50.             try 
  51.             {
  52.  
  53.                 //open the connection
  54.                 conn.Open();
  55.                 //set the connection
  56.                 cmd.Connection = conn;
  57.                 //get the SQL statement to be executed
  58.                 cmd.CommandText = sqlInsert;
  59.                 //execute the query
  60.                 cmd.ExecuteNonQuery();
  61.                 //display a message
  62.                 MessageBox.Show("New Record Added!....");
  63.                 //close the connection
  64.                 conn.Close();
  65.  
  66.             }
  67.             catch (Exception ex)
  68.             {
  69.                 //this will display some error message if something 
  70.                 //went wrong to our code above during execution
  71.                 MessageBox.Show(ex.ToString());
  72.             }
  73.             //we call the loadrecord() function after adding a new record
  74.             loadrecord();
  75.  
  76.  
  77.         }
  78.  
  79.     }
  80. }

Tags

Add new comment

About text formats
  • 1306 views