Beginning Visual Basic 2005 (2006)

Chapter 16

Figure 16-10

4.Now enter a title and price for the new book and click the Add button. You will see a message in the status bar indicating that the record has been added, and you will also see that the number of records has changed (to 27) as shown in Figure 16-11.

Figure 16-11

Now that you have added a record, examine what you actually did.

How It Works

Remember that the only data that you can add is a new book title and its price. So instead of selecting the data in each of these fields, deleting it, and then entering the new data, you want to be able to simply

536

Database Programming with SQL Server and ADO.NET

click the New button. The job of the New button is to clear the book title and price fields for you. All you need to do is set the Text properties of these text boxes to an empty string as shown here:

‘ Clear the book title and price fields...

txtBookTitle.Text = “” txtPrice.Text = “”

When you click the New button, the fields are cleared. If you are updating or editing a record, those changes are lost. You would normally put logic into your application to prevent that problem, but for this example that type of validation was left out.

The primary key used in the titles table is not the database’s Identity column. Identity columns use a sequential number and automatically increments the number for you when a new row is inserted. Instead of an Identity column, the primary key is made up of a category prefix and a sequential number. This means that you must first determine the maximum number used in a category and then increment that number by 1 and use the new number and category prefix for the new key.

The first thing that you want to do in the btnAdd_Click event procedure is declare your local variables and objects that will be used here. The intPosition variable will be used to save the current record position, and the intMaxID variable will be used to set and increment the maximum sequential number for a category. The strID will be used to store the primary key from the authors table and to set the new key for the authors table. Finally, the objCommand object will be used to build a query to insert a new record into the titleauthor and titles tables.

Before you do anything, you want to save the position of the current record that you are on. This enables you to go back to this record once you reload the DataView object, which will contain the new record that you will add in this procedure:

intPosition = objCurrencyManager.Position

You need to execute a command on the database to work out what ID to give your new title. You us a SqlCommand object to do this. You pass in an SQL string and the connection that you use throughout your program. This SQL string will select the maximum value in the title_id column, where the title_id value begins with the prefix of DM.

There is no category for demo, so you add all of the test records under this category and use the category prefix of DM, enabling you to identify quickly the records that you have inserted just in case you want to get rid of them manually later.

Because the MAX function you use is an aggregate function (meaning that it is a function that works on groups of data), the data is returned without a column name. Therefore, you use the AS keyword in the SELECT statement and tell SQL Server to assign a column name to the value, in this case MaxID. You use a LIKE clause in the SELECT statement to tell SQL Server to search for all values that begin with DM:

Dim maxIdCommand As SqlCommand = New SqlCommand( _ “SELECT MAX(title_id) AS MaxID “ & _

“FROM titles WHERE title_id LIKE ‘DM%’”, objConnection)

537

Chapter 16

This sets up your command object but doesn’t execute it. To execute it, you need to open the connection and then call one of the SqlCommand execute methods. In this case you use ExecuteScalar:

‘ Open the connection, execute the command objConnection.Open()

Dim maxId As Object = maxIdCommand.ExecuteScalar()

ExecuteScalar is a useful method when you have a database command that returns a single value. Other commands you’ve used so far have returned a whole table of values (you have used these as the SelectCommand of a data adapter), or no values at all (you have executed these with ExecuteNonQuery). In this case, you are interested only in only one number, so you can use ExecuteScalar. This returns the first column of the first row in the result set. In this case, there is only one column and one row, so that is what you get.

You want to check for a Null value returned from the command, so you compare the resulting Object against the Value property of the DBNull class:

‘ If the MaxID column is null...

If maxId Is DBNull.Value Then

If the expression evaluates to True, you have no primary key in the titles table that begins with DM, so you set the initial value of the intMaxID variable to a value of 1000. You choose 1000 because all of the other primary keys contain a numeric value of less than 1000:

‘ Set a default value of 1000...

intMaxID = 1000

If the column value evaluates to False, then you have at least one primary key in the titles table that begins with DM. In this case, you need to obtain the integer portion of this ID to work out which integer to use for your ID. To do this, you must convert your maxId object to a String:

Else

‘ otherwise set the strID variable to the value in MaxID...

strID = CType(maxId, String)

