Click here to Skip to main content
15,911,786 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi
I have a winforms app in C# witch i completed, but now i want to reduce the dependecies such as sqlserver express.
What i´m trying to do is alter my code from sql server to SQLite.
So i google´d it and followed a tutorial, i made the alterations but i´m having some trouble, can anyone take a look at the code and see what the problem is?
I get the error
C#
An unhandled exception of type 'Finisar.SQLite.SQLiteException' occurred in SQLite.NET.dll
Additional information: unsupported file format

In the line sal_con.Open();

This form is suposed to get the info from a database called db.db (witch i already converted from sql to sqlit format) table "temp" and print it to a crystal reports report.

If i can get this form to work the other forms will be easy to migrate.
Thanks in advance

What I have tried:

OLD CODE:
C#
    public partial class report : Form
    {
        public static SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BD.mdf;Integrated Security=True;Connect Timeout=30");
        
public report()
        {
            InitializeComponent();
        }
        public void report_Load(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from temp";
            cmd.ExecuteNonQuery();
            bdDataSet ds = new bdDataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds.temp);
            hytherm myReport = new hytherm();
            myReport.SetDataSource(ds);
            crystalReportViewer1.ReportSource = myReport;
            con.Close();

            con.Open();
            SqlCommand cmd2 = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "truncate table dbo.temp";
            cmd.ExecuteNonQuery();
            con.Close();
        }



NEW CODE:
C#
        private void SetConnection()
        {
            sql_con = new SQLiteConnection
                ("Data Source=db.db;Version=3");
        }

        private void ExecuteQuery(string txtQuery)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery;
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
        public void report_Load(object sender, EventArgs e)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandType = CommandType.Text;
            sql_cmd.CommandText = "select * from temp";



            SQLiteDataAdapter da = new SQLiteDataAdapter(sql_cmd);
            DB.Fill(DS);
            hytherm myReport = new hytherm();
            myReport.SetDataSource(DS);
            crystalReportViewer1.ReportSource = myReport;
            sql_con.Close();

            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandType = CommandType.Text;
            sql_cmd.CommandText = "DELETE FROM table temp";
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
    }
}
Posted
Updated 23-Nov-16 0:35am
v3

1 solution

1. Why are you doing the cmd.ExecuteNonQuery on the select command? It executes the command, but does nothing with the data that is returned.
2. SQLite does not have a TRUNCATE command. Instead of TRUNCATE, do a "DELETE FROM" command without a WHERE clause. SQLite has been optimised to treat that as a truncate.
3. SQLite does not use the "dbo" for the main schema. If you want to include the schema name (which is optional), use "main".
 
Share this answer
 
Comments
Marc-IT 23-Nov-16 6:08am    
Hy thanks for the tips, i updated the code.
I still get the error thoug in line sql_con.Open();
The object reference was not defined as an instance of an object.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900