Wrox Programmer Forums
|
BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5
This is the forum to discuss the Wrox book Beginning Visual Basic 2005 Databases by Thearon Willis; ISBN: 9780764588945
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 4th, 2010, 08:26 PM
Registered User
 
Join Date: Jul 2010
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default Loading ListViews with Blank Fields

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

Last edited by Ray Ahern; July 4th, 2010 at 08:28 PM..
 
Old July 5th, 2010, 08:52 AM
Thearon's Avatar
Wrox Author
 
Join Date: Dec 2003
Posts: 396
Thanks: 0
Thanked 8 Times in 8 Posts
Default

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
The Following User Says Thank You to Thearon For This Useful Post:
Ray Ahern (July 5th, 2010)
 
Old July 5th, 2010, 09:32 AM
Registered User
 
Join Date: Jul 2010
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default Thanks

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
 
Old July 5th, 2010, 10:05 AM
Thearon's Avatar
Wrox Author
 
Join Date: Dec 2003
Posts: 396
Thanks: 0
Thanked 8 Times in 8 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing blank fields when printing a form Wt2010 Access 1 May 18th, 2010 04:25 PM
Problem with blank fields in Shipping Address radrussell BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 November 23rd, 2009 11:57 AM
Access Reports - hiding blank fields Gandalf_the_Grey Access 3 December 12th, 2006 07:58 PM
Blank fields - queries with many tables dvarrin Crystal Reports 0 February 1st, 2005 05:43 AM
Parameters fields coming up blank in report jjshah BOOK: Professional Crystal Reports for VS.NET 0 January 26th, 2005 03:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.