Solved

c# Update and Delete

Posted on 2011-03-16
28
772 Views
Last Modified: 2013-12-17
Hi All,

Im sure im missing something very simple here but just cant get this working (Newbie im afraid).

I am using the following code:

 // Create OLEDB SQL Command String, Create OLEDB DataAdapter then Create Dataset and Fill
                    // tblUsernames DataAdapter
                    Static_Database.Static_Database_Object.SETGETSQL = "select * from tblUsernames";
                    Static_Database.Static_Database_Object.Create_Command();
                    oleDbDataAdapterTest = Static_Database.Static_Database_Object.Create_OleDbDataAdapter();
                    // REMOVED - datasettest = Static_Database.Static_Database_Object.Create_DataSet();

                    // tblDepartments DataAdapter
                    Static_Database.Static_Database_Object.SETGETSQL = "select * from tblDepartments";
                    Static_Database.Static_Database_Object.Create_Command();
                    oleDbDataAdapter1 = Static_Database.Static_Database_Object.Create_OleDbDataAdapter();

                    // tblStatus DataAdapter
                    Static_Database.Static_Database_Object.SETGETSQL = "select * from tblStatus";
                    Static_Database.Static_Database_Object.Create_Command();
                    oleDbDataAdapter2 = Static_Database.Static_Database_Object.Create_OleDbDataAdapter();

                    // Add the DataAdapters into the Dataset
                    oleDbDataAdapterTest.Fill(datasettest, "tblUsernames");
                    oleDbDataAdapter1.Fill(datasettest, "tblDepartments");
                    oleDbDataAdapter2.Fill(datasettest, "tblStatus");

                    {
                        if (datasettest.Tables.Count > 0)
                        {
                            // Populate Controls
                            this.Populate_Controls();
                        }
                        // Close that Database
                        Static_Database.Static_Database_Object.CloseDataBase();
                    }
                }

                else if (Static_Database.Static_Database_Object is SQLDB)
                {
                    // Create OLEDB SQL Command String, Create SQL DataAdapter then Create Dataset and Fill
                    {
                        if (datasettest.Tables.Count > 0)
                        {
                            // Populate Controls
                            this.Populate_Controls();
                        }
                        // Close that Database
                        Static_Database.Static_Database_Object.CloseDataBase();
                    }
                }
            }
        }

        // Populate all controls from the DataSet
        private void Populate_Controls()
        {
            lstUsernames.DataSource = datasettest.Tables["tblUsernames"];
            lstUsernames.DisplayMember = "Username";
            lstUsernames.ValueMember = "Username";

            cboEditDepartment.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkDepartment");
            cboEditDepartment.DataSource = datasettest.Tables["tblDepartments"];
            cboEditDepartment.DisplayMember = "Department";
            cboEditDepartment.ValueMember = "ID";

            cboEditStatus.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkStatus");
            cboEditStatus.DataSource = datasettest.Tables["tblStatus"];
            cboEditStatus.DisplayMember = "Status";
            cboEditStatus.ValueMember = "ID";

            txtEditUsername.DataBindings.Add("Text", datasettest.Tables[0], "Username");
            txtEditPassword.DataBindings.Add("Text", datasettest.Tables[0], "Password");
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
                // OLEDB Delete Here
        }

When i run the following command:

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            datasettest.Tables[0].Rows.Add(txtEditUsername);
            datasettest.Tables[1].Rows.Add(txtEditPassword);
            datasettest.Tables[2].Rows.Add(cboEditDepartment);
            datasettest.Tables[3].Rows.Add(cboEditStatus);
   
            datasettest.AcceptChanges();
        }

I get the following error:

Unable to cast object of type 'System.Windows.Forms.TextBox' to type 'System.IConvertible'.Couldn't store <System.Windows.Forms.TextBox, Text: Simon> in ID Column.  Expected type is Int32.

How would I get this to update the database? As i understand .AcceptChanges only effects the Dataset and does not write back to the database?

Cheers Al
Si
0
Comment
Question by:SimonGreen2004
  • 13
  • 8
  • 2
  • +3
28 Comments
 
LVL 19

Expert Comment

by:Rikin Shah
Comment Utility
Use Textbox.Text property instead of Textbox.

Thanks,
Rikin
0
 
LVL 19

Expert Comment

by:Rikin Shah
Comment Utility
I guess cboEditDepartment is a combo box...

Change it to cboEditDepartment.SelectedItem.Text


