Tidying up listbox

[Almostgonemad]

Hi im wondering can anyone help me I have all my details displayed in a listbox but when i run the code the listbox just doesnt look good everything continues from left to right across the listbox! as you will see from my code i have CustomerID, Forename, Surname, Street, Town, County, Telephone id like the next customers details to appear on the next line then not continuing along the same line.
I hope this makes sense and someone can help

Thanks
Heres my code:

Public Class AmendView

Private Sub AmendView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DisplayListBox(5)
End Sub
Private Sub DisplayListBox(ByVal num As Integer)


'Loads recordset & outputs to list box

Dim ConnectionString As String
Dim SQLString As String
Dim TitleString As String = " "
Dim conn As System.Data.OleDb.OleDbConnection
Dim dr As System.Data.OleDb.OleDbDataReader
Dim cmd As System.Data.OleDb.OleDbCommand

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & "Opticians.accdb "

conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

'Access the fields
SQLString = "SELECT CustomerID,Surname,Forename,Street,Town,COunty,Telephone FROM CustomerTable "
Try 'was database found etc.

cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
conn.Open()

If (ConnectionState.Open.ToString = "Open") Then

dr = cmd.ExecuteReader()

If dr.HasRows Then
DisplayCustomersAmendViewListBox.Items.Clear()

While dr.Read
If Not IsDBNull(dr.Item("CustomerId")) Then
TitleString += dr.Item("CustomerID") & " "
TitleString += dr.Item("Surname") & " "
TitleString += dr.Item("Forename") & " "
TitleString += dr.Item("Street") & " "
TitleString += dr.Item("Town") & " "
TitleString += dr.Item("County") & " "
TitleString += dr.Item("Telephone") & " "


DisplayCustomersAmendViewListBox.Items.Add(TitleString)

End If
End While
End If
End If

Catch
MessageBox.Show("Error accessing database")
End Try
conn.Close()
DisplayCustomersAmendViewListBox.Items.Add(" ")
DisplayCustomersAmendViewListBox.Items.Add("Count:" & DisplayCustomersAmendViewListBox.Items.Count - 1)

End Sub

[kareninstructor]

You would be better off using a DataGridView. My guess is that the font for your ListBox is causing the data to look bad. If you must stay with a Listbox then change the font to Coutier New but again a DataGridView would make life easier

After executing the reader

dr = cmd.ExecuteReader()

You can load the data into a DataTable

MyDataTable.Load(dr)
MyDataGridView.DataSource = MyDataTable

[kareninstructor]

Just noticed you are not resetting TitleString as I added below

If dr.HasRows Then

   DisplayCustomersAmendViewListBox.Items.Clear()

   While dr.Read
      If Not IsDBNull(dr.Item("CustomerId")) Then
         TitleString += dr.Item("CustomerID") & " "
         TitleString += dr.Item("Surname") & " "
         TitleString += dr.Item("Forename") & " "
         TitleString += dr.Item("Street") & " "
         TitleString += dr.Item("Town") & " "
         TitleString += dr.Item("County") & " "
         TitleString += dr.Item("Telephone") & " "
 
         DisplayCustomersAmendViewListBox.Items.Add(TitleString)

      End If

      TitleString = ""

   End While
End If

[BackWoodsCoder]

or you could fix it by removing the plus sign on the first line when you begin to build the title string. Should be using the & operator as well to concatenate strings.

[Hack]

You would be better off using a DataGridView

Or a ListView...

[kareninstructor]

Or a ListView...

agree...just not a ListBox for this.

[Almostgonemad]

Hi
I reset the title string and now all the entries are displaying in the right order! but now i hav eanother problem when i choose the customer i want to amend the only details that appear are customerid and surname in all the textboxes. they should have all the details in the right textboxes for example customerid in customerid textbox? hope this makes sense can anyone have a look at my code??? pls
Public Class AmendView

Private Sub AmendView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DisplayListBox(10)
CustomerIDTextBox.MaxLength = 5
SurnameTextBox.MaxLength = 30
ForenameTextBox.MaxLength = 30
StreetTextBox.MaxLength = 40
TownTextBox.MaxLength = 40
CountyTextBox.MaxLength = 40
TelephoneTextBox.MaxLength = 15

End Sub
Private Sub DisplayListBox(ByVal num As Integer)


