asked on

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

Rikin Shah

Use Textbox.Text property instead of Textbox.

Thanks,
Rikin

Rikin Shah

I guess cboEditDepartment is a combo box...

Change it to cboEditDepartment.SelectedItem.Text

Thanks,
Rikin

robasta

 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

SOLUTION

Kusala Wijayasena

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Simon Green

ASKER

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

SOLUTION

Sudhakar Pulivarthi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Simon Green

ASKER

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

Sudhakar Pulivarthi

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.

Simon Green

ASKER

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

Sudhakar Pulivarthi

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.

Simon Green

ASKER

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

systan

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

Simon Green

ASKER

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

Simon Green

ASKER

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

Sudhakar Pulivarthi

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.

Simon Green

ASKER

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

Sudhakar Pulivarthi

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

Simon Green

ASKER

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

Sudhakar Pulivarthi

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

SOLUTION

Sudhakar Pulivarthi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Simon Green

ASKER

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

Sudhakar Pulivarthi

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.

Simon Green

ASKER

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

systan

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?

ASKER CERTIFIED SOLUTION

Kusala Wijayasena

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Simon Green

ASKER

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

SOLUTION

Simon Green

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Simon Green

ASKER

Thanks for helping me on this one everyone:)