Thanks,
Rikin
0
 
LVL 14

Expert Comment

by:robasta
Comment Utility
 datasettest.Tables[0].Rows.Add(txtEditUsername);

Open in new window


should be

 datasettest.Tables[0].Rows.Add(txtEditUsername.Text);


do the same (append '.Text') to the other textboxes inside btnUpdate_Click event
0
 
LVL 11

Assisted Solution

by:Kusala Wijayasena
Kusala Wijayasena earned 250 total points
Comment Utility
To get the text from TextBox and binded ComboBox, use following mechanism

string textboxVal = textBox1.Text;
string comboboxVal = comboBox1.SelectedValue.ToString(); 

Open in new window


by the way, I do have another question regarding following code

datasettest.Tables[0].Rows.Add(txtEditUsername);
datasettest.Tables[1].Rows.Add(txtEditPassword);
datasettest.Tables[2].Rows.Add(cboEditDepartment);
datasettest.Tables[3].Rows.Add(cboEditStatus);

Open in new window


Why do you use separate datatable to hold single value instead of using a datatable hold all the values ?

-Kusala
 


0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi All,

Many thanks for the comments. I have now used:

datasettest.Tables[0].Rows.Add(txtEditUsername.Text);
datasettest.Tables[1].Rows.Add(txtEditPassword.Text);

but when trying to run .AcceptChanges it throws up the following Errors:

Input string was not in a correct format.Couldn't store <Test> in ID Column.  Expected type is Int32.

Also

datasettest.Tables[2].Rows.Add(cboEditDepartment.SelectedItem.Text);
datasettest.Tables[3].Rows.Add(cboEditStatus.SelectedItem.Text);

Reports the follwoing Error:

'object' does not contain a definition for 'Text' and no extension method 'Text' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)

Kusala - What would be a better way to handle the tables? Im a newbie and am only just learning. This basically is what i have done from reading examples, etc.

Cheers Again All
Si
0
 
LVL 11

Assisted Solution

by:Sudhakar Pulivarthi
Sudhakar Pulivarthi earned 250 total points
Comment Utility

datasettest.Tables[0].Rows.Add(txtEditUsername.Text);
datasettest.Tables[1].Rows.Add(txtEditPassword.Text);

Tables[0] is department table -- Ur wrongly added the data to the row.

SelectedItem will not have Text so remove it.
datasettest.Tables[2].Rows.Add(cboEditDepartment.SelectedItem.Text);
datasettest.Tables[3].Rows.Add(cboEditStatus.SelectedItem.Text);
--to--
datasettest.Tables[2].Rows.Add(cboEditDepartment.SelectedItem);
datasettest.Tables[3].Rows.Add(cboEditStatus.SelectedItem);
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Sudhakar-Pulivarthi,

I have now changed the code to:

datasettest.Tables[1].Rows.Add(txtEditUsername.Text);
datasettest.Tables[1].Rows.Add(txtEditPassword.Text);
datasettest.Tables[2].Rows.Add(cboEditDepartment.SelectedItem);
datasettest.Tables[3].Rows.Add(cboEditStatus.SelectedItem);

Vut the same message "Input string was not in a correct format.Couldn't store <Test> in ID Column.  Expected type is Int32"

Also probably a daft question but how do i tell what is which table? is it the order you populate it?

Cheers
Si
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
Comment Utility
Yes it is the order we populate the dataset the data tables will be kept. In ur scenario:
datasettest.Tables[0] is tblUsernames
datasettest.Tables[1] is tblDepartments
datasettest.Tables[2] is tblStatus

Hence u have to change as:
datasettest.Tables[0].Rows.Add(txtEditUsername.Text);
datasettest.Tables[0].Rows.Add(txtEditPassword.Text);
datasettest.Tables[1].Rows.Add(cboEditDepartment.SelectedItem);
datasettest.Tables[2].Rows.Add(cboEditStatus.SelectedItem);

Note: If ur not sure with the table indexes u can use table name instead as:
datasettest.Tables["tblUsernames"] to avoid confusion in order, usually it happens when ur filling more tables in dataset/ some other dll is providing dataset where we do not know the order.
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Sudhakar-Pulivarthi,

Im still getting a "Input string was not in a correct format.Couldn't store <Simon> in ID Column.  Expected type is Int32"

I have attached a screenshot incase that helps?

