DataBindings et mise à jour de table SQL Server

Marsh Posté le 19-02-2006 à 22:05:01

Je souhaite faire un formulaire pour mettre à jour une table SQL Server en C#. J'ai créé une connexion que j'ai lié à un SqlDataAdapter. Ensuite, j'ai un DataSet, rempli par le SQLDataAdapter, et qui est databindé à des TextBox, et un DateTimePicker.

Voici mon code :

Code :

using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Windows.Forms;
namespace ConsoleApplication1
{
class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.TabControl tabControl1;
private System.Windows.Forms.TabPage tabPage1;
private System.Windows.Forms.TabPage tabPage2;
private System.Windows.Forms.ListBox lstNoms;
private System.Windows.Forms.ListBox lstPseudos;
private System.Data.SqlClient.SqlConnection sqlConnection1;
private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
private System.Windows.Forms.TextBox txtNom;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.TextBox txtPrenom;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.TextBox txtPseudo;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.DateTimePicker dtDateNaissance;
private System.Windows.Forms.Button cmdAppliquer;
private System.Windows.Forms.Button cmdAnnuler;
private System.Data.DataSet DataSet1;
#region Généré par l'assistant
private void InitializeComponent()
{
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.tabControl1 = new System.Windows.Forms.TabControl();
this.tabPage1 = new System.Windows.Forms.TabPage();
this.lstNoms = new System.Windows.Forms.ListBox();
this.tabPage2 = new System.Windows.Forms.TabPage();
this.lstPseudos = new System.Windows.Forms.ListBox();
this.txtNom = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.txtPrenom = new System.Windows.Forms.TextBox();
this.label3 = new System.Windows.Forms.Label();
this.txtPseudo = new System.Windows.Forms.TextBox();
this.dtDateNaissance = new System.Windows.Forms.DateTimePicker();
this.label4 = new System.Windows.Forms.Label();
this.cmdAppliquer = new System.Windows.Forms.Button();
this.cmdAnnuler = new System.Windows.Forms.Button();
this.tabControl1.SuspendLayout();
this.tabPage1.SuspendLayout();
this.tabPage2.SuspendLayout();
this.SuspendLayout();
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=THOMAS;packet size=4096;integrated security=SSPI;data source=THOMA" +
"S;persist security info=False;initial catalog=cyber_cafe";
//
// tabControl1
//
this.tabControl1.Controls.Add(this.tabPage1);
this.tabControl1.Controls.Add(this.tabPage2);
this.tabControl1.Location = new System.Drawing.Point(8, 8);
this.tabControl1.Name = "tabControl1";
this.tabControl1.SelectedIndex = 0;
this.tabControl1.Size = new System.Drawing.Size(240, 304);
this.tabControl1.TabIndex = 0;
//
// tabPage1
//
this.tabPage1.Controls.Add(this.lstNoms);
this.tabPage1.Location = new System.Drawing.Point(4, 22);
this.tabPage1.Name = "tabPage1";
this.tabPage1.Size = new System.Drawing.Size(232, 278);
this.tabPage1.TabIndex = 0;
this.tabPage1.Text = "Noms";
//
// lstNoms
//
this.lstNoms.Location = new System.Drawing.Point(8, 8);
this.lstNoms.Name = "lstNoms";
this.lstNoms.Size = new System.Drawing.Size(216, 264);
this.lstNoms.TabIndex = 0;
this.lstNoms.SelectedIndexChanged += new System.EventHandler(this.ListBox_IndexChanged);
//
// tabPage2
//
this.tabPage2.Controls.Add(this.lstPseudos);
this.tabPage2.Location = new System.Drawing.Point(4, 22);
this.tabPage2.Name = "tabPage2";
this.tabPage2.Size = new System.Drawing.Size(232, 278);
this.tabPage2.TabIndex = 1;
this.tabPage2.Text = "Pseudos";
//
// lstPseudos
//
this.lstPseudos.Location = new System.Drawing.Point(8, 8);
this.lstPseudos.Name = "lstPseudos";
this.lstPseudos.Size = new System.Drawing.Size(216, 264);
this.lstPseudos.TabIndex = 0;
//
// txtNom
//
this.txtNom.Location = new System.Drawing.Point(440, 40);
this.txtNom.Name = "txtNom";
this.txtNom.Size = new System.Drawing.Size(184, 20);
this.txtNom.TabIndex = 1;
this.txtNom.Text = "";
//
// label1
//
this.label1.Location = new System.Drawing.Point(264, 40);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(168, 20);
this.label1.TabIndex = 2;
this.label1.Text = "Nom";
this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
//
// label2
//
this.label2.Location = new System.Drawing.Point(264, 72);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(168, 20);
this.label2.TabIndex = 4;
this.label2.Text = "Prénom";
this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
//
// txtPrenom
//
this.txtPrenom.Location = new System.Drawing.Point(440, 72);
this.txtPrenom.Name = "txtPrenom";
this.txtPrenom.Size = new System.Drawing.Size(184, 20);
this.txtPrenom.TabIndex = 3;
this.txtPrenom.Text = "";
//
// label3
//
this.label3.Location = new System.Drawing.Point(264, 104);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(168, 20);
this.label3.TabIndex = 6;
this.label3.Text = "Pseudo";
this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
//
// txtPseudo
//
this.txtPseudo.Location = new System.Drawing.Point(440, 104);
this.txtPseudo.Name = "txtPseudo";
this.txtPseudo.Size = new System.Drawing.Size(184, 20);
this.txtPseudo.TabIndex = 5;
this.txtPseudo.Text = "";
//
// dtDateNaissance
//
this.dtDateNaissance.Location = new System.Drawing.Point(440, 136);
this.dtDateNaissance.Name = "dtDateNaissance";
this.dtDateNaissance.Size = new System.Drawing.Size(184, 20);
this.dtDateNaissance.TabIndex = 9;
this.dtDateNaissance.Value = new System.DateTime(1970, 1, 1, 1, 0, 0, 0);
//
// label4
//
this.label4.Location = new System.Drawing.Point(264, 136);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(168, 20);
this.label4.TabIndex = 8;
this.label4.Text = "Date de naissace";
this.label4.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
//
// cmdAppliquer
//
this.cmdAppliquer.Location = new System.Drawing.Point(256, 288);
this.cmdAppliquer.Name = "cmdAppliquer";
this.cmdAppliquer.Size = new System.Drawing.Size(88, 24);
this.cmdAppliquer.TabIndex = 10;
this.cmdAppliquer.Text = "Appliquer";
this.cmdAppliquer.Click += new System.EventHandler(this.cmdAppliquer_Click);
//
// cmdAnnuler
//
this.cmdAnnuler.Location = new System.Drawing.Point(536, 288);
this.cmdAnnuler.Name = "cmdAnnuler";
this.cmdAnnuler.Size = new System.Drawing.Size(88, 24);
this.cmdAnnuler.TabIndex = 11;
this.cmdAnnuler.Text = "Annuler";
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(632, 317);
this.Controls.Add(this.cmdAnnuler);
this.Controls.Add(this.cmdAppliquer);
this.Controls.Add(this.label4);
this.Controls.Add(this.dtDateNaissance);
this.Controls.Add(this.label3);
this.Controls.Add(this.txtPseudo);
this.Controls.Add(this.txtPrenom);
this.Controls.Add(this.txtNom);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.tabControl1);
this.Name = "Form1";
this.Text = "Gestion des clients";
this.tabControl1.ResumeLayout(false);
this.tabPage1.ResumeLayout(false);
this.tabPage2.ResumeLayout(false);
this.ResumeLayout(false);
}
#endregion
public Form1()
{
this.InitializeComponent();
this.sqlDataAdapter1 = new SqlDataAdapter();
this.sqlDataAdapter1.SelectCommand = new SqlCommand("SELECT cli_id, cli_login, cli_password, cli_surname, cli_firstname, (cli_surname + ' ' + cli_firstname) AS cli_firstandsurname, cli_dateofbirth FROM client", this.sqlConnection1);
this.sqlDataAdapter1.UpdateCommand = new SqlCommand("UPDATE client SET cli_login = @cli_login, cli_password = @cli_password, cli_firstname = @cli_firstname, cli_surname = @cli_surname, cli_dateofbirth = @cli_dateofbirth WHERE cli_id = @cli_id", this.sqlConnection1);
this.sqlDataAdapter1.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@cli_id", System.Data.SqlDbType.Int, 4, "cli_id" ));
this.sqlDataAdapter1.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@cli_login", System.Data.SqlDbType.VarChar, 16, "cli_login" ));
this.sqlDataAdapter1.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@cli_surname", System.Data.SqlDbType.VarChar, 30, "cli_surname" ));
this.sqlDataAdapter1.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@cli_firstname", System.Data.SqlDbType.VarChar, 30, "cli_firstname" ));
this.sqlDataAdapter1.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@cli_password", System.Data.SqlDbType.VarChar, 30, "cli_password" ));
this.sqlDataAdapter1.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@cli_dateofbirth", System.Data.SqlDbType.DateTime, 8, "cli_dateofbirth" ));
this.DataSet1 = new DataSet();
this.sqlDataAdapter1.Fill(this.DataSet1, "client" );
this.txtNom.DataBindings.Add("Text", this.DataSet1.Tables["client"], "cli_surname" );
this.txtPrenom.DataBindings.Add("Text", this.DataSet1.Tables["client"], "cli_firstname" );
this.txtPseudo.DataBindings.Add("Text", this.DataSet1.Tables["client"], "cli_login" );
this.dtDateNaissance.DataBindings.Add("Value", this.DataSet1.Tables["client"], "cli_dateofbirth" );
this.lstNoms.DisplayMember = "cli_firstandsurname";
this.lstNoms.DataSource = this.DataSet1.Tables["client"].DefaultView;
this.lstPseudos.DisplayMember = "cli_login";
this.lstPseudos.DataSource = this.DataSet1.Tables["client"].DefaultView;
}
private void ListBox_IndexChanged(object sender, System.EventArgs e)
{
this.BindingContext[ this.DataSet1.Tables["client"] ].Position = ((ListBox)sender).SelectedIndex;
}
private void cmdAppliquer_Click(object sender, System.EventArgs e)
{
if(this.DataSet1.HasChanges())
{
this.sqlDataAdapter1.Update(this.DataSet1, "client" );
this.DataSet1.Tables["client"].AcceptChanges();
}
else
{
MessageBox.Show("Aucune modification à faire !" );
}
}
}
}

