Keeping Track of Books Using VB (Matt Gertz)

Keeping Track of Books Using VB (Matt Gertz)

Rate This
  • Comments 31

I have a confession to make.  I’m not a database guy.  I’ve done a lot of varied coding over my career, but somehow I’ve never been directly involved in the guts of database coding.  Whenever my code needed some sort of data-caching mechanism, I’ve tended to “roll my own,” as the saying goes.  Therefore, when we first started working on Linq for VS2008, I was quite excited as it was my chance to really learn something about the ins and outs of data programming.  As it happened, within a few weeks of scheduling that, I was promoted from Compiler Lead to Dev Manager, and so while I tried hard to stay on top of the technology, there just wasn’t time for me to really dig into the guts of the system.

Fortunately, SQL Server Compact Edition and Linq have made it easier for me to code a data app, even if I’m not involved with the code itself directly.  I decided to put this to the test this weekend to help me get a handle on my ever-growing book collection.  If I’ve got one bad addiction, it’s got to be the purchasing of way too many books.  From high-brow literature, philosophy, physics texts, and ancient classics all the way to dime-a-dozen science fiction books, I’m just not able to walk out of a bookstore without a full stack of any or all of them.  I’m not even able to avoid the bookstores anymore, thanks to online bookstores being omnipresent, and as a result books are very nearly furniture in my house.  (Sometimes I even manage to find time to read some of them.)

The biggest problem with having a large collection of books is that I’ll sometimes forget which books I already have. Back in the 80’s, I used to keep track of them with a Hypercard stack I’d created for that purpose, but my Macintosh Centris 650 has long since been recycled, and I’d never set anything equivalent up on my successive Windows machines.  So, armed with VS2008, I finally decided to give it a try this weekend.

Creating the Database

I’ve got to say that I really, really like SQL Compact Edition, which is installed by default with VS2008 and which can also be downloaded for free from this site.  It’s perfect for the sort of work that I do, as it stores everything in a file on the local machine and that data is easily portable to other machines.  Setting up a database is also really easy if you don’t have one ready-made, and you can even create it from within Visual Studio:

1.       Launch Visual Studio 2008.

2.       Choose “View” and then “Server Explorer.”

3.       In the resulting tool window, right-click the “Add Connection…” command.

4.       You’ll get a dialog which allows you to choose your data source. 

a.       Choose “SQL Server Compact 3.5 (.NET Framework)” and “My Computer.”

b.      In the “Connection Properties” area, click the “Create…” button to create a new database.

c.       Another dialog will come up, allowing you to name the database file (I named mine “GertzLibrary”), and you can also specify passwords here if you like.  (My database of books not being a private thing, I skipped that step.)  Once you’ve got this all taken care of, press “OK” and your database has been created!

d.      Back on the first dialog, you’ll need to add the password if, if relevant, before pressing “OK.”  Otherwise, just press “OK” and the connection to the database will be created.

5.       Your database will now appear in the Server Explorer tool window.  It’s an empty database, so let’s add a table:

a.       Expand your database and right-click on “Tables,” choosing “Create Table.”

b.      In the resulting dialog, give your table a name (mine is “Books”), and add the column information.  I chose:

                                                               i.       “ISBN” – nvarchar 16, primary key.  (Think of the primary key as the thing which uniquely identifies a record.  ISBN numbers are unique, whereas titles and author names certainly aren’t.)

                                                             ii.      Title – nvarchar 128.

                                                            iii.      Full Title – nvarchar 256.

                                                           iv.      Author – nvarchar 128.

                                                             v.      Publisher – nvarchar 128.

                                                           vi.      Owned – int, no nulls allowed.  This field will tell me whether I own the book or simply want it.  I’ll treat it as a Boolean in my code.

That’s it; the database is complete!  It doesn’t have any records yet, but we’ll get to that later.

Using the Database