'Loads recordset & outputs to list box

Dim ConnectionString As String
Dim SQLString As String
Dim TitleString As String = " "
Dim conn As System.Data.OleDb.OleDbConnection
Dim dr As System.Data.OleDb.OleDbDataReader
Dim cmd As System.Data.OleDb.OleDbCommand

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & "Opticians.accdb "

conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

'Access the fields
SQLString = "SELECT CustomerID,Surname,Forename,Street,Town,County,Telephone FROM CustomerTable "
Try 'was database found etc.

cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
conn.Open()

If (ConnectionState.Open.ToString = "Open") Then

dr = cmd.ExecuteReader()

If dr.HasRows Then
DisplayCustomersAmendViewListBox.Items.Clear()

While dr.Read
'If Not IsDBNull(dr.Item("CustomerId")) Then
TitleString += dr.Item("CustomerID") & " "
TitleString += dr.Item("Surname") & " "
TitleString += dr.Item("Forename") & " "
TitleString += dr.Item("Street") & " "
TitleString += dr.Item("Town") & " "
TitleString += dr.Item("County") & " "
TitleString += dr.Item("Telephone") & " "


DisplayCustomersAmendViewListBox.Items.Add(TitleString)

'End If


TitleString = " "

End While
End If
End If

Catch
MessageBox.Show("Error accessing database")
End Try
conn.Close()
DisplayCustomersAmendViewListBox.Items.Add(" ")
DisplayCustomersAmendViewListBox.Items.Add("Count:" & DisplayCustomersAmendViewListBox.Items.Count - 1)

End Sub

Private Sub AmendButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AmendButton.Click
Dim ConnectionString As String
Dim SQLString As String
Dim cmd As System.Data.OleDb.OleDbCommand
Dim conn As System.Data.OleDb.OleDbConnection
Dim dr As System.Data.OleDb.OleDbDataReader
'Dim NumRowsAddedInteger As Integer

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & Application.StartupPath & "Opticians.accdb"
conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

CustomerIDTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
SurnameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
ForenameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
StreetTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
TownTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
CountyTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
TelephoneTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)

SQLString = "SELECT * FROM CustomerTable"
SQLString += "Where '" & CustomerIDTextBox.Text & "','" & SurnameTextBox.Text & "','" & ForenameTextBox.Text & "','" & StreetTextBox.Text & "','" & TownTextBox.Text & "','" & CountyTextBox.Text & "','" & TelephoneTextBox.Text & ")" 'SELECT ITEM FROM CAR REG TEXTBOX

Try
conn.Open()
If ConnectionState.Open.ToString = "Open" Then
cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
dr = cmd.ExecuteReader()
If dr.HasRows Then
dr.Read()
If Not IsDBNull(dr.Item("CustomerID")) Then
CustomerIDTextBox.Text = dr.Item("CustomerID").ToString
End If
If Not IsDBNull(dr.Item("Surname")) Then
SurnameTextBox.Text = dr.Item("Surname").ToString
End If
If Not IsDBNull(dr.Item("Forename")) Then
ForenameTextBox.Text = dr.Item("Forename").ToString
End If
If Not IsDBNull(dr.Item("Street")) Then
StreetTextBox.Text = dr.Item("Street").ToString ' ENTERS INFORMATION INTO CORRESPONDING TEXTBOXES
End If
If Not IsDBNull(dr.Item("Town")) Then
TownTextBox.Text = dr.Item("Town").ToString
End If
If Not IsDBNull(dr.Item("County")) Then
CountyTextBox.Text = dr.Item("County").ToString
End If
End If
End If
Catch ex As Exception
End Try
End Sub
End Class

[kareninstructor]

First off you really need to get educated outside of a project on how to work with data. For instance the following SQL statement has a WHERE clause that simple does not make sense.

SQLString = "SELECT * FROM CustomerTable"
SQLString += "Where '" & CustomerIDTextBox.Text & "','" & SurnameTextBox.Text & "','" & ForenameTextBox.Text & "','" & StreetTextBox.Text & "','" & TownTextBox.Text & "','" & CountyTextBox.Text & "','" & TelephoneTextBox.Text & ")" 'SELECT ITEM FROM CAR REG TEXTBOX

