Asked by:
SQL Insert into an array data into a table doesnt work :s (using MySql Server)

Question
-
Hi, I'm developping a C# application, and I've got some problem getting my SQL command working...
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!!
Code Snippetusing 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éesstring[] 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