Then you can extract the integer portion of the key by using the Remove method of the string variable, strID. The Remove method removes the specified number of characters from a string. You specify the offset at which to begin removing characters and the number of characters to be removed. This method returns a new string with the removed characters. In this line of code, you are removing the prefix of

DM from the string so that all you end up with is the integer portion of the string. You then use the CType function to convert the string value, which contains a number, to an Integer value, which you place in the intMaxID variable. Finally, you increment it by one to get the integer portion of the ID that you will use:

‘Get the integer part of the string...

intMaxID = CType(strID.Remove(0, 2), Integer)

‘Increment the value...

intMaxID += 1 End If

After you get the integer part, you build a new primary key in the strID variable by concatenating the numeric value contained in the intMaxID variable with the prefix DM:

538

Database Programming with SQL Server and ADO.NET

‘ Finally, set the new ID...

strID = “DM” & intMaxID.ToString

Next, you build the SQL statements to insert a new row of data into the titles and titleauthor tables. If you look closely, there are two separate INSERT statements in the CommandText property of your objCommand object. The two separate INSERT statements are separated by a semicolon, which enables you to concatenate multiple SQL statements. The SQL statements that you build use placeholders that will get filled in by the SqlParameter objects.

Note that because of the relationship between the titles table and the authors table, you must first insert a new title for an author into the titles table and then insert the relationship between the title and the author in the titleauthor table. You’ll notice that your INSERT statements are specifying the columns that you want to insert data into and then the values that are to be inserted, some of which are represented by placeholders.

You have seen the properties of the SqlCommand object before. This time, however, you are using properties rather than the constructor. You set the Connection property to a SqlConnection object and then set the CommandText property to the SQL string that you want executed, in this case, the two separate INSERT statements:

objCommand.Connection = objConnection objCommand.CommandText = “INSERT INTO titles “ & _

“(title_id, title, type, price, pubdate) “ & _ “VALUES(@title_id,@title,@type,@price,@pubdate);” & _

“INSERT INTO titleauthor (au_id, title_id) VALUES(@au_id,@title_id)”

You then add entries to the Parameters collection property for each of your placeholders in the preceding SQL statements. Where the same parameter name is used twice in the CommandText property — as title_id is here — you need only one SqlParameter object:

‘Add parameters for the placeholders in the SQL in the

‘CommandText property...

‘Parameter for the title_id column...

objCommand.Parameters.AddWithValue (“@title_id”, strID)

‘Parameter for the title column...

objCommand.Parameters.AddWithValue (“@title”, txtBookTitle.Text)

‘Parameter for the type column objCommand.Parameters.AddWithValue (“@type”, “Demo”)

‘Parameter for the price column...

objCommand.Parameters.AddWithValue _

(“@price”, txtPrice.Text).DbType = DbType.Currency

‘Parameter for the pubdate column objCommand.Parameters.AddWithValue (“@pubdate”, Date.Now)

‘Parameter for the au_id column...

objCommand.Parameters.AddWithValue (“@au_id”, BindingContext _ (objDataView).Current(“au_id”))

539

Chapter 16

For the @title_id parameter, you use the strID variable that you created and set earlier in this method. For the @title parameter, you use the text in the Book Title text box entered by the user. For the @price parameter, you use the text in the Price text box. However, the Text property is a String. SQL Server cannot automatically convert between a String and a Currency data type, so you specify that the parameter is of the DbType.Currency data type.

For @au_id you need to use the ID of the currently selected author. There are no bound controls for the au_id column, so you need to use some code to obtain the value. Take a close look at that particular statement:

BindingContext(objDataView).Current(“au_id”)

Here you are getting the form’s BindingContext for the objDataView data source, which is the one you’re using for all of your bound controls. When you’re accessing a DataView through Binding Context, the Current property returns a DataRowView object. This object represents the view of the particular row that the user is currently looking at. You are then able to select a particular column from that row, thus giving you a specific value. Here, of course, you are obtaining the au_id column.

The remaining parameters mark that the new record is a Demo record and timestamp the record with the current date and time:

‘Parameter for the type column objCommand.Parameters.AddWithValue (“@type”, “Demo”)

‘Parameter for the pubdate column objCommand.Parameters.AddWithValue (“@pubdate”, Date.Now)

After you add all your parameters, you execute the command using the ExecuteNonQuery method. This causes your SQL statements to be executed and the data inserted. After your new data is inserted, you close the database connection.

