Hi!
I am new to this forum and C# so please bear with me. I am trying to use C# together with MySQL but so far all I have been able to do is to view records from my database.
When I try to insert, update or delete records I am unable to do so and I am sure it is a problem in my syntax. I don't have the errors I get when I try to perform the aforementioned actions but I hope with the code below someone can help me out....PLEEEEEEEEEEEEEEASE!!
C# Program Code
---------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using MySql.Data.MySqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace Data_Access
{
public partial class DataAccess : Form
{
//Private Global Members to be used in various methods
private MySqlConnection conn;
private MySqlDataAdapter dataAdapter;
private DataTable datatable;
private DataSet ds;
private int currRecord = 0;
private int totalRecord = 0;
private bool insertSelected;
private string msg;
private DataRow row;
public DataAccess()
{
InitializeComponent();
}
private void btnloadtable_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor; //Hourglass
//Connection String for MySQL
conn = new MySqlConnection("server=localhost;database=clarify;uid=root;password=;");
//Command String
string commandstring = "SELECT * FROM users WHERE users.active = 1";
//Data Adapter - Stores the Command String & the Connection
dataAdapter = new MySqlDataAdapter(commandstring, conn); //Connnects to database when asked
//Dataset - local container of data
ds = new DataSet();
//Storing the result of the query
dataAdapter.Fill(ds, "users");
//Datatable(s)
datatable = ds.Tables["users"];
currRecord = 0;
//Count Records in the Table
totalRecord = datatable.Rows.Count;
FillControls(); //Show current record on the form
InitializeCommands(); //Prepare commands for Insert, Update & Delete
Togglecontrols(true); //Enable corresponding controls
this.Cursor = Cursors.Default; //Returns default cursor
//Show Number of Records
lblrecord.Text = (currRecord + 1) + " Out of " + (totalRecord) + " Records.";
btnnext.Enabled = true;
btnprevious.Enabled = true;
btnadd.Enabled = true;
btndelete.Enabled = true;
btnedit.Enabled = true;
btncancel.Enabled = true;
btnsave.Enabled = true;
}
private void InitializeCommands()
{
//Preparing INSERT SQL Command
dataAdapter.InsertCommand = conn.CreateCommand();
dataAdapter.InsertCommand.CommandText =
"INSERT INTO users " +
"(username, password, active, level)" +
"VALUES (@username, @password, @active, @level)";
AddParams(dataAdapter.InsertCommand, "user_id", "username",
"password", "active", "level");
//Preparing UPDATE SQL Command
dataAdapter.UpdateCommand = conn.CreateCommand();
dataAdapter.UpdateCommand.CommandText =
"UPDATE users SET " +
"(username = @username, password = @password, active = @active, level = @level)" +
"WHERE user_id = @user_id";
AddParams(dataAdapter.UpdateCommand, "user_id", "username",
"password", "active", "level");
//Preparing DELETE SQL Command
dataAdapter.DeleteCommand = conn.CreateCommand();
dataAdapter.DeleteCommand.CommandText =
"DELETE FROM users WHERE user_id = @user_id";
AddParams(dataAdapter.DeleteCommand, "user_id");
}
private void AddParams(MySqlCommand cmd, params string[] cols)
{
//Adding parameter to SQL Commands (PROBLEM AREA!!)
foreach (string col in cols)
{
cmd.Parameters.Add("@" + col, MySqlDbType.Binary, 0, col);
}
}
private void Togglecontrols(bool val)
{
//Textboxes
txtusername.ReadOnly = val;
txtpassword.ReadOnly = val;
txtactive.ReadOnly = val;
txtlevel.ReadOnly = val;
//Command Buttons
btnloadtable.Enabled = val;
btnnext.Enabled = val;
btnprevious.Enabled = val;
btnadd.Enabled = val;
btnedit.Enabled = val;
btndelete.Enabled = val;
btnsave.Enabled = !val; //False
btncancel.Enabled = !val; //False
}
private void FillControls()
{
//Load Data Into Textboxes
txtuserid.Text = datatable.Rows[currRecord]["user_id"].ToString();
txtusername.Text = datatable.Rows[currRecord]["username"].ToString();
txtpassword.Text = datatable.Rows[currRecord]["password"].ToString();
txtactive.Text = datatable.Rows[currRecord]["active"].ToString();
txtlevel.Text = datatable.Rows[currRecord]["level"].ToString();
}
private void btnprevious_Click(object sender, EventArgs e)
{
currRecord--;
if (currRecord = totalRecord)
currRecord = 0;
FillControls();
//Move & Show Recordcount
lblrecord.Text = (currRecord + 1) + " Out of " + (totalRecord) + " Records.";
}
private void btnedit_Click(object sender, EventArgs e)
{
Togglecontrols(false);
}
private void btnsave_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
DataRow row = datatable.Rows[currRecord]; //Represents a row of data
row.BeginEdit();
//Transfers values of Textboxes to DB
row[1] = txtusername.Text;
row[2] = txtpassword.Text;
row[3] = txtactive.Text;
row[4] = txtlevel.Text;
row.EndEdit();
dataAdapter.Update(ds, "users"); //Saves changes in Repository (MEMORY)
ds.AcceptChanges(); //Saves changes in Database
Togglecontrols(true);
insertSelected = false;
this.Cursor = Cursors.Default;
msg = "Changes Were Successfully Saved";
MessageBox.Show(msg, "Update");
}
private void btncancel_Click(object sender, EventArgs e)
{
if(insertSelected == true)
{
btndelete_Click(null, null); //Deletes a new blank inserted row
insertSelected = false;
}
FillControls();
Togglecontrols(true);
}
private void btnadd_Click(object sender, EventArgs e)
{
insertSelected = true;
DataRow row = datatable.NewRow();
datatable.Rows.Add(row);
totalRecord = datatable.Rows.Count;
currRecord = totalRecord - 1;
row["user_id"] = totalRecord + 1; //New Record ID
txtuserid.Text = totalRecord.ToString();
txtusername.Text = "";
txtpassword.Text = "";
txtactive.Text = "";
txtlevel.Text = "";
Togglecontrols(false);
}
private void btndelete_Click(object sender, EventArgs e)
{
//Prompt the User
DialogResult result = MessageBox.Show(
"Are You Sure You Want to Delete the Current Record?",
"Confirm Current Deletion", MessageBoxButtons.YesNo);
if(result == DialogResult.Yes)
DataRow row = datatable.Rows[currRecord];
row.Delete();
dataAdapter.Update(ds, "users");
ds.AcceptChanges();
msg = "Record Has Been Deleted";
MessageBox.Show(msg, "Delete");
totalRecord--;
currRecord = totalRecord - 1;
FillControls();
}
private void DataAccess_Load(object sender, EventArgs e)
{
//Disable Buttons on Form Load
btnnext.Enabled = false;
btnprevious.Enabled = false;
btnadd.Enabled = false;
btndelete.Enabled = false;
btnedit.Enabled = false;
btncancel.Enabled = false;
btnsave.Enabled = false;
}
}
}
C# Designer Code
-----------------------
namespace Data_Access
{
partial class DataAccess
{
///
/// Required designer variable.
///
private System.ComponentModel.IContainer components = null;
///
/// Clean up any resources being used.
///
/// true if managed resources should be disposed; otherwise, false.
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.txtuserid = new System.Windows.Forms.TextBox();
this.txtusername = new System.Windows.Forms.TextBox();
this.label3 = new System.Windows.Forms.Label();
this.txtpassword = new System.Windows.Forms.TextBox();
this.label4 = new System.Windows.Forms.Label();
this.txtactive = new System.Windows.Forms.TextBox();
this.label5 = new System.Windows.Forms.Label();
this.txtlevel = new System.Windows.Forms.TextBox();
this.label6 = new System.Windows.Forms.Label();
this.btnloadtable = new System.Windows.Forms.Button();
this.btnnext = new System.Windows.Forms.Button();
this.btnprevious = new System.Windows.Forms.Button();
this.btnedit = new System.Windows.Forms.Button();
this.btndelete = new System.Windows.Forms.Button();
this.btnsave = new System.Windows.Forms.Button();
this.btncancel = new System.Windows.Forms.Button();
this.lblrecord = new System.Windows.Forms.Label();
this.btnadd = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// label1
//
this.label1.AutoSize = true;
this.label1.Font = new System.Drawing.Font("Bubblegum Superstar", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label1.Location = new System.Drawing.Point(85, 18);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(245, 16);
this.label1.TabIndex = 0;
this.label1.Text = "Data Access for MySQL ";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label2.Location = new System.Drawing.Point(22, 58);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(54, 13);
this.label2.TabIndex = 1;
this.label2.Text = "User ID:";
//
// txtuserid
//
this.txtuserid.BackColor = System.Drawing.Color.White;
this.txtuserid.Location = new System.Drawing.Point(147, 55);
this.txtuserid.Name = "txtuserid";
this.txtuserid.ReadOnly = true;
this.txtuserid.Size = new System.Drawing.Size(127, 20);
this.txtuserid.TabIndex = 2;
//
// txtusername
//
this.txtusername.BackColor = System.Drawing.Color.White;
this.txtusername.Location = new System.Drawing.Point(147, 97);
this.txtusername.Name = "txtusername";
this.txtusername.ReadOnly = true;
this.txtusername.Size = new System.Drawing.Size(127, 20);
this.txtusername.TabIndex = 4;
//
// label3
//
this.label3.AutoSize = true;
this.label3.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label3.Location = new System.Drawing.Point(22, 100);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(67, 13);
this.label3.TabIndex = 3;
this.label3.Text = "Username:";
//
// txtpassword
//
this.txtpassword.BackColor = System.Drawing.Color.White;
this.txtpassword.Location = new System.Drawing.Point(147, 139);
this.txtpassword.Name = "txtpassword";
this.txtpassword.ReadOnly = true;
this.txtpassword.Size = new System.Drawing.Size(127, 20);
this.txtpassword.TabIndex = 6;
//
// label4
//
this.label4.AutoSize = true;
this.label4.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label4.Location = new System.Drawing.Point(22, 142);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(65, 13);
this.label4.TabIndex = 5;
this.label4.Text = "Password:";
//
// txtactive
//
this.txtactive.BackColor = System.Drawing.Color.White;
this.txtactive.Location = new System.Drawing.Point(147, 182);
this.txtactive.Name = "txtactive";
this.txtactive.ReadOnly = true;
this.txtactive.Size = new System.Drawing.Size(127, 20);
this.txtactive.TabIndex = 8;
//
// label5
//
this.label5.AutoSize = true;
this.label5.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label5.Location = new System.Drawing.Point(22, 185);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(47, 13);
this.label5.TabIndex = 7;
this.label5.Text = "Active:";
//
// txtlevel
//
this.txtlevel.BackColor = System.Drawing.Color.White;
this.txtlevel.Location = new System.Drawing.Point(147, 228);
this.txtlevel.Name = "txtlevel";
this.txtlevel.ReadOnly = true;
this.txtlevel.Size = new System.Drawing.Size(127, 20);
this.txtlevel.TabIndex = 10;
//
// label6
//
this.label6.AutoSize = true;
this.label6.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label6.Location = new System.Drawing.Point(22, 231);
this.label6.Name = "label6";
this.label6.Size = new System.Drawing.Size(42, 13);
this.label6.TabIndex = 9;
this.label6.Text = "Level:";
//
// btnloadtable
//
this.btnloadtable.Location = new System.Drawing.Point(315, 55);
this.btnloadtable.Name = "btnloadtable";
this.btnloadtable.Size = new System.Drawing.Size(125, 27);
this.btnloadtable.TabIndex = 11;
this.btnloadtable.Text = "&Load Table";
this.btnloadtable.UseVisualStyleBackColor = true;
this.btnloadtable.Click += new System.EventHandler(this.btnloadtable_Click);
//
// btnnext
//
this.btnnext.Enabled = false;
this.btnnext.Location = new System.Drawing.Point(159, 312);
this.btnnext.Name = "btnnext";
this.btnnext.Size = new System.Drawing.Size(125, 27);
this.btnnext.TabIndex = 12;
this.btnnext.Text = "&Next >>";
this.btnnext.UseVisualStyleBackColor = true;
this.btnnext.Click += new System.EventHandler(this.btnnext_Click);
//
// btnprevious
//
this.btnprevious.Enabled = false;
this.btnprevious.Location = new System.Drawing.Point(16, 312);
this.btnprevious.Name = "btnprevious";
this.btnprevious.Size = new System.Drawing.Size(125, 27);
this.btnprevious.TabIndex = 13;
this.btnprevious.Text = "<< &Previous ";
this.btnprevious.UseVisualStyleBackColor = true;
this.btnprevious.Click += new System.EventHandler(this.btnprevious_Click);
//
// btnedit
//
this.btnedit.Location = new System.Drawing.Point(315, 132);
this.btnedit.Name = "btnedit";
this.btnedit.Size = new System.Drawing.Size(125, 27);
this.btnedit.TabIndex = 14;
this.btnedit.Text = "&Edit";
this.btnedit.UseVisualStyleBackColor = true;
this.btnedit.Click += new System.EventHandler(this.btnedit_Click);
//
// btndelete
//
this.btndelete.Location = new System.Drawing.Point(315, 171);
this.btndelete.Name = "btndelete";
this.btndelete.Size = new System.Drawing.Size(125, 27);
this.btndelete.TabIndex = 15;
this.btndelete.Text = "&Delete";
this.btndelete.UseVisualStyleBackColor = true;
this.btndelete.Click += new System.EventHandler(this.btndelete_Click);
//
// btnsave
//
this.btnsave.Location = new System.Drawing.Point(315, 234);
this.btnsave.Name = "btnsave";
this.btnsave.Size = new System.Drawing.Size(125, 27);
this.btnsave.TabIndex = 16;
this.btnsave.Text = "&Save";
this.btnsave.UseVisualStyleBackColor = true;
this.btnsave.Click += new System.EventHandler(this.btnsave_Click);
//
// btncancel
//
this.btncancel.Location = new System.Drawing.Point(315, 267);
this.btncancel.Name = "btncancel";
this.btncancel.Size = new System.Drawing.Size(125, 27);
this.btncancel.TabIndex = 17;
this.btncancel.Text = "&Cancel";
this.btncancel.UseVisualStyleBackColor = true;
this.btncancel.Click += new System.EventHandler(this.btncancel_Click);
//
// lblrecord
//
this.lblrecord.AutoSize = true;
this.lblrecord.Font = new System.Drawing.Font("Old English Text MT", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.lblrecord.Location = new System.Drawing.Point(12, 351);
this.lblrecord.Name = "lblrecord";
this.lblrecord.Size = new System.Drawing.Size(0, 20);
this.lblrecord.TabIndex = 18;
//
// btnadd
//
this.btnadd.Location = new System.Drawing.Point(315, 93);
this.btnadd.Name = "btnadd";
this.btnadd.Size = new System.Drawing.Size(125, 27);
this.btnadd.TabIndex = 19;
this.btnadd.Text = "&Add";
this.btnadd.UseVisualStyleBackColor = true;
this.btnadd.Click += new System.EventHandler(this.btnadd_Click);
//
// DataAccess
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(452, 373);
this.Controls.Add(this.btnadd);
this.Controls.Add(this.lblrecord);
this.Controls.Add(this.btncancel);
this.Controls.Add(this.btnsave);
this.Controls.Add(this.btndelete);
this.Controls.Add(this.btnedit);
this.Controls.Add(this.btnprevious);
this.Controls.Add(this.btnnext);
this.Controls.Add(this.btnloadtable);
this.Controls.Add(this.txtlevel);
this.Controls.Add(this.label6);
this.Controls.Add(this.txtactive);
this.Controls.Add(this.label5);
this.Controls.Add(this.txtpassword);
this.Controls.Add(this.label4);
this.Controls.Add(this.txtusername);
this.Controls.Add(this.label3);
this.Controls.Add(this.txtuserid);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Name = "DataAccess";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Data Access for MySQL";
this.Load += new System.EventHandler(this.DataAccess_Load);
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.TextBox txtuserid;
private System.Windows.Forms.TextBox txtusername;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.TextBox txtpassword;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.TextBox txtactive;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.TextBox txtlevel;
private System.Windows.Forms.Label label6;
private System.Windows.Forms.Button btnloadtable;
private System.Windows.Forms.Button btnnext;
private System.Windows.Forms.Button btnprevious;
private System.Windows.Forms.Button btnedit;
private System.Windows.Forms.Button btndelete;
private System.Windows.Forms.Button btnsave;
private System.Windows.Forms.Button btncancel;
private System.Windows.Forms.Label lblrecord;
private System.Windows.Forms.Button btnadd;
}
}
MySQL Relations
-------------------
-- phpMyAdmin SQL Dump
-- version 2.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 03, 2008 at 06:02 AM
-- Server version: 5.0.27
-- PHP Version: 5.2.1
--
-- Database: `clarify`
--
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`user_id` int(11) NOT NULL auto_increment,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`active` tinyint(1) NOT NULL default '0',
`level` int(11) NOT NULL default '1',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`user_id`, `username`, `password`, `active`, `level`) VALUES
(1, 'deathrow', 'deathrow', 1, 3),
(2, '[email protected]', 'wangai', 1, 2),
(3, '[email protected]', 'mude', 1, 1),
(4, '@.', '1', 1, 1),
(5, '@..', '2', 1, 1);
I have grown fond of C# so any assistance to this issue will be much appreciated.
Regards.
Inserting, Updating & Deleting Records in MySQL using C#
-
Alexey.mdr
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24