Gunsmith Db and App Part 4.2: Disposition Code

Personal Projects › GunsmithDb

In Part 4.1, I showed the user flow for disposing of a gun. In this part, we’ll go over the VB code and MySQL commands to get the disposition working.

Search For An Acquisition Transaction

General Overview

VB checks each of the textboxes in the GUI and creates a MySQL query to reflect the criteria the user is attempting to search by. The query is sent to the MySQL database and the result is inserted into the GUI’s DataGridView, which is displayed as a spreadsheet.

MySQL Query

The MySQL query selects:

  • From the customer table: customer id, first name, last name, company name, address, license number, phone number
  • From the gun table: gun id, serial number
  • From the manufacturers table: manufacturer name
  • From the models table: model name
  • From the caliber table: caliber
  • From the acquisition table: transaction date

The query only selects these things from acquisitions that have not been disposed of; in other words, only where the gun id in the disposition table is NULL. No matter what the user is searching by, the following must always be the first part of the query: [sql] SELECT customer.customer_id, gun.gun_id, customer.firstname AS FirstName, customer.lastname AS LastName, customer.company AS Company, customer.address1, customer.address2, customer.city, customer.state, customer.zip, customer.licensenum, customer.phone_number, manufacturers.name AS Manufacturer, models.name AS Model, caliber.name AS Caliber, gun.serialnum AS SerialNumber, acquisition.transaction_date FROM gun LEFT JOIN acquisition INNER JOIN customer ON acquisition.customer_id = customer.customer_id ON gun.gun_id = acquisition.gun_id LEFT JOIN disposition ON gun.gun_id = disposition.gun_id LEFT JOIN manufacturers ON gun.man_id = manufacturers.man_id LEFT JOIN models ON gun.model_id = models.model_id LEFT JOIN category ON gun.cat_id = category.cat_id LEFT JOIN type ON gun.type_id = type.type_id LEFT JOIN mancountry ON gun.mancountry_id = mancountry.mancountry_id LEFT JOIN caliber ON gun.cal_id = caliber.cal_id WHERE disposition.gun_id IS NULL [/sql] This query is the longest and most complex query in the entire project. The first several lines are simply the different columns we want to select. Anytime there is an ‘AS’, this is simply renaming the output from the column name to whatever is after the ‘AS’. For instance, customer.firstname AS FirstName will rename the column from ‘firstname’ to ‘FirstName’ in the output. I do this on a few columns to make the end spreadsheet display look a little more professional. The table after the FROM is critical – all of the joins will reference this table. Since most of the joins are LEFT JOINS, we know from the post on MySQL JOIN that everything we want to select from the gun table will be selected, but anything else joined by the LEFT JOINS will result in ‘null’ if they do not exist in the database. This means that all of the guns are selected from the gun table (even the ones disposed of!). We will take care of the disposed guns later on in the query. Next, we have our first JOIN command. Every JOIN must end with an ON statement which declares the condition with which you want to join. The first JOIN looks like: [SQL] LEFT JOIN acquisition INNER JOIN customer ON acquisition.customer_id = customer.customer_id ON gun.gun_id = acquisition.gun_id [/SQL] You’ll notice the ON statement for the LEFT JOIN is after another JOIN! This is a nested JOIN statement. The output of the search should include all of the guns in the database first and foremost. Next, we want to join the acquisition date to the output table. The JOIN for that is [SQL] LEFT JOIN acquisition ON gun.gun_id = acquisition.gun_id; [/SQL] However, we also need the connect the customer’s information for that specific acquisition transaction. Since the customer’s information depends on the acquisition, the JOIN for the customer needs to be nested inside of the join for the acquisition. The rest of the JOINS are just simple MySQL LEFT JOIN commands, joining the rest of the tables for the SELECT output. Since we searched through all of the guns, after everything is JOINED we need to weed out just the guns that have not been disposed of. In order to do that, I added a WHERE command which says we only want the output to include guns that don’t have a disposition id (indicating they haven’t been disposed of). [SQL] WHERE disposition.gun_id IS NULL [/SQL] Remember, that query is only the part which will always be true. The query will need to be appended for the actual search to happen.

VB Code

The above query is stored in a string variable called ‘query’. Next, VB attempts to connect to the database. Every function within the VB code attempts to open a connection at the beginning and attempts to close the connection at the end. This way, the connection will not time out and VB will not try to open more than one connection. Both of these would result in an error that the end user wouldn’t be able to fix. [vb] Dim query As String = “SELECT customer.customer_id, gun.gun_id, customer.firstname AS FirstName, customer.lastname AS LastName, customer.company AS Company, customer.address1, customer.address2, customer.city, customer.state, customer.zip, customer.licensenum, customer.phone_number, manufacturers.name AS Manufacturer, models.name AS Model, caliber.name AS Caliber, gun.serialnum AS SerialNumber, acquisition.transaction_date FROM gun LEFT JOIN acquisition INNER JOIN customer ON acquisition.customer_id = customer.customer_id ON gun.gun_id = acquisition.gun_id LEFT JOIN disposition ON gun.gun_id = disposition.gun_id LEFT JOIN manufacturers ON gun.man_id = manufacturers.man_id LEFT JOIN models ON gun.model_id = models.model_id LEFT JOIN category ON gun.cat_id = category.cat_id LEFT JOIN type ON gun.type_id = type.type_id LEFT JOIN mancountry ON gun.mancountry_id = mancountry.mancountry_id LEFT JOIN caliber ON gun.cal_id = caliber.cal_id WHERE disposition.gun_id IS NULL “ Try conn.Close() Catch ex As Exception End Try Try ‘conn.connectionstring = constr conn.Open() Catch ex As MySqlException MsgBox(“An Error Occurred. “ & ex.Number & “ – “ & ex.Message) End Try [/vb] Next, I use a parameterized query to append the SELECT statement with the criteria that the user wants to search by. This is the same method as previously discussed in the acquisition code post, so I’ll just show a few examples here. [vb] Dim adap As MySqlDataAdapter = New MySqlDataAdapter(query, conn) Dim ds As New DataSet() Dim check As Boolean = True If Not Dfname.Text = “” Then If Not check Then query += “ firstname LIKE @firstname” Else query += “ AND firstname LIKE @firstname” End If check = True End If If Not Dlname.Text = “” Then If Not check Then query += “ lastname LIKE @lastname” Else query += “ AND lastname LIKE @lastname” End If check = True End If If Not Dcompany.Text = “” Then If Not check Then query += “ company LIKE @company” Else query += “ AND company LIKE @company” End If check = True End If adap = New MySqlDataAdapter(query, conn) If Not Dfname.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@firstname”, “%” + Dfname.Text + “%”) End If If Not Dlname.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@lastname”, “%” + Dlname.Text + “%”) End If If Not Dcompany.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@company”, “%” + Dcompany.Text + “%”) End If [/vb] Finally, the query is sent sent to the database and the output is displayed in the GUI. [vb] Try adap.Fill(ds) dsearch_grid.DataSource = ds.Tables(0) ds = Nothing da = Nothing dsearch_grid.Columns(“customer_id”).Visible = False dsearch_grid.Columns(“gun_id”).Visible = False Catch ex As MySqlException Finally conn.Close() End Try [/vb]

