• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 867
  • Last Modified:

c# Update and Delete

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
Simon Green
Asked:
Simon Green
  • 13
  • 8
  • 2
  • +3
5 Solutions
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Use Textbox.Text property instead of Textbox.

Thanks,
Rikin
0
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
I guess cboEditDepartment is a combo box...

Change it to cboEditDepartment.SelectedItem.Text


Thanks,
Rikin
0
 
robastaCommented:
 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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Kusala WijayasenaSoftware EngineerCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:

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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
systanCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
systanCommented:
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
 
Kusala WijayasenaSoftware EngineerCommented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
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
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
Appologies ... Jut realiased Add and Update is already there:)

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

Cheers Again
Si
0
 
Simon GreenSenior Infrastructure and Security ManagerAuthor Commented:
Thanks for helping me on this one everyone:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 8
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now