How to execute INSERT INTO in SQLite with ODBC in c#

Question

The function of application is to select some values from one database (PSQL) and insert it into another database (SQLite). But code below does not work, it stops at executing line and shows no error, but last forever (also if I use SELECT TOP 1 ...).

//... odbc conection to DSN, this works fine
odbc.dbsqlite.Open();
odbc.dbpsql.Open();

//sql command
OdbcCommand comsqlite = odbc.dbsqlite.CreateCommand();
OdbcCommand compsql = odbc.dbpsql.CreateCommand();

//SQL for select ... this works
compsql.CommandText = "SELECT DISTINCT ..." 

compsql.Parameters.AddWithValue("@sifra", "VP");
...

// from here is problem
try
{
    OdbcDataReader dbReader = compsql.ExecuteReader();
    OdbcTransaction transaction = odbc.dbsqlite.BeginTransaction();

    var ordinal = new
    {
       cenik = dbReader.GetOrdinal("sifra"),
       ident = dbReader.GetOrdinal("ident"),
       klasi = dbReader.GetOrdinal("klasi"),
       cena  = dbReader.GetOrdinal("cena"),
       eankoda = dbReader.GetOrdinal("eankoda"),
    };

    int count = 0;

    while (dbReader.Read())
    {
       //here single variable gets results 
       var cena = Convert.ToDouble(dbReader.GetDouble(ordinal.cena));
       var ident = Convert.ToString(dbReader.GetString(ordinal.ident));
       var cenik = Convert.ToString(dbReader.GetString(ordinal.cenik));
       var klasi = Convert.ToString(dbReader.GetString(ordinal.klasi));
       var eanko = Convert.ToString(dbReader.GetString(ordinal.eankoda));

       comsqlite.CommandText = "INSERT INTO ARTIKLI (KLASI, CENA, BARKODA, CENIK, IDENT) VALUES (?,?,?,?,?);";

       comsqlite.Parameters.AddWithValue("@KLASI", klasi);
       comsqlite.Parameters.AddWithValue("@CENA", cena);
       comsqlite.Parameters.AddWithValue("@BARKODA", eanko);
       comsqlite.Parameters.AddWithValue("@CENIK", cenik);
       comsqlite.Parameters.AddWithValue("@IDENT", ident);

        if (count % 1000 == 0)
        {
           transaction.Commit();
           transaction.Dispose();
           **comsqlite.ExecuteNonQuery(); //here it stops and give no results**
           transaction = odbc.dbsqlite.BeginTransaction();

         }
         count++;

         }

         comsqlite.Dispose();
         odbc.dbsqlite.Close();

         transaction.Commit();
         transaction.Dispose();

         dbReader.Close();
         compsql.Dispose();
         odbc.dbpsql.Close();

        }
        catch (Exception e)
        {
            Console.WriteLine("Error: "+ e);
            throw;
        }

Well at least try to move the ExecuteNonQuery before the dispose of the transaction. And the ExecuteNonQuery should be executed for each insert not every 1000 reads. — Steve, Jul 05 '16 at 09:54
Also the Command should be associated to the transaction, I don't see anywhere the setting of the [DbCommand.Transaction](https://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.transaction(v=vs.110).aspx) property — Steve, Jul 05 '16 at 09:56

score 1 · Answer 1 · answered Jul 05 '16 at 09:51

1

I am not sure what your CommandText looks like at this point, but you should try to set some single quotation marks around the values being strings/characters in your database.

comsqlite.CommandText = "INSERT INTO ARTIKLI (KLASI, CENA, BARKODA, CENIK, IDENT) VALUES ('?','?','?','?','?');";

answered Jul 05 '16 at 09:51

R. Pülsinger

165
11

1

Those are parameters, they don't need quotation marks – Steve Jul 05 '16 at 09:51
@Steve correct, those are parameters, because I use paremetrization for SQL. – DaniKR Jul 05 '16 at 09:57
@R.Pülsinger it is the same "error", stays in same line, If I use INSERT as string with no parametrization, and VALUES with quotation marks... – DaniKR Jul 05 '16 at 10:00
@DaniKr You declare it as `comsqllite` but you use it as `comsqlite`. Maybe its just the code, otherwhise you should see it in your IDE – R. Pülsinger Jul 05 '16 at 10:10
@R.Pülsinger oh thanks, this is my typing mistake to SO :) but it is not a case – DaniKR Jul 05 '16 at 10:17
Ok, I figure out, problem was with my database, I am using DB browser for SQLite and I did not write changes into, so it has lock my database. – DaniKR Jul 11 '16 at 08:24

How to execute INSERT INTO in SQLite with ODBC in c#

1 Answers1