Deleting selected item from a listbox

Question

Miss Confused 0 Newbie Poster

14 Years Ago

Hi
Can anyone have a look at my code and tell me where im going wrong???
What im trying to do is display my details in a listbox then the one that is selected frm the listbox is displayed in the textboxes and when delete button is clicked it is flagged at deleted??? This is what should happen!!:@

I kow there is definitely a problem with this piece of code im setting the index and length to constant numbers so if a different name is clicked it does not work!! is there another way to set this to allow for any length or index position??

CustomerIDTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 4)
        SurnameTextBox.Text = DisplayCustomersListBox.Text.Substring(5, 7)
        ForenameTextBox.Text = DisplayCustomersListBox.Text.Substring(12, 10)
        StreetTextBox.Text = DisplayCustomersListBox.Text.Substring(20, 7)
        TownTextBox.Text = DisplayCustomersListBox.Text.Substring(26, 14)
        CountyTextBox.Text = DisplayCustomersListBox.Text.Substring(39, 8)
        TelephoneTextBox.Text = DisplayCustomersListBox.Text.Substring(47, 1

1)

Here is the rest of my code:

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,Street,Town,County,Telephone 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") & "   "
                            TitleString += dr.Item("Street") & "   "
                            TitleString += dr.Item("Town") & "   "
                            TitleString += dr.Item("County") & "   "
                            TitleString += dr.Item("Telephone") & "   "

                            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)


        CustomerIDTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 4)
        SurnameTextBox.Text = DisplayCustomersListBox.Text.Substring(5, 7)
        ForenameTextBox.Text = DisplayCustomersListBox.Text.Substring(12, 10)
        StreetTextBox.Text = DisplayCustomersListBox.Text.Substring(20, 7)
        TownTextBox.Text = DisplayCustomersListBox.Text.Substring(26, 14)
        CountyTextBox.Text = DisplayCustomersListBox.Text.Substring(39, 8)
        TelephoneTextBox.Text = DisplayCustomersListBox.Text.Substring(47, 11)

        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 numRowsDeletedInteger 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 "
        SQLString += "DeletedFlag= True"        'UPDATES DELETED FLAG TO TRUE
        SQLString += "Where '" & CustomerIDTextBox.Text & "'" & "= CustomerID" & "','" & SurnameTextBox.Text & "'" & "= Surname" & "','" & ForenameTextBox.Text & "'" & "= Forename" & "','" & 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
                    numRowsDeletedInteger = cmd.ExecuteNonQuery()

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

vb.net

4 Contributors
5 Replies
148 Views
5 Days Discussion Span
Latest Post 14 Years Ago Latest Post by Oxiegen

Smith5646 24 Junior Poster in Training

14 Years Ago

Your SQL statement is messed up. Where clauses need "AND"s between comparisons, not commas.

'12345' = CustomerID AND 'Smith' = Surname

Run this in the debugger and break on line 130 (based on the above code) and look at SQLString. Also, before you have problems with it later, your SQLString built in line 82 is wrong too. Select where clauses also need "AND"s between comparisons, not commas.

Oxiegen 88 Basically an Occasional Poster

14 Years Ago

I was thinking, for your first problem.
Because you use a three space divider between the values from the database for each item in the listbox.
Couldn't you use that piece of knowledge for startindex and length in the SubString method?
That way, these values will always be dynamic.

Dim startIindex As Integer = 0
Dim stringLength As Integer = 0
stringLength = DisplayCustomersListBox.Text.IndexOf("   ")
CustomerIDTextBox.Text = DisplayCustomersListBox.Text.Substring(0, stringLength)
' Get the startindex of the next value separated by "   "
startIndex = DisplayCustomersListBox.Text.IndexOf("   ") + 3
' Get the startindex of the following "   ", counting from the previous one
stringLength = DisplayCustomersListBox.Text.IndexOf("   ", startIndex) 
SurnameTextBox.Text = DisplayCustomersListBox.Text.Substring(startIndex, stringLength)
.....

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jigneshk 0 Newbie Poster · Answer 1 · 2010-03-28T11:25:04+00:00

how can i search dropdown in vb.net form?
i used sql server 2005

Miss Confused 0 Newbie Poster · Answer 2 · 2010-03-28T20:53:33+00:00

I was thinking, for your first problem.
Because you use a three space divider between the values from the database for each item in the listbox.
Couldn't you use that piece of knowledge for startindex and length in the SubString method?
That way, these values will always be dynamic.
Dim startIindex As Integer = 0
Dim stringLength As Integer = 0
stringLength = DisplayCustomersListBox.Text.IndexOf("   ")
CustomerIDTextBox.Text = DisplayCustomersListBox.Text.Substring(0, stringLength)
' Get the startindex of the next value separated by "   "
startIndex = DisplayCustomersListBox.Text.IndexOf("   ") + 3
' Get the startindex of the following "   ", counting from the previous one
stringLength = DisplayCustomersListBox.Text.IndexOf("   ", startIndex) 
SurnameTextBox.Text = DisplayCustomersListBox.Text.Substring(startIndex, stringLength)
.....

Thanks
Problem solved!!

Oxiegen 88 Basically an Occasional Poster Featured Poster · Answer 3 · 2010-03-29T01:20:39+00:00

First of, jigneshk. This is not your thread. Start a new one if you have a question.
Second. ComboBox's has two methods called FindString and FindStringExact. The second argument tells the method which item to start with in the zero-based list.

ComboBox1.SelectedIndex = ComboBox.FindString("<string to search for>", 0) 'Finds the first item that starts with the specified string
ComboBox1.SelectedIndex = ComboBox.FindStringExact("<exact string to search for>", 0) 'Finds the first item that matches the specified string

how can i search dropdown in vb.net form?
i used sql server 2005