Question retrieving information from sqlite database

rowlandsfc

Active member
Joined
Feb 3, 2019
Messages
43
Location
bridgend
Programming Experience
Beginner
Hi i have a basic application that is used to display customer information from a sql database, i am using a datagridview to display the information, i also have a form that has texboxdes on it to enter customer information for adding to that database which currently works. what i need to do now is id like to be able to search for a specific customer id(custid) in the table and get their information and fill in the same textboxes so that i can edit their info if needed and then save it back to the database.

Adding customer info to database:
namespace NewSQLiteCardiff
{
    public partial class AddCustomer : Form
    {
        public AddCustomer()
        {
            InitializeComponent();
        }

        SQLiteConnection conn;

        private void AddCustomer_Load(object sender, EventArgs e)
        {
            // connects to the customers database
            try
            {
                conn = new SQLiteConnection();
                conn.ConnectionString = dbConnection.source;
                conn.Open();
            }
            catch (Exception ex)
            {
                conn.ConnectionString = dbConnection.source;
                conn.Close();

                MessageBox.Show(ex.Message);
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                // adds customers details to the database
                cmd.CommandText = @"INSERT INTO customer (title, " + "firstname, " + "lastname, " + "dob, " + "nicode, " + "email, " + "password, " + "allowance) VALUES (@setTitle, @setFirstname, @setLastname, @setDOB, @setNICode, @setEmail, @setPassword, @setAllowance)";
                cmd.Parameters.AddWithValue("setTitle", cb_title.Text);
                cmd.Parameters.AddWithValue("setFirstname", txtFirst_Name.Text);
                cmd.Parameters.AddWithValue("setLastname", txtSurname.Text);
                cmd.Parameters.AddWithValue("setDOB", dtp_DOB.Text);
                cmd.Parameters.AddWithValue("setNICode", txtNI_Code.Text);
                cmd.Parameters.AddWithValue("setEmail", txtEmail.Text);
                cmd.Parameters.AddWithValue("setPassword", txtPassword.Text);
                cmd.Parameters.AddWithValue("setAllowance", txtAllowance.Text);

                int recordsChanged = cmd.ExecuteNonQuery();
                MessageBox.Show("Customer Added");
                conn.Close();

                Customers customers = new Customers();
                customers.Show();
                this.Hide();

            }
        }

        // cancel button to cancel adding new customer and returnsto the customers tables

        private void btnCancel_Click(object sender, EventArgs e)
        {
            Customers customers = new Customers();
            customers.Show();
            this.Hide();

        }
    }
}

any help would be appreciated
 
Solution
First thing, don't use the load event especially for SQL. The load event is also known for swallowing errors, and I don't believe it's ever been fixed, but I may stand corrected on that. It's also better practice to use the Shown event instead.

Secondly, learn to construct your SQL statements properly. What is the point in using parameters if you are going to cut into the string on line 35? These quotes are not needed : " + "

Thirdly, don't use your UI as a model or some type of data model in any way. Set a class of Customer and give that Customer class some properties for the parameters you are using instead.

NoUserHere

Well-known member
Joined
Sep 5, 2018
Messages
2,138
Programming Experience
10+
First thing, don't use the load event especially for SQL. The load event is also known for swallowing errors, and I don't believe it's ever been fixed, but I may stand corrected on that. It's also better practice to use the Shown event instead.

Secondly, learn to construct your SQL statements properly. What is the point in using parameters if you are going to cut into the string on line 35? These quotes are not needed : " + "

Thirdly, don't use your UI as a model or some type of data model in any way. Set a class of Customer and give that Customer class some properties for the parameters you are using instead.
 
Solution

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
3,386
Location
Chesapeake, VA
Programming Experience
10+
The load event is also known for swallowing errors, and I don't believe it's ever been fixed,
For 64-bit Windows, exceptions are swallowed by the system and is by design. If you still have 32-bit Windows, then you can still catch the exception. If you are running as 32-bit on 64-bit Windows, see my first statement. This is by design and there was an extensive explanation by Raymond Cheng in one of his blog posts in Old Thing New Thing, but I can't find it right now. There is supposedly another explanation in Paul Bett's blog, but I've never read his post and the links that point to his explanation seem to be dead. The gist of the reason why the exception is thrown is that 64-bit Windows can potentially get into a deep recursive "loop" if the exception was attempted to be bubbled back up, so a design decision was made by the Windows engineers to simply swallow the exception.

