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. 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. 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’.

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.