Click here to Skip to main content
15,886,199 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
It's nearly 4am here in my country, and I've been like this already for 2 days, trying to figure out these:

1. import excel to datagrid
2. save the imported excel in the datagrid to mysql database

I have no problem with no. 1, I can easily do that, but what the heck with my problem no.2. I've been staying awake late already for two days trying to figure this out.

Please someone help me, this a module in my senior project, ive search everywhere in google and what nothing I find. Person to help me with this, will be a tribute to my Senior Project. THANK YOU

Currently this is what I have:
C#
private void button1_Click(object sender, EventArgs e)
       {
           OpenFileDialog ofd = new OpenFileDialog();

           if (ofd.ShowDialog() == DialogResult.OK)
           {

               string path = System.IO.Path.GetFullPath(ofd.FileName);

               string querry = "SELECT * FROM [Sheet1$]";

               OleDbConnection conn = new OleDbConnection();

               conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" + path + "'" + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";

               OleDbDataAdapter adapter = new OleDbDataAdapter(querry, conn);

               DataSet dataSet = new DataSet();

               adapter.Fill(dataSet);

               dataGridView1.DataSource = dataSet.Tables[0];

               departmentsTableAdapter.Update(mbmsDataSet.departments);
           }
           else
           {
               ofd.Dispose();
           }
       }
Posted
Updated 17-Aug-11 10:18am
v2

i was able to solve it by myself, oh well after 4 days. I know this will be a good use for those doing similar to mine...fellows here's the sample code you may use :D

C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace training2
{
   
    public partial class Form1 : Form
    {
        
        f*** sh*t = new f***();
        

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            

            sh*t.GetData(dataGridView1);

        }



        public class f***
        {
            
             
             public MySqlConnection connection = new MySqlConnection("SERVER=localhost;DATABASE=mbms;UID=root;PASSWORD=qwerty123;");
             public MySqlDataAdapter adapter;
             public DataTable table = new DataTable();
             

            public void InitialiseDataAccessObjects()
            {
                

                this.adapter = new MySqlDataAdapter("SELECT  office_dept, function, proj_act, pa_code, fundspecial_acct, year FROM departments", this.connection);
               
                
                //MySqlCommand delete = new MySqlCommand("DELETE FROM departments WHERE pa_code = @pc", this.connection);
                MySqlCommand insert = new MySqlCommand("INSERT INTO departments (office_dept, function, proj_act, pa_code, fundspecial_acct, year) VALUES (@od, @f, @pa, @pc, @fa, @y )", this.connection);
                MySqlCommand update = new MySqlCommand("UPDATE departments SET office_dept = @od, function = @f, proj_act = @pa, fundspecial_acct = @fa, year = @y WHERE pa_code = @pc", this.connection);
                MySqlCommand delete = new MySqlCommand("DELETE FROM departments WHERE pa_code = @pc", this.connection);


                delete.Parameters.Add("@pc", MySqlDbType.Int32, 11, "pa_code");

                insert.Parameters.Add("@od", MySqlDbType.VarChar, 45, "office_dept");
                insert.Parameters.Add("@f", MySqlDbType.VarChar, 45, "function");
                insert.Parameters.Add("@pa", MySqlDbType.VarChar, 45, "proj_act");
                insert.Parameters.Add("@pc", MySqlDbType.Int32, 4, "pa_code");
                insert.Parameters.Add("@fa", MySqlDbType.VarChar, 8, "fundspecial_acct");
                insert.Parameters.Add("@y", MySqlDbType.Year, 4, "year");

                update.Parameters.Add("@od", MySqlDbType.VarChar, 45, "office_dept");
                update.Parameters.Add("@f", MySqlDbType.VarChar, 45, "function");
                update.Parameters.Add("@pa", MySqlDbType.VarChar, 45, "proj_act");
                update.Parameters.Add("@pc", MySqlDbType.Int32, 4, "pa_code");
                update.Parameters.Add("@fa", MySqlDbType.VarChar, 45, "fundspecial_acct");
                update.Parameters.Add("@y", MySqlDbType.Year, 4, "year");

                

                
                this.adapter.DeleteCommand = delete;
                this.adapter.InsertCommand = insert;
                this.adapter.UpdateCommand = update;

                this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                
            }



            public void GetData(DataGridView f***ing)
            {
                // Retrieve the data.

                
                adapter.Fill(table);

                f***ing.DataSource = table;
                

                // The table can be used here to display and edit the data.
                // That will most likely involve data-binding but that is not a data access issue.
            }

            public void excel(DataGridView datagrid)
            {
                OpenFileDialog ofd = new OpenFileDialog();

                

                if (ofd.ShowDialog() == DialogResult.OK)
                {

                    string path = System.IO.Path.GetFullPath(ofd.FileName);

                    string querry = "SELECT * FROM [Sheet1$]";

                    OleDbConnection conn = new OleDbConnection();

                    conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" + path + "'" + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";

                    OleDbDataAdapter fag = new OleDbDataAdapter(querry, conn);



                    fag.AcceptChangesDuringFill = false;

                    
                    fag.Fill(table);

                    
                    datagrid.DataSource = table;

                    



                }

                else
                {

                    ofd.Dispose();
                }
            }

            
            public void SaveData()
            {
                // Save the data.
                this.adapter.Update(this.table);
                
            }
        }
        
        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'mbmsDataSet.departments' table. You can move, or remove it, as needed.
            this.departmentsTableAdapter.Fill(this.mbmsDataSet.departments);
            // TODO: This line of code loads data into the 'mbmsDataSet.departments' table. You can move, or remove it, as needed.
            
            sh*t.InitialiseDataAccessObjects();
        }

        private void button2_Click(object sender, EventArgs e)
        {

            sh*t.excel(dataGridView1);   
        }

        private void button3_Click(object sender, EventArgs e)
        {
            sh*t.SaveData();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            
        }
    }
}
 
