Click here to Skip to main content
15,567,466 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm having trouble with a segment of code where i wish to update the application SQLite database with some entries from a remote MySQL database. The code executes without errors but still does not reflect in the database. Please i need help figuring out what I'm missing out.
Below is the code segment:

C#
#region Offline Upload

            if (items_going_offline > 0)
            {
                update_progress.SafeInvoke(d => d.Visible = true);

                DialogResult result = MessageBox.Show("Host " + items_going_offline + " document(s) to OFFLINE", "Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (result == DialogResult.Yes)
                {
                    #region Incoming
                    if (MySqlincoming_refNos.Count > 0)
                    {
                        foreach (string re in MySqlincoming_refNos)
                        {
                            path_offline.Clear();
                            path_online.Clear();

                            select_query_online = "select * from " + store.database() + "incoming where ref_no = @ref";

                            MySql_cmd = new MySqlCommand(select_query_online, online_con1);

                            MySql_cmd.Parameters.AddWithValue("@ref", (string)re);

                            Retry.retry.Do(() => store.openConnection("mysql", online_con1, con1), TimeSpan.FromSeconds(10), 5);

                            DataSet dsI = new DataSet();
                            MySqlDataAdapter daI = new MySqlDataAdapter(MySql_cmd);
                            daI.Fill(dsI);

                            if (dsI.Tables[0].Rows.Count > 0)
                            {
                                refNo = (string)dsI.Tables[0].Rows[0][1];
                                subject = (string)dsI.Tables[0].Rows[0][2];
                                date = (DateTime)dsI.Tables[0].Rows[0][3];
                                senders = (string)dsI.Tables[0].Rows[0][4]; ;
                                receiver = (string)dsI.Tables[0].Rows[0][5];

                                try
                                {
                                    for (int a = 1; a < 21; a++)
                                    {
                                        if (dsI.Tables[0].Rows[0][5 + a] != DBNull.Value)
                                        {
                                            string f = (string)dsI.Tables[0].Rows[0][5 + a];
                                            path_online.Add(f.Remove(0, 39));
                                        }
                                    }
                                }
                                catch (Exception ex)
                                {
                                    MessageBox.Show("Unable to read data\n" + ex.Message);
                                }

                                
                                List<byte[]> downloadedFiles = new List<byte[]>();
                                //download the files
                                store.downloadFile(store.FTP(), path_online, ref downloadedFiles);

                                Retry.retry.Do(() => store.openConnection("sqlite", online_con1, con1), TimeSpan.FromSeconds(10), 5);

                                using (SQLiteTransaction Sqlite_transaction = con1.BeginTransaction())
                                {
                                    try
                                    {
                                        insert_query_online = "insert into incoming (ref_no, subject, date, sender, receiver, page_1, page_2, page_3, page_4, page_5, page_6, page_7, page_8, page_9, page_10, page_11, page_12, page_13, page_14, page_15, page_16, page_17, page_18, page_19, page_20) values(@ref_no, @subject, @date, @sender, @receiver, @page_1, @page_2, @page_3, @page_4, @page_5, @page_6, @page_7, @page_8, @page_9, @page_10, @page_11, @page_12, @page_13, @page_14, @page_15, @page_16, @page_17, @page_18, @page_19, @page_20)";

                                        Sqlite_cmd = new SQLiteCommand(insert_query_offline, con1);

                                        Sqlite_cmd.Parameters.AddWithValue("@ref_no", (string)refNo);
                                        Sqlite_cmd.Parameters.AddWithValue("@subject", (string)subject);
                                        Sqlite_cmd.Parameters.AddWithValue("@date", (DateTime)date);
                                        Sqlite_cmd.Parameters.AddWithValue("@sender", (string)senders);
                                        Sqlite_cmd.Parameters.AddWithValue("@receiver", (string)receiver);

                                        //make some pages = null
                                        for (int a = 0; a < 20; a++)
                                        {
                                            if(a < downloadedFiles.Count)
                                                Sqlite_cmd.Parameters.AddWithValue("@page_" + (a + 1) + "", (object)downloadedFiles[a]);
                                            else
                                                Sqlite_cmd.Parameters.AddWithValue("@page_" + (a + 1) + "", null);
                                        }

                                        //Retry.retry.Do(() => Sqlite_cmd.ExecuteNonQuery(), TimeSpan.FromSeconds(10), 5);
                                        Sqlite_cmd.ExecuteNonQuery();
                                        Sqlite_transaction.Commit();

                                        update_progress.SafeInvoke(d => d.Increment(1));

                                        offlineCountdown--;
                                    }
                                    catch (Exception ex)
                                    {
                                        MessageBox.Show("Unable to UPDATE an Offline incoming item");
                                        Sqlite_transaction.Rollback();
                                    }
                                }
                                store.closeConnection("sqlite", online_con1, con1);
                            } 
                            store.closeConnection("mysql", online_con1, con1);
                        }
                    }
                    #endregion


What I have tried:

[remove duplicate code]
Posted
Updated 24-Sep-16 12:19pm
v2

1 solution

You say your code executes with no errors but the data does not insert into the database.

You need to get comfortable with using debugging tools in visual studio.

how to use breakpoints in visual studio - Google Search[^]

Using Breakpoints[^]

Debugging in Visual Studio[^]

You've provided a sample code, which is great, but there is a lot of code missing in order to make this a running application. If you are taking data from mysql and moving it to sql lite, you should look at sql lite and mysql tutorials in .net

connect to sql lite c# - Google Search[^]

Connect to mysql c# - Google Search[^]

The idea here being take your issue and break it down into smaller pieces. So in that spirit, you would start by writing code to connect to mysql. Once the connection works, write the code to execute a query against mysql. Then see that you get the expect results once the query runs. If the query doesn't run...boom...there is your problem. But should the query run, write the code to connect to sql lite and test to see that is working. If that code doesn't work, then you know your issue is with sql lite. If the connection works, write a sample insert, if the insert works you know you've got the correct code to insert into sql lite. Keep going with this and you'll get to the point that you've fixed your own code without having to wait around for strangers on the internet to write your app for you.

So instead of guessing what your code is doing, use the above links and suggestions to step through your code with break points and see at what point your code is having issues. We don't have access to your computer, hard drive, databases, nor can we read minds. There is a lot missing here other than code (example: schema/sample data) for anyone to replicate your issue with given code to be able to help you. So the best way to help you is to encourage you to help yourself by using the debugger in visual studio.
 
Share this answer
 
Comments
Emejulu JVT 24-Sep-16 23:37pm    
Thanks, I've been using the debugger for quite some time, but seems I'll have to try harder. Just for the record, the DataSet gets filled properly with all data i need to insert.

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