OleDbException was unhandled C# to access database

Question

I am using an Access database with a table called Contact. The table contains these fields ContactID, FirstName, LastName, SpouseName, Phone, Email, WeddingDate, StreetAddress, Suburb, City, PostCode, Country and Comments. ContactID is an AutoNumber and the PK and WeddingDate is date/time. All the rest are text fields.

I have a simple form with 4 text boxes on it for the firstName, LastName, Phone, and Email with a save button. Here is my code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace project1
{
public partial class DataSource : Form
{
    OleDbConnection connection=new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Wills\Projects\Windows\project1\project1\project1.accdb");
    public DataSource()
    {
        InitializeComponent();
    }

    private void DataSource_Load(object sender, EventArgs e)
    {
        connection.Open();
    }
    private void btnSave_Click(object sender, EventArgs e)
    {
        string vsql = string.Format("INSERT INTO Contact values(@FirstName, @LastName, @Phone, @Email)", txtFirstName.Text, txtLastName.Text, txtPhone.Text, txtEmail.Text);
        OleDbCommand vcom = new OleDbCommand(vsql, connection);
        vcom.ExecuteNonQuery();
        MessageBox.Show("Data store successfully");
        vcom.Dispose();
    }

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

I am getting the error on the line vcom.ExecuteNonQuery(); saying "OleDbException was unhandled No value given for one or more required parameters."

I have tried to change the insert statement but with no luck and looking at other examples on the forum I cannot get any of the changes to work with my solution. Any ideas or help would be much appreciated.

Jieqin Jieqin · Accepted Answer · 2013-05-31T02:57:40

I've tried this line from your code.

string vsql = string.Format(
        "INSERT INTO Contact values(@FirstName, @LastName, @Phone, @Email)",
        txtFirstName.Text, txtLastName.Text, txtPhone.Text, txtEmail.Text);

The result is INSERT INTO Contact values(@FirstName, @LastName, @Phone, @Email).

I think you're looking for this.

string vsql = string.Format(
        "INSERT INTO Contact values('{0}', '{1}', '{2}', '{3}')", 
        txtFirstName.Text, txtLastName.Text, txtPhone.Text, txtEmail.Text);

Also, if you are trying to insert into specific columns, you'll need to use a different insert code. Try this.

string vsql = string.Format(
        "INSERT INTO Contact (FirstName, LastName, Phone, Email) " + 
        "values ('{0}', '{1}', '{2}', '{3}')", 
        txtFirstName.Text, txtLastName.Text, txtPhone.Text, txtEmail.Text);

OleDbException was unhandled C# to access database

1 Answers