Share this answer
 
v2
Comments
Mawool 11-Apr-14 22:35pm    
please help me :(
Why updateCommand didnt work, but insert command work. below is my code like your code (with little manipulation):
public void SerahTerima(DataGridView dataGrid)
{
MySqlConnection connection = new MySqlConnection("SERVER=localhost;DATABASE=db_segel;UID=root;PASSWORD=;convert zero datetime=True");
MySqlDataAdapter adapter = new MySqlDataAdapter(); ;
DataTable table = new DataTable();
//this.adapter = new MySqlDataAdapter("SELECT * FROM `usage`", this.connection);
this.adapter = new MySqlDataAdapter("SELECT c.nama_pel, b.nomor, a.posisi, a.prev_segel, c.gardu, c.tarif, c.daya, a.tgl_pasang, a.id_segel, a.keterangan, c.id_pel, a.pelaksana, a.tgl_input FROM (`usage` a INNER JOIN `segel` b ON b.id_segel=a.id_segel) INNER JOIN `pelanggan` c ON c.id_pel = a.id_pelanggan ORDER BY a.tgl_pasang", this.connection);
MySqlCommand insert = new MySqlCommand("INSERT INTO `usage` values ('null', @s, @p, @pl, @t, @k, @ti, @tp, @po, @pr)", this.connection);
MySqlCommand update = new MySqlCommand("UPDATE segel SET status = 'Used' WHERE nomor = '@n'", this.connection);
insert.Parameters.Add("@s", MySqlDbType.Int32, 16, "id_segel");
insert.Parameters.Add("@p", MySqlDbType.VarChar, 12, "id_pel");
insert.Parameters.Add("@pl", MySqlDbType.VarChar, 32, "pelaksana");
insert.Parameters.Add("@t", MySqlDbType.VarChar, 4, "tarif");
insert.Parameters.Add("@k", MySqlDbType.Text, 255, "keterangan");
insert.Parameters.Add("@ti", MySqlDbType.Date, 16, "tgl_input");
insert.Parameters.Add("@tp", MySqlDbType.Date, 16, "tgl_pasang");
insert.Parameters.Add("@po", MySqlDbType.VarChar, 64, "posisi");
insert.Parameters.Add("@pr", MySqlDbType.VarChar, 16, "prev_segel");

update.Parameters.Add("@s", MySqlDbType.Int32, 16, "id_segel");
update.Parameters.Add("@n", MySqlDbType.VarChar, 16, "nomor");

this.adapter.InsertCommand = insert;
this.adapter.UpdateCommand = update;
this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//// Save the data.
this.adapter.Update(this.table);
}
Jura Arzadon 3-Mar-16 21:57pm    
Hello I just wonder what are the specific function of the three buttons that you used on your source code? Hopefully, for your quick response. :) regards to @mrpotatohead and @Mika Wendelius
If I understood the question correctly, one way to do this is to:
- open a connection to MySql database
- define a command object with the insert statement (INSERT INTO...)
- define the parameters for the values to insert in the statement
- loop through the data and on each row in the datatable
- set the parameter values
- execute the insert statement
- close and dispose the connection

And if you want to do this elegantly, you wrap the inserts in the loop inside a transaction which you will commit (before closing the connection) if everything goes fine in the loop and rollback if you have errors.
 
Share this answer
 
Comments
mrpotatohead 17-Aug-11 16:29pm    
do you have a sample code?..so I can use it creating the code
Wendelius 17-Aug-11 16:38pm    
Don't have directly any sample in mind, but here's some links for the classes:
- MySqlConnection[^]
- MySqlCommand[^]
- MySqlParameter[^]
- MySqlTransaction[^]

There are lots of snippets in those documentations so I believe that you can get a good start from there.

