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.

At this time, we will add a functionality our “Save Record” button by adding some pieces of code. And here’s the following code.

OleDbCommand cmd = new OleDbCommand(); 
//set our SQL Insert INTO statement
string sqlInsert = "INSERT INTO tbluseraccounts ( username, userusername, userpassword, usertype ) VALUES('" + txtname.Text + "','" + txtuser.Text + "','" + txtpass.Text + "','" + txttype.Text  + "')";
try 
{
       
    //open the connection
    conn.Open();
    //set the connection
    cmd.Connection = conn;
    //get the SQL statement to be executed
    cmd.CommandText = sqlInsert;
    //execute the query
    cmd.ExecuteNonQuery();
    //display a message
    MessageBox.Show("New Record Added!....");
    //close the connection
    conn.Close();
                                    
}
catch (Exception ex)
{
    //this will display some error message if something 
    //went wrong to our code above during execution
    MessageBox.Show(ex.ToString());
}
//we call the loadrecord() function after adding a new record
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:

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.

And here’s all the codes used in this application.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
 
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        //declare new variable named dt as New Datatable
        DataTable dt = new DataTable();
        //this line of code used to connect to the server and locate the database (usermgt.mdb)
        static string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + Application .StartupPath + "/usermgt.mdb";
        OleDbConnection conn = new OleDbConnection(connection);
        
        public Form1()
        {
            
            InitializeComponent();// calling the function
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
           
        }
        private void loadrecord()
        {
            string sql = "Select * from tbluseraccounts";
            OleDbDataAdapter da = new OleDbDataAdapter(sql , conn);
            da.Fill(dt);
            dataGridView1.DataSource = dt; 
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            loadrecord(); 
        }
 
        private void btninsert_Click(object sender, EventArgs e)
        {
           
            OleDbCommand cmd = new OleDbCommand(); 
            //set our SQL Insert INTO statement
            string sqlInsert = "INSERT INTO tbluseraccounts ( username, userusername, userpassword, usertype ) VALUES('" + txtname.Text + "','" + txtuser.Text + "','" + txtpass.Text + "','" + txttype.Text  + "')";
            try 
            {
                   
                //open the connection
                conn.Open();
                //set the connection
                cmd.Connection = conn;
                //get the SQL statement to be executed
                cmd.CommandText = sqlInsert;
                //execute the query
                cmd.ExecuteNonQuery();
                //display a message
                MessageBox.Show("New Record Added!....");
                //close the connection
                conn.Close();
                                                
            }
            catch (Exception ex)
            {
                //this will display some error message if something 
                //went wrong to our code above during execution
                MessageBox.Show(ex.ToString());
            }
            //we call the loadrecord() function after adding a new record
            loadrecord();
 
 
        }
 
    }
}

Add new comment

1460 views