The best method to locate a customer in your case is to search on the customer identifier assuming the identifier is unique, otherwise I highly suggest fixing it so the customer identifier is unique. When first populating your data consider placing the ID in the value member of the ListBox so then when needed to search for a customer you have the ID to use for searching.

Where syntax http://www.1keydata.com/sql/sqlwhere.html

See attachment (VS2008) for one idea on obtaining an ID and locating a record.

[Almostgonemad]

There doesnt seem to be a problem with this where clause its working perfectly for what i want it to do in the project. The problem seems to be in these lines of code
CustomerIDTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
SurnameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
ForenameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
StreetTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
TownTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
CountyTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
TelephoneTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
when i change the index position and the length it messes up everything that is in the textboxes for example i can get them working for one of the customers but if i choose a different customer all the data is mixed up in the textboxes

[kareninstructor]

There doesnt seem to be a problem with this where clause its working perfectly for what i want it to do in the project. The problem seems to be in these lines of code
CustomerIDTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
SurnameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
ForenameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
StreetTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
TownTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
CountyTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
TelephoneTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
when i change the index position and the length it messes up everything that is in the textboxes for example i can get them working for one of the customers but if i choose a different customer all the data is mixed up in the textboxes

Have you looked at the model I demo in the attached project? This has nothing to do with if your query works on not but instead how to track the ID.

In regards to your query, you are doing way too much to locate a row even if it works it needs to be simplified as suggested in the attached project.

[kareninstructor]

This makes no sense either

CustomerIDTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
SurnameTextBox.Text =    DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
ForenameTextBox.Text =   DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
StreetTextBox.Text =     DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
TownTextBox.Text =       DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
CountyTextBox.Text =     DisplayCustomersAmendViewListBox.Text.Substring(0, 11)
TelephoneTextBox.Text =  DisplayCustomersAmendViewListBox.Text.Substring(0, 11)

Each control is being set to the same value

[Almostgonemad]

the project you attached doesnt open on my computer.
As i explained when i change the values to match one entry it will work and display that entry perfectly in the textboxes but if i select a different customer it wont display them is there a way around this?
Heres what ive got now
CustomerIDTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 4)
SurnameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(5, 7)
ForenameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(12, 8)
StreetTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(20, 7)
TownTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(26, 11)
CountyTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(37, 7)
TelephoneTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(44, 9)

[cicatrix]

Can you enclose your code into [code]your code[/code] tags.

[kareninstructor]

the project you attached doesnt open on my computer.
As i explained when i change the values to match one entry it will work and display that entry perfectly in the textboxes but if i select a different customer it wont display them is there a way around this?
Heres what ive got now
CustomerIDTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(0, 4)
SurnameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(5, 7)
ForenameTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(12, 8)
StreetTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(20, 7)
TownTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(26, 11)
CountyTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(37, 7)
TelephoneTextBox.Text = DisplayCustomersAmendViewListBox.Text.Substring(44, 9)

If you can not open the project then you are not using VS2008 or VS2010 but must be VS2003 or VS2005. I assume that if a poster does not indicate which version of VS they have then assume VS2008 or higher.

So open the form with notepad and examine the code (it will work in VS2005). I can honestly tell you that you really need to rethink how you are going about this since it's not working a new path is needed.

[Almostgonemad]

