Gunsmith Db and App Part 4.2: Disposition Code

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *