Gunsmith Db and App Part 5: Search

We have already discussed the gunsmith database and app in depth.  If you want to catch up, there are posts an overview, adding customers, acquisition flow and code, and disposition flow and code.

In this post, we will go over the third and final major function of the app: searching.  Sometimes, the user may only want to search for previous acquisitions or dispositions instead of adding a new customer, gun, acquisition or disposition.2016-01-23_18h22_33

The search tab looks very similar the search windows that come up with the user wants to search for a customer or a gun in the acquisition and disposition tabs.

User Flow

The user can search by one or more of any of the available fields on the left side of the window.  If the user is unsure of the spelling, they can type partial words into any of the textboxes.  For instance, if the user types just the letter ‘a’ into the first name text box, all of the acquisitions and dispositions where the customer’s first name contained the letter ‘a’ will be searched.

2016-01-23_21h02_23

Additionally, the user can choose which columns are displayed by checking and unchecked the boxes along the right side of the GUI.  By default, all of the columns are shown.  To hide columns, the user unchecks the columns he or she does not want to see and clicks ‘Show’.

2016-01-23_21h06_44

Code

The query and code for the search is exactly the same as discussed in the disposition code post.  Since it is review, I’ll just post the query and code here.  If you’d like a more in depth explanation, check out the previous 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.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.Open()
Catch ex As MySqlException
MsgBox(“An Error Occurred. ” & ex.Number & “ – “ & ex.Message)
End Try

Dim adap As MySqlDataAdapter = New MySqlDataAdapter(query, conn)
Dim ds As New DataSet()
Dim check As Boolean = True
If Not Sfname.Text = “” Then
If Not check Then
query += ” firstname LIKE @firstname”
Else
query += ” AND firstname LIKE @firstname”
End If
check = True
End If

If Not Slname.Text = “” Then
If Not check Then
query += ” lastname LIKE @lastname”
Else
query += ” AND lastname LIKE @lastname”
End If
check = True
End If

If Not Scname.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 Sfname.Text = “” Then
adap.SelectCommand.Parameters.AddWithValue(“@firstname”, “%” + Sfname.Text + “%”)
End If

If Not Slname.Text = “” Then
adap.SelectCommand.Parameters.AddWithValue(“@lastname”, “%” + Slname.Text + “%”)
End If

If Not Scname.Text = “” Then
adap.SelectCommand.Parameters.AddWithValue(“@company”, “%” + Scname.Text + “%”)
End If

 

Try
adap.Fill(ds)

acq_grid.DataSource = ds.Tables(0)
ds = Nothing
da = Nothing
acq_grid.Columns(“customer_id”).Visible = False

Catch ex As MySqlException
Aerror.Text = ex.ToString()
Finally

End Try

[/vb]

That is it for the searching.  Just as the disposition, this portion of the code was pretty quick and easy to write since most of it was very similar to things I had already done in the acquisition and disposition portions.

In the next post, we’ll discuss some final thoughts on the project and some ideas for future versions that I’d like to eventually implement.  If you are interested in trying out the software and looking for bugs, please contact me.

Leave a Reply

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