How to add record to SQL Server Compact

Question

I have looked at many videos and answers, on the web I now have a working example.

I wish to drag and drop files onto a panel and have the name I provide plus a path to the file recorded into the database. Some examples show three lines for the SQL part I have one.

SqlCeCommand cmd = new SqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) + (File))", con); 

Others have different lines.

When the program is executed normally without the SQL query then the line

MessageBox.Show("Attempted write DB " + textBox1.Text +" "+ File);  

will display the given information that should be entered into the Database.

I am now faced with database error messages which I don’t know what exactly is wrong.

“There was an error parsing the query. [Token line number =1,Token line offset =72,Token in error = File]".

My Code is outlined below.

namespace NStacks1
{
    public partial class Form1 : Form
    {
        SqlCeConnection con = new SqlCeConnection("Data Source=C:\\Users\\userename\\Documents\\Visual Studio 2010\\Projects\\NStacks1\\NStacks1\\NStacks.sdf");
        SqlCeCommand cmd;
        public Form1()
        {
            InitializeComponent();
        }

        private void panel1_DragDrop(object sender, DragEventArgs e)
        {
            if (textBox1.Text.Equals(""))
            {
                MessageBox.Show("Blank name please enter a name and try again.");
            }
            else
            {
                string[] Files = (string[])e.Data.GetData(DataFormats.FileDrop, false);
                foreach (string File in Files)
                {
                    try
                    {
                        con.Open();
                        SqlCeCommand cmd = new SqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) + (File))", con);
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Attempted write DB " + textBox1.Text +" "+ File);
                        con.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                        con.Close();
                    }
                }
            }
        }

        private void panel1_DragEnter(object sender, DragEventArgs e)
        {
            e.Effect = DragDropEffects.All;
        }
    }
}

Answer 1

INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) + (File))

You're telling the DB to INSERT INTO the table NStacks the VALUES "(textBox1.Text) + (File)" on the column NStacksName ONLY, as you're missing a comma , to separate the values. You aren't inserting a string, you're inserting non-existent keywords, creating the SQL parse error. This is how the SQL is interpreted (note the red wiggle line):

Strings in SQL must be enclosed with single-quotes ' like ... VALUES ('value1', 'value2'), looking like this:

Also, you need to sanitize your SQL with parameters! (answer is in PHP), with C# it will look like:

SqlCeCommand command = new SqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES(@NStacksName, @NStacksItem)", con);
command.Parameters.Add(new SqlCeParameter("NStacksName", textBox1.Text));
command.Parameters.Add(new SqlCeParameter("NStacksItem", File));
command.ExecuteNonQuery();

Or with a DB connection class:

using System;
using System.Data;
using System.Data.SqlServerCe;

namespace DataBase
{
    public class DBConnection
    {
        private SqlCeConnection sqlConnection;

        public DBConnection(string connectionString)
        {
            sqlConnection = new SqlCeConnection(connectionString);
        }

        private bool CloseConnection(SqlConnection sqlConnection)
        {
            try
            {
                sqlConnection.Close();
                return true;
            }
            catch (SqlException e)
            {
                //Handle exception
                return false;
            }
        }

        private bool OpenConnection(SqlConnection sqlConnection)
        {
            try
            {
                sqlConnection.Open();
                return true;
            }
            catch (SqlCeException e)
            {
                //Handle exception
                return false;
            }
        }

        public DataTable NonQuery(string sqlString, params SqlCeParameter[] sqlParameters)
        {
            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("Affected Rows", typeof(int)));
            if (this.OpenConnection(this.sqlConnection))
            {
                try
                {
                    SqlCeCommand sqlCommand = new SqlCeCommand(sqlString, this.sqlConnection);
                    sqlCommand.Parameters.AddRange(sqlParameters);

                    table.Rows.Add(sqlCommand.ExecuteNonQuery());
                }
                catch (SqlCeException e)
                {
                    table.Rows.Add(0);
                    //Handle exception
                }
                finally
                {
                    this.CloseConnection(this.sqlConnection);
                }
            }
            return table;
        }

        public DataTable Query(string sqlString, params SqlCeParameter[] sqlParameters)
        {
            DataTable table = new DataTable();
            if (this.OpenConnection(this.sqlConnection))
            {
                try
                {
                    SqlCeCommand sqlCommand = new SqlCeCommand(sqlString, this.sqlConnection);
                    sqlCommand.Parameters.AddRange(sqlParameters);

                    SqlCeDataAdapter sqlDataAdapter = new SqlCeDataAdapter(sqlCommand);
                    sqlDataAdapter.Fill(table);
                }
                catch (SqlCeException e)
                {
                    //Handle exception
                }
                finally
                {
                    this.CloseConnection(this.sqlConnection);
                }
            }
            return table;
        }
    }
}

And calling it like:

// Outside foreach loop (better make it a class field and initialize this
// inside the class constructor)
DBConnection db = new DBConnection(connectionString);

// Inside foreach loop
DataTable result = db.NonQuery("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES(@NStacksName, @NStacksItem)",
    new SqlCeParameter("NStacksName", textBox1.Text),
    new SqlCeParameter("NStacksItem", File));

Answer 2

I think you pass one parameter where it required 2 parameter. you need to write like this

SqlCeCommand cmd = new SqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) , (File))", con);