Public Class DeleteCustomer

    Private Sub DeleteCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DisplayItems(5) ' Loads Method to display the list
         End Sub
    Private Sub DisplayItems(ByVal num As Integer)


        'Loads recordset & outputs to list box

        Dim ConnectionString As String
        Dim SQLString As String
        Dim TitleString As String = "   "
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dr As System.Data.OleDb.OleDbDataReader
        Dim cmd As System.Data.OleDb.OleDbCommand

        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "

        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

        'Access the fields
        SQLString = "SELECT CustomerID,Surname,Forename FROM CustomerTable"
        Try 'was database found etc.
            conn.Open()
            If ConnectionState.Open Then
                cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
                dr = cmd.ExecuteReader()

                If dr.HasRows Then
                    DisplayCustomersListBox.Items.Clear()

                    While dr.Read
                        If Not IsDBNull(dr.Item("CustomerId")) Then
                            TitleString += dr.Item("CustomerID") & "   "
                            TitleString += dr.Item("Surname") & "   "
                            TitleString += dr.Item("Forename") & "   "

                            DisplayCustomersListBox.Items.Add(TitleString)

                        End If

                        TitleString = "  "

                    End While
                End If
                dr.Close()
            End If

        Catch
            MessageBox.Show("Error accessing database")
        End Try
        conn.Close()
        'DisplayCustomersListBox.Items.Add(" ")
        'DisplayCustomersListBox.Items.Add("Count:" & DisplayCustomersListBox.Items.Count - 1)

    End Sub

    Private Sub DisplayCustomersListbox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DisplayCustomersListBox.SelectedIndexChanged
        Dim ConnectionString As String
        Dim SQLString As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dr As System.Data.OleDb.OleDbDataReader
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians"
        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

        StreetTextBox.Text = DisplayCustomersListBox.Text.Substring(20, 7)
        TownTextBox.Text = DisplayCustomersListBox.Text.Substring(26, 11)
        CountyTextBox.Text = DisplayCustomersListBox.Text.Substring(37, 7)
        TelephoneTextBox.Text = DisplayCustomersListBox.Text.Substring(44, 9)
        SQLString = "SELECT * FROM CustomerTable "
        SQLString += "Where '" & StreetTextBox.Text & "','" & TownTextBox.Text & "','" & CountyTextBox.Text & "','" & TelephoneTextBox.Text & ")"

        Try
            conn.Open()
            If ConnectionState.Open.ToString = "Open" Then
                cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
                dr = cmd.ExecuteReader()
                If dr.HasRows Then
                    dr.Read()
                    If Not IsDBNull(dr.Item("Street")) Then
                        StreetTextBox.Text = dr.Item("Street").ToString
                    End If
                    If Not IsDBNull(dr.Item("Town")) Then
                        TownTextBox.Text = dr.Item("Town").ToString
                    End If
                    If Not IsDBNull(dr.Item("County")) Then
                        CountyTextBox.Text = dr.Item("County").ToString
                    End If
                    If Not IsDBNull(dr.Item("Telephone")) Then
                        TelephoneTextBox.Text = dr.Item("Telephone").ToString
                    End If

                    If Not IsDBNull(dr.Item("DeletedFlag")) Then
                        DeleteCheckBox.Checked = dr.Item("DeletedFlag").ToString
                    End If
                End If
            End If
        Catch ex As Exception
        End Try
    End Sub

    Private Sub DeleteCustomerButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteCustomerButton.Click
        Dim ConnectionString As String
        Dim SQLString As String
        Dim whichButtonDialogResult As DialogResult
        Dim Street As String = ""
        Dim Town As String = ""
        Dim County As String = ""
        Dim Telephone As Integer = (0)
        Dim numRowsAddedInteger As Integer
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim conn As System.Data.OleDb.OleDbConnection
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "
        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
        SQLString = "UPDATE CustomerTable Set "
        SQLString += "DeletedFlag= True"        'UPDATES DELETED FLAG TO TRUE
        SQLString += "Where '" & StreetTextBox.Text & "'" & "= Street" & "','" & TownTextBox.Text & "= Town" & "','" & CountyTextBox.Text & "= County" & "','" & TelephoneTextBox.Text & "= Telephone"
        cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
        whichButtonDialogResult = MessageBox.Show("Are You Sure You Want To Mark Record As Deleted?", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If whichButtonDialogResult = DialogResult.Yes Then  'VERIFIES SELECTION
            Try
                conn.Open()
                If ConnectionState.Open.ToString = "Open" Then
                    numRowsAddedInteger = cmd.ExecuteNonQuery()

                    MessageBox.Show("Number of rows deleted :" + numRowsAddedInteger.ToString)
                End If
            Catch                               'CONFIRMS DELETION
                MessageBox.Show("Number of rows deleted :" + numRowsAddedInteger.ToString)
            End Try
            conn.Close()
            Me.Focus()
            DisplayItems(5)
        Else
        End If
    End Sub
End Class

[kareninstructor]

This is a VS2005 version since you could not open the VS2008 version. I also zipped the file on a completely different machine so there should be no issues unless you are using a version of VS less than 2005.

[Almostgonemad]

Hi
thanks for that i do have vb 2008 so had to convert that but for some reason it worked and the last one didnt!!!
There is alot of code i can use in that thanks alot!
Fingers crossed from here on!!