Cheers
Si
 Error
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
Comment Utility
Hi, i think data bindings is causing problem. Pls comment the databindings first and check whether the updation happens with out error. In case u get some probs
Kindly post the sample project and clearly specify what u want to do in the project.
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Again Sudhakar-Pulivarthi,

I have commented out the following lines for the Databinding:

            txtEditUsername.DataBindings.Add("Text", datasettest.Tables[0], "Username");
            txtEditPassword.DataBindings.Add("Text", datasettest.Tables[0], "Password");

and it seems to work (or at least not error).

Unfortunatly im using 3rd party controls so the project will not compile without this, so cannot post. Basically though i have a control 2 x Textbox's and 2 a Combos.

Usrrname (Text)
Password (Text)
Status (Combo)
Active (Combo)

These releate to my database which is attached. I just want to be able to edit/update/delete from my control to the database.I have also attached my cose for the control.

Cheers Again
Si

 Training.accdb
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Drawing;
using System.Drawing.Imaging;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;

namespace testapp
    {
    public partial class uctTest : DevExpress.XtraEditors.XtraUserControl
    {
        public uctTest()
        {
            InitializeComponent();
        }

        private void uctTest_Load(object sender, EventArgs e)
        {
            // Get Company Name and Site fro App.Config
            txtCompanyName.Text = ConfigurationSettings.AppSettings["CompanyName"];
            txtCompanySite.Text = ConfigurationSettings.AppSettings["CompanySite"];
            {
                if (Static_Database.Static_Database_Object is OLEDB)
                {
                    // Create OLEDB SQL Command String, Create OLEDB DataAdapter then Create Dataset and Fill
                    // tblUsernames DataAdapter
                    Static_Database.Static_Database_Object.SETGETSQL = "select * from tblUsernames";
                    Static_Database.Static_Database_Object.Create_Command();
                    oleDbDataAdapterTest = Static_Database.Static_Database_Object.Create_OleDbDataAdapter();
                    // REMOVED - datasettest = Static_Database.Static_Database_Object.Create_DataSet();

                    // tblDepartments DataAdapter
                    Static_Database.Static_Database_Object.SETGETSQL = "select * from tblDepartments";
                    Static_Database.Static_Database_Object.Create_Command();
                    oleDbDataAdapter1 = Static_Database.Static_Database_Object.Create_OleDbDataAdapter();

                    // tblStatus DataAdapter
                    Static_Database.Static_Database_Object.SETGETSQL = "select * from tblStatus";
                    Static_Database.Static_Database_Object.Create_Command();
                    oleDbDataAdapter2 = Static_Database.Static_Database_Object.Create_OleDbDataAdapter();

                    // Add the DataAdapters into the Dataset
                    oleDbDataAdapterTest.Fill(datasettest, "tblUsernames");
                    oleDbDataAdapter1.Fill(datasettest, "tblDepartments");
                    oleDbDataAdapter2.Fill(datasettest, "tblStatus");

                    {
                        if (datasettest.Tables.Count > 0)
                        {
                            // Populate Controls
                            this.Populate_Controls();
                        }
                        // Close that Database
                        Static_Database.Static_Database_Object.CloseDataBase();
                    }
                }

                else if (Static_Database.Static_Database_Object is SQLDB)
                {
                    // Create OLEDB SQL Command String, Create SQL DataAdapter then Create Dataset and Fill
                    {
                        if (datasettest.Tables.Count > 0)
                        {
                            // Populate Controls
                            this.Populate_Controls();
                        }
                        // Close that Database
                        Static_Database.Static_Database_Object.CloseDataBase();
                    }
                }
            }
        }

        // Populate all controls from the DataSet
        private void Populate_Controls()
        {
            lstUsernames.DataSource = datasettest.Tables["tblUsernames"];
            lstUsernames.DisplayMember = "Username";
            lstUsernames.ValueMember = "Username";

            cboEditDepartment.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkDepartment");
            cboEditDepartment.DataSource = datasettest.Tables["tblDepartments"];
            cboEditDepartment.DisplayMember = "Department";
            cboEditDepartment.ValueMember = "ID";

            cboEditStatus.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkStatus");
            cboEditStatus.DataSource = datasettest.Tables["tblStatus"];
            cboEditStatus.DisplayMember = "Status";
            cboEditStatus.ValueMember = "ID";

            txtEditUsername.DataBindings.Add("Text", datasettest.Tables[0], "Username");
            txtEditPassword.DataBindings.Add("Text", datasettest.Tables[0], "Password");
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
                // OLEDB Delete Here
        }

        private void btnAddUser_Click(object sender, EventArgs e)
        {
            this.txtEditUsername.Clear();
            this.txtEditPassword.Clear();

            this.txtEditUsername.Enabled = true;
            this.txtEditPassword.Enabled = true;
            this.cboEditDepartment.Enabled = true;
            this.cboEditStatus.Enabled = true;
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            datasettest.Tables[1].Rows.Add(txtEditUsername.Text);
            datasettest.Tables[1].Rows.Add(txtEditPassword.Text);

            datasettest.Tables[2].Rows.Add(cboEditDepartment.SelectedItem);
            datasettest.Tables[3].Rows.Add(cboEditStatus.SelectedItem);
    
            datasettest.AcceptChanges();
        }

        // Enable Controls ready for Editing
        private void btnEdit_Click(object sender, EventArgs e)
        {
            this.txtEditUsername.Enabled = true;
            this.txtEditPassword.Enabled = true;
            this.cboEditDepartment.Enabled = true;
            this.cboEditStatus.Enabled = true;
        }

        // Mouse Click for Control Colours
        private void Generic_MouseClick(object sender, MouseEventArgs e)
        {
            ((Control)sender).BackColor = Color.LightBlue;
        }

        // Key Down for Control Colours
        private void Generic_KeyDown(object sender, KeyEventArgs e)
        {
            ((Control)sender).BackColor = Color.LightBlue;
        }
    }
}