The database is now “known” to Visual Studio, but we’re not actually using it yet.  To do that, let’s start by creating a Windows Application – I’ve called mine “VBLibrary.”  You’ll see a tool window near the Solution Explorer called “Data Sources.” (If you can’t find it, you can use the “Data”/”Show Data Sources” command to bring it up.)  In the middle of that tool window, you should see a link called “Add New Data Source” – go ahead and click it. (The command is also available from the “Data” menu.)

You’ll be taken to the “Add Data Source” wizard, which is super easy to use: 

1.       On the first page, make sure that the “Database” object is selected, and click “Next.” 

2.       On the second page, make sure your database is selected in the drop-down and press “Next” again.  You’ll be prompted to add the database file to the project if you like; I selected “No” since I don’t need to distribute my database with my project and prefer to just have one copy on my machine.

3.       Click “Finish” on the final page.

The Data Sources window should now show your table.  To represent the data on the form is really easy – just drag the table to the form!  But before you do that, you’ll want to make a few tweaks.  I prefer standard controls UI instead of using a datagrid (that latter is the default), so let’s select “Books,” click the drop-down that appears next to it, and choose “Details.”  After doing this, you’ll note that the icon has changed for “Books” to something more form-like and less grid-like.  Next, right-click “Books,” and choose “Edit in Data Designer.”  The Data Designer will appear with a representation of your table on it – it allows you to do some customization on how data is actually used in the application. 

1.       Select “Title” in the table, and in the property grid change “NullValue” to “(Empty)” – that is, treat the title as the empty string whenever no data exists for it, instead of throwing an exception.  Repeat this for “Full Title,” “Author,” and “Publisher.” I’m doing this because I don’t necessarily care if they are filled in.

2.       Select “Owned” in the table, and in the property grid make the following changes

a.       Change “DataType” to System.Boolean.  (Once you do this, you’ll see that “Owned” in the Data Sources window has changed to a check-box type.)

b.      Change “DefaultValue” to “True” – most of the entries you’ll make into the database will be books that you own, so that should be made the default.

c.       Change “AllowDBNull” to be “False” – no nulls allowed here, since you either own the book or you don’t.

Now, you can close the Data Designer.  From the Data Sources window, drag the “Books” table to your form, and voila! You’ve got all of your data controls set out for you, with the binding code generated automatically for each.  There’s even a navigation bar automatically generated for you, to allow you to navigate through the records you’ll be creating and accessing.

Cleaning Up the Form

You can drag the controls around and modify them just like any other control.  You can even delete ones you decide aren’t necessary.  For example, I deleted the label “Owned” in front of “Checkbox1” and just changed the text of that checkbox to “Owned” – I personally don’t use labels with checkboxes.

To make data entry easier for the user, you should click each field and, in the property grid, change its MaxLength property to match the length of the data values so that users don’t inadvertently type in more data than the field can hold in the database – our code generator does not do that automatically when creating the controls. 

I also like to keep users from trying to save bogus records.  In this case, a record is bogus if it doesn’t have an ISBN number (since we defined that to be always required when creating the table).  I therefore added the following code to the form (right-click the form, choose “View Code”):

    Private Sub ISBNTextBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) _

Handles ISBNTextBox.TextChanged, BindingNavigatorAddNewItem.MouseUp

        Dim enableButtons As Boolean = (ISBNTextBox.TextLength <> 0)

        Me.BindingNavigatorAddNewItem.Enabled = enableButtons

        Me.BindingNavigatorCountItem.Enabled = enableButtons

        Me.BindingNavigatorMoveFirstItem.Enabled = enableButtons

        Me.BindingNavigatorMoveNextItem.Enabled = enableButtons

        Me.BindingNavigatorMovePreviousItem.Enabled = enableButtons

        Me.BindingNavigatorPositionItem.Enabled = enableButtons

        Me.BooksBindingNavigatorSaveItem.Enabled = enableButtons

    End Sub


Basically, this just determines whether or not the ISBN value is empty, and disables all of the viewer controls except Delete if so – otherwise, it enables them all.  The Handles clause indicates that I check this whenever a new record (and, by default, blank) record is added or whenever the field changes values.  If I don’t disable the controls, the user might try to save or navigate away from an unfinished record, leading to an exception.  (I could just handle the exception, but I prefer to keep users from getting into trouble in the first place.)

