Windows Form Textboxs to MS Access Database

[jnbradl]

I am trying to create a "simple" c# windows application for adding data from textboxes to a MS Access database on the local machine. I am new to C# and have not found a good tutorial on how to do this. I have viewed multiple sites and this is my first go at it. I am recieving a "syntax error in the INSERT INTO statement" message during debug.

Help would be appreciated. Sorry about formatting issues if it does not post correctly(first evolution).

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.OleDb;




namespace FirearemsSerial
{


    public partial class Firearms : Form
    {


        public Firearms()
        {
            InitializeComponent();
        }


        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void addnewButton_Click(object sender, EventArgs e)
        {
            string SERIAL = serialTextbox.Text;
            string MANU = manuTextbox.Text;
            string MODEL = modelTextbox.Text;
            string CALIBER = caliberTextbox.Text;
            string VALUE = valueTextbox.Text;
            string DESCRIP = descripTextbox.Text;


            {

                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\AyJack\My Documents\FirearmsS.mdb");
            OleDbCommand cmmd = new OleDbCommand("INSERT INTO Firearm (SERIAL, MANU, MODEL, CALIBER, VALUE, DESCRIP) " + "VALUES (@SERIAL, @MANU, @MODEL, @CALIBER, @VALUE,@DESCRIP)", conn);


                cmmd.Parameters.Add("@SERIAL", OleDbType.VarChar).Value = SERIAL;
                cmmd.Parameters.Add("@MANU", OleDbType.VarChar).Value = MANU;
                cmmd.Parameters.Add("@MODEL", OleDbType.VarChar).Value = MODEL;
                cmmd.Parameters.Add("@CALIBER", OleDbType.VarChar).Value = CALIBER;
                cmmd.Parameters.Add("@VALUE", OleDbType.VarChar).Value = VALUE;
                cmmd.Parameters.Add("@DESCRIP", OleDbType.VarChar).Value = DESCRIP;


                conn.Open();
                cmmd.ExecuteNonQuery();                
                conn.Close();

                MessageBox.Show("Record Added");


            }

        }

        private void clearButton_Click(object sender, EventArgs e)
        {
            serialTextbox.Text = "";
            manuTextbox.Text = "";
            modelTextbox.Text = "";
            caliberTextbox.Text = "";
            valueTextbox.Text = "";
            descripTextbox.Text = "";


        }

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

[hericles]

What error are you getting?

[jnbradl]

I am recieving a "syntax error in the INSERT INTO statement" message during debug

Thank you

[hericles]

Try taking out the concatenation operator and just have the insert statement as one statement. I'm really sure why you have decided to split up anyway.

[jnbradl]

Thankyou for your help! I am doing it this way, because I really do not know what I am doing. I am trying to pick this up on my own while trying to do projects that help with what I am reading as well as have some application in my life.

If you know of the correct way, and willing to share, or point me in a direction that has a good tutorial, I would greatly appreciate it.

Thanks again

[samsylvestertty]

The field name "VALUE" is a keyword in access that produces the error. Use [VALUE] instead of VALUE in the field part
i.e.)

OleDbCommand cmmd = new OleDbCommand("INSERT INTO Firearm (SERIAL, MANU, MODEL, CALIBER, [VALUE], DESCRIP) " + "VALUES (@SERIAL, @MANU, @MODEL, @CALIBER, @VALUE,@DESCRIP)", conn);

FYI
If you get an syntax error while executin a query, try executing the query in the backend ( MS Access in your case ). You'll get an idea of the problem.

[jnbradl]

Thank you for the replies. samsylvesertty's suggestion cleared up the intitial syntax error, but now I am getting an error that states this... "The INSERT INTO statement contains the following unknown field name: 'SERIAL'. Make sure you have typed name correctly, and try the operation again."

Any suggestions?

Could anyone show the me correct way to do this for learning purposes? Searches across the net bring back little help (probably due to my lack of knowlege on the subject).

Thanks again.

[jnbradl]

I was able to fix the issue(s).

I am posting the working code for future searchers.

MS Access Database name = Firearms.mdb
Table Name = Firearm
Field Names = Serial, Manufacturer, Model, Caliber, Value, Description

Thank you everyone for the help.

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.OleDb;




namespace FirearemsSerial
{


    public partial class Firearms : Form
    {
        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jnbradl\My Documents\Firearms.mdb");