Personally, I prefer to use the Shown event, but I believe that a lot of the legacy WinForms examples and demos use the Load event to get the mind share of VB developers whose mindshare MS was trying to capture to get them to convert over to .NET Framework using C# or VB.NET. At that time it was a non-issue because it was believed that most people would be using 32-bit Windows and only power users would using 64-bit. Alas, the DevDiv engineers were not privy to the thoughts of the Windows engineers who were pushing for 64-bit Windows to become more dominant.
 

NoUserHere

Well-known member
Joined
Sep 5, 2018
Messages
2,138
Programming Experience
10+
For 64-bit Windows, exceptions are swallowed by the system and is by design.
I've always called this a bug. But when you can't fix the bug, why not steamroll it into the framework as a design pattern, principle or whatever you may want to call it. Yep, sure sounds like something coming from Microsoft! Thanks for the explanation though, it was more insightful than I had originally anticipated it to be.

Ok I will look into the shown event, I've never used it, just following what my college course is teaching atm
It's no different to using the load event. It's just a better and more practical event to use in my opinion. If your school question why you used the shown event instead, you can link them Skydiver's answer. ;)

You should further make the other changes I proposed above.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
3,386
Location
Chesapeake, VA
Programming Experience
10+

NoUserHere

Well-known member
Joined
Sep 5, 2018
Messages
2,138
Programming Experience
10+
So this goes deeper and is somewhere at the bas of the kernel, it appears?

I wonder why the decision was made to bury or allow the exception to be swallowed?
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
3,386
Location
Chesapeake, VA
Programming Experience
10+
I still haven't found the Raymond Chen explanation (which I thought was the most complete and easiest to understand), but the links in this SO answer may help:
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,921
Location
Sydney, Australia
Programming Experience
10+
I have to disagree with the idea that the Shown event is better than the Load event. Shown didn't even exist in early versions of .NET and was added in .NET 2.0. The main practical difference between the two is that Load is raised before the form is displayed for the first time and Shown is raised after the form is displayed for the first time. In my .NET 1.1 days, there was at least one occasion where I had to handle the Activated event and use a Boolean to determine whether it was the first time or not. I suspect that Shown was added in order to avoid that messy requirement.

Generally speaking, you want the work to be done before the form is displayed, so that the form is complete when it is displayed. If you do the work in Shown then the form will be displayed but frozen until the work is complete. Personally, I think that not showing the form is better than showing it in an unusable state. Of course, if you show some sort of waiting dialogue then that's a different story. If you are going to kick off work asynchronously then doing so on Load starts it sooner, if only marginally, than doing so on Shown. The answer to the issue of swallowing unhandled exceptions is to use an explicit exception handler.
 

NoUserHere

Well-known member
Joined
Sep 5, 2018
Messages
2,138
Programming Experience
10+
Uncanny, we've been here before so you know my response. Guess we will agree to disagree again.

I'll flavour using the shown event, thanks. Also, any loading that needs doing can be done normally providing you set long running tasks to run on a non UI thread. Perhaps I should have been more clear here... I certainly don't advocate doing any SQL work in any event. As its not nearly implied enough that long running code executions are best run on their own threads or a worker thread.

Also, like all events on Winforms, they are for driving minor actions, so it wouldn't be advised to do any "heavy-load" work directly on them. Since it doesn't take much to jam up the UI on Winforms, I'd advise keeping "worker code" outside of those events personally. But we all have our preferences, likes, dislikes, agreements and disagreements. I hope this is something that will be later explained to our OP by their collage.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
3,386
Location
Chesapeake, VA
Programming Experience
10+
Fatal execution exception not caught:
C#:
using System;
using System.Drawing;
using System.Threading;
using System.Windows.Forms;

class Program : Form
{
    public Program()
    {
        Load += (o, e) => ThrowAndCatch();
    }

    void ThrowAndCatch()
    {
        try
        {
            DoDangerousOperation();
        }

        catch (Exception ex)
        {
            MessageBox.Show($"I caught it!: {ex}");
        }

        MessageBox.Show("Done.");
    }

    unsafe void DoDangerousOperation()
    {
        int [] x = { 6 };
        fixed(int * ptrX = x)
        {
            int* ptrY = ptrX;
            while ((long)ptrY >= 0)
            {
                *ptrY = 12;
                ptrY -= 1;
            }
        }
    }

    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
        Application.Run(new Program());
    }
}

Nevermind... Shown event doesn't catch the exception either.
 
Top Bottom