C# Inserting Data from a form into an access Database

Question

I started learning about C# and have become stuck with inserting information from textboxes into an Access database when a click button is used.

The problem I get is during the adding process. The code executes the Try... Catch part and then returns an error saying "Microsoft Access Database Engine" and doesn't give any clues.

Here is the code:

namespace WindowsFormsApplication1
{
    public partial class FormNewUser : Form
    {
        public FormNewUser()
        {
            InitializeComponent();
        }

        private void BTNSave_Click(object sender, EventArgs e)
        {
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\kenny\Documents\Visual Studio 2010\Projects\Copy Cegees\Cegees\Cegees\Login.accdb";

            String Username = TEXTNewUser.Text;
            String Password = TEXTNewPass.Text;

            OleDbCommand cmd = new OleDbCommand("INSERT into Login (Username, Password) Values(@Username, @Password)");
            cmd.Connection = conn;

            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                cmd.Parameters.Add("@Username", OleDbType.VarChar).Value = Username;
                cmd.Parameters.Add("@Password", OleDbType.VarChar).Value = Password;

                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Data Added");
                    conn.Close();
                }
                catch (OleDbException ex)
                {
                    MessageBox.Show(ex.Source);
                    conn.Close();
                }
            }
            else
            {
                MessageBox.Show("Connection Failed");
            }
        }
    }
}

Try MessageBox.Show(ex.Message); and you will probably get a more understandable message — Johan Nordli, Oct 09 '13 at 15:00
You need some [`using` statements](http://msdn.microsoft.com/en-us/library/yh598w02.aspx) for the `OleDbConnection ` and `OleDbCommand`. Are you salting and hashing the password? — Dustin Kingen, Oct 09 '13 at 15:01
using these mate using System; using System.Collections.Generic; using System.Data.OleDb; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; — Kentao, Oct 09 '13 at 15:29

score 10 · Accepted Answer · answered Oct 09 '13 at 15:36

10

Password is a reserved word. Bracket that field name to avoid confusing the db engine.

INSERT into Login (Username, [Password])

answered Oct 09 '13 at 15:36

HansUp

95,961
11
77
135

score 4 · Answer 2 · answered Mar 03 '14 at 07:22

This answer will help in case, If you are working with Data Bases then mostly take the help of try-catch block statement, which will help and guide you with your code. Here i am showing you that how to insert some values in Data Base with a Button Click Event.

 private void button2_Click(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
    @"Data source= C:\Users\pir fahim shah\Documents\TravelAgency.accdb";

     try
       {
           conn.Open();
           String ticketno=textBox1.Text.ToString();                 
           String Purchaseprice=textBox2.Text.ToString();
           String sellprice=textBox3.Text.ToString();
           String my_querry = "INSERT INTO Table1(TicketNo,Sellprice,Purchaseprice)VALUES('"+ticketno+"','"+sellprice+"','"+Purchaseprice+"')";

            OleDbCommand cmd = new OleDbCommand(my_querry, conn);
            cmd.ExecuteNonQuery();

            MessageBox.Show("Data saved successfuly...!");
          }
         catch (Exception ex)
         {
             MessageBox.Show("Failed due to"+ex.Message);
         }
         finally
         {
             conn.Close();
         }

score 1 · Answer 3 · answered Oct 09 '13 at 15:03

and doesnt give any clues

Yes it does, unfortunately your code is ignoring all of those clues. Take a look at your exception handler:

catch (OleDbException  ex)
{
    MessageBox.Show(ex.Source);
    conn.Close();
}

All you're examining is the source of the exception. Which, in this case, is "Microsoft Access Database Engine". You're not examining the error message itself, or the stack trace, or any inner exception, or anything useful about the exception.

Don't ignore the exception, it contains information about what went wrong and why.

There are various logging tools out there (NLog, log4net, etc.) which can help you log useful information about an exception. Failing that, you should at least capture the exception message, stack trace, and any inner exception(s). Currently you're ignoring the error, which is why you're not able to solve the error.

In your debugger, place a breakpoint inside the catch block and examine the details of the exception. You'll find it contains a lot of information.

He could do `MessageBox.Show(ex.ToString());` which will pretty print the error. — Dustin Kingen, Oct 09 '13 at 15:15
Thanks for this tip. heres a picture of the error message http://img.photobucket.com/albums/v232/tdgjfed/error_zpsd24b6ef8.png It says its a syntax error in the insert into statement — Kentao, Oct 09 '13 at 15:28

score 0 · Answer 4 · edited Jun 20 '18 at 08:16

0

 private void Add_Click(object sender, EventArgs e) {
 OleDbConnection con = new OleDbConnection(@ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\HP\Desktop\DS Project.mdb");
 OleDbCommand cmd = con.CreateCommand();
 con.Open();
 cmd.CommandText = "Insert into DSPro (Playlist) values('" + textBox1.Text + "')";
 cmd.ExecuteNonQuery();
 MessageBox.Show("Record Submitted", "Congrats");
 con.Close();
}

edited Jun 20 '18 at 08:16

4b0

21,981
30
95
142

answered Jun 20 '18 at 08:12

Irtaza Ali

19
1

3

It's good practice on Stack Overflow to add an explanation as to why your solution should work. – 4b0 Jun 20 '18 at 08:16
3

@Irtaza try providing an explanation for your code. The point is for the person asking the question to learn. – Jacques Jun 20 '18 at 08:25
While this code may answer the question, it is better to explain how to solve the problem and provide the code as an example or reference. Code-only answers can be confusing and lack context. – Robert Columbia Jun 20 '18 at 12:52

score 0 · Answer 5 · answered Dec 13 '20 at 15:42

My Code to insert data is not working. It showing no error but data is not showing in my database.

public partial class Form1 : Form { OleDbConnection connection = new OleDbConnection(check.Properties.Settings.Default.KitchenConnectionString); public Form1() { InitializeComponent(); }

    private void Form1_Load(object sender, EventArgs e)
    {
        
    }

    private void btn_add_Click(object sender, EventArgs e)
    {
        OleDbDataAdapter items = new OleDbDataAdapter();
        connection.Open();
        OleDbCommand command = new OleDbCommand("insert into Sets(SetId, SetName,  SetPassword) values('"+txt_id.Text+ "','" + txt_setname.Text + "','" + txt_password.Text + "');", connection);
        command.CommandType = CommandType.Text;
        command.ExecuteReader();
        connection.Close();
        MessageBox.Show("Insertd!");
    }
}

C# Inserting Data from a form into an access Database

5 Answers5

Linked