This is the one spot in your code that is really subject to failure, so very basic error handling is included here. You execute your INSERT statement inside the Try block of your error handler, and if an error is encountered, the code in the Catch block will be executed. The code there simply displays a message box that shows the error encountered:

‘ Execute the SqlCommand object to insert the new data...

Try

objCommand.ExecuteNonQuery() Catch SqlExceptionErr As SqlException

MessageBox.Show(SqlExceptionErr.Message) Finally

‘ Close the connection...

objConnection.Close() End Try

Then the FillDataSetAndView and BindFields procedures are called to reload the DataView object and to clear and rebind your controls. This ensures that you get all new data added, updated, or deleted in the tables in SQL Server.

540

Database Programming with SQL Server and ADO.NET

You then reposition the DataView object back to the record that was being displayed by setting the Position property of the CurrencyManager using the intPosition variable. This variable was set using the current record position at the beginning of this procedure.

The position that you set here is only approximate. It does not take into account any records that have been inserted or deleted by someone else or you. It is possible that the title you just inserted for a specific author could be returned prior to the title that was displayed before. If you need more detailed control over the actual record position, you need to add more code to handle finding and displaying the exact record that was displayed; however, this is beyond the scope of this book.

After you reposition the record that is being displayed, you call the ShowPosition procedure to show the current record position.

Finally, you display a message in the status bar indicating that the record has been added.

In the next Try It Out, you code the btnUpdate_Click procedure. This procedure is a little simpler because all you need to do is update existing records in the titles table. You do not have to add any new records, so you do not have to select any data to build a primary key.

Try It Out

Updating Records

1.To the btnUpdate_Click event procedure, add the following highlighted code:

Private Sub btnUpdate_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles btnUpdate.Click

‘Declare local variables and objects...

Dim intPosition As Integer

Dim objCommand As SqlCommand = New SqlCommand()

‘Save the current record position...

intPosition = objCurrencyManager.Position

‘Set the SqlCommand object properties...

objCommand.Connection = objConnection objCommand.CommandText = “UPDATE titles “ & _

“SET title = @title, price = @price WHERE title_id = @title_id” objCommand.CommandType = CommandType.Text

‘Add parameters for the placeholders in the SQL in the

‘CommandText property...

‘Parameter for the title field...

objCommand.Parameters.AddWithValue (“@title”, txtBookTitle.Text)

‘Parameter for the price field...

objCommand.Parameters.AddWithValue (“@price”, txtPrice.Text).DbType _

=DbType.Currency

‘Parameter for the title_id field...

541

Chapter 16

objCommand.Parameters.AddWithValue _

(“@title_id”, BindingContext(objDataView).Current(“title_id”))

‘Open the connection...

objConnection.Open()

‘Execute the SqlCommand object to update the data...

objCommand.ExecuteNonQuery()

‘Close the connection...

objConnection.Close()

‘Fill the DataSet and bind the fields...

FillDataSetAndView()

BindFields()

‘Set the record position to the one that you saved...

objCurrencyManager.Position = intPosition

‘Show the current record position...

ShowPosition()

‘ Display a message that the record was updated...

ToolStripStatusLabel1.Text = “Record Updated” End Sub

2.Run your project. You can update the price of the book that you have just added, or you can update the price of another book. Choose a book, change the price in the Price field, and then click the Update button.

Once the record has been updated, you will see the appropriate message in the status bar and the record will still be the current record, as shown in Figure 16-12.

Figure 16-12

542

Database Programming with SQL Server and ADO.NET

How It Works

As always, the first thing that you want to do is declare your variables and objects. You need one variable to save the current record position and one object for the SqlCommand object. Next, you save the current record position just as you did in the last procedure.

By adding the following code, you set the Connection property of the SqlCommand object using your objConnection object. Then you set the CommandText property using a SQL string. The SQL string here contains an UPDATE statement to update the title and price columns in the titles table. Notice that there are three placeholders in this UPDATE statement. Two placeholders are for the title and price, and one is for the title_id in the WHERE clause:

‘ Set the SqlCommand object properties...

objCommand.Connection = objConnection objCommand.CommandText = “UPDATE titles “ & _

“SET title = @title, price = @price WHERE title_id = @title_id” objCommand.CommandType = CommandType.Text

Again, after you set the CommandText property, you set the CommandType property to indicate that this is a SQL string.