        public Firearms()
        {
            InitializeComponent();
        }
        int i;


        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void addnewButton_Click(object sender, EventArgs e)
        {
            string SERIAL = serialTextbox.Text;
            string MANU = manuTextbox.Text;
            string MODEL = modelTextbox.Text;
            string CALIBER = caliberTextbox.Text;
            string VALUE = valueTextbox.Text;
            string DESCRIP = descripTextbox.Text;


            {

            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jnbradl\My Documents\Firearms.mdb");
            OleDbCommand cmmd = new OleDbCommand("INSERT INTO Firearm ([SERIAL], [MANUFACTURER], [MODEL], [CALIBER], [VALUE], [DESCRIPTION]) VALUES (@SERIAL, @MANU, @MODEL, @CALIBER, @VALUE, @DESCRIP)", conn);


                cmmd.Parameters.Add("@SERIAL", OleDbType.VarChar).Value = SERIAL;
                cmmd.Parameters.Add("@MANU", OleDbType.VarChar).Value = MANU;
                cmmd.Parameters.Add("@MODEL", OleDbType.VarChar).Value = MODEL;
                cmmd.Parameters.Add("@CALIBER", OleDbType.VarChar).Value = CALIBER;
                cmmd.Parameters.Add("@VALUE", OleDbType.VarChar).Value = VALUE;
                cmmd.Parameters.Add("@DESCRIP", OleDbType.VarChar).Value = DESCRIP;


               conn.Open();
               cmmd.ExecuteNonQuery();                
               conn.Close();


                MessageBox.Show("Record Added");


            }

        }

        private void clearButton_Click(object sender, EventArgs e)
        {
            serialTextbox.Text = "";
            manuTextbox.Text = "";
            modelTextbox.Text = "";
            caliberTextbox.Text = "";
            valueTextbox.Text = "";
            descripTextbox.Text = "";


        }

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

        private void showallButton_Click(object sender, EventArgs e)
        {
            string cmmd = "select * from Firearm ";
            OleDbCommand vcom = new OleDbCommand(cmmd, conn);
            DataSet vds = new DataSet();
            OleDbDataAdapter vda = new OleDbDataAdapter(vcom);
            vda.Fill(vds, "res");
            dataGridView1.DataSource = vds.Tables["res"];

            vda.Dispose();
            vcom.Dispose();


        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            i = dataGridView1.SelectedCells[0].RowIndex;
            serialTextbox.Text = dataGridView1.Rows[i].Cells[0].Value.ToString();
            manuTextbox.Text = dataGridView1.Rows[i].Cells[1].Value.ToString();
            modelTextbox.Text = dataGridView1.Rows[i].Cells[2].Value.ToString();
            caliberTextbox.Text = dataGridView1.Rows[i].Cells[3].Value.ToString();
            valueTextbox.Text = dataGridView1.Rows[i].Cells[4].Value.ToString();
            descripTextbox.Text = dataGridView1.Rows[i].Cells[5].Value.ToString();


        }
    }
}

[jnbradl]

Any suggestions on how to add an "Update Button" to my form which will update the record populated? As you can see from the above code, i can select data from datagridview and populate the text boxes. When i attempt to use code from some searches, it does not update but shifts my info around in the database. I think the problem may be due to the Id no(primary key in Access) not being used on my form?

Here is the code I have tried with no success.

Thanks again.

string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jnbradl\My Documents\Firearms.mdb";
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            OleDbCommand cmd = new OleDbCommand("UPDATE Firearm SET SERIAL = ?,MANUFACTURER=?, MODEL = ?, CALIBER = ?, [VALUE] =?, DESCRIPTION=? WHERE IDNo = ?", conn);

            cmd.Parameters.Add("@SERIAL", OleDbType.VarChar).Value = serialTextbox.Text;
            cmd.Parameters.Add("@MANUFACTURER", OleDbType.VarChar).Value = manuTextbox.Text;
            cmd.Parameters.Add("@MODEL", OleDbType.VarChar).Value = modelTextbox.Text;
            cmd.Parameters.Add("@CALIBER", OleDbType.VarChar).Value = caliberTextbox.Text;
            cmd.Parameters.Add("@VALUE", OleDbType.VarChar).Value = valueTextbox.Text;
            cmd.Parameters.Add("@DESCRIPTION", OleDbType.VarChar).Value = descripTextbox.Text;

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            serialTextbox.Text = "";
            manuTextbox.Text = "";
            modelTextbox.Text = "";
            caliberTextbox.Text = "";
            valueTextbox.Text = "";
            descripTextbox.Text = "";

            MessageBox.Show("Updated");

[samsylvestertty]

Hi again. Can you specify the problem you faced in updating a record.
especially can you explain

but shifts my info around in the database

Meanwhile, y u gave "?" in place of parameters. Can't you give the parameter name as you did for insert?

Yes a primary (unique) key is essential for a table to be updated. A row must be identified by either a single or a collection of fields. Only then you can specify the DB what record to update. My advice is to create a new field ( that is unique "IDNo as you specified" ). Make sure it is not duplicated.