How To Add Record To Sql Server Compact

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 re

Solution 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:

SqlCeCommandcommand=newSqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES(@NStacksName, @NStacksItem)", con);
command.Parameters.Add(newSqlCeParameter("NStacksName", textBox1.Text));
command.Parameters.Add(newSqlCeParameter("NStacksItem", File));
command.ExecuteNonQuery();

Or with a DB connection class:

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

namespaceDataBase
{
    publicclassDBConnection
    {
        private SqlCeConnection sqlConnection;

        publicDBConnection(string connectionString)
        {
            sqlConnection = new SqlCeConnection(connectionString);
        }

        privateboolCloseConnection(SqlConnection sqlConnection)
        {
            try
            {
                sqlConnection.Close();
                returntrue;
            }
            catch (SqlException e)
            {
                //Handle exceptionreturnfalse;
            }
        }

        privateboolOpenConnection(SqlConnection sqlConnection)
        {
            try
            {
                sqlConnection.Open();
                returntrue;
            }
            catch (SqlCeException e)
            {
                //Handle exceptionreturnfalse;
            }
        }

        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)DBConnectiondb=newDBConnection(connectionString);

// Inside foreach loopDataTableresult= db.NonQuery("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES(@NStacksName, @NStacksItem)",
    newSqlCeParameter("NStacksName", textBox1.Text),
    newSqlCeParameter("NStacksItem", File));

Solution 2:

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

SqlCeCommandcmd=newSqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) , (File))", con);