CODEWITHSUNDEEP
c#sqlite
user2788405
user2788405

Reputation: 325

c# to sqlite input string not in correct format

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

Upvotes: 2

Views: 2690

Answers (1)

JLott
JLott

Reputation: 1828

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();
}

Upvotes: 2

Related Questions