IF EXISTS when creating table query

Question

I need to write q query where I check if the table exists, and if it does I need to insert value in there. I'm always getting compile errors cause my syntax is wrong. Cab anyone please point out to me the proper code?Appreciate it

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{

    public Form1()
    {
        InitializeComponent();
        createDataBase();
        createDataTable();
    }

    private void createDataBase()
    {
       SQLiteDataAdapter  dataBase = new SQLiteDataAdapter();
    }


    private SQLiteConnection getConnection() 
    { 
        return new SQLiteConnection("Data Source=file1.db"); 
    }


    private void ExecuteQuery(string txtQuery)
    {            
     using (SQLiteConnection sqlcon =  getConnection())
     {
            using (SQLiteCommand sqlcmd = sqlcon.CreateCommand())
            {
                sqlcmd.CommandText = txtQuery;
                sqlcon.Open();
                sqlcmd.ExecuteNonQuery();
            }
        }
    }


    private void createDataTable()
    {
        //ExecuteQuery("DROP TABLE IF EXISTS 'RECORDS'");
        //ExecuteQuery("CREATE TABLE RECORDS ( ID varchar(255))");
        //ExecuteQuery("CREATE TABLE (IF NOT EXISTS) 'RECORDS'");
        //("IF (EXISTS (SELECT ID FROM sqlite_master WHERE NAME = 'RECORDS'))");
    }


    private void button1_Click(object sender, EventArgs e)
    {
          AddValue(textBox1.Text);  
        ////ExecuteQuery("IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> NULL AND TABLE_NAME = 'RECORDS')) BEGIN INSERT INTO RECORDS (ID) VALUES ('" + textBox1.Text + "')");    
    }


    private void AddValue(string value)
    {
      ExecuteQuery("INSERT INTO RECORDS (ID) VALUES ('" + value + "')");
    }
}

}

What have you tried?

madfriend
– madfriend

2012-06-21 21:45:55 +00:00
Commented Jun 21, 2012 at 21:45 — madfriend
– madfriend, Commented Jun 21, 2012 at 21:45
post the compile errors.

Mike Corcoran
– Mike Corcoran

2012-06-21 21:46:13 +00:00
Commented Jun 21, 2012 at 21:46 — Mike Corcoran
– Mike Corcoran, Commented Jun 21, 2012 at 21:46
SQLite error near "RECORDS": syntax error

Den Nemoff
– Den Nemoff

2012-06-21 21:48:24 +00:00
Commented Jun 21, 2012 at 21:48 — Den Nemoff
– Den Nemoff, Commented Jun 21, 2012 at 21:48
i tried all sorts of versions, can't copy them all here

Den Nemoff
– Den Nemoff

2012-06-21 21:50:00 +00:00
Commented Jun 21, 2012 at 21:50 — Den Nemoff
– Den Nemoff, Commented Jun 21, 2012 at 21:50

Steve · Accepted Answer · 2012-06-21 22:08:33Z

1

This is an example

  SQLiteCommand cmd = new SQLiteCommand();
  cmd.Connection = con;
  cmd.Connection.Open();
  cmd.CommandText = "SELECT name FROM sqlite_master WHERE name='MAIN'";
  SQLiteDataReader rdr = cmd.ExecuteReader();
  if (rdr.HasRows)
  {
        cmd.CommandText = "DROP TABLE 'MAIN'";
        cmd.ExecuteNonQuery();
  }

but this is directly from the SQLite site

  SQLiteCommand cmd = new SQLiteCommand();
  cmd.Connection = con;
  cmd.CommandText = "DROP TABLE IF EXIST 'MAIN'";
  cmd.Connection.Open();
  cmd.ExecuteNonQuery();

answered Jun 21, 2012 at 22:08

Steve

217k22 gold badges242 silver badges296 bronze badges

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

4 Comments

Den Nemoff Over a year ago

hi Steve, I inserted exec query because I couldnt understand how to implement your version of it. I now get an error telling that SQLite error no such table: RECORDS, however it would let me compile and run the code, until i insert something in textbox, can you please once again look at my code, i construct a proper version of it?thank you

Steve Over a year ago

It's difficult to say where is your problem from the code above. From the error message I suppose that you call the method datatable() and the table "RECORDS" doesn't exist in you SQLite database. However I can't see where your code call the method datatable(). The method ExecuteQuery() is correct, apart from two closing braces missing.

Den Nemoff Over a year ago

Hi Steve, well that's exactly where I'm having problems. I just can't figure out what is the correct way to construct it. If I choose to run this ExecuteQuery("CREATE TABLE RECORDS ( ID varchar(255))"); >>>>>> it will run only once, and then give me error: RECORDS already exist. If I choose to run this ExecuteQuery("CREATE TABLE (IF NOT EXISTS) 'RECORDS'"); it will give a syntax error, same thing happens with ExecuteQuery("IF (EXISTS (SELECT ID FROM sqlite_master WHERE NAME = 'RECORDS'))");.So, I am completely confused what is the correct way implementing the syntax for this query

Steve Over a year ago

Which kind of provider are you using for SQLite? I suppose the System.Data.SQLite. Am i right?

s_nair · Accepted Answer · 2012-06-21 21:51:19Z

1

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --your insert logic
END

answered Jun 21, 2012 at 21:51

s_nair

8004 silver badges12 bronze badges

4 Comments

s_nair Over a year ago

Sorry I haven't tested on SQLite. I hope it will because the given sample is in ANSI.

Den Nemoff Over a year ago

I tries to follow this pattern, but I always get compile errors.

s_nair Over a year ago

Not sure why it is failing in your case. Following example is fully functional in my case where I have a table called Person. Please post your actual SQL for more support. IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> NULL AND TABLE_NAME = 'Person')) BEGIN select * from Person END

Den Nemoff Over a year ago

Hi s_nair! as far as I understand implementing query this way, would require putting it in the button_click method, since it will create table and insert item subsequently. I've tried to do that, but I failed to accomplish anything.I inserted my best version of it, in the code, I would appreciate If you look it up and correct it. Thank you!

Collectives™ on Stack Overflow

IF EXISTS when creating table query

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related