I am developing a CRUD in that whenever i am trying to upgrade items it show me "You have an error in your Sql syntax; check the manual that corresponds MariaDB server for right for right syntax to use near 'Name = as', Last Name = 's' WHERE ID = '32' at Line 120 "
I know its a very minute error which is not coming in my notice. Plz help me out.
And thanks in Advance
public partial class Form1 : Form
{
static string conString = "datasource=127.0.0.1;port=3306;username=root;password=;database=testc1;";
MySqlConnection con = new MySqlConnection(conString);
MySqlCommand cmd;
MySqlDataAdapter adapter;
DataTable dt = new DataTable();
public Form1()
{
InitializeComponent();
//DataGridView Properties
dataGridView1.ColumnCount = 4;
dataGridView1.Columns[0].Name = "Id";
dataGridView1.Columns[1].Name = "First Name";
dataGridView1.Columns[2].Name = "Last Name";
dataGridView1.Columns[3].Name = "Address";
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
//selection mode
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.MultiSelect = false;
}
// string connectionString = "datasource=127.0.0.1;port=3306;username=root;password=;database=test;";
//"SELECT * FROM `user1"
private void Add(string firstname, string lastname, string team)
{
//string conString = "datasource=127.0.0.1;port=3306;username=root;password=;database=test;";
string sql = "INSERT INTO user1(`id`, `first_name`, `last_name`, `address`) VALUES (NULL, '" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "')";
cmd = new MySqlCommand(sql, con);
//Open connection and excute insert
try
{
con.Open();
if (cmd.ExecuteNonQuery()>=0)
{
ClearText();
MessageBox.Show("Successfully Inserted");
}
con.Close();
Retrieve();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
con.Close();
}
}
//add to dgview
private void populate(String id, String first_name, String last_name, String address)
{
dataGridView1.Rows.Add(id, first_name, last_name, address);
}
//for retrieving from db
private void Retrieve()
{
dataGridView1.Rows.Clear();
//SQL Statement
string sql = "SELECT * FROM `user1`";
cmd = new MySqlCommand(sql, con);
//Open con, retrieve, fill DGview
try
{
con.Open();
adapter = new MySqlDataAdapter(cmd);
adapter.Fill(dt);
//loop through dt
foreach (DataRow row in dt.Rows)
{
populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString());
}
con.Close();
//clear dt
dt.Rows.Clear();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
con.Close();
}
}
//update db
private void Update(int id, string first_name, string last_name, string add)
{
//sql stmt
string sql = "UPDATE user1 first_name = @firstname, last_name = @lastname, add = @address WHERE id = @id";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@firstname", first_name);
cmd.Parameters.AddWithValue("@lastname", last_name);
cmd.Parameters.AddWithValue("@address", add);
cmd.Parameters.AddWithValue("@id", id);
//Open con, update, retrieve dgview
try
{
con.Open();
adapter = new MySqlDataAdapter(cmd);
adapter.UpdateCommand = con.CreateCommand();
adapter.UpdateCommand.CommandText = sql;
if (adapter.UpdateCommand.ExecuteNonQuery()>= 0)
{
ClearText();
MessageBox.Show("Updated Successfully");
}
con.Close();
Retrieve();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
con.Close();
}
}
//Deleting Db
private void Delete(int id)
{
//sql stmt
string sql = "DELETE FROM user1 WHERE ID=" + id + "";
cmd = new MySqlCommand(sql, con);
//open con, excute delete, close con
try
{
con.Open();
adapter = new MySqlDataAdapter(cmd);
adapter.DeleteCommand = con.CreateCommand();
adapter.DeleteCommand.CommandText = sql;
//promt for confirmation
if (MessageBox.Show("Sure??", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)== DialogResult.OK)
{
if (cmd.ExecuteNonQuery()>=0)
{
ClearText();
MessageBox.Show("Deleted Successfully");
}
}
con.Close();
Retrieve();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//cleat textbox
private void ClearText()
{
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void dataGridView_MouseClick(object sender, MouseEventArgs e)
{
textBox1.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
textBox2.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
textBox3.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
}
private void Addbtn_Click(object sender, EventArgs e)
{
Add(textBox1.Text, textBox2.Text, textBox3.Text);
}
private void Retrievebtn_Click(object sender, EventArgs e)
{
Retrieve();
}
private void Updatebtn_Click(object sender, EventArgs e)
{
String selected = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
int id = Convert.ToInt32(selected);
Update(id, textBox1.Text, textBox2.Text, textBox3.Text);
}
private void Deletebtn_Click(object sender, EventArgs e)
{
String selected = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
int id = Convert.ToInt32(selected);
Delete(id);
}
private void Clearbtn_Click(object sender, EventArgs e)
{
dataGridView1.Rows.Clear();
}
}
