CODEWITHSUNDEEP
c#sqlite
Den Nemoff
Den Nemoff

Reputation: 31

SQLite not saving record

I have a textbox called ID and a save button. I would like to simply store in SQLite database file whatever I write in the textbox once I click on save button.

The code is compiling and the program is running without any errors but I dont see any records saved in my database file ( file1.db), which I created under same application.

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
    private SQLiteConnection sqlcon;
    private SQLiteCommand sqlcmd;
    private SQLiteDataAdapter dataBase;
    private DataSet dataSet = new DataSet();
    private DataTable dataTable = new DataTable();


    public Form1()
    {
        InitializeComponent();
    }

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

    private void ExecuteQuery(string txtQuery)
    {            
     using (SQLiteConnection sqlcon = new SQLiteConnection("Data Source=file1.db"))
     {
            using (SQLiteCommand sqlcmd = sqlcon.CreateCommand())
            {
                sqlcon.Open();
                sqlcmd.CommandText = txtQuery;
                sqlcmd.ExecuteNonQuery();
            }
        }
    }

    private void createDataTable()
    {
        dataTable = new DataTable();
        string txtQuery1 = "CREATE TABLE RECORDS ( ID varchar(255))";
        ExecuteQuery(txtQuery1);
    }

    private void button1_Click(object sender, EventArgs e)
    {
        string txtQuery2 = "INSERT INTO RECORDS (ID) VALUES ('" + textBox1.Text + "')";
        ExecuteQuery(txtQuery2);
    }

Upvotes: 2

Views: 2238

Answers (2)

Andrew Savinykh
Andrew Savinykh

Reputation: 26270

You are not executing the query. Try this:

private void button1_Click(object sender, EventArgs e)
{
  string txtQuery = "INSERT INTO RECORDS ID VALUES    ('" + textBox1.Text + "')";  
  ExecuteQuery(txtQuery);
}

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88044

A couple things.

First, I don't see where you are actually passing the query to your ExecuteQuery method. You need to modify your button click method to look like:

protected void button1_Click(object sender, EventArgs e)
{
  string txtQuery = "INSERT INTO RECORDS ID VALUES  ('" + textBox1.Text + "')";
  ExecuteQuery(txtQuery);
}

Second, the query itself looks odd. Usually the structure is something like

INSERT INTO RECORDS(ID) VALUES('myvalue')

Have you tried executing it directly?

Finally, I would highly suggest you rewrite this and get rid of your global connection and command variables. Besides being bad practice what you have can leak memory and generally cause other issues. Anything that implements IDisposable, such as SQLiteConnection and SQLiteCommand should be wrapped in a using clause. For example:

using (SQLiteConnection conn = new SQLiteConnection("conn string")) {
  using (SQLiteCommand cmd = conn.CreateCommand()) {
    sqlcmd.CommandText = txtQuery;
    sqlcmd.ExecuteNonQuery();    
  }
}

This will automatically clean up after each query is run. Also, due to connection pooling, it is just as fast as if you had used global variables to cache those. Finally, in the event of an error you don't have to worry about memory leaks.

Upvotes: 2

Related Questions