INSERT error ASP.NET

Question

Hey I get an error saying there is something wrong with my code when inserting into a database, can't quite find it. The error suggests it is something in the INSERT statement, but appears on the line "cmd.ExecuteNonQuery();". I'm using an access database.

Error: Syntax error in INSERT INTO statement.

con.Open();
string mysql; 
mysql = "INSERT INTO [User](FirstName,Surname,Age,HouseNumber,PostCode,Username,Password) 
         VALUES (?,?,?,?,?,?,?)";
OleDbCommand cmd = new OleDbCommand(mysql, con);
cmd.Parameters.AddWithValue("@p1", tbFirstName.Text);
cmd.Parameters.AddWithValue("@p2", tbSurname.Text);
cmd.Parameters.AddWithValue("@p3", int.Parse(tbAge.Text));
cmd.Parameters.AddWithValue("@p4", tbAddress1.Text);
cmd.Parameters.AddWithValue("@p5", tbPostCode.Text);
cmd.Parameters.AddWithValue("@p6", tbUsername.Text);
cmd.Parameters.AddWithValue("@p7", tbPassword.Text);
cmd.ExecuteNonQuery();
con.Close();

Syntax error in INSERT INTO statement. The database fields reflect that of the insert statement. — Chris, May 09 '13 at 16:10

Damith · Accepted Answer · 2013-05-09T16:44:42.953

5

when you add parameters with value you need to convert it to matching type, if age is number then

cmd.Parameters.AddWithValue("@p3", int.Parse(tbAge.Text));

and also User is key word you can try with

"INSERT INTO [User] ([FirstName], [Surname], [Age], [HouseNumber], [PostCode], [Username], [Password]) VALUES (?,?,?,?,?,?,?)";

edited May 09 '13 at 16:44

answered May 09 '13 at 16:14

Damith

62,401
13
102
153

Add this, thank you, but still getting the same error message. – Chris May 09 '13 at 16:17
2

@ChristopherDunne `[PASSWORD]` is also reserved word, better that you can test by adding `[]` for all columns and table names – Damith May 09 '13 at 16:46
Fixed it, thanks, all these complicated answers and it was just that :) – Chris May 09 '13 at 16:54

score -1 · Answer 2 · edited May 09 '13 at 16:46

-1

Have you tried replacing the ? with your parameters?

Correction: I believe you have to add OleDBParameters like so:

con.Open();
string mysql; 
mysql = "INSERT INTO User(FirstName,Surname,Age,HouseNumber,PostCode,Username,Password) 
     VALUES (@p1,@p2,@p3,@p4,@p5,@p6,@p7)";
OleDbCommand cmd = new OleDbCommand(mysql, con);
cmd.Parameters.AddRange(new OleDbParameter[]
{
    new OleDbParameter("@p1", tbFirstName.Text),
    new OleDbParameter("@p2", tbSurname.Text),
           ...
});
cmd.ExecuteNonQuery();
con.Close();

edited May 09 '13 at 16:46

Ry-

218,210
55
464
476

answered May 09 '13 at 16:30

Bearcat9425

1,580
1
11
12

Access databases require "?" – Chris May 09 '13 at 16:31
Tried it just incase, did nothing. Error remains, thanks though. – Chris May 09 '13 at 16:36
There appears to be a bracket missing, where is it closed, will give it a go, cheers. – Chris May 09 '13 at 16:41
Now I'm getting... ArgumentException was unhandled by user code... The OleDbParameter is already contained by another OleDbParameterCollection. – Chris May 09 '13 at 16:50
`The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used` – Damith May 09 '13 at 16:50
It's ok I changed it back to "?" and this new fix seems to have created more problems? – Chris May 09 '13 at 16:52
What does your connection string look like, are you using Jet or ACE. the above suggestion was taken from a previously accepted answer in similar topic. See http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database – Bearcat9425 May 09 '13 at 16:56

INSERT error ASP.NET

2 Answers2