Open in new window

0
 
LVL 14

Expert Comment

by:systan
Comment Utility
hi
I did not test your code, so by just looking;
Do you always used [0]?,  I think it's [2] for department and [3] for status.
I think your repeating the table orders?

cboEditDepartment.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkDepartment");
...
...
...
cboEditStatus.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkStatus");
...
...
...

Just my sight;
I think, that's one of the errors you should look out.

Sorry if I got it wrong, but that's the way I see it.  You initialized the index wrong.


You can also try to change
from selecteditem to selectedText or selecteditem.toString
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Systan,

I have tried changing the above but to no avail. I think i should be using [0] as lkDepartment is a table within the username table (Which looks up another table - Deparmtents).

Im pretty sure this is something simple i just can figure out what. Doing a lot of reading last night im thinking it maybe and issue with the primary key, but i have checked the Database and it has a PrimaryKey set.

Cheers
Si
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Again All,

I have just renamed the primary Key olum or testing. Im deffinatly sure its something to do with this now as i get the following error:

Input string was not in a correct format.Couldn't store <gfdsgfsd> in Usernames_ID Column.  Expected type is Int32

Usernames_ID Colum is indeed the Primary Key Column of the USernames Table. gfdsgfsd what what i typed in the Username textbox.

Why is it trying to save this in the Primary Key Colum in the database? It should be added to the Username Column in the database?

Cheers
Si
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
Comment Utility
Hi Simon,

Instead of providing the select * command specify the columns you want to use from the tables like
select username,password from tblUsernames

When u provide select * ur ID columns are also used in databinding process.
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Sudhakar-Pulivarthi,

Many thanks for that - im getting much closed now:) Its adding to the Dataset now .... However (As nothing is Easy), its complain about:

"Input string was not in a correct format.Couldn't store <gfdsgfsd> in Usernames_ID Column.  Expected type is Int32"

For bothe the Department and the Status Column, but i need these as the ComboBox usesthese as it value memeber:

cboEditDepartment.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkDepartment");
            cboEditDepartment.DataSource = datasettest.Tables["tblDepartments"];
            cboEditDepartment.DisplayMember = "Department";
            cboEditDepartment.ValueMember = "ID";

Also i notice when i call datasettest.AcceptChanges(); it ass 3 x Copies of the Data? I.E I add Roger as a Username and call datasettest.AcceptChanges(); when i look in the Listsbox Roger is there 3 times?

Cheers again and many thanks for all your help so far.
Si
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
Comment Utility
Hi Simon,

There is one more change need to be made! Usually when we bind with adapters, INSERT, UPDATE and DELETE commands are generated automatically. Which again works in generic trying to work on all columns. causing problems. Hence write these commands to be used by adapter to perform it.
Refer here for more help and info om these topic.
http://www.akadia.com/services/dotnet_update_form.html
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi again Sudhakar-Pulivarthi,