Change The Customer

General Overview

Clicking the ‘Change Customer’ button will open a new window which allows the user to search for and select a new customer. This allows the gun to be disposed of to a person other than the person the gun was acquired from.

MySQL Query

The MySQL queries for searching and adding customers is identical to those discussed in the acquisition post. Check out the in depth discussion on how these work here. [sql] “SELECT customer.customer_id, customer.firstname AS FirstName, customer.lastname AS LastName, customer.company AS Company, customer.address1, customer.address2, customer.city, customer.state, customer.zip, customer.phone_number AS phone, customer.licensenum FROM customer WHERE “ [/sql]

VB Code

As with the query, the VB code is identical to in the acquisition post. [vb] Dim query As String = “SELECT customer.customer_id, customer.firstname AS FirstName, customer.lastname AS LastName, customer.company AS Company, customer.address1, customer.address2, customer.city, customer.state, customer.zip, customer.phone_number AS phone, customer.licensenum FROM customer WHERE “ Dim adap As New MySqlDataAdapter Dim ds As New DataSet() Dim check As Boolean = False adap = New MySqlDataAdapter(query, conn) If Not ACfname.Text = “” Then If Not check Then query += “ firstname LIKE @firstname” Else query += “ And firstname Like @firstname” End If check = True End If If Not AClname.Text = “” Then If Not check Then query += “ lastname Like @lastname” Else query += “ And lastname Like @lastname” End If check = True End If If Not ACcompany.Text = “” Then If Not check Then query += “ company Like @company” Else query += “ And company Like @company” End If check = True End If adap = New MySqlDataAdapter(query, conn) If Not ACfname.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@firstname”, “%” + ACfname.Text + “%”) End If If Not AClname.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@lastname”, “%” + AClname.Text + “%”) End If If Not ACcompany.Text = “” Then adap.SelectCommand.Parameters.AddWithValue(“@company”, “%” + ACcompany.Text + “%”) End If Try adap.Fill(ds) csearch_grid.DataSource = ds.Tables(0) ds = Nothing csearch_grid.Columns(“customer_id”).Visible = False conn.Close() conn.Dispose() Catch ex As MySqlException Finally conn.Close() End Try [/vb]

Submitting the Disposition

General Overview

When the user clicks submit, the GUI sends an INSERT command to the MySQL database to insert the new disposition. The variable fields are read from the ‘Selected Customer’ and ‘Selected Gun’ panels.

MySQL Query

The following portion of the query is always true. [sql] INSERT INTO disposition (dis_id, gun_id, customer_id, dis_date, transaction_date, date_entered [/sql]

VB Code

The above query is stored in a string variable called ‘query’. [vb] Try conn.Close() Catch ex As Exception End Try Try conn.Open() Catch ex As MySqlException MsgBox(“An Error Occurred. “ & ex.Number & “ – “ & ex.Message) End Try Dim query As String = “INSERT INTO disposition (dis_id, gun_id, customer_id, dis_date, transaction_date, date_entered” [/vb] The only additional item which may or may not be added to the database during a disposition is a ‘code’. VB checks to see if anything is typed into the code text box, appends the query if necessary for that, then uses the parameterized query to fill in the rest of the variables and submit the information to the database: [vb] If Not Dcode.Text = “” Then query += “, code” End If query += “) VALUES (NULL, @gun_id, @customer_id, NOW(), @transaction_date, NOW()” If Not Dcode.Text = “” Then query += “, @code” End If query += “)” Dim cmd As New MySqlCommand(query, conn) derror.Text = query cmd.Parameters.AddWithValue(“@gun_id”, gunid.Text) cmd.Parameters.AddWithValue(“@customer_id”, SCcustomer_id.Text) cmd.Parameters.AddWithValue(“@transaction_date”, dtransdate.Value) cmd.ExecuteNonQuery() derror.Text = “submitted” conn.Close() [/vb] And that is it! The disposition portion of the database and GUI were very quick and easy for me to design and finish because they both mirror their acquisition counterparts pretty well. The GUI is now at a level where I am looking for people to test out installing it, using it, breaking it, etc. If you are interested, please contact me. As always, I would love your help and feedback. Click here to go to Part 5: Search.