0
votes

I want to insert data into a database via sqlite but each time I got the error below:

Connection Established Successfully...

code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error near "hpi_caseid": syntax error at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action 2 paramReader) at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable 1 commandType) at ConsoleApplication1.SqliteDataAccess.SaveCase(CaseModel CasesData) in C:\Users\mansourm\Documents\Visual Studio 2015\Projects\ConsoleApplication1\ConsoleApplication1\SqliteDataAccess.cs:line 29 at ConsoleApplication1.Program.test() in C:\Users\mansourm\Documents\Visual Studio 2015\Projects\ConsoleApplication1\ConsoleApplication1\Program.cs:line 249

namespace ConsoleApplication1
{
    public class SqliteDataAccess
    {
        public static void SaveCase(CaseModel CasesData)
        {
            using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
            {
                cnn.Execute("INSERT INTO CasesData hpi_caseid VALUES @hpi_caseid", CasesData);
            }
        }

        private static string LoadConnectionString(string id = "Default")
        {
            return ConfigurationManager.ConnectionStrings[id].ConnectionString;
        }
    }
}



foreach (var c in ec.Entities)
{

    var newCase = new CaseModel();

    if (c.Attributes.Contains("hpi_caseid")) //Case ID
        newCase.hpi_caseid = Convert.ToInt64(c.Attributes["hpi_caseid"]);


    SqliteDataAccess.SaveCase(newCase);

}


public class CaseModel
{

    public long hpi_caseid;
}

//App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="Default" connectionString="Data Source=C:\Users\mansourm\Documents\Visual Studio 2015\Projects\ConsoleApplication1\ConsoleApplication1\hptest.db; Version=3;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
</configuration>

Data Base screenshot

I have tried with a relative path also but I got the same error.

Thank you in advance!

1

1 Answers

1
votes

It seems you have missed the parenthesis after your table's name and after the Values keyword, i.e around hpi_caseid and @hpi_caseid:

cnn.Execute("INSERT INTO CasesData (hpi_caseid) VALUES (@hpi_caseid)", CasesData);

Also you have declared the parameter but didn't specify it's value, you need to specify a value something like this

yourCommand.Parameters.Add(new SqliteParameter("@hpi_caseid", yourValue));