OK i have read the following but even more confused now .... To simplify things i have created a new "simple Project" with my code in and have put it for download here: http://www.network-db.co.uk/files/TestApp.zip. I have also attached my database to here.

I'm just so stuck on Deleting, Updating/Adding. What i though would be fairly simple is proving very difficult to get working:(

Any code you could help me out with would be awsome.

Cheers Again
Si
 Training.accdb
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
Comment Utility
Hi Simon,

Pls use this code for btnUpdate_Click event and check. It is adding properly to the grid and no redundant is found.

Let me know is this ur requirement was..
private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {


                datasettest.Tables[0].Rows.Add(txtEditUsername.Text, txtEditPassword.Text, ((DataRowView)cboEditDepartment.SelectedItem)[0], ((DataRowView)cboEditStatus.SelectedItem)[0]);
                
                datasettest.AcceptChanges();

                this.txtEditUsername.Enabled = false;
                this.txtEditPassword.Enabled = false;
                this.cboEditDepartment.Enabled = false;
                this.cboEditStatus.Enabled = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Open in new window

0
 
LVL 11

Assisted Solution

by:Sudhakar Pulivarthi
Sudhakar Pulivarthi earned 250 total points
Comment Utility
Hi,

I assumed and made some code modification to add new department/status information along with user details. pls check with the code below and let me know is it this ur looking for.
Note: Changes are affected only in dataset
public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\\Training.accdb");

            OleDbCommand cmdusernames = new OleDbCommand("SELECT Username, Password, lkDepartment, lkStatus FROM tblUsernames", cn);
            OleDbCommand cmddepartments = new OleDbCommand("SELECT * FROM tblDepartments", cn);
            OleDbCommand cmdstatus = new OleDbCommand("SELECT * FROM tblStatus", cn);

            oleDbDataAdapterTest = new OleDbDataAdapter(cmdusernames);
            oleDbDataAdapter1 = new OleDbDataAdapter(cmddepartments);
            oleDbDataAdapter2 = new OleDbDataAdapter(cmdstatus);

            // Add the DataAdapters into the Dataset
            oleDbDataAdapterTest.Fill(datasettest, "tblUsernames");
            oleDbDataAdapter1.Fill(datasettest, "tblDepartments");
            oleDbDataAdapter2.Fill(datasettest, "tblStatus");
            
            lstUsernames.DataSource = datasettest.Tables["tblUsernames"];
            lstUsernames.DisplayMember = "Usernames.Username";
            lstUsernames.ValueMember = "Username";

            
            cboEditDepartment.DataSource = datasettest.Tables["tblDepartments"];
            cboEditDepartment.DisplayMember = "Department";
            cboEditDepartment.ValueMember = "ID";

            
            cboEditStatus.DataSource = datasettest.Tables["tblStatus"];
            cboEditStatus.DisplayMember = "Status";
            cboEditStatus.ValueMember = "ID";

            AddBindings();
        }

        private void btnAddUser_Click(object sender, EventArgs e)
        {
            this.txtEditUsername.Clear();
            this.txtEditPassword.Clear();

            this.txtEditUsername.Enabled = true;
            this.txtEditPassword.Enabled = true;
            this.cboEditDepartment.Enabled = true;
            this.cboEditStatus.Enabled = true;

            RemoveBindings();
        }

        private void AddBindings()
        {
            cboEditDepartment.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkDepartment");
            cboEditStatus.DataBindings.Add("SelectedValue", datasettest.Tables[0], "lkStatus");
            txtEditUsername.DataBindings.Add("Text", datasettest.Tables[0], "Username");
            txtEditPassword.DataBindings.Add("Text", datasettest.Tables[0], "Password");
        }
        private void RemoveBindings()
        {
            cboEditDepartment.DataBindings.Clear();
            cboEditStatus.DataBindings.Clear();
            txtEditUsername.DataBindings.Clear();
            txtEditPassword.DataBindings.Clear();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                int deptID = 1;
                int statusID = 1;

                // Check whether new record was inserted/previous record itself is used.
                DataRowView deptRow = ((DataRowView)cboEditDepartment.SelectedItem);
                if (deptRow == null)
                {
                    // Get the last ID generated and increment it.
                    if (datasettest.Tables[1].Rows.Count > 0)
                    {
                        deptID = (int)datasettest.Tables[1].Rows[datasettest.Tables[1].Rows.Count - 1][0] + 1;
                    }

                    // It is a newly added department in combo box.
                    datasettest.Tables[1].Rows.Add(deptID, cboEditDepartment.Text);
                }
                else
                {
                    deptID = (int)deptRow[0];
                }

                DataRowView statusRow = ((DataRowView)cboEditStatus.SelectedItem);
                if (statusRow == null)
                {
                    // Get the last ID generated and increment it.
                    if (datasettest.Tables[2].Rows.Count > 0)
                    {
                        statusID = (int)datasettest.Tables[2].Rows[datasettest.Tables[2].Rows.Count - 1][0] + 1;
                    }
                    // It is a newly added status in combo box.
                    datasettest.Tables[2].Rows.Add(statusID, cboEditStatus.Text);
                }
                else
                {
                    statusID = (int)statusRow[0];
                }

                datasettest.Tables[0].Rows.Add(txtEditUsername.Text, txtEditPassword.Text, deptID, statusID);

                
                datasettest.AcceptChanges();

                this.txtEditUsername.Enabled = false;
                this.txtEditPassword.Enabled = false;
                this.cboEditDepartment.Enabled = false;
                this.cboEditStatus.Enabled = false;

                AddBindings();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


    }