Press “F5.”  Your application launches.  You can now add records (using the button that looks like a “plus” sign), save records, and move through records. Your database is updated when you press “Save” in the toolbar.  Cool stuff!

The Missing Linq

I promised there would be a little Linq code in this application, so I’m going to use Linq to generate a report for me.  The idea would be that I could create a list of books that I’m interested in but don’t own, and save that to a file to either view when I’m looking for books online to print to take to the bookstore with me.

Let’s add a button to the form and call it “Generate Report.”  Double-click the button to take us to the click handler for it.  The first thing we’ll need to do is query the database for the books we’re interested in:

Dim unownedBooks = From book In GertzLibraryDataSet.Books _

Where book.Owned = False _

Select book.Author, book.Title


In this line, two interesting things are going on.  First, VB will automatically create (on the fly) a type of object (known as an “anonymous type”) which can hold two strings (“Author” and “Title,” with type information identical to that of the corresponding database values).  Second, it will create an enumerable set of those using only the records that have “Owned” = “False.”

I can then enumerate over these objects and create something human-readable:

Dim reportResults As New List(Of String)

Dim reportLine As String

For Each unownedBook In unownedBooks

reportLine = unownedBook.Author & ", """ & unownedBook.Title & """"




I’ll want to save this report information to a file, and so I’ll need a file dialog to help with that.  From the toolbox, drag a “SaveFileDialog” object to the form – it will appear in the tray at the bottom with the other windowless controls already there.  Select it and in the property grid make the following changes:

·         NameàSaveReportDlg

·         DefaultExtàtxt (this will automatically add a “.txt” extension to the filename if necessary)

·         FileNameàMyReport (the default filename to create; the user can change this)

·         Filterà Text files (*.txt)|*.txt (populates the file type drop-down in the dialog)

·         TitleàSave Report (appears in the title bar of the dialog)

Now, back in the button handler I’ll then add the following code:

        If Me.SaveReportDlg.ShowDialog = Windows.Forms.DialogResult.OK Then

My.Computer.FileSystem.WriteAllText(Me.SaveReportDlg.FileName, _

“Books to Buy” & vbCrLf, False)

My.Computer.FileSystem.WriteAllText(Me.SaveReportDlg.FileName, _

"------------------" & vbCrLf, True)

For Each unownedBook In unownedBooks

My.Computer.FileSystem.WriteAllText(Me.SaveReportDlg.FileName, _

unownedBook.Author & ", """ & unownedBook.Title & """" & vbCrLf


        End If


And I’m done!  If I click F5 and press the report button, I’ll get a report saved to disk which looks something like the following (assuming I’ve added such books to the database):

Books to Buy


Paul Vick, “The Visual Basic .NET Programming Language”

The Unicode Consortium, “The Unicode Standard, Version 5.0”

Matt Gertz, “The Novel I Will Really, Truly, Honestly Write One of These Days (I Mean It)”

Other Ideas

Since starting this hobby project, I’ve learned that there are web services which can return you all of the book data you need, given an ISBN number.  This would make data entry super easy, since you could then just enter the ISBN number from the back of the book and click some button to populate the rest.  I’ve going to give that a try next, and if I can find time to get it working, I’ll report back with the details.

‘Til next time…


Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • oh my god!!!!!IT WORKED!!!!!!!!!!! I can not tell you how much i appreciate this! I've been working on this for MONTHS, and finally it works! now i can distribute it on the internet, THANK YOU!!!!! (plus i can use it as a regular webbrowser!) Is there ANY way i can repay you?

  • Not a problem, that's why we're here. :-)  Glad it works now!


  • (Man, I really feel guilty for asking all this help, but...) I have 2 tiny little problems, not as urgent as the last one. Ok, first of all, I am not sure how to write a line of code that allows the user to delete a bookmark. All I have is the clear all bookmarks code. And second, I made a timer for form1. As the code, I put:

    TextBox1.Text = CType(TabControl1.SelectedTab.Controls.Item(0), WebBrowser).Url.ToString

    (To clarify, Textbox1.text is the textbox to put in the website URL you want to navigate to)

    But, I added more code, so that when I click on the textbox, the timer stops, when I click away, it starts. Anyway, I go to Then, I click on the video. Instead of the textbox still saying, it says the URL of the video. Good so far! Ok, so I open a new tab...then all heck breaks loose, hehe. I get this error message:

    NullReferenceException was unhandled

    Object reference not set to an instance of an object.

    That's why I need help...that error message to you makes sense. To me, it sounds like absolute gibberish :) D'you know what it means?

  • Hi, Jerry,

    (1) Deleting something from a combobox is pretty easy (we're still on the combobox, right?).  You want ComboBox1.Items.RemoveAt(index), where index is the 0-based index of the thing you want to remove.  You can also use the Remove() method if you have a reference to the object.  For example:


    will remove the currently selected item.  

    (2) Three possibilities occur to me:  First, NullReferenceException often means that you've forgotten to use "New" when declaring a variable.  The debugger should be pointing you at teh variable that has the NULL refernence -- check and see if you've forgotten to use a "New."  Second, something in the code could have been set the value of the variable to Nothing, which would produce the same behavior.  Third, you're relying on SelectedTab to have a value -- if SelectedTab has no value (i.e., there is no selected tab), then you'll get this error.  You should always check the value of variables (particularly the ones that might change, like "SelectedTab" or SelectedItem") before trying to use them.

    Hope this helps,


  • P.S.  I accidentally used "RemoveAt" instead of "Remove" in the example -- it should be:


  • ok, problem with the first one. i know how to remove the bookmarks from the combobox, but if i dont remove it from my.settings.bookmarks, it'll reload next time i load up my webbrowser

  • That's correct, you'll need to change the value of the relevant setting to match the state of affairs -- My.Settings.Bookmarks = (whatever function you're using to generate that semi-colon delimited string).


  • Ok, so everything is going well. But, i have an idea for ya, if you ever feel like posting another blog. Try covering the update feature of a program, that way you don't have to go through an ordeal just to get a new version :) .

  • Hi, Jerry.  Actually, I did do a blog on publishing updates -- see if has what you're looking for.  (If not, let me know.)


  • Sorry, no :( i meant as in the application can check for updates, and if it finds one it downloads it without work from you, like that.

  • Ah, you mean ClickOnce deployment -- that's the service which supports this sort of thing.  I actually have a couple of paragraphs on this in Part 9 of the Euchre series.  I didn't write more because, well, there's just not much to write about it.  It just "works" -- you publish (going through an easy-to-use wizard), and then you republish when needed -- anyone who downloaded your app will automatically get the next version they next time they start it.  You can control this experience more from the Publish tab in the project properties, but the default behavior covers the 99% case for most people.

    However, I can certainly do a refresher on this subject in an upcoming blog, which goes into this in more depth!



  • just to clarify, it checks for an update, and then if it finds one it downloads it from a website via FTP. just wanted to be clear :)

  • Pretty much.  It checks for an update from wherever the publisher specifies -- by default, this is the same place it was published, although the publisher has the option to specify a separate location for updates as well.  The location in any event can be either a URL, a net share, or drive location.  But I'll get into that more in the future if/when I have an article on this.


  • Hmm well today, i felt like making a database. So, i came back to this article. Set it up with the form, but with no extra code, and it works. Sorta. Ok, so i run it, then add a record. Just named it, "Hello" and clicked save. Exited, and ran it again. Record is still there! Good so far. So, since it is a record i don't need, i delete it. Then, i click save. This error comes up:

    "InvalidOperationException was unhandled. Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

    The line of code it had the problem with is this:


  • Thanks for the report, Jerry.  I'll take a look at it and loop back when I get a second to check it out.


Page 2 of 3 (31 items) 123