Gunsmith Db and App Part 2: Adding Customers

Personal Projects › GunsmithDb
,

publish In Gunsmith Db and App Part 1 I discussed the overall goal of this project and an overview of where I was at that time. I finally had the time (and desire) to pick this project up again and realized pretty quickly that I needed to change how the database is designed. Previously, I had the tables:

  • acquisition
  • caliber
  • category
  • disposition
  • gun
  • mancountry
  • manufacturers
  • models
  • test
  • type

The thought here is that each transaction would be either an acquisition or disposition. However, I had no way to track the person on the other end of the transaction (the customer). Additionally, the acquisition and disposition tables did not reference a gun! I’m sure there are many more little things like this that I missed the first time around, so hopefully going through everything again will make these things evident. First, I added a new table for customers. [SQL] CREATE TABLE customer (customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, firstname VARCHAR(50), lastname VARCHAR(50), company VARCHAR(50), address1 VARCHAR(75) NOT NULL, address2 VARCHAR(75), city VARCHAR(50) NOT NULL, state VARCHAR(2) NOT NULL, zip MEDIUMINT(5) UNSIGNED NOT NULL, licensenum INT(11) NOT NULL); [/SQL] Next, I added a column for customer_id to the acquisition and disposition tables. This will be a foreign key that references the primary key for each customer in the customer table. The AFTER command adds the column customer_id after the column acq_date. Without this command, customer_id would be added to the end of the table (which would also be fine!). [SQL] ALTER TABLE acquisition ADD customer_id INT(11) NOT NULL AFTER acq_date; [/SQL] Next, I added a column for gun_id to the acquisition and disposition tables. This way, each transaction references the gun involved. [SQL] ALTER TABLE disposition ADD gun_id INT(11) NOT NULL AFTER dis_id; [/SQL] After I got the database in order, I wanted to finish up the GUI. I had thought the GUI was pretty close to being at about a beta level, where I could set it up at my friend’s shop and have him start using it. The plan was to have him use it while I finished up more features so that if things were really not working for him, I could incorporate them before I put too much work going down a path he didn’t like. However, after realizing I needed a table for customers, I also realized I will need to redo some of the GUI design with regards to customers. Below is a screenshot of what the GUI currently looks like (it changed very slightly since Part 1). gundbgui My original plan was for the first version of the software to look pretty much like this. The user would have to enter in the customer’s information (name, address, license number) and in a later version I would add the ability to search for a customer based on name, location, or license number. However, since each customer will have his or her own entry in the customer table, I do not want to risk my friend accidentally adding the same customer twice. This could quickly create a bad situation on the database end, so I want to prevent it before it can happen. I want the user to be able to find the customer based on almost any information (name, address, license number, etc). Before the GUI adds a new customer to the database, it must also check to make sure that customer doesn’t already exist. MySQL can make sure every entry is unique by using the UNIQUE command but if the user types the name in wrong or if the customer moves in between transactions (therefore their address changes), MySQL won’t know that customer is unique. This is a complex issue and I don’t plan on solving all of it in one go. The first part I will work on is adding the search capability to the GUI. There should only be one search function for both acquisition and disposition, this way I don’t have to duplicate my efforts. My initial thought is to add a Customer tab, which will include all of the fields that the customer table has. A user will fill in at least one textbox on this tab and press a ‘Search’ button, which will be used to search the database for all of the customers that meet the criteria. A box will pop up with the possible solutions and the user can choose which customer they are intending. If they want to add a new customer, there will be the option to do that as well. Once a customer is chosen, all I will need to know is the unique customer_id from the database. This will be stored as a global variable and referenced in the acquisition and disposition tabs. A possible issue I can see right away is that there may be multiple customers returned from the search. I will need to research how to have a popup window with a variable number of outputs. Depending on how my friend prefers to search for customers, I might also want to add a filter option to the popup window that the search produces.

Searching for Customers

The user will need to search for a customer during both acquisition and disposition. Since this will be used in mutliple locations, I went ahead and added a ‘Customer’ tab to the window. Capture Visual Studio makes GUI design very easy – you simply click and drag to place and resize the items. Again, the buttons and textboxes on the right are for testing purposes only and will be removed before I send this off to my friend. I named each of the textboxes and buttons on this tab with a prefix C for Customer tab. For instance, the First Name box is named Cfname and the Last Name box is named Clname. This is so that I won’t have to repeatedly look for what the boxes are named. The search is trigged off of the ‘Submit | Add’ button. The code for when that button is clicked is below: [vb] Private Sub Csearch_Click(sender As Object, e As EventArgs) Handles Csearch.Click Dim query As String = “SELECT * FROM customer WHERE” Dim obj As New customerform Dim adap As MySqlDataAdapter = New MySqlDataAdapter(query, conn) Dim ds As New DataSet() Dim check As Boolean = False If Not Cfname.Text = “” Then If Not check Then query += “ firstname = @firstname” Else query += “ AND firstname = @firstname” End If check = True End If If Not Clname.Text = “” Then If Not check Then query += “ lastname = @lastname” Else query += “ AND lastname = @lastname” End If check = True End If If Not Ccname.Text = “” Then If Not check Then query += “ company = @company” Else query += “ AND company = @company” End If check = True End If adap = New MySqlDataAdapter(query, conn) If Not Cfname.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@firstname”, Cfname.Text) End If If Not Clname.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@lastname”, Clname.Text) End If If Not Ccname.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@company”, Ccname.Text) End If testbox.Text = query Try adap.Fill(ds) obj.customergrid.DataSource = ds.Tables(0) ds = Nothing da = Nothing conn.Close() conn.Dispose() obj.Show() Catch ex As MySqlException TextBox11.Text = ex.ToString() Finally conn.Close() End Try End Sub [/vb] There is a lot going on here. The general idea is that I need a MySQL query that follows the formula [SQL] SELECT * FROM customer WHERE [condition1] AND [condition2] AND [condition3].. [/SQL] The conditions are whatever the user typed in. For instance, if the user types ‘Amy’ into the first name textbox, Cfname, then the condition would be [SQL] firstname = ‘Amy’ [/SQL] I don’t want to restrict the user to search by something particular, so the query will change for each search. The code checks each of the text boxes and appends the initial query (“SELECT * FROM customer WHERE”) with a specific condition. The variable check _is used to check if a condition is the first condition. Every condition after the first must begin with “ AND”, but the first condition cannot. Each of the If loops are the same. They first check to see if the textbox has any characters in it. If it does, it checks to see if it is the first condition (via the _check variable). If it is the first condition, the query simply gets appended with the new condition; otherwise, it adds “ AND” followed by the query. Finally, it changes the check variable to True. The @ symbol in each condition is a method to prevent SQL Injection. I’ll be posting about SQL Injection next week. Next, I create a new MySqlDataAdapter with the final query. This is what will be pushed out to the MySQL database. conn is a global variable with a connection string to my database. There are a series of if statements following this to prevent SQL Injection. The AddWithValue parameter cannot be used inside the original if statements because the query variable changes in each if statement. Therefore, I have to first loop through each textbox to build the query and then loop through them again to add the values from the textboxes safely to the query. The next section, starting with Try opens the new window and populates a DataGridView with the data from the MySQL query. This is a good start but there are still many things that need to be finished for the Customer tab:

  • Specific error message if nothing is found
  • Ability to select which customer you want
  • Add wildcard to search
  • Ability to edit a customer