Also note that those classes use MySql Connector/Net so if you don't have it already, you can download it from http://dev.mysql.com/downloads/connector/net/[^]
mrpotatohead 17-Aug-11 16:36pm    
to clarify..im talking about importing the microsoft excel in the datagrid(done that), then saving it to the mysql database. :)
Wendelius 17-Aug-11 16:40pm    
Yes I noticed, but based on your code the datagrid seems to be bounded to a datatable so if possible, loop through the rows of the datatable instead of looping through the data grid.
mrpotatohead 17-Aug-11 16:52pm    
okay I will do your suggestions thanks
Hello! I've used your code now but I got some issues... When I open my Excel document. It's only opening 1 row (I can't write more rowns in the document if I do it aint working, I get the error message "That it may be problems with on-null-, unique- or foreign key-") but that aint the problem, and when I wants to save to my MySQL Database I get the error message "Fatal Error". Can anyone look at my code and help me? Reegards Robin


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;


namespace training2
{
    public partial class Form1 : Form
    {
        MySql hello = new MySql();
        public Form1()
        {
            InitializeComponent();
        }
         private void button1_Click(object sender, EventArgs e)
        {
              hello.GetData(dataGridView1);
        }
         public class MySql
        {
            
             
             public MySqlConnection connection = new MySqlConnection("SERVER=localhost;DATABASE=deviaq;UID=root;PASSWORD=Robban542;");
             public MySqlDataAdapter adapter;
             public DataTable table = new DataTable();
             
 
            public void InitialiseDataAccessObjects()
            {


                this.adapter = new MySqlDataAdapter("SELECT  MOVEX_BsNr, Namn, Adress FROM preem", this.connection);
               
                
                //MySqlCommand delete = new MySqlCommand("DELETE FROM departments WHERE pa_code = @pc", this.connection);
                MySqlCommand insert = new MySqlCommand("INSERT INTO preem (MOVEX_BsNr, Namn, Adress) VALUES (@od, @f, @pa, @pc, @fa, @y )", this.connection);
                MySqlCommand update = new MySqlCommand("UPDATE preem SET MOVEX_BsNr = @od, function = @f, proj_act = @pa, fundspecial_acct = @fa, year = @y WHERE pa_code = @pc", this.connection);
                MySqlCommand delete = new MySqlCommand("DELETE FROM preem WHERE Namn = @pc", this.connection);


                delete.Parameters.Add("@pc", MySqlDbType.Int32, 11, "MOVEX_BsNr");

                insert.Parameters.Add("@od", MySqlDbType.Text, 45, "Namn");
                insert.Parameters.Add("@f", MySqlDbType.Text, 45, "Adress");
                /*insert.Parameters.Add("@pa", MySqlDbType.VarChar, 45, "proj_act");
                insert.Parameters.Add("@pc", MySqlDbType.Int32, 4, "pa_code");
                insert.Parameters.Add("@fa", MySqlDbType.VarChar, 8, "fundspecial_acct");
                insert.Parameters.Add("@y", MySqlDbType.Year, 4, "year");
 
                update.Parameters.Add("@od", MySqlDbType.VarChar, 45, "office_dept");
                update.Parameters.Add("@f", MySqlDbType.VarChar, 45, "function");
                update.Parameters.Add("@pa", MySqlDbType.VarChar, 45, "proj_act");
                update.Parameters.Add("@pc", MySqlDbType.Int32, 4, "pa_code");
                update.Parameters.Add("@fa", MySqlDbType.VarChar, 45, "fundspecial_acct");
                update.Parameters.Add("@y", MySqlDbType.Year, 4, "year");*/
 
                
 
                
                this.adapter.DeleteCommand = delete;
                this.adapter.InsertCommand = insert;
                this.adapter.UpdateCommand = update;
 
                this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                
            }
        
         public void GetData(DataGridView MySQLl)
            {
                // Retrieve the data.

                
                adapter.Fill(table);
 
                MySQLl.DataSource = table;
                
 
                // The table can be used here to display and edit the data.
                // That will most likely involve data-binding but that is not a data access issue.
            }
 
            public void excel(DataGridView datagrid)
            {
                OpenFileDialog ofd = new OpenFileDialog();
 
                
 
                if (ofd.ShowDialog() == DialogResult.OK)
                {
 
                    string path = System.IO.Path.GetFullPath(ofd.FileName);
 
                    string querry = "SELECT * FROM [Sheet1$]";
 
                    OleDbConnection conn = new OleDbConnection();
 
                    conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" + path + "'" + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
 
                    OleDbDataAdapter fag = new OleDbDataAdapter(querry, conn);
 

 
                    fag.AcceptChangesDuringFill = false;
 
                    
                    fag.Fill(table);
 
                    
                    datagrid.DataSource = table;
 
                    
 

 
                }
 
                else
                {
 
                    ofd.Dispose();
                }
            }
 
            
            public void SaveData()
            {
                // Save the data.
                this.adapter.Update(this.table);
                
            }
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'mbmsDataSet.departments' table. You can move, or remove it, as needed.
            //this.departmentsTableAdapter.Fill(this.mbmsDataSet.departments);
            // TODO: This line of code loads data into the 'mbmsDataSet.departments' table. You can move, or remove it, as needed.
            
            hello.InitialiseDataAccessObjects();
        }

       

        private void button2_Click(object sender, EventArgs e)
        {
             hello.excel(dataGridView1);  
        }

        private void button3_Click(object sender, EventArgs e)
        {
            hello.SaveData();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900