c# to sqlite input string not in correct format

Question

I'm trying to transfer c# List values to an SQLite table. (Noob here)

Problem:

I'm getting the following error: "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 values. I would love to input it as a string, can someone help me with that syntax - i just don't kw it.

Here is the list:

         string[] fileLines = File.ReadAllLines(ofd.FileName);

            string[] section1 = fileLines.SkipWhile(line => !line.Contains("Seq#")).TakeWhile(line => !line.Contains("Total Records")).Where(line => Regex.IsMatch(line, @"[\s|\d]{4}\d")).Select(line => line.PadRight(198)).ToArray();   


            int[] pos = new int[10] { 0, 6, 18, 47, 53,57, 61, 68, 82, 97 }; //setlen&pos to read specific colmn vals
            int[] len = new int[10] { 6, 12, 29, 6 , 4, 4, 7, 14, 15, 6 };


            foreach (string line in section1)
            {
                //if (line.StartsWith("0")) break; // reads whole file, could slow it down ..

                for (int j = 0; j < 10; j++) 
                {
                    val[j] = line.Substring(pos[j], len[j]).Trim(); // add each column value in row to array
     //****    RIGHT HERE       list.Add(val[j]); // column values stored in list

                }

            }

Here is the SQLITE part:

  private void button4_Click(object sender, EventArgs e)
    {
        // We use these three SQLite objects:
        SQLiteConnection sqlite_conn;
        SQLiteCommand sqlite_cmd;

        // create a new database connection: // Maybe error here - video was different
        sqlite_conn = new SQLiteConnection(@"Data Source=database.db;Version=3;");

        // open the connection:
        sqlite_conn.Open();

        // create a new SQL command:
        sqlite_cmd = sqlite_conn.CreateCommand();

        // Let the SQLiteCommand object know our SQL-Query:
        sqlite_cmd.CommandText = "CREATE TABLE newtable1 (Seq integer primary key, Field integer , Description integer);";

        // Now lets execute the SQL ;D                                                                                  
        sqlite_cmd.ExecuteNonQuery();

        sqlite_cmd.CommandText = "INSERT INTO table1 (Seq, Field, Description) VALUES (@p1, @p2, @p3)";
        sqlite_cmd.Parameters.AddWithValue("@p1", 1);  // dummy initial values 
        sqlite_cmd.Parameters.AddWithValue("@p2", 2);
        sqlite_cmd.Parameters.AddWithValue("@p3", 3);
        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]); THE ERROR OCCURS HERE
            sqlite_cmd.Parameters["@p3"].Value = Convert.ToInt32(list[i + 2]); // 
            sqlite_cmd.ExecuteNonQuery();
        }
    }

Key notables:, I was using finisar.sqlite but then switched to ADO.Net 2.0 provider for SQLite.

Also, it would prefer if we could get the Seq, Field, and Key columns to contain strings instead of integers. I just don't know the exact syntax to transfer everything after that to string

what is list[i + 1] when the error is thrown? Probably not an int — Jonesopolis
– Jonesopolis, Commented Oct 31, 2013 at 15:06
Please show some sample lines of your input file to be read and converted — Steve
– Steve, Commented Oct 31, 2013 at 15:08
Try setting a breakpoint before the query is executed so you know the values that are trying to be assigned. — JLott
– JLott, Commented Oct 31, 2013 at 15:23
@JLott your answer below seems like its on the right track but can you pls help me with converting to integers Would I do Convert.ToInt32(list[i]); ? But then how would I convert this back to string?? can you pls help with exact syntax - thx — user2788405
– user2788405, Commented Oct 31, 2013 at 17:04

Diego Montania · Accepted Answer · 2022-09-23 08:36:20Z

2

+50

To create the Table to have strings, do this:

using System.Data.SQLite;

string createTable = "CREATE TABLE newtable1 (Seq varchar(20) primary key, Field varchar(20), Description varchar(20))";
    
SQLiteCommand command = new SQLiteCommand(createTable, sqlite_conn);
command.ExecuteNonQuery();

This will create it with string values.

Then you should have no problem doing this:

sqlite_cmd.CommandText = "INSERT INTO table1 (Seq, Field, Description) VALUES (@p1, @p2, @p3)";
sqlite_cmd.Parameters.AddWithValue("@p1", "1");  // dummy initial values 
sqlite_cmd.Parameters.AddWithValue("@p2", "2");
sqlite_cmd.Parameters.AddWithValue("@p3", "3");

for (int i = 0; i < 500; i += 3)
{
    qlite_cmd.Parameters.AddWithValue("@p1", list[i]);
    sqlite_cmd.Parameters.AddWithValue("@p2", list[i+1]);
    sqlite_cmd.Parameters.AddWithValue("@p3", list[i+2]);
    sqlite_cmd.ExecuteNonQuery();
}

edited Sep 23, 2022 at 8:36

Diego Montania

3566 silver badges13 bronze badges

answered Oct 31, 2013 at 15:31

JLott

1,8384 gold badges36 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

c# to sqlite input string not in correct format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related