Gunsmith Db and App Part 3.2: Acquisition Code
In Part 3.1, I showed the user flow for acquiring a gun. In this portion, we will go over the VB code and MySQL commands to get the acquisition working.
Search For a New Customer
General Overview
VB checks each of the textboxes in the customer search panel and adds those values to a string, which will be sent to MySQL as a SELECT query. The result of this query is placed into a datasource which then is inserted into the DataGridView, which displays as a spreadsheet.
MySQL Query
The MySQL query needs to select the customer id (which is the foreign key for the customer table), the customer’s name, company name, address, phone number, and license number. No matter what the user is searching for, this portion of the query will always be true: [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
This query is put into a string variable in VB named ‘query’. The ‘AS’ portions of the query will rename the displayed column names in the GUI to be whatever is after ‘AS’. For instance, without the first ‘AS’, the first name column would be displayed as ‘firstname’ instead of ‘FirstName’. After the WHERE clause, we need to put our search criteria. However, we want a user to be able to search by anything, which means we don’t necessarily know what needs to go after the WHERE clause. To get around this, I wrote a series of If statements that checks each of the textboxes within the customer search panel. If the textbox has text in it, the query variable will be appended appropriately. Every criteria after the first must have ‘AND’ at the beginning. In order to check for this, I created a boolean variable, check, which checks to see if the current criteria is the first or not. If it is the first, no ‘AND’ is added. The first few If statements are below. All of the If statements are generally the same. [vb] 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 [/vb] After each textbox is checked, I then create a new MySQL Data Adapter and fill in the parameterized query to prevent SQL Injection (discussed in depth here): [vb] 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 [/vb] Finally, I create the datasource and fill in the columns of the DataGridView. I don’t want the customer id to be visible since it isn’t beneficial to the end user, so I hide that column. [vb] Try adap.Fill(ds) csearch_grid.DataSource = ds.Tables(0) ds = Nothing da = Nothing csearch_grid.Columns(“customer_id”).Visible = False Catch ex As MySqlException Finally conn.Close() End Try [/vb]
Select A Customer
General Overview
There are two ways for the user to select a customer: double clicking the name or right clicking and selecting ‘Select Customer’. I created a function titled SelectCustomerAcqFunction so that I can reference one function for both methods. I select the row that the mouse is currently on, then grab the items in each column. Since I disabled the ability to reorganize the table, I know what each column is and can easily set the text labels in the GUI for the selected customer.
VB Code
First, I get the current row and set the labels which are always required (customer idea and name). From there, I check to make sure each column is filled. If it is, I set the appropriate label. The first few columns are shown below: [vb] Dim i As Integer Try i = csearch_grid.CurrentRow.Index Catch End Try Dim cid As String = csearch_grid.Item(0, i).Value SCcustomer_id.Text = csearch_grid.Item(0, i).Value SCfname.Text = csearch_grid.Item(1, i).Value SClname.Text = csearch_grid.Item(2, i).Value If csearch_grid.Item(3, i).Value IsNot DBNull.Value Then SCcompany.Text = csearch_grid.Item(3, i).Value End If If csearch_grid.Item(4, i).Value IsNot DBNull.Value Then SCaddress1.Text = csearch_grid.Item(4, i).Value End If If csearch_grid.Item(5, i).Value IsNot DBNull.Value Then SCaddress2.Text = csearch_grid.Item(5, i).Value End If If csearch_grid.Item(6, i).Value IsNot DBNull.Value Then SCcity.Text = csearch_grid.Item(6, i).Value End If [/vb]
Firearm Dropdowns
General Overview
Next, the user must enter all of the firearm details. All of the dropdowns are pulled directly from the database; this way, the dropdowns are always up to date.
VB Code
Since there are many dropdowns, I created a function so that I don’t have to duplicate the same code many times. The function has four inputs: the SQL query, the table that I want to query, the row I want from the query, and the dropdown to fill. [vb] Public Sub cbfill(sStmt As String, table As String, row As String, combobox As ComboBox) Dim cmd As New MySqlCommand(sStmt, conn) Dim da As MySqlDataAdapter = New MySqlDataAdapter(cmd) Dim dt As New DataTable(table) da.Fill(dt) If dt.Rows.Count > 0 Then combobox.DataSource = dt combobox.DisplayMember = row ‘What is displayed combobox.ValueMember = row ‘The ID of the row End If combobox.SelectedIndex = -1 End Sub [/vb] In order to fill the caliber dropdown, I simply call: [vb] cbfill(“SELECT name FROM caliber”, “caliber”, “name”, Dcaliber) [/vb]
Submitting The Acquisition
General Overview
First, I check to make sure everything that is required is filled out. If it is not, whatever is missing is put into an error message and displayed in the notification box. Once everything that is required is filled out, a query is built and submitted to the database.
VB Code
In order to check if the required items are filled out, I check to see if there is anything in those textboxes. If a required textbox is empty, the string variable ‘errmsg’ is appended with the appropriate warning. The first few textboxes are checked below,, after I connect to the database: [vb] Dim errmsg As String = “” 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 ‘ check that required things are filled out, vbcr if label, vbcrlf if textobx If SCcustomer_id.Text = “cid” Then errmsg += vbCrLf + “Select Customer” End If If cat_id Is Nothing Then errmsg += vbCrLf + “Category” End If If type_id Is Nothing Then errmsg += vbCrLf + “Type” End If If cal_id Is Nothing Then errmsg += vbCrLf + “Caliber” End If [/vb] In order to get a carriage return between each warning, I use vbCrLf If the variable ‘errmsg’ is not empty, the notification box gets filled with all of the warnings: [vb] Aerror.Text = “The following need to be completed: “ + errmsg [/vb] If everything is filled out properly, I submit it to the database. There are two tables that must be submitted to: gun and acquisition. We have to do these separately, as you cannot insert into multiple tables at the same time.
MySQL Queries
In order to submit to the gun table, we must at a minimum have the following query: [sql] INSERT INTO gun (gun_id, type_id, cal_id, man_id, model_id, mancountry_id, serialnum, date_entered, cat_id” [/sql] However, the user may have entered more information than this. As with the customer search, I use several If statements to see what, if anything, should be added to the query: [vb] If Not Ayear.Text = “” Then query += “, year” End If If adeadline_yes.Checked Then query += “, deadline” End If If Not Anotes.Text = “” Then query += “, note” End If [/vb] Finally, I close the end parenthesis and add the VALUES portion of the query: [vb] query += “) VALUES (NULL, @type_id, @cal_id, @man_id, @model_id, @mancountry_id, @serialnum, NOW(), @cat_id” [/vb] We must loop through the same textboxes again to add their values: [vb] If Not Ayear.Text = “” Then query += “, @year” End If If adeadline_yes.Checked Then query += “, @deadline” End If If Not Anotes.Text = “” Then query += “, @note” End If query += “)” [/vb] I could potentially shorten the code and time by creating a query_value string that adds the values in the first if statement loops. This addition may be done in a further revision. Finally, we insert the values using the Parameterized Query to prevent SQL Injection and submit it to the database: [vb] cmd.Parameters.AddWithValue(“@type_id”, type_id) cmd.Parameters.AddWithValue(“@cal_id”, cal_id) cmd.Parameters.AddWithValue(“@man_id”, man_id) cmd.Parameters.AddWithValue(“@model_id”, model_id) cmd.Parameters.AddWithValue(“@mancountry_id”, mancountry_id) cmd.Parameters.AddWithValue(“@serialnum”, Aserialnum.Text) cmd.Parameters.AddWithValue(“@cat_id”, cat_id) cmd.Parameters.AddWithValue(“@deadline”, Adeadline.Value) cmd.Parameters.AddWithValue(“@note”, Anotes.Text) cmd.Parameters.AddWithValue(“@year”, Ayear.Text) Try cmd.ExecuteNonQuery() Catch ex As MySqlException Aerror.Text = ex.ToString() Finally End Try [/vb] Submitting to the acquisition table is the same. However, one of the required columns in the acquisition table is the related gun_id. Since we just inserted the gun_id and it is auto incrementing, we could just check for the largest gun_id. However, this is bad practice. A better solution is to use MySQL’s LAST_INSERT_ID command. This command will select the last primary key of the last insert from the current user. [vb] query = “Select LAST_INSERT_ID()” cmd = New MySqlCommand(query, conn) Dim reader As MySqlDataReader = cmd.ExecuteReader() Dim gun_id As String Try While reader.Read() gun_id = reader.GetString(0) End While reader.Close() Catch ex As MySqlException Aerror.Text = ex.ToString() Finally reader.Close() End Try [/vb] After we have the gun_id, we can submit to the acquisition table using the same methods as above: [vb] query = “INSERT INTO acquisition (acq_id, acq_date, customer_id, gun_id, transaction_date, date_entered) VALUES (NULL, @acq_date, @customer_id, @gun_id, @transaction_date, NOW())” cmd = New MySqlCommand(query, conn) cmd.Parameters.AddWithValue(“@acq_date”, AAcquired.Value) cmd.Parameters.AddWithValue(“@customer_id”, SCcustomer_id.Text) cmd.Parameters.AddWithValue(“@gun_id”, gun_id) cmd.Parameters.AddWithValue(“@transaction_date”, Atransdate.Value) Try cmd.ExecuteNonQuery() Catch ex As MySqlException Aerror.Text = ex.ToString() Finally ‘ conn.Close() End Try Aerror.Text = “Acquisition submitted” [/vb] Finally, we close the connection: [vb] conn.Close() [/vb] And that’s it! We successfully inserted an acquisition! Click here to go to Part 4.1: Disposition Flow.