Loading ListViews with Blank Fields

Ray Ahern · July 4th, 2010, 08:26 PM

Hi

I'm creating a database app based on many of the concepts in the Beginning VB05 Databases book (Similar to TimeTracker - totally different purpose). The MS Access 2003 db that sits under has a list of projects and dates including: Early Start, Baseline Start, Actual Start, Baseline Finish Actual Finish. Of course as some projects may not have actually finished there will be blanks in some fields.

My problems are two:

First Problem:

Using objData to populate the list view of Projects as follows. Any blank dates cause the ListView control to stop populating - it doesn't throw an exception - just stops populating. Am I doing something terribly wrong?

Code:

Get all projects in a DataReader object
                objData.SQL = "usp_ProjectDetails"
                objData.InitializeCommand()
                objData.OpenConnection()
                objData.DataReader = objData.Command.ExecuteReader

                'See if any data exists before continuing
                If objData.DataReader.HasRows Then
                    lvwADProjects.Items.Clear()

                    'Process all rows
                    While objData.DataReader.Read()
                        'create a new ListViewItem
                        objListViewItem = New ListViewItem

                        'add data to the listviewitem


                        objListViewItem.Tag = objData.DataReader.Item("Project_ID")

                        'add the subitems to the listviewitem

                        objListViewItem.Text = objData.DataReader.Item("Project_Title")
                        objListViewItem.SubItems.Add(objData.DataReader.Item("Phase_Title"))
                        objListViewItem.SubItems.Add(objData.DataReader.Item("Project_Status"))
                        objListViewItem.SubItems.Add(objData.DataReader.Item("Early_Finish"))
                        objListViewItem.SubItems.Add(objData.DataReader.Item("SH_Surname"))

                        'add the listviewitem to the listview control

                        lvwADProjects.Items.Add(objListViewItem)

                    End While
                End If

                objData.DataReader.Close()

            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)

Second Problem:

I'm trying to insert projects with dates into the Database. But I consistently get a failed to convert String to DateTime error. This one is driving me batty.

Here's a line of the insertion code:

objData.AddParameter("@Early_Start", OleDb.OleDbType.DBDate, 20, CType(txtADProjectCurrentStart.Text, Date))

The parameter is @Early_Start in the SQL line and the format in Access in DateTime. The SQL usp works fine in Access with the same format data as appears in the text box.

ANY help appreciated as this is stumping me on an otherwise great app!

Cheers

Ray

Thearon · July 5th, 2010, 08:52 AM

Ray,

Are the dates in the database null? You could test them for a null value before loading them into your listview control.

Using VS2005 you can:
If IsDbNull(objData.DataReader.Item("Early_Finish")) Then
objListViewItem.SubItems.Add(String.Empty)
Else
objListViewItem.SubItems.Add(objData.DataReader.It em("Early_Finish"))
End If

Using VS2008 you can:
objListViewItem.SubItems.Add(If IsDbNull(objData.DataReader.Item("Early_Finish")), String.Empty,objData.DataReader.Item("Early_Finish "))

Date is a data type and is not initialized until you initialize it. Use the Now property of the DateTime structure which contains the current date and time on the computer.

Try using this line of code when inserting dates into your database:

objData.AddParameter("@Early_Start", OleDb.OleDbType.DBDate, 20, CType(txtADProjectCurrentStart.Text, Now))

Thearon

Ray Ahern · July 5th, 2010, 09:32 AM

Wow - that was quick coming back! Super impressed :-)

The date fields I'm bringing in are null. Six at a time (Early/Planned, Baseline and Actual for Start and Finish dates), so I'll have to test each individually I guess - but that should work - thank you!

On the second issue though, I don't want the time now. 'Current' Start is actually the date that we currently expect the project to start ... not today's date - a misleading variable name perhaps!

Typically I want to be able to pick up an (Australian format or local format) date e.g. 4/7/2010 ... (happy Independence Day) .... or again that text box may well be blank ... and transfer any result back to the database. I don't quite get the concept of having to initialize the data type. Is that in the declarations?

Not sure if that all makes sense. Hope so.

Kind regards

Ray

Thearon · July 5th, 2010, 10:05 AM

Ray,

You can use the DateTime structure and declare it as an object and set it to the appropriate date and time that you desire and then pass that object as a parameter to your query. Something along the lines of:

Dim objEarlyStartDate AsDateTime = #7/4/2010#

If you need to also set a start time you can use the following format:

Dim objEarlyStartDate AsNewDateTime(2010, 7, 4, 14, 0, 0)

The parameters are year, month, day, hour, minute, second. Notice that you use military time for the hour so that this time would represent 2 PM.

I hope this helps.

Thearon