You need to add the appropriate parameters to the Parameters collection. The first parameter that you add is for the title column in your UPDATE statement. The title of the book is coming from the Text property of the txtBookTitle text box on your form.

The second parameter is for the price in your UPDATE statement. This parameter will be used to update the price of a book, and the data is coming from the txtPrice text box on your form. Once again, you need to set the DbType explicitly for this parameter.

This last parameter was for your WHERE clause in the UPDATE statement. The data for the Value property comes directly from the form’s BindingContext, as the au_id did in the Adding Records example.

The rest of the procedure is similar to the btnAdd_Click event procedure.

You code the final procedure, btnDelete_Click, in the next Try It Out.

Try It Out

Deleting Records

1.To include delete functionality in your project, add the following highlighted code to the btnDelete_Click event procedure:

Private Sub btnDelete_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles btnDelete.Click

‘Declare local variables and objects...

Dim intPosition As Integer

Dim objCommand As SqlCommand = New SqlCommand()

‘Save the current record position - 1 for the one to be

‘deleted...

intPosition = Me.BindingContext(objDataView).Position - 1

‘ If the position is less than 0 set it to 0...

543

Chapter 16

If intPosition < 0 Then intPosition = 0

End If

‘Set the Command object properties...

objCommand.Connection = objConnection objCommand.CommandText = “DELETE FROM titleauthor “ & _

“WHERE title_id = @title_id;” & _

“DELETE FROM titles WHERE title_id = @title_id”

‘Parameter for the title_id field...

objCommand.Parameters.AddWithValue _

(“@title_id”, BindingContext(objDataView).Current(“title_id”))

‘Open the database connection...

objConnection.Open()

‘Execute the SqlCommand object to update the data...

objCommand.ExecuteNonQuery()

‘Close the connection...

objConnection.Close()

‘Fill the DataSet and bind the fields...

FillDataSetAndView()

BindFields()

‘Set the record position to the one that you saved...

Me.BindingContext(objDataView).Position = intPosition

‘Show the current record position...

ShowPosition()

‘ Display a message that the record was deleted...

ToolStripStatusLabel1.Text = “Record Deleted” End Sub

2.That’s it for this project, so test this newest functionality. Run your project, choose any book that you want to delete, and then click the Delete button. Keep in mind, though, that the pubs database is a sample database for everyone to use, and it’s probably a good idea to delete a book that you have added. Before you delete a book, however, take note of the record count that is displayed on the form. (See Figure 6-13.) You may see an error because of a constraint in the database. This is because there is sales data for this book. Find the book you added and it will not have sales data associated with it.

After the delete has been performed, you will see one less record in the record count on the form.

How It Works

This procedure is a little more involved than the btnUpdate_Click procedure, because of the relationship of titles to authors. Remember that there is a relationship table to join authors and titles. You must delete the row in the titleauthor relationship table before you can delete the row of data in the titles table. Therefore, you need two DELETE statements in your SQL string.

544

Database Programming with SQL Server and ADO.NET

Figure 16-13

Notice that this time after you declare your variables, you specify the Position property minus 1. This will allow for the user being on the last record and deleting it. You have also allowed for the user being on the first record as you check the value of the intPosition variable. If it is less than 0, you know that the user was on the first record, so you set it to 0; this means that when you restore the record position later, it will once again be on the first record.

Notice also that you have not used the CurrencyManager object this time. Instead, you used the Binding Context object and specified the objDataView object as the object to be manipulated. Remember that the BindingContext object is automatically part of the form, and there is nothing you need to do to have it added. The reason for using the BindingContext object here is to demonstrate how to use it and so that you know that you do not have to use the CurrencyManager object to navigate the records contained in the objDataView:

‘Declare local variables and objects...

Dim intPosition As Integer

Dim objCommand As SqlCommand = New SqlCommand()

‘Save the current record position - 1 for the one to be

‘deleted...

intPosition = Me.BindingContext(objDataView).Position - 1

‘ If the position is less than 0 set it to 0...

If intPosition < 0 Then intPosition = 0

End If

When you set the properties of your SqlCommand object, the SQL string specified in the CommandText property contained two DELETE statements separated by a semicolon. The first DELETE statement deletes the relationship between the titles and authors tables for the book being deleted. The second DELETE statement deletes the book from the titles table:

545