Deleting from database from datagridview

Question

I have been to quite a few other topic on the site and cant seem to get this thing to work. I am creating an inventory database program to track my firearms. I have a form (AmmunitionForm.cs) that contains a DataGridView (ammoDGV) and a delete button. I can make it delete from the grid but I cannot seem to get the changes to affect the database. This is right on the fringes of my knowledge on the subject to keep that in mind. Here is my code that I currently have (note the delete button method):

    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 System.Data.SqlClient;
    using System.Data.Sql;

    namespace Firearm_Asset_Management_Database
    {
      public partial class AmmunitionForm : Form
    {
        public AmmunitionForm()
       {
            InitializeComponent();
       }

      private void AmmunitionForm_Load(object sender, EventArgs e)
      {
        // TODO: This line of code loads data into the         'fAMDatabaseDataSet.AmmunitionTable' table. You can move, or remove it, as needed.
        this.ammunitionTableTableAdapter.Fill(this.fAMDatabaseDataSet.AmmunitionTable);

    }

    private void addAmmoButton_Click(object sender, EventArgs e)
    {
        NewAmmunition newAmmunition = new NewAmmunition();
        newAmmunition.Show();
    }

    private void refreshButton_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\users\brmcbrid\documents\visual studio 2010\Projects\Firearm Asset Management Database\Firearm Asset Management Database\FAMDatabase.mdf;Integrated Security=True;User Instance=True");
        string query = "select * from AmmunitionTable";
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        ammoDGV.DataSource = dt;
    }

    private void closeButton_Click(object sender, EventArgs e)
    {
        this.Close();
    }

    private void deleteButton_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\users\brmcbrid\documents\visual studio 2010\Projects\Firearm Asset Management Database\Firearm Asset Management Database\FAMDatabase.mdf;Integrated Security=True;User Instance=True");
        SqlCommand cmd = new SqlCommand();
        if (ammoDGV.Rows.Count > 1 && ammoDGV.SelectedRows[0].Index != ammoDGV.Rows.Count - 1)
        {
            cmd.CommandText = "DELETE FROM AmmunitionTable WHERE Caliber=" + ammoDGV.SelectedRows[0].Cells[0].Value.ToString() + "";
            con.Open();
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            con.Close();
            ammoDGV.Rows.RemoveAt(ammoDGV.SelectedRows[0].Index);
            MessageBox.Show("Row has been Deleted");
        }
      }
    }
 }

Any help would be much appreciated!

Answer 1

What exactly is in your first column? It's caliber I supposed. And is it in database save as integer? If not, you should insert single quotas in your SQL command like this:

"DELETE FROM AmmunitionTable WHERE Caliber='" + ammoDGV.SelectedRows[0].Cells[0].Value.ToString() + "'";