Why this Error occur when I want INSERT into SQLite in C# windowForm ?

Question

0.00/5 (No votes)

See more:

I created an app with C# WindowsForm and Sqlite. I can delete, but I can't insert records.

I use one connectionString, and I check my data and sql query for insert.

All of this seems OK, but I get an error such as this:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.SQLite.dll

Additional information: Operation is not valid due to the current state of the object.

and this is my InsertButton Click code :

C#

if (catID == 0 || txtRating.Text ==null || txtback.Text==null||txtgenerel.Text==null||txtimportant.Text==null|| FileName==null)
            {
                MessageBox.Show("لطفا اطلاعات را کامل وارد کنید");
            }
            else{
                CopyImage(FileName);
                string q2 = "INSERT INTO Persons (CatID,FullName,Rating,BackGround,GeneralDetale,ImportantDetale,PicName) VALUES (" + catID + ","+txtFullName.Text+"," + txtRating.Text + "," + txtback.Text + "," + txtgenerel.Text + "," + txtimportant.Text + "," + imgnumber + ")";
            SQLiteCommand cmd1 = new SQLiteCommand(q2, cn);
            //try0
            //{
                cn.Open();
                cmd1.ExecuteNonQuery();
                cn.Close();
                Refresh();
            //}
            //catch
            //{
            //    MessageBox.Show("An Error Was Occured", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            //}
            }

the Error is on this line : cn.open();

Posted 12-Dec-14 11:57am

‫محم د‬‎

Updated 12-Dec-14 13:45pm

Dave Clemmer

v2

Add a Solution

2 solutions

Solution 1

Mostly, you left out the apostrophes to surround the string values.

The best solution to this is to use a parameterized statement.

C#

    CopyImage(FileName);
    string q2 = "INSERT INTO Persons (CatID,FullName,Rating,BackGround,GeneralDetale,ImportantDetale,PicName) VALUES ( @catID , @FullName , @Rating , @back , @txtgenerel , @important , @imgnumber)";
SQLiteCommand cmd1 = new SQLiteCommand(q2, cn);

cmd1.Parameters.AddWithValue ( "@CatID" , CatID ) ;
// Repeat for the other parameters


    cn.Open();
    cmd1.ExecuteNonQuery();
    cn.Close();

Posted 12-Dec-14 13:13pm

PIEBALDconsult

Updated 12-Dec-14 13:19pm

v2

Comments

[no name] 12-Dec-14 19:22pm

Boah why I don't see that. My 5.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2014-12-12T15:10:00

Answer 1 explain about your next issue you will face when you fix connection issue. You may have sharing the same connection and when you try to open the connection it may be already open or some other state which can't do the open operation. All these issue because of the shared connection you better follow the below practice.

C#

string sql = "INSERT INTO Persons (CatID,FullName,Rating,BackGround,GeneralDetale,ImportantDetale,PicName) VALUES ( @catID , @FullName , @Rating , @back , @txtgenerel , @important , @imgnumber)";
//don't reuse same connection over different methods, create new when you need
//and dispose it after use, using block will do it for you
using ( var oConn = new SqliteConnection (connectionString ) )
using( var oCmd = new SqliteCommand ( sql, oConn ))
{
    oConn.Open ();
    //set all parameter values

    //excute commad
    oCmd.ExecuteNonQuery ();
}