Open in new window

0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Sudhakar-Pulivarthi,

Excellent - thats doing just what i wanted:) only 1 more question, well 2 if i May?

1st - How do i get it to delete rowns? and
2nd - How do i get the dataset updates to go back to that database?

Many thanks again for your valued time and help
Si
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
Comment Utility
Hi Simon,

This is the code for delete event.

private void btnDelete_Click(object sender, EventArgs e)
        {
            // Check whether any users sre present in the table.
            if (datasettest.Tables[0].Rows.Count > 0)
            {
                // Create the filter to obtain the record from the tblusernames table in dataset
                StringBuilder selectFilter = new StringBuilder();
                selectFilter.Append("[Username] = '").Append(txtEditUsername.Text).Append("' AND ");
                selectFilter.Append("[Password] = '").Append(txtEditPassword.Text).Append("' AND ");
                selectFilter.Append("[lkDepartment] = '").Append(((DataRowView)cboEditDepartment.SelectedItem)[0]).Append("' AND ");
                selectFilter.Append("[lkStatus] = '").Append(((DataRowView)cboEditStatus.SelectedItem)[0]).Append("'");

                // Get the required record from the tblusernames
                DataRow[] detailsRows = datasettest.Tables[0].Select(selectFilter.ToString());
                if (detailsRows.Length > 0)
                {
                    // Remove the record.
                    datasettest.Tables[0].Rows.Remove(detailsRows[0]);
                }
            }
        }

To update the data back to database as i said above we need to implement the INSERT,UPDATE, DELETE command on the adapter as in the link i have provided.
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Sudhakar-Pulivarthi:,

