problems with command.ExecuteNonQuery(); when working with databases Please help, for assignment

Question

Here is my code

namespace SDD_Single_Project___Michael
{
    public partial class NewUser : Form
    {
        private OleDbConnection connection = new OleDbConnection();

        public NewUser()
        {
            InitializeComponent();
            connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\schoolwork\Year 11\SDD\3 SINGLE TASK\SDD Single Project - Michael \SDD Single Project - Michael \bin\Persondata.accdb;
Persist Security Info=False;";
    }

    private void btnBack_Click(object sender, EventArgs e)
    {
        this.Hide(); //hides this page
        MainScreen frm = new MainScreen(); //finds the next screen (the main game)
        frm.Show(); //shows it
    }

    private void btnSubmit_Click(object sender, EventArgs e)
    {
        try {
               connection.Open(); // opens the connection
               OleDbCommand command = new OleDbCommand();
               command.Connection = connection;
               command.CommandText = "insert into Persondata  where  ( FirstName,LastName,Address,Suburb,Email,Mobile) values ( '" + txtFirst.Text + "' , '" + txtLast.Text + "' , '" + txtAddress.Text + "' , '" + txtSuburb.Text + "' , '" + txtEmail.Text + "' , '" + txtMobile.Text + "' ) ";
               // finds where its going to, finds the columns it is going to fill, finds the text boxes that is going to fill them

               command.ExecuteNonQuery();  // error occurs here!!!
               MessageBox.Show("Data Saved");
               connection.Close(); // closes the connection
           }
           catch (Exception ex)
           { 
               MessageBox.Show("Error   " + ex); 
           } //if there is a error message box will appear informing it 
        }
    }
}

The error is occurring at the command.ExecuteNonQuery(); and nothing I can do will fix it, the error happens once I fill in all the information into the textboxes and press the submit button.

The error says it is a syntax error in INSERT INTO statement at

System.Data.Ole.DbCommand.ExecuteNonQuery(); 

Please help! It's for an assignment! I've been trying to solve it forever. All help is appreciated.

Answer 1

There is no WHERE part in INSERT syntax. You should delete it from your sql.

INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table 
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES 
        }
    }
}

OleDbCommand doesn't support named parameters.

From OleDbCommand.Parameters property

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. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

If the parameters in the collection do not match the requirements of the query to be executed, an error may result.

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

command.CommandText = @"insert into Persondata(FirstName,LastName,Address,Suburb,Email,Mobile) 
                       values (?, ?, ?, ?, ?, ?)";

Then add your parameter values with SqlParameterCollection.Add method

Also use using statement to dispose your database connections.

using(OleDbConnection connection = new OleDbConnection(connString))
using(OleDbCommand command = connection.CreateCommand())
{
      command.CommandText = @"insert into Persondata(FirstName,LastName,Address,Suburb,Email,Mobile) 
                              values (?, ?, ?, ?, ?, ?)";
      //Add your parameter values with right order.
      connection.Open();
      command.ExecuteNonQuery();
}

Answer 2

You shouldn't use WHERE in INSERT statement

 command.CommandText = "insert into Persondata  ( FirstName,LastName,Address,Suburb,Email,Mobile) values ( @param1,@param2,@param3,@param4,@param5,@param6) ";
 command.Parameters.AddWithValue("@param1", txtFirst.Text);
 command.Parameters.AddWithValue("@param2",txtLast.Text);
....
...
command.Parameters.AddWithValue("@param6",txtMobile.Text);

Answer 3

The SQL statement in your application is incorrect. The insert SQL clause does not allow use of where. This is an excerpt from MSDN, with the structure of INSERT INTO clause.

[ WITH <common_table_expression> [ ,...n ] ] INSERT  {
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table 
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES 
        }
    } } [;]

The correct way to use a SQL statement is(also taken from MSDN):

INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');

A where could be used though, if you were using a SELECT INTO statement.

So, to solve your problem, you will need to remove the where clause from your SQL statement.