How Do I Delete And Update Rows From A Db And Navigate Keeping Dataconnection As A Separate Class

Question

0.00/5 (No votes)

See more:

.NET4.5

C#

private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                ds.Tables[0].Rows[inc].Delete();
                objConnect.UpdateDatabase(ds);
                this.ds.Reset();

                MaxRows = ds.Tables[0].Rows.Count;
                inc = inc-1;
                NavigateRecords(ds);
                MessageBox.Show("Records Deleted");
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
        }

I dont understand what is wrong with the code. I am creating this data navigation and manipulation app. The error is on the first line "Cannot find table 0" IndexOutOfRange. Please if anyone can help!

ADDED:
COde from the comment:

C#

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


namespace testNavData
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      DatabaseConnection objConnect;
      string conString;

      DataSet ds;
      DataRow dRow;

      int MaxRows;
      int inc = 0;

      private void Form1_Load(object sender, EventArgs e)
      {
         DataSet ds1=new DataSet();
         try 
         {
            objConnect = new DatabaseConnection();
            conString = Properties.Settings.Default.EmployeesConnectionString;

            objConnect.connection_string = conString;
            objConnect.Sql = Properties.Settings.Default.SQL;;
            //MessageBox.Show(get{return objConnect.Sql});

            ds = objConnect.GetConnection;

            MaxRows = ds.Tables[0].Rows.Count;

            NavigateRecords(ds);
         }
         catch (Exception err)
         {
            MessageBox.Show(err.Message);
         }
      }
      private void NavigateRecords(DataSet ds)
      {
         dRow = ds.Tables[0].Rows[inc];

         txtFirstName.Text = dRow.ItemArray.GetValue(1).ToString();
         txtSurname.Text = dRow.ItemArray.GetValue(2).ToString();
         txtJobTitle.Text = dRow.ItemArray.GetValue(3).ToString();
         txtDepartment.Text = dRow.ItemArray.GetValue(4).ToString();
      }

      private void btnAddNew_Click(object sender, EventArgs e)
      {
         txtFirstName.Clear();
         txtSurname.Clear();
         txtJobTitle.Clear();
         txtDepartment.Clear();

         btnAddNew.Enabled = false;
         btnSave.Enabled = true;
         btnCancel.Enabled = true;
      }

private void btnCancel_Click(object sender, EventArgs e)
{
NavigateRecords(ds);
btnCancel.Enabled = false;
btnAddNew.Enabled = true;
btnSave.Enabled = false;
}

private void btnSave_Click(object sender, EventArgs e)
{
DataRow row = ds.Tables[0].NewRow();
row[1] = txtFirstName .Text;
row[2] = txtSurname.Text;
row[3] = txtJobTitle.Text;
row[4] = txtDepartment.Text;

ds.Tables[0].Rows.Add(row);
try
{
objConnect.UpdateDatabase(ds);
MaxRows = MaxRows + 1;
inc = MaxRows - 1;

this.ds.Tables[0].Clear(); 


MessageBox.Show("Database is Updated");
}
catch(Exception err)
{
MessageBox.Show(err.Message);
}
btnCancel.Enabled = false;
btnSave.Enabled = false;
btnAddNew.Enabled = true;

}



public string Sql { get; set; }

private void btnNext_Click(object sender, EventArgs e)
{
if (inc != (MaxRows-1))
{
inc= inc+1;
NavigateRecords(ds);
}
else
{
if (inc != (MaxRows - 1))
{

inc = (MaxRows-1);
NavigateRecords(ds);

}

}
}

private void btnPrevious_Click(object sender, EventArgs e)
{
if (inc > 0)
{

inc= inc-1;
NavigateRecords(ds);

}
else
{
if (inc != 0)
{

inc = 0;
NavigateRecords(ds);

}


}


}

