asked on

Update using DataGridView Control

I am trying to teach myself how to use the DataGridViewControl. What I would like to do is simply display data from a table of my MySql Database, and then click on any row item, edit the item, and update my mysql databse after the user presses enter or clicks save.

I have been able to display data from one of my MySql tables, but I can't seem to get a grasp on how I can update my database with the edited cell. It appears as this would be easier if I was using SqlServer, but i must use MySql. I posted my code that I used to load the DataGridViewControl.

private void InitializeDataGridView(DataSet ds)
{
try
{
for (int i = 0; i < ds.Tables.Count; i++)
{
DataRow newRow = ds.Tables[0].NewRow();

newRow.ItemArray = ds.Tables[i].Rows[0].ItemArray;

ds.Tables[0].Rows.Add(newRow);
}
dataGridView.DataSource = ds.Tables[0];
dataGridView.Refresh();
}

nbkbar7

This what you're looking for?

If not lem'me know and I'll throw together some code and we can step through it together...

Dave

http://forums.mysql.com/read.php?38,115063,115063

Gautham Janardhan

IDataAdapter FAdapter = new OleDbDataAdapter(UrConnection);
DataSet FInternalDataSet = new DataSet(); // replace with ur dataset

try
                  {
                        ((OleDbDataAdapter)FAdapter).SelectCommand
                              = new OleDbCommand
                              ("SELECT COMPANYCODE,USERNAME,ALERTCODE,OPERATOR,VALUE"
                              +" FROM TR_ALERTDET WHERE 1=2");
                        ((OleDbDataAdapter)FAdapter).FillSchema(FInternalDataSet
                              ,SchemaType.Mapped,"TR_ALERTDET");
                        ((OleDbDataAdapter)FAdapter).SelectCommand
                              = new OleDbCommand
                              ("SELECT COMPANYCODE,USERNAME,ALERTCODE,OPERATOR,VALUE"
                              +" FROM TR_ALERTDET WHERE USERNAME = @USERNAME");
                        ((OleDbDataAdapter)FAdapter).SelectCommand.Parameters.Add
                              ("@USERNAME",null);

                        ((OleDbDataAdapter)FAdapter).DeleteCommand
                              = new OleDbCommand
                              ("DELETE FROM TR_ALERTDET WHERE USERNAME = @USERNAME");
                        ((OleDbDataAdapter)FAdapter).DeleteCommand.Parameters.Add
                              ("@USERNAME",null);
                        ((OleDbDataAdapter)FAdapter).DeleteCommand.Parameters["@USERNAME"]
                              .SourceColumn = "USERNAME";
                        ((OleDbDataAdapter)FAdapter).DeleteCommand.
                              Parameters["@USERNAME"].SourceVersion
                              = DataRowVersion.Current;

                        ((OleDbDataAdapter)FAdapter).InsertCommand =
                              new OleDbCommand
                              ("INSERT INTO TR_ALERTDET(COMPANYCODE,USERNAME"+
                              ",ALERTCODE,OPERATOR,VALUE) VALUES(@COMPANYCODE,@USERNAME"+
                              ",@ALERTCODE,@OPERATOR,@VALUE)");
                        foreach(DataColumn Col in InternaDataTable.Columns)
                        {
                              ((OleDbDataAdapter)FAdapter).InsertCommand.Parameters.Add
                                    ("@"+Col.ColumnName,null);
                              ((OleDbDataAdapter)FAdapter).InsertCommand.Parameters["@"+Col.ColumnName]
                                    .SourceColumn =Col.ColumnName;
                              ((OleDbDataAdapter)FAdapter).InsertCommand.
                                    Parameters["@"+Col.ColumnName].SourceVersion
                                    = DataRowVersion.Current;
                        }
                  }
                  catch(Exception ex)
                  {
                        throw new Exception("Exception in prepare commands "+ex.Message);
                  }

i'm ising a table called tr_alertdet and where username is the primary key u can change the command for ur table respectively

while saving just call
((OleDbDataAdapter)FAdapter).Update(FInternalDataSet)

AlexNek

you must call the write command after wdit the data
in this sample
http://msdn2.microsoft.com/en-us/library/fbk67b6z.aspx
it is line
dataAdapter.Update((DataTable)bindingSource1.DataSource);

LeeHenry

ASKER

Dave,

I saw this example on the mysql forums. I've already tried to get this to work. When I copy and paste the exact code, and use the country database like they mention, I can display the information, but nothing else is working. I set breakpoints on all the other functions (dataGridView_RowValidating etc...), and they are not reaching them. I didn't see anything in the properties of the dataGridView where I needed to specify these functions anywhere. I am probably missing something simple..??? any suggestions

AlexNex,

I think your example was for SqlServer.

gauthampj,

I'm going to take a look at yours later on tomm..

thanks for the tips

ASKER CERTIFIED SOLUTION

nbkbar7

membership

This solution is only available to members.

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

Start Free Trial

LeeHenry

ASKER

kbkbar7,

I'm not sure if i'm going to work on this again.. i've been busy with something else... If i find out i'm not going to get into this, i'll go ahead and give you the points since you were very helpfull.. I should be letting you know soon.. thanks again for the response