0

I am trying to create an employee database. The code gives no error. I can access the database quite smoothly. But, database can't be updated. I am posting my database Connection class portion and insert.cs. guys, please tell me where I made a mistake. So that I can also correct update.cs and delete.cs

This is the database connection class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace employeDB1
{
    class DatabaseConnection
    {
        private string sql_string;
        private string strCon;
        System.Data.SqlClient.SqlDataAdapter da_1;

        public string Sql
        {
            set
            {
                sql_string = value;
            }
        }

        public string connection_string
        {
            set
            {
                strCon = value;
            }
        }

        public System.Data.DataSet GetConnection
        {
            get
            {
                return MyDataSet();
            }
        }

        private System.Data.DataSet MyDataSet()
        {
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
            con.Open();
            da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);
            System.Data.DataSet ds = new System.Data.DataSet();
            da_1.Fill(ds);
            con.Close();
            return ds;
        }

        public void UpdateDatabase(System.Data.DataSet ds)
        {
            System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da_1);
            cb.DataAdapter.Update(ds.Tables[0]);
        }
    }
}

Insert.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace employeDB1
{
    public partial class Insert : Form
    {
        public Insert()
        {
            InitializeComponent();
        }
        DatabaseConnection objConnect;

        string conString;
        int inc = 0;
        int MaxRows;
        DataSet ds = new DataSet();
        DataRow dRow;

        public void Insert_Load(object sender, EventArgs e)
        {
            try
            {
                objConnect = new DatabaseConnection();
                conString = Properties.Settings.Default.EmployeeConnectionString;

                objConnect.connection_string = conString;
                objConnect.Sql = Properties.Settings.Default.SQL;
                ds = objConnect.GetConnection;
                MaxRows = ds.Tables[0].Rows.Count;
            }
            catch(Exception err)
            {
                MessageBox.Show(err.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataRow row = ds.Tables[0].NewRow();
            row[0] = int.Parse(textBox1.Text);
            row[1] = textBox2.Text;
            row[2] = textBox3.Text;
            row[3] = int.Parse(textBox4.Text);
            ds.Tables[0].Rows.Add(row);
            try
            {
                objConnect.UpdateDatabase(ds);
                MaxRows = MaxRows + 1;
                inc = MaxRows - 1;
                MessageBox.Show("RECORD INSERTED");
            }
            catch(Exception err)
            {
                MessageBox.Show(err.Message);
            }
        }

        private void NavigateRecords()
        {
            dRow = ds.Tables[0].Rows[inc];
            textBox1.Text = dRow.ItemArray.GetValue(0).ToString();
            textBox2.Text = dRow.ItemArray.GetValue(1).ToString();
            textBox3.Text = dRow.ItemArray.GetValue(2).ToString();
            textBox4.Text = dRow.ItemArray.GetValue(3).ToString();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if(inc > 0)
            {
                inc--;
                NavigateRecords();
            }
            else
            {
                MessageBox.Show("First Record");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if(inc < MaxRows - 1)
            {
                inc++;
                NavigateRecords();
            }
            else
            {
                MessageBox.Show("Last Record");
            }
        }
    }
}
Mohit S
  • 13,723
  • 6
  • 34
  • 69
  • possible duplicate of [I want to insert data into a SQL Server databse using C#](http://stackoverflow.com/questions/21153845/i-want-to-insert-data-into-a-sql-server-databse-using-c-sharp) – marc_s Jan 16 '14 at 15:45
  • What is StrCon? Is it possible you are using the User Instance and AttachDbFileName settings? If so, stop doing that. Your applications each create their own copy of the database, so when one updates data, the other one doesn't see it, because it's a different copy of the same database. – Aaron Bertrand Jan 16 '14 at 15:52
  • As a side issue from what I can see here it looks like you are not disposing of objects at all, especially the `SqlConnection`. You are calling the `Close()` method but what if an exception occurs and this never happens? It would be better to wrap the connection inside a `using` block to guarantee the connection is released back to the connection pool regardless of success/error. Read http://stackoverflow.com/questions/61092/close-and-dispose-which-to-call for more details. – Peter Monks Jan 16 '14 at 16:00
  • I am a beginner. Will you please elaborately explain what I have to do!!! – user2971714 Jan 16 '14 at 17:05

0 Answers0