As sorry was one of the originalproblems. I didnt understand what to do even after reading the like (several times now) :(

Cheers
Si
0
 
LVL 14

Expert Comment

by:systan
Comment Utility
This code is really different, it looks wild and dirty.
Anyway there are many ways to kill a cat.
How's this?, is it solved?
0
 
LVL 11

Accepted Solution

by:
Kusala Wijayasena earned 250 total points
Comment Utility
I have corrected your sample program. Just check it out

public enum CurrentMode
{
    None,
    Add,
    Edit,
    Delete
}

public partial class Form1 : Form
{
    private DataSet dsTraining;
    private CurrentMode currentMode;
    private OleDbConnection cn;
    private OleDbDataAdapter da;
    
    public Form1()
    {
        dsTraining = new DataSet("Training");
        currentMode = CurrentMode.None;
        
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Training.accdb");
        cn.Open();

        da = new OleDbDataAdapter("SELECT * FROM tblDepartments", cn);
        da.Fill(dsTraining, "tblDepartments");
        
        da.SelectCommand.CommandText = "SELECT * FROM tblStatus";
        da.Fill(dsTraining, "tblStatus");

        da.SelectCommand.CommandText = "SELECT * FROM tblUsernames";
        da.Fill(dsTraining, "tblUsernames");

        OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
        da.InsertCommand = cb.GetInsertCommand();
        da.DeleteCommand = cb.GetDeleteCommand();
        da.UpdateCommand = cb.GetUpdateCommand();
        
        SetDataSources();
        AddBindings();
    }

    private void SetDataSources()
    {
        lstUsernames.DataSource = dsTraining.Tables["tblUsernames"];
        lstUsernames.DisplayMember = "Username";
        lstUsernames.ValueMember = "ID";

        cboEditDepartment.DataSource = dsTraining.Tables["tblDepartments"];
        cboEditDepartment.DisplayMember = "Department";
        cboEditDepartment.ValueMember = "ID";

        cboEditStatus.DataSource = dsTraining.Tables["tblStatus"];
        cboEditStatus.DisplayMember = "Status";
        cboEditStatus.ValueMember = "ID";
    }
    
    private void AddBindings()
    {
        cboEditDepartment.DataBindings.Add("SelectedValue", dsTraining.Tables["tblUsernames"], "lkDepartment");
        cboEditStatus.DataBindings.Add("SelectedValue", dsTraining.Tables["tblUsernames"], "lkStatus");
        txtEditUsername.DataBindings.Add("Text", dsTraining.Tables["tblUsernames"], "Username");
        txtEditPassword.DataBindings.Add("Text", dsTraining.Tables["tblUsernames"], "Pass");
    }

    private void RemoveBindings()
    {
        cboEditDepartment.DataBindings.Clear();
        cboEditStatus.DataBindings.Clear();
        txtEditUsername.DataBindings.Clear();
        txtEditPassword.DataBindings.Clear();
    }

    private void ClearControls()
    {
        txtEditUsername.Clear();
        txtEditPassword.Clear();
        cboEditDepartment.SelectedIndex = 0;
        cboEditStatus.SelectedIndex = 0;
    }

    private void EnableControls(bool enable)
    {
        txtEditUsername.Enabled = enable;
        txtEditPassword.Enabled = enable;
        cboEditDepartment.Enabled = enable;
        cboEditStatus.Enabled = enable;
    }

    private void btnAddUser_Click(object sender, EventArgs e)
    {
        currentMode = CurrentMode.Add;
        RemoveBindings();
        ClearControls();
        EnableControls(true);
        txtEditUsername.Focus();
    }
    
    private void btnUpdate_Click(object sender, EventArgs e)
    {
        if (currentMode == CurrentMode.Add)
        {
            DataRow dr = dsTraining.Tables["tblUsernames"].NewRow();
            dr["Username"] = txtEditUsername.Text;
            dr["Pass"] = txtEditPassword.Text;
            dr["lkDepartment"] = cboEditDepartment.SelectedValue;
            dr["lkStatus"] = cboEditStatus.SelectedValue;
            dsTraining.Tables["tblUsernames"].Rows.Add(dr);
            da.Update(dsTraining, "tblUsernames"); //Update Database
            AddBindings();
            lstUsernames.SelectedIndex = lstUsernames.Items.Count - 1;
            EnableControls(false);

            currentMode = CurrentMode.None;
        }
        else if (currentMode == CurrentMode.Edit)
        {
           
        }
    }

    private void btnDelete_Click(object sender, EventArgs e)
    {
        if (lstUsernames.Items.Count != 0)
        {
            dsTraining.Tables["tblUsernames"].Rows[lstUsernames.SelectedIndex].Delete();
            da.Update(dsTraining, "tblUsernames"); //Update Database
        }
        
    }
}

Open in new window


Note: Please avoid using "Password" as field name. Because it is a keyword and makes conflicts and end-up with having errors (http://www.daniweb.com/software-development/vbnet/threads/173074)

Therefore, I have chained the "Password" field of "tblUsernames" table as "Pass"

-Kusala
0
 
LVL 1

Author Comment

by:SimonGreen2004
Comment Utility
Hi Everyone,

Thanks again for all you help. and deffinatly thanks to Sudhakar-Pulivarthi for sticking with this thread:).

It certainly has been a weekend of reading.

Kusala, Just what i was after for the DB Update.

Im actually starting to understand whats going on now (I think) and have a good reference to go off. How do i delete and update using the new code above from Kusala.

I am actually finding it very hard to find any simple DB example for C# on the internet they all seem to VB.Net.

Cheers Again Everyone
Si
0
 
LVL 1

Assisted Solution

by:SimonGreen2004
SimonGreen2004 earned 0 total points
Comment Utility
Appologies ... Jut realiased Add and Update is already there:)

Thanks again for everybodys help on this. its much appriciated:)

Cheers Again
Si
0
 
LVL 1

Author Closing Comment

by:SimonGreen2004
Comment Utility
Thanks for helping me on this one everyone:)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now