Le probleme avec ce code, c'est que si je change la valeur d'un champ, et que je veux cliquer sur Accepter, alors j'ai la message box "Pas de modif"... Par contre, si je selectionne un autre tuple.. et que je clique sur accepter.. alors la ça met à jour... Y a t il une solution propre, pour mettre a jour sans changer de tuple... Je desespère vraiment, je n'ai pas trouvé de code "potable" qui fonctionne.. merci d'avance

Marsh Posté le 19-02-2006 à 22:05:01

Marsh Posté le 23-09-2008 à 16:14:13

c'est con ça, j'ai le souci là, et j'aurais bien aimé la réponse qui va avec la question ^^

Marsh Posté le 23-09-2008 à 19:13:39

l'idéal est de procéder comme suit:

créer un bindingSource dont le dataSource est le dataset et le datamember est la table du dataset.

lié les controles au bindingSource.
et quand tu cliques sur ton bouton, tu appeles la méthodes "EndEdit" du bindingSource et le tour est joué ;-)

---------------
quand un homme raisonne mal c'est qu'il n'a pas les données pour raisonner mieux (diderot)

Marsh Posté le 24-09-2008 à 01:52:02

c'est ce que je fais faire, parceque là ça me fait n'importe quoi, et j'ai passé 3 heures à pas trouver pourquoi.

je te dis demain si ça passe :jap:

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed

Name *

Email *

URL

Message *