none
SQL Insert into an array data into a table doesnt work :s (using MySql Server) RRS feed

  • Question

  • Hi, I'm developping a C# application, and I've got some problem getting my SQL command working... Tongue Tied

    So here my application:

    The application uploads a text file (user selects path), the application picks some specific informations (for every X lines, the 1rst, 2nd and 11th for ex.), those datas are put in a DataGridView. It works. On the other side, I have a form for connecting to database (MySql Server), a textbox where we enter SQL commands and an other DataGridView binded to a Dataset. Everything works, even if my code isnt the most clearer... But the aim of the application is that the datas extracted from the .txt could directly go into the database by clicking a button.

    I tried to make the SQL INSERT INTO method directly into the loop where it extracts datas, so that (we have everytime 3 differents datas) the 3 datas go into row[0], row[1] and row[2], and then those rows would go in the db.
    I can't make that "INSERT INTO [table] (XXX,XXX) VALUES (@row[0], @row[1]);";

    Here is my code if you want to take a look at it (its in french cause I actually live in France^^:

    Thanks for helping me of this one plz!! Smile

    Code Snippet

    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 MySql.Data;
    using MySql.Data.MySqlClient;
    using System.IO;

    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            //Variables globales
            string var1, var2, var3, path;
            string[] row = new string[2];       
            //instanciation de la conenxion et du dataset
            private MySqlConnection Connection = new MySqlConnection();
            private MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
            private DataSet ds = new DataSet();

           
            public Form1()
            {
                InitializeComponent();
            }

            private void PopulateDataGridView()
            {
                // mise en tableau des 3 données


                string[] row0 = { var1, var2, var3 };
                // mise en grid du tableau
                dataGridView1.Rows.Add(row0);
                dataGridView1.Columns[0].DisplayIndex = 1;
                row = row0;
                string InsertCmd = "INSERT INTO Produits (Reference, Designation) VALUES (@row[0], @row[1]);";

                MyAdapter.SelectCommand = new MySqlCommand(InsertCmd, Connection);

                Connection.Close();
            }

           
           
            private void btpath_Click(object sender, EventArgs e)
            {
                OpenFileDialog fdlg = new OpenFileDialog();
                fdlg.Title = "Fichier Produits";
                fdlg.InitialDirectory = @"c:\";
                fdlg.Filter = "Fichier Texte | *.txt";
                fdlg.FilterIndex = 2;
                fdlg.RestoreDirectory = true;
                if (fdlg.ShowDialog() == DialogResult.OK)
                {
                    tbpath.Text = fdlg.FileName;
                    path = fdlg.FileName;
                }
            }

            private void btlire_Click(object sender, EventArgs e)
            {
                //donne le chemin et le nom du fichier au constructeur StreamReader
                FileStream fs = new FileStream(path, FileMode.Open);
                StreamReader sr = new StreamReader(fs, Encoding.UTF8);
                string ligne;

                //lit la première ligne du texte
                ligne = sr.ReadLine();

                //continue de lire jusqu'à la fin du fichier
                while (ligne != null)
                {
                    var1 = ""; var2 = ""; var3 = "";

                    //on passe à la ligne suivante
                    ligne = sr.ReadLine();


                    if (ligne == "#CART")
                    {
                        // Boucle de récupération par redondance des intervals de lignes
                        for (int compteur = 0; compteur < 14; ligne = sr.ReadLine(), compteur++)
                           
                            switch (compteur)
                            {
                                case 1:
                                    var1 = ligne;
                                    break;
                                case 2:
                                    var2 = ligne;
                                    break;
                                case 13:
                                    var3 = ligne;
                                    break;
                            }
                                                                                  
                        // Appel de la méthode Populate
                        PopulateDataGridView();
                        
                    }
                }

                //ferme le fichier
                sr.Close();
                fs.Close();
            }

            private void btconnect_Click(object sender, EventArgs e)
            {
                //Reset de la ConnectionString
                string ConnnectionStr = String.Empty;

                if ((tbhote.Text.Length == 0) || (tbbase.Text.Length == 0) || (tblogin.Text.Length == 0))
                {
                    //Message d'erreur si champs non remplis
                    MessageBox.Show("Erreur, les champs Hôte, Base et Login doivent être remplis", "Erreur",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    // On définit la chaine de connexion
                 ConnnectionStr = "Database=" + tbbase.Text + ";Data Source=" + tbhote.Text + ";User=" + tblogin.Text + ";Password=" + tbpwd.Text;
                }

                Connection.ConnectionString = ConnnectionStr;

                try
                {
                    // On ouvre la connexion
                    Connection.Open();
                }
                catch (MySqlException Ex)
                {
                    MessageBox.Show("Erreur SQL:\n" + Ex.Message, "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

                    MessageBox.Show("Vous êtes maintenant connecté à la Base de Données " + tbbase.Text + ". Vous pouvez désormais éxécuter vos requêtes SQL!", "Connexion Réussie!", MessageBoxButtons.OK);
                    MessageBox.Show("Si vous n'éxécutez pas de requêtes SQL, n'oubliez pas de vous DECONNECTER de la base!", "ATTENTION ! ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }

            private void execsql_Click(object sender, EventArgs e)
            {
                // On définit la requête SELECT à exécuter
                string MySQLCmd = tbsql.Text;

                // On associe cette requête à la propriété SelectCommand du MySqlDataAdapter
                MyAdapter.SelectCommand = new MySqlCommand(MySQLCmd, Connection);

                // On remplit le dataset avec le résultat de la commande SELECT
                MyAdapter.Fill(ds);

                // On affiche le datagrid
                dataGrid1.DataSource = ds.Tables[0];
                dataGrid1.Refresh();

                // On ferme la connexion le plus tôt possible
                Connection.Close();
            }
                   
            private void btclear_Click(object sender, EventArgs e)
            {
                ds.Clear();
            }
           
            private void btdeconnect_Click(object sender, EventArgs e)
            {
                // On ferme la connexion le plus tôt possible
                Connection.Close();
            }


        }
    }


    Friday, October 10, 2008 12:49 PM