Tutorial: Databases

Inserting New Records in Our Table

At this point, we have tables set up to add onto our database. Being able to insert data is one of the most important aspects about databases because a database is meant to hold massive amounts of unique information. To add a new record is similar to adding a new piece of data on a spreadsheet; we want to make sure our data is correctly contained in the correct data column. Here we have a table for student grades and GPA as follows:

We want to make sure data types are in the right column so GPA would be a decimal column while first name should be a string column and so fourth. For this tutorial, we will be going back into our grocery list, "grocerylist" table and insert information regarding food. Recall the columns of out table:

We will want to connect our database once again as well as create an Insert object. To do this, we would create a new object called Insert, that allows us to use helper actions to delete the existing table inside of our database.

use Libraries.Data.Database.Database use Libraries.Data.Database.Insert Database database database:Connect("localhost", "myfirstdatabase", "root", "password") Insert insert

What we want to do first is select the table we want to insert data using SetTable() and pass in the "grocerylist" table. Once that is complete, all inserts will go into that table and we'll want to add 5 entries of data per row: FoodID, FoodName, FoodCost, FoodCalories, FoodQuantity.

What we want to do first is use SetTable() on our 'insert' object which allows us to select a specific table inside our database. For example, SetTable() takes in one parameter, the table title and in this case we will be passing in "grocerylist" into the action. Then, if we want, we can keep a running sum to see how many rows will be affected in total; we can call this integer value "rowsAffected" and initialize it as 0.

Next, what we will be wanting to do is add in values using Add(); the action add requires two parameters: the column name and the data type value associated with the column. For example, say that we have a column called "firstName," we would want to pass "firstName" in the first parameter and because names are strings, we will insert "Bob" as the second parameter. In our grocerylist table, let's insert 1 for FoodId, Chicken Breast for FoodName, 2.99 for FoodCost, 120 for FoodCalories, and 2 for FoodQuantity using the Add() action.

insert:Add("FoodID", 1)
 insert:Add("FoodName", "Chicken Breast")
 insert:Add("FoodCost", 2.99)
 insert:Add("FoodCalories", 120)
 insert:Add("FoodQuantity", 2)

database:Insert(insert)
 rowsAffected = rowsAffected + 1
 insert:Empty()

We have our data loaded inside this insert object, but it is not actually in our database yet. Using our database object, like previous actions, we will now call in the Insert() action and pass in 'insert' within the parameter. This will update our table in our database in real time and it is also worth noting that Insert() will return either 0 or 1 where 0 means it did not add the row into the database and 1 meaning it has successfully added the row into the database. Now, we want to increment the counter, rowsAffected and then empty our insert object using the action Empty(). It is necessary that before we add another record, we have to Empty() the object or else we will have compilation errors.

Congrats! Now, we can finally multiple entries of data into our table! We can view the entire code below.

Next Tutorial

In the next tutorial, we will discuss updating databases, which describes how to update data in a database table.