joe mb

joe mb

  • 1.9k
  • 1
  • 3.4k

How to Insert Records into Access database table

Aug 17 2011 11:12 PM
Hi,

I have used an example code from one of the contributors (here) to display data within a DatagridView. I can display and navigate through the data already found in the table. When I enter new data and click insert, the new record is apparently inserted and displayed within the DatagridView (as seen for "Dummy Record" below).



However this same record is not inserted into the physical "student table" within Access database. There are no errors produced.
What am I missing?

Kindly help.

Here is the code:

================

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.VisualBasic;
 
namespace test01
{
  public partial class Form1 : Form
  {
  public Form1()
  {
  InitializeComponent();
  }
  OleDbConnection con;
  OleDbCommand cmd;
  OleDbDataAdapter adapter;
  DataSet ds;
  int rno;
 
  void loaddata()
  {
  adapter = new OleDbDataAdapter("select * from student", con);
  ds = new DataSet();//student-> table name in stud.mdb file
  adapter.Fill(ds, "student");
  ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true);//creating primary key for Tables[0] in dataset
  dataGridView1.DataSource = ds.Tables[0];
  }
  void showdata()
  {
  txtSno.Text = ds.Tables[0].Rows[rno][0].ToString();
  txtSname.Text = ds.Tables[0].Rows[rno][1].ToString();
  txtCourse.Text = ds.Tables[0].Rows[rno][2].ToString();
  }
 
  private void Form1_Load(object sender, EventArgs e)
  {
  con = new OleDbConnection(@" Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\stud.mdb;Persist Security Info=False");
  loaddata();
  showdata();
  }
 
  private void btnFirst_Click(object sender, EventArgs e)
  {
  if (ds.Tables[0].Rows.Count > 0)
  {
  rno = 0;
  showdata();
  }
  else
  MessageBox.Show("no records");
  }
 
  private void btnPrevious_Click(object sender, EventArgs e)
  {
  if (ds.Tables[0].Rows.Count > 0)
  {
  if (rno > 0)
  {
  rno--;
  showdata();
  }
  else
  MessageBox.Show("First Record");
  }
  else
  MessageBox.Show("no records");
  }
 
  private void btnNext_Click(object sender, EventArgs e)
  {
  if (ds.Tables[0].Rows.Count > 0)
  {
  if (rno < ds.Tables[0].Rows.Count - 1)
  {
  rno++;
  showdata();
  }
  else
  MessageBox.Show("Last Record");
  }
  else
  MessageBox.Show("no records");
  }
 
  private void btnLast_Click(object sender, EventArgs e)
  {
  if (ds.Tables[0].Rows.Count > 0)
  {
  rno = ds.Tables[0].Rows.Count - 1;
  showdata();
  }
  else
  MessageBox.Show("no records");
  }
 
  private void btnInsert_Click(object sender, EventArgs e)
  {
  cmd = new OleDbCommand("insert into student values(" + txtSno.Text + ",' " + txtSname.Text + " ',' " + txtCourse.Text + " ')", con);
  con.Open();
  int n = cmd.ExecuteNonQuery();
  con.Close();
  if (n > 0)
  {
  MessageBox.Show("record inserted");
  loaddata();
  }
  else
  MessageBox.Show("insertion failed");
  }
 
  private void btnSearch_Click(object sender, EventArgs e)
  {
  int n = Convert.ToInt32(Interaction.InputBox("Enter sno:", "Search", "20", 200, 200));
  DataRow drow = ds.Tables[0].Rows.Find(n);
  if (drow != null)
  {
  rno = ds.Tables[0].Rows.IndexOf(drow);
  txtSno.Text = drow[0].ToString();
  txtSname.Text = drow[1].ToString();
  txtCourse.Text = drow[2].ToString();
  }
  else
  MessageBox.Show("Record not found");
  }
 
  private void btnUpdate_Click(object sender, EventArgs e)
  {
  cmd = new OleDbCommand("update student set sname='" + txtSname.Text + "',course='" + txtCourse.Text + "' where sno=" + txtSno.Text, con);
  con.Open();
  int n = cmd.ExecuteNonQuery();
  con.Close();
  if (n > 0)
  {
  MessageBox.Show("Record Updated");
  loaddata();
  }
  else
  MessageBox.Show("Update failed");
  }
 
  private void btnDelete_Click(object sender, EventArgs e)
  {
  cmd = new OleDbCommand("delete from student where sno=" + txtSno.Text, con);
  con.Open();
  int n = cmd.ExecuteNonQuery();
  con.Close();
  if (n > 0)
  {
  MessageBox.Show("Record Deleted");
  loaddata();
  }
  else
  MessageBox.Show("Deletion failed");
  }
 
  private void btnClear_Click(object sender, EventArgs e)
  {
  txtSno.Text = txtSname.Text = txtCourse.Text = "";
  }
 
  private void btnExit_Click(object sender, EventArgs e)
  {
  this.Close();
  }
  }
}
==========================================

Joe

Answers (2)