Chapter 6/7 ListView Woes

BigRob · March 3rd, 2006, 05:50 AM

Hi All,

I have completed up to chapter 7 and decided to travel slightly off the beaten track. I thought it would be nice to be able to search for projects by name. With that in mind I created my select query in Access (which works fine) I then added my search box(txtSearch) and button to the toolstrip. I set my button to call my newly created ActionSearch(). I then attempted to create the action using loadprojects() as a template - but I cannot seem to get it to fire. Here is my code as it stands at the moment.

Select Query:

Code:

SELECT ProjectID, ProjectName, ProjectDescription, SequenceNumber, LastUpdateDate
FROM Projects
WHERE ProjectName LIKE [@ProjectName] & "*"
ORDER BY SequenceNumber;

ActionSearch()

Code:

 Private Sub ActionSearch()
        'Declare Variables
        Dim objListViewItem As ListViewItem

        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Get all projects in DataReader object
                objData.SQL = "usp_SelectProjectsByName"
                objData.InitializeCommand()

                'Add the Parameters to the Parameter collection
                objData.AddParameter("@ProjectName", Data.OleDb.OleDbType.VarChar, _
               50, (txtSearch.Text))

                objData.OpenConnection()
                objData.DataReader = objData.Command.ExecuteReader

                'See if any data exists before continuing
                If objData.DataReader.HasRows Then

                    'Clear previous list
                    lvwProjects.Items.Clear()

                    'Process all rows
                    While objData.DataReader.Read()

                        'Create a new ListViewItem
                        objListViewItem = New ListViewItem

                        'Add data to the ListViewItem
                        objListViewItem.Text = objData.DataReader.Item("ProjectName")
                        objListViewItem.Tag = objData.DataReader.Item("ProjectID")

                        'Add the sub items to the ListView item
                        objListViewItem.SubItems.Add(objData.DataReader.Item _
                        ("ProjectDescription"))
                        objListViewItem.SubItems.Add(objData.DataReader.Item _
                        ("SequenceNumber"))
                        objListViewItem.SubItems.Add(Format(objData.DataReader. _
                        Item("LastUpdateDate"), "g"))

                        'Add the ListViewItem to the ListView control
                        lvwProjects.Items.Add(objListViewItem)

                    End While
                End If

                objData.DataReader.Close()
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using

        'Cleanup
        objListViewItem = Nothing
    End Sub

Apologies for the length of the post and thanks in advance.

Rob

Thearon · March 4th, 2006, 11:13 AM

Where in your code do you call the ActionSearch procedure? Is that part of code ever getting executed?

Thearon

BigRob · March 5th, 2006, 06:48 AM

Hi Thearon,

I added the txtSearch box and btnSearch Button to the tool strip and then added the following code to the 'Menu & Toolbar procedures"

Code:

 Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        ActionSearch()
    End Sub

Thanks for responding and I appreciate any assistance you can give.

Rob

Thearon · March 6th, 2006, 07:10 PM

Rob,

Did you set a breakpoint in the btnSearch_Click procedure? Are you reaching this point in your code?

Thearon

BigRob · March 7th, 2006, 07:24 AM

Hi again Thearon,

I tried setting a breakpoint and the button fired the ActionSearch() fine. Upon further investigation I have however, discovered where it appears to stop. When I reach the following point nothing else happens.

Code:

If objData.DataReader.HasRows Then

                    'Clear previous list
                    lvwProjects.Items.Clear()

                    'Process all rows
                    While objData.DataReader.Read()

                        'Create a new ListViewItem
                        objListViewItem = New ListViewItem

                        'Add data to the ListViewItem
                        objListViewItem.Text = objData.DataReader.Item("ProjectName")
                        objListViewItem.Tag = objData.DataReader.Item("ProjectID")

                        'Add the sub items to the ListView item
                        objListViewItem.SubItems.Add(objData.DataReader.Item _
                        ("ProjectDescription"))
                        objListViewItem.SubItems.Add(objData.DataReader.Item _
                        ("SequenceNumber"))
                        objListViewItem.SubItems.Add(Format(objData.DataReader. _
                        Item("LastUpdateDate"), "g"))

                        'Add the ListViewItem to the ListView control
                        lvwProjects.Items.Add(objListViewItem)

                    End While
                End If

                objData.DataReader.Close()
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using

        'Cleanup
        objListViewItem = Nothing
    End Sub

Any ideas? Apologies for my ineptness, but before VS2005, the last time I did any programming was on my ZX81. :D

Thanks again for your help.

Rob

Thearon · March 7th, 2006, 08:18 PM

Rob,

Try setting a break point at

If objData.DataReader.HasRows Then

When you encounter the break point then query the objData.DataReader to check it's status. Ensure it is instantiated and not equal to Nothing.

Thearon

BigRob · March 8th, 2006, 11:41 AM

Hi Thearon,

I followed your advice and have found that the DataReader is not receiving any data. I've checked the state of the connection and its definately open. I then tried adding a value to the parameter directly within the code and still nothing happened. I can only assume that the query is not being fired correctly, but I can't see why.

Any ideas?

Thanks, Rob

Thearon · March 9th, 2006, 08:31 AM

What does the code for the DataReader look like? It sounds like maybe it is not be initialized or not getting the command set to execute.

Thearon