CODEWITHSUNDEEP
c#sqlitemethodsinsert
user2980509
user2980509

Reputation: 152

What is wrong with my SQLite code? The error i get: SQLite error near"name":syntax error

I have looked at many examples on the web and tried to adjust my program according to that, none of that helped me.. Please can you point out what should I do to fix this problem.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Restaurant_app_1.Business_Layer;

namespace Restaurant_app_1.Data_Access_Layer
{
    class PersonSQLProvider : PersonProviderBase
    {
        private SQLiteConnection _sqlConnection;
        private string _connStr = "Data Source=c:\\DataStores\\PersonDatabase.s3db;Version=3";

Here it tries to insert data into the database, when it reaches the .ExecuteNonQuery() method it jumps to the catch and throw me the error.

        public override int Insert(Manager Manager)
        {
            int rc = 0;

            try
            {
                _sqlConnection = new SQLiteConnection(_connStr);
                _sqlConnection.Open();

                string insertQuery = "INSERT INTO PersonDatabase([_staffFirstName], [_staffLastName], [_staffAge], [_staffID], [_staffeMail], [_staffNumber], [_staffStreet], [_staffProvince], [_staffCity], [_staffPostalCode], [_staffAreaCode], [_staffPhoneNumber], [_staffType]) VALUES(" +
                                     "@First name, @Last name, @Age, @ID, @eMail, @Staff number, @Street, @Province, @City, @Postal Code, @Area code, @Number, @Type)";
                SQLiteCommand sqlCommand = new SQLiteCommand(insertQuery, _sqlConnection);
                SQLiteParameter[] sqlParams = new SQLiteParameter[]
                {
                new SQLiteParameter("@First name",DbType.String),
                new SQLiteParameter("@Last name",DbType.String),
                new SQLiteParameter("@Age",DbType.Int32),
                new SQLiteParameter("@ID",DbType.String),
                new SQLiteParameter("@eMail",DbType.String),
                new SQLiteParameter("@Staff number",DbType.String),
                new SQLiteParameter("@Street",DbType.String),
                new SQLiteParameter("@Province",DbType.String),
                new SQLiteParameter("@City",DbType.String),
                new SQLiteParameter("@Postal Code",DbType.String),
                new SQLiteParameter("@Area code",DbType.String),
                new SQLiteParameter("@Number",DbType.String),
                new SQLiteParameter("@Type",DbType.String)
                };

                sqlParams[0].Value = Manager.FirstName;
                sqlParams[1].Value = Manager.LastName;
                sqlParams[2].Value = Manager.Age;
                sqlParams[3].Value = Manager.ID;
                sqlParams[4].Value = Manager.Email;
                sqlParams[5].Value = Manager.StaffNr;
                sqlParams[6].Value = Manager.AddressStreet;
                sqlParams[7].Value = Manager.AddressProvince;
                sqlParams[8].Value = Manager.AddressCity;
                sqlParams[9].Value = Manager.AddressPostalCode;
                sqlParams[10].Value = Manager.AreaCode;
                sqlParams[11].Value = Manager.Number;
                sqlParams[12].Value = Manager.PhoneType;

                sqlCommand.Parameters.AddRange(sqlParams);
                rc = sqlCommand.ExecuteNonQuery();

                if (rc == 1)
                {
                    rc = 0;
                }
                _sqlConnection.Close();
            }
            catch (SQLiteException ex)
            {
                if (ex.ErrorCode == SQLiteErrorCode.Constraint)
                {
                    rc = -1;
                }
                else
                {
                    throw ex;  
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rc;
        }
    }
}

Upvotes: 1

Views: 283

Answers (2)

juergen d
juergen d

Reputation: 204756

Variable names cannot contains spaces. Change

@First name, @Last name,

to

@Firstname, @Lastname,

for instance.

Upvotes: 2

David Pilkington
David Pilkington

Reputation: 13620

I do not think that you can have spaces in the Parameter names

@First name

@Last name

@Staff number

@Postal Code

@Area code

These will all have the same problem. Remove the space and it should work

@FirstName

@LastName

@StaffNumber

@PostalCode

@AreaCode

Upvotes: 3

Related Questions