Convert sqlite int table to string and then input list

Question

I want to transfer items from my LIST in c# to a TABLE in SQLite. I had no clue how to do this until someone suggested that I use parameters:

This was my initial attempt: (Of course it is wrong)

    SQLiteConnection sqlite_conn;
    SQLiteCommand sqlite_cmd;

    sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");

    sqlite_conn.Open();

    sqlite_cmd = sqlite_conn.CreateCommand();

    sqlite_cmd.CommandText = "CREATE TABLE table1 (Seq integer primary key, Field integer , Description integer );";

    sqlite_cmd.ExecuteNonQuery();

    for (int i = 0; i < 500; i+=3)
    {
        // Lets insert something into our new table:
        sqlite_cmd.CommandText = "INSERT INTO table1 (Seq, Field, Description) VALUES (list[i], list[i+1], list[i+2]);"; THIS IS WHERE PROBLEM OCCURS!

        sqlite_cmd.ExecuteNonQuery(); 
    }

    sqlite_conn.Close();
}

This is what a user suggested I should do ( add parameters and stuff) :

 sqlite_cmd.CommandText = "INSERT INTO table1 (Seq, Field, Description) VALUES (@p1,       @p2, @p3)";
  sqlite_cmd.Parameters.AddWithValue("@p1", 0);  // dummy initial values 
  sqlite_cmd.Parameters.AddWithValue("@p2", 0);
  sqlite_cmd.Parameters.AddWithValue("@p3", 0);
  for (int i = 0; i < 500; i+=3)
{
   sqlite_cmd.Parameters["@p1"].Value = Convert.ToInt32(list[i]);
  ** sqlite_cmd.Parameters["@p2"].Value = Convert.ToInt32(list[i+1]); ERROR OCCURS HERE
   sqlite_cmd.Parameters["@p3"].Value = Convert.ToInt32(list[i+2]); 
   sqlite_cmd.ExecuteNonQuery(); 
 }

The Problem :

Inside the for loop, as indicated by the 2 stars, I am getting an error saying: "Input string was not in a correct format."

I tried to look at the solutions to previous posts which had the same error as this but not much luck so far.

I think my error is that the list initially has string values but later I try inputting into it integer sqlite columns (see create table statement in original code)

Since the original list values are all strings, how would I simply make a table insqlite which stores STRING values from my list and avoid the error?

Can someone pls help me with the exact syntax?

Pretty sure your problem is just that the index method cant do the add of i+1. If you added those together outside the brackets you'd probably have it. — crthompson
– crthompson, Commented Oct 31, 2013 at 21:08
also, you might have wanted to do ++i instead of i+1. You are incrementing your for loop by 3 each time, and could be simply incrementing i inside the loop. — crthompson
– crthompson, Commented Oct 31, 2013 at 21:12

CL. · Accepted Answer · 2013-10-31 22:56:12Z

2

To declare a database column as a text column instead of an integer column, replace integer with text.

To set a string parameter, do not convert it into an integer; just drop the Convert.ToInt32 call and use the value directly.

answered Oct 31, 2013 at 22:56

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2788405 Over a year ago

Hey, I tried your method, still no luck - I'm still getting the error "Input string was not in a correct format." Anything on why this is happening ? @CL.

Collectives™ on Stack Overflow

Convert sqlite int table to string and then input list

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related