How to dodge a table already existing in sqlite

Question

Everytime I run my program (f5 key) I get the error table chang9 already exists.. Does this mean that I will have to make a new sqlite table everytime I test my program??

I tried using the good ol' : DROP Table 'chang9'". But then I realized that after the program runs I cannot view the database table in the firefox viewer because it's been dropped.

How Do I avoid the table already exists error , while at the same time being able to view it after I'm done running my code?

 EDITED CODE with if not exists statement:

  // 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 IF NOT EXISTS 'tab1' (Seq text, Field text, Desc text, Len text, Dec text, Typ text, Percnt text, Pop text, Alzero text, MaxLen text );";

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

          sqlite_cmd.CommandText = "INSERT INTO tab1 (Seq, Field, Desc, Len, Dec, Typ, Percnt, Pop, Alzero, MaxLen) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)";
          sqlite_cmd.Parameters.AddWithValue("@p1", 6);  // dummy initial values 
          sqlite_cmd.Parameters.AddWithValue("@p2", 878); 
          sqlite_cmd.Parameters.AddWithValue("@p3", 56);
          sqlite_cmd.Parameters.AddWithValue("@p4", 6);
          sqlite_cmd.Parameters.AddWithValue("@p5", 546);
          sqlite_cmd.Parameters.AddWithValue("@p6", 565);
          sqlite_cmd.Parameters.AddWithValue("@p7", 568);
          sqlite_cmd.Parameters.AddWithValue("@p8", 526);
          sqlite_cmd.Parameters.AddWithValue("@p9", 586);
          sqlite_cmd.Parameters.AddWithValue("@p10", 526);


          for (int i = 0; i < NumListValues; i += 10) // Filling SQlite table rows and columns with values from our list 
          {

              sqlite_cmd.Parameters.AddWithValue("@p1", list[i]);
              sqlite_cmd.Parameters.AddWithValue("@p2", list[i+1]);
              sqlite_cmd.Parameters.AddWithValue("@p3", list[i+2]);
              sqlite_cmd.Parameters.AddWithValue("@p4", list[i+3]);
              sqlite_cmd.Parameters.AddWithValue("@p5", list[i+4]);
              if (i > 490)
                  break; 
              sqlite_cmd.Parameters.AddWithValue("@p6", list[i+5]);
              sqlite_cmd.Parameters.AddWithValue("@p7", list[i+6]);
              sqlite_cmd.Parameters.AddWithValue("@p8", list[i+7]);
              sqlite_cmd.Parameters.AddWithValue("@p9", list[i+8]);
              sqlite_cmd.Parameters.AddWithValue("@p10", list[i+9]);
              sqlite_cmd.ExecuteNonQuery();

          }

       // sqlite_cmd.CommandText = " drop table if exists  'chan9'";


          sqlite_conn.Close();

score 4 · Answer 1 · answered Nov 04 '13 at 14:35

4

You could use "CREATE TABE chang9 ... IF NOT EXISTS" to create it only if it does not exists.

Then if you want to empty it, you may "DELETE FROM chang9" before filling it again.

answered Nov 04 '13 at 14:35

Kek

3,145
2
20
26

Your method works, but when i run it the second time, my table is doubled. I.e before it would have 50 rows, then when I click run again it duplicates itself and has 100 rows. – user2788405 Nov 04 '13 at 17:57
Hey sorry, @Kek I forgot to tag – user2788405 Nov 04 '13 at 18:01
well, that's why I proposed a "Delete from chang9" after the create table if not exists. This should empty it. no? – Kek Nov 05 '13 at 08:57

score 2 · Answer 2 · answered Nov 04 '13 at 14:37

2

You could drop your table before you do your create, instead of at the end of your processing.

That way your table and its data is available after you run you rcode, and you still get a fresh table at every run.

answered Nov 04 '13 at 14:37

oerkelens

5,053
1
22
29

How to dodge a table already existing in sqlite

2 Answers2