Trying to insert data into an access database from C#, but it fails, and I have no idea why

Question

After browsing this site for some time, I have been able to make a start on a piece of code that adds information from a set of text boxes into a pre-set database.

Here is that code:

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=[[Rather not release private information, but trust me, this part works]]";

var _ID = LblID.Text;
var _FirstName = TxtUsername.Text;
var _LastName = TxtPassword.Text;
var _DOB = TxtFirstName.Text;
var _Number1 = TxtLastName.Text;
var _Number2 = TxtDOB.Text;
var _Email = TxtNumber2.Text;
var _Username = TxtEmail.Text;
var _Password = TxtNumber1.Text;

using (OleDbConnection conn = new OleDbConnection(connString))
{

    OleDbCommand cmd = new OleDbCommand("INSERT into Users ([_ID], [_FirstName], [_LastName], [_DOB], [_Number1], [_Number2], [_Email], [_Username], [_Password]) Values(@ID Number, @First Name, @Last Name, @Date of Birth, @Phone Number 1, @Phone Number 2, @Email, @Username, @Password)");

    cmd.Connection = conn;

    conn.Open();

    if (conn.State == ConnectionState.Open)
    {
        cmd.Parameters.Add("@ID Number", OleDbType.VarChar).Value = _ID;
        cmd.Parameters.Add("@First Name", OleDbType.VarChar).Value = _FirstName;
        cmd.Parameters.Add("@Last Name", OleDbType.VarChar).Value = _LastName;
        cmd.Parameters.Add("@Date of Birth", OleDbType.VarChar).Value = _DOB;
        cmd.Parameters.Add("@Phone Number 1", OleDbType.VarChar).Value = _Number1;
        cmd.Parameters.Add("@Phone Number 2", OleDbType.VarChar).Value = _Number2;
        cmd.Parameters.Add("@Email", OleDbType.VarChar).Value = _Email;
        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.Message);
            conn.Close();
        }
    }
    else
    {
        MessageBox.Show("Connection Failed");
    }
}

Its a bit long, but stay with me here.

This code is very similar to one I have found from an answer to a previous question, of course, renaming variables and such to integrate the code into my application.

However, after doing a step by step breakdown of how the application is processing this, it seems to fail here:

try
{
    cmd.ExecuteNonQuery();
    MessageBox.Show("Data Added");
    conn.Close();
}

More specifically, the cmd.ExecuteNonQuery().

It seems that the try stops at this part, and skips straight to the catch.

My question is, what am I doing wrong? How badly have I fuc- messed this up?

If you need any specifics, just comment and I'll respond as soon as I can.

Thanks in advance.

Edit 1: Forgot to add the error message, my apologies.

"Syntax error (missing operator) in query expression '@ID Number'."

Edit 2: Not sure if this should be an edit, but now I'm getting an entirely new problem.

After reading the comments, I have changed the area names (both in the application and in the database), and now I'm getting an entirely new error message.

"The INSERT INTO statement contains the following unknown field name: '_ID'."

I have a feeling it will complain the same for the rest of them.

Sorry about all of the trouble.

at first glance, I suspect that spaces are not allowed in parameter names. JET cannot parse that. but to have a chance for a useful answer, please add the full exception info to your question. — Cee McSharpface, May 23 '18 at 10:59
Try removing the spaces from your parameter names (i.e. use `@IDNumber` instead of `@ID Number`) in both the `cmd.Parameters.Add...` lines and the query itself — Diado, May 23 '18 at 10:59
"skips straight to the catch" whereupon you will have an exception, full of useful debugging information. Please share the message etc. — spender, May 23 '18 at 11:01
Apologies for that, I'll add the error message to the question. In regards to the naming, should I rename the columns in the database as well? Will just doing @ID_Number work? — Lt Wub, May 23 '18 at 11:02
@LtWub: You should avoid using spaces in column names for sure. You can use underscore, of you can just use PascalCase, whichever you prefer... just not spaces! — musefan, May 23 '18 at 11:06
@LtWub: Just don't confuse "parameters" with field names... they are completely independent and do not have to match. For example, the parameter could be called `@MyFirstParameter` and it would still be fine — musefan, May 23 '18 at 11:10
identifiers in the select clause and value clause of an insert statement are matched with the destination columns by order only, even if they are aliased. some think this is a disease, and it can cause nasty errors that often go unnoticed for a long time. therefore use proper formatting and double-check parameter order in this kind of statements. — Cee McSharpface, May 23 '18 at 11:12
Alright, after the changes made, a new error has appeared. I have included the details in the newest edit. — Lt Wub, May 23 '18 at 11:15
match the schema of the `Users` table with the column names in the insert clause. I bet there are no underscores starting the column names. — Cee McSharpface, May 23 '18 at 11:16
@LtWub: Please don't mix multiple questions in one. Stick with your first error so that can be answered. If the solution to problem 1, then leads to a different error. Then make a second question — musefan, May 23 '18 at 11:17
How do I close the question? The problem has been fixed thanks to all of your help. — Lt Wub, May 23 '18 at 11:27

Sergey Kalinichenko · Accepted Answer · 2018-05-23T11:20:20.077

0

Spaces are not allowed in Access SQL without backquotes around them. Since parameter names are not tied to anything in the database, simply remove the spaces from them:

... VALUES (@IDNumber, @FirstName, @LastName, ...)

cmd.Parameters.Add("@IDNumber", OleDbType.VarChar).Value = _ID;
cmd.Parameters.Add("@FirstName", OleDbType.VarChar).Value = _FirstName;
cmd.Parameters.Add("@LastName", OleDbType.VarChar).Value = _LastName;

"The INSERT INTO statement contains the following unknown field name: '_ID'."

This happens because _ID does not match the name of the column in your database. Unlike parameter names of which you have complete control, column names are part of the database schema, so you need to use the exact name, including any spaces that it may have. Use square brackets for names with spaces:

INSERT INTO Users ([ID Number], [First Name], ... ) ...

edited May 23 '18 at 11:20

answered May 23 '18 at 11:02

Sergey Kalinichenko

714,442
84
1,110
1,523

The thing is, I have the sections in my database as 'ID Number', with the spaces. Should I change all of those too? – Lt Wub May 23 '18 at 11:07
@musefan Spaces are *definitely* wrong here, you don't need exception details for that. If it turns out that OP has a different problem, I could always edit to add more details. Regardless of the edit, however, the space issue will need to be solved. – Sergey Kalinichenko May 23 '18 at 11:07
@LtWub Right, strings in square brackets, i.e. `_FirstName`, `_LastName`, etc. must match what you have in the database, with spaces in them. – Sergey Kalinichenko May 23 '18 at 11:09
@musefan You missed the point I was trying to make: if OP edits to add an exception saying "connection could not be made," then I'd edit to explain what to do to fix the connection issue, but I would keep the "remove spaces" part in place, because it is something the OP must fix, regardless of any other problems his code may have. – Sergey Kalinichenko May 23 '18 at 11:12
If I may interject, the spaces in the words was also an error. Solving that has allowed me to uncover this new error message, so I'd say that this quick comment has helped quite a bit. – Lt Wub May 23 '18 at 11:17

score 0 · Answer 2 · answered May 23 '18 at 11:24

As the exception tells you, you are missing a comma in your VALUES list right after the @ID parameter.

For your second error-message: do you have a column in your database-table that is called _ID ?

Next to that, you do not have to close the connection explicitly, since you're already using a using statement for the db-connection.

Trying to insert data into an access database from C#, but it fails, and I have no idea why

2 Answers2