All along I felt that the problem was a configuration issue and alas I found the root cause to the program failures! The connection string specified in the application configuration XML file (app.config) did not match the database file location specified in the database properties (PO_Data.mdf) connection string. Because the configuration file had a different path but the same database name the error message indicated âthe same name exists, or specified file cannot be opened, or it is located on UNC share."
The problem exists in the VB
Express Code download examples beginning with Chapter 7; this is the reason that the List Box fails to populate with names even though the database is connected. However it is also the reason that the example code would not run in Chapter 8 and Chapter 9 and the problem may carry over to the later chapters as well.
Listed below are the two conflicting connection string specifications:
[u]App.Config XML File Connection String</u>:
<add name="Personal_Organizer.Settings.PO_DataConnectio nString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="H:\Wile y-2\Wrox Visual Basic Express 2005 Starter Kit\Code\Chapter 9\Personal Organizer Database File\PO-Data.mdf";Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
[u]PO_Data.mdf Properties Connection String:</u>
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\User Name\My Documents\Visual Studio 2005\VB_2005_Express_Code\Chapter 09\Personal Organizer Database File\PO-Data.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True
It appears that the example code was developed on a machine with an âHâ drive as the source of the sample files. To resolve the problem I copied over the path in use on my system completely replacing all the text between H:\Wiley-2 and PO_Data.mdf, as shown in the bold text highlighted in the specifications listed above. This action eliminated the SQL Exception error at runtime.
It should be noted however that fixing the connection specification issue does not resolve all remaining code issues. I will provide two examples, the first failure related to null entries in the database when populating the Person Details control in Chapter 7.
Null entries in the database example prevent the Person Details control from being populated and an exception error to be thrown. Both the birth date and the gift categories generate an exception that states âConversion from type 'DBNull' to type 'Date' is not validâ for database entries with no entry in the field. I resolved this error with the following code revisions highlighted and in bold-italic below:
Public Function GetPerson(ByVal PersonID As Integer) As Person
Dim GetPersonAdapter As New _PO_DataDataSetTableAdapters.PersonTableAdapter
Dim GetPersonTable As New _PO_DataDataSet.PersonDataTable
Dim PersonDataView As DataView = GetPersonTable.DefaultView
PersonDataView.RowFilter = "ID = " + PersonID.ToString
If .Count > 0 Then
Dim objPerson As New Person
objPerson.ID = CType(.Item("ID"), Integer)
objPerson.FirstName = .Item("NameFirst").ToString.Trim
objPerson.LastName = .Item("NameLast").ToString.Trim
objPerson.HomePhone = .Item("PhoneHome").ToString.Trim
objPerson.CellPhone = .Item("PhoneCell").ToString.Trim
objPerson.Address = .Item("Address").ToString.Trim
If .Item("DateOfBirth").ToString > "" Then
'objPerson.BirthDate = CType(.Item("DateOfBirth"), Date)
objPerson.BirthDate = CType(.Item("DateOfBirth"), Date).Date
objPerson.BirthDate = CType("1/1/1900", Date)
objPerson.EmailAddress = .Item("EmailAddress").ToString.Trim
objPerson.Favorites = .Item("Favorites").ToString.Trim
If .Item("GiftCategories").ToString > "" Then
'objPerson.GiftCategories = CType(.Item("GiftCategories"), Integer)
objPerson.GiftCategories = .Item("GiftCategories")
objPerson.Notes = .Item("Notes").ToString.Trim
Note that I left the original code statement included but remarked out so that the changes would be obvious. I hope this find helps others as well! I have also communicated these findings to Andrew Parsons.