Adding to a Database - C# with Access

Question

Below is the code I'm using to connect to an Access Database. I'm trying to insert the data (which is a form the user fills in). But always the exception is caught and no data gets inserted.

I've double checked the variable names for spelling errors,they are all correct.

private void addConfirm_Click(object sender, EventArgs e)
    {
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\hospitalApplication\HealthCare.accdb;Persist Security Info=False;";

        String name = typedName.Text;
        String addr = typedAddr.Text;
        String DOB = typedDOB.Text;
        String phone = typedPhone.Text;
        String emergency = typedEmergency.Text;
        String dateOfReg = typedDateReg.Text;
        String patientstatus = typedStatus.Text;
        String bedNo = typedBed.Text;
        String blood = typedBlood.Text;
        String visit = typedVisit.Text;
        String diagnosis = typedDiagnosis.Text;
        String doctorID = typedDoctor.Text;


        OleDbCommand cmd = new OleDbCommand("INSERT into Patients (Full Name, Address,Date of Birth,Phone No,Emergency Contact,Date of Registration,Patient Status,bedID,Blood Type,Date of Visit,Diagnosis,doctorID) Values(@name, @addr,@DOB,@phone,@emergency,@dateOfReg,@patientstatus,@bedNo,@blood,@visit,@diagnosis,@doctorID)", conn);
        cmd.Connection = conn;

        conn.Open();

        if (conn.State == ConnectionState.Open)
        {
            cmd.Parameters.Add("@name", OleDbType.VarChar).Value = name;
            cmd.Parameters.Add("@addr", OleDbType.VarChar).Value = addr;
            cmd.Parameters.Add("@DOB", OleDbType.VarChar).Value = DOB;
            cmd.Parameters.Add("@phone", OleDbType.VarChar).Value = phone;
            cmd.Parameters.Add("@emergency", OleDbType.VarChar).Value = emergency;
            cmd.Parameters.Add("@dateOfReg", OleDbType.VarChar).Value = dateOfReg;
            cmd.Parameters.Add("@patientstatus", OleDbType.VarChar).Value = patientstatus;
            cmd.Parameters.Add("@bedNo", OleDbType.Integer).Value = bedNo;
            cmd.Parameters.Add("@blood", OleDbType.VarChar).Value = blood;
            cmd.Parameters.Add("@visit", OleDbType.VarChar).Value = visit;
            cmd.Parameters.Add("@diagnosis", OleDbType.VarChar).Value = diagnosis;
            cmd.Parameters.Add("@doctorID", OleDbType.Integer).Value = doctorID;

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

What's the exception? The first step in correcting an error is reading the error message. — David
– David, Commented Mar 17, 2015 at 18:12

David · Accepted Answer · 2015-03-17 18:15:30Z

3

Your SQL in invalid. The spaces are confusing the query parser.

The best approach is to not use spaces in your table/column names. But if you must, then you'll need to enclose them in brackets to tell the query parser that both words identify one item. Something like this:

INSERT into Patients ([Full Name], ...

Do this for any table/column name which includes either spaces or reserved words. (You can do this for all of the table/column names if you'd like.)

answered Mar 17, 2015 at 18:15

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding to a Database - C# with Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related