private void btnLast_Click(object sender, EventArgs e)
{
if (inc != M

Posted 12-Jul-15 4:20am

Member 11341510

Updated 12-Jul-15 6:01am

Wendelius

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-12T04:34:00

Solution 1

Most likely you haven't created any tables in this dataset so the dataset exists but the tables collection is empty.

Make sure that you populate correct dataset or create the tables in your code (depending what is the logic of the application).

In order to help more, we should see the part of the code where you create and populate the dataset since the problem lies over there, not in the delete command :)

Posted 12-Jul-15 4:34am

Wendelius

Comments

Member 11341510 12-Jul-15 11:17am

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

namespace testNavData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

DatabaseConnection objConnect;
string conString;

DataSet ds;
DataRow dRow;

int MaxRows;
int inc = 0;

private void Form1_Load(object sender, EventArgs e)
{

DataSet ds1=new DataSet();
try
{
objConnect = new DatabaseConnection();
conString = Properties.Settings.Default.EmployeesConnectionString;

objConnect.connection_string = conString;
objConnect.Sql = Properties.Settings.Default.SQL;;
//MessageBox.Show(get{return objConnect.Sql});

ds = objConnect.GetConnection;

MaxRows = ds.Tables[0].Rows.Count;

NavigateRecords(ds);
}
catch (Exception err)
{

MessageBox.Show(err.Message);
}
}
private void NavigateRecords(DataSet ds)
{

dRow = ds.Tables[0].Rows[inc];

txtFirstName.Text = dRow.ItemArray.GetValue(1).ToString();
txtSurname.Text = dRow.ItemArray.GetValue(2).ToString();
txtJobTitle.Text = dRow.ItemArray.GetValue(3).ToString();
txtDepartment.Text = dRow.ItemArray.GetValue(4).ToString();
}

private void btnAddNew_Click(object sender, EventArgs e)
{
txtFirstName.Clear();
txtSurname.Clear();
txtJobTitle.Clear();
txtDepartment.Clear();

btnAddNew.Enabled = false;
btnSave.Enabled = true;
btnCancel.Enabled = true;
}

private void btnCancel_Click(object sender, EventArgs e)
{
NavigateRecords(ds);
btnCancel.Enabled = false;
btnAddNew.Enabled = true;
btnSave.Enabled = false;
}

private void btnSave_Click(object sender, EventArgs e)
{
DataRow row = ds.Tables[0].NewRow();
row[1] = txtFirstName .Text;
row[2] = txtSurname.Text;
row[3] = txtJobTitle.Text;
row[4] = txtDepartment.Text;

ds.Tables[0].Rows.Add(row);
try
{
objConnect.UpdateDatabase(ds);
MaxRows = MaxRows + 1;
inc = MaxRows - 1;

this.ds.Tables[0].Clear();

MessageBox.Show("Database is Updated");
}
catch(Exception err)
{
MessageBox.Show(err.Message);
}
btnCancel.Enabled = false;
btnSave.Enabled = false;
btnAddNew.Enabled = true;

}

public string Sql { get; set; }

private void btnNext_Click(object sender, EventArgs e)
{
if (inc != (MaxRows-1))
{
inc= inc+1;
NavigateRecords(ds);
}
else
{
if (inc != (MaxRows - 1))
{

inc = (MaxRows-1);
NavigateRecords(ds);

}

}
}

private void btnPrevious_Click(object sender, EventArgs e)
{
if (inc > 0)
{

inc= inc-1;
NavigateRecords(ds);

}
else
{
if (inc != 0)
{

inc = 0;
NavigateRecords(ds);

}

}

}

private void btnLast_Click(object sender, EventArgs e)
{
if (inc != M

Member 11341510 12-Jul-15 11:51am

kindly help plz plz

Wendelius 12-Jul-15 12:02pm

From the code you sent, I can't find where do you fill the dataset ds

Also you define a dataset ds1 in form load. As far as I can see this is not used and should be removed in order to avoid using wrong dataset

Member 11341510 12-Jul-15 12:40pm

yes you are right, I will remove the ds1 dataset:

I have created a class DatabaseConnection.cs

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

namespace testNavData
{
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 dat_set = new System.Data.DataSet();
da_1.Fill(dat_set, "Table_Data_1");
con.Close();
return dat_set;
}
public void UpdateDatabase(System.Data.DataSet ds)
{
System.Data.SqlClient.SqlCommandBuilder cb = new SqlCommandBuilder(da_1);
cb.DataAdapter.Update(ds.Tables[0]);
}
}

}

Wendelius 12-Jul-15 12:52pm

The likeliest situation is that the ds in the form load is different then the ds in delete button's click event or you create a completely new dataset into the ds somewhere.

Having that said I'd suggest that you use the debugger to see what happens in each of the methods you call and all the time monitor the ds dataset and changes in it.

Member 11341510 12-Jul-15 16:13pm

this is the complete code...im still not getting it :(