Populating list box from database problems

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

39 Replies - 1284 Views - Last Post: 22 January 2014 - 04:09 PM Rate Topic: -----

#31 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9059
  • View blog
  • Posts: 34,018
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 07 January 2014 - 12:31 PM

So.. where's your delete code?

Unless you do some serious binding you would need to make a direct DB hit (the non-query-execute) kind where you use a SQL DELETE statement.. and feed it the key for the row you want to delete.
Was This Post Helpful? 0
  • +
  • -

#32 WarMacheen  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 51
  • Joined: 08-February 11

Re: Populating list box from database problems

Posted 07 January 2014 - 01:19 PM

View Postmodi123_1, on 07 January 2014 - 12:31 PM, said:

So.. where's your delete code?

Unless you do some serious binding you would need to make a direct DB hit (the non-query-execute) kind where you use a SQL DELETE statement.. and feed it the key for the row you want to delete.


I want to delete all of the rows in the db, but on a separate button press. For now this code is simply returning what was captured at the time. Any new database additions are not reflected in the listbox when the code is executed

 Dim Builder As New OleDb.OleDbConnectionStringBuilder With
                    {
                        .Provider = "Microsoft.ACE.OLEDB.12.0",
                        .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "CalcDB"),
                        .PersistSecurityInfo = False
                    }


        Try


            Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                  "Data Source=|DataDirectory|\CalcDB.accdb"
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}


                    Dim comm As New OleDbCommand("SELECT TOP 10 Operation FROM CalcOps", cn)


                    comm.CommandType = CommandType.Text
                    cmd.Connection.Open()
                    comm.ExecuteNonQuery()
                    cmd.Connection.Close()

                    Dim ds As New DataSet




                    If ds.Tables.Count > 0 AndAlso ds.Tables("CalcOps").Rows.Count > 0 Then
                        ListBox1.DataSource = ds.Tables("CalcOps")
                    End If

                End Using
            End Using


        Catch ex As Exception
             MessageBox.Show("Error: " + ex.Message)
        End Try


    End Sub

Was This Post Helpful? 0
  • +
  • -

#33 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9059
  • View blog
  • Posts: 34,018
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 07 January 2014 - 01:22 PM

What? I would suggest taking a break and reading over the tutorials I linked in post #10.

Quote

I want to delete all of the rows in the db,

okay.. so no where clause needed in your DELETE sql statement.

Quote

Any new database additions are not reflected in the listbox when the code is executed

Yes.. unless you refreshed the data - what you captured, and sits in memory with the dataset, does not reflect any DB changes.
Was This Post Helpful? 0
  • +
  • -

#34 WarMacheen  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 51
  • Joined: 08-February 11

Re: Populating list box from database problems

Posted 07 January 2014 - 01:42 PM

Thank you, I am indeed going to take a break as I've been banging my head against the wall with this all day. I will read over things again tonight and bang my head some more later.

This post has been edited by andrewsw: 07 January 2014 - 01:51 PM

Was This Post Helpful? 0
  • +
  • -

#35 WarMacheen  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 51
  • Joined: 08-February 11

Re: Populating list box from database problems

Posted 09 January 2014 - 08:31 AM

After taking a break from this and coming back last night, this morning the application is finally starting to come together. I fixed and cleaned up some things. I would greatly appreciate you guys' insight on why these problems are creeping up. Final problems I'm trying to resolve include:

  • Save operation button doesn't work after the Retrieve button populates ListBox1 with database items

  • Clear button doesn't work after Retrieve button populates ListBox1. Error = Items collection cannot be modified when the DataSource property is set. I understand this error, but am not sure how to go about correcting it

  • ListBox1 double click event doesn't work after Retrieve button populates the list. The list was supposed to be populated, then an operation could be double clicked and it would send the first and second number to the corresponding text boxes. This event does work as long as the Retrieve button is never used.

  • Save to DB button doesn't work after Retrieve button is used. Error = "Conversion from type DataRowView to type String is not valid"


I think fixing the clear button might resolve a lot of the issues, but still won't help with the ListBox1 double click event not working after a retrieval.

This is the code in it's entirety so far.

Option Explicit On
Option Strict On
Option Infer Off


Imports System.Data.OleDb


Public Class frmMain

    Private Sub ButtonAdd_Click(sender As Object, e As EventArgs) Handles m3.Click
        'Adds the numbers together and the tag remembers the operator

        If NumOne.Text = ("") Then
            MessageBox.Show("Please enter the first number")
            NumOne.Focus()
        ElseIf NumTwo.Text = ("") Then
            MessageBox.Show("Please enter the second number")
            NumTwo.Focus()
        End If

        Dim m As New MathOp2()
        Dim r As Integer

        Try

            r = CInt(CStr(m.Add(CInt(NumOne.Text), CInt(NumTwo.Text))))

            Result.Text = CStr(r)

            ListBox1.Tag = "+"

        Catch ex As Exception

        End Try

    End Sub

    Private Sub ButtonSub_Click(sender As Object, e As EventArgs) Handles ButtonSub.Click
        'Subtracts the numbers

        If NumOne.Text = ("") Then
            MessageBox.Show("Please enter the first number")
            NumOne.Focus()
        ElseIf NumTwo.Text = ("") Then
            MessageBox.Show("Please enter the second number")
            NumTwo.Focus()
        End If

        Dim m As New MathOp2()
        Dim r As Integer

        Try
            r = CInt(CStr(m.Subtract(CInt(NumOne.Text), CInt(NumTwo.Text))))

            Result.Text = CStr(r)

            ListBox1.Tag = "-"
        Catch ex As Exception

        End Try
    End Sub

    Private Sub ButtonMul_Click(sender As Object, e As EventArgs) Handles ButtonMul.Click
        'Multiplies the numbers

        If NumOne.Text = ("") Then
            MessageBox.Show("Please enter the first number")
            NumOne.Focus()
        ElseIf NumTwo.Text = ("") Then
            MessageBox.Show("Please enter the second number")
            NumTwo.Focus()
        End If

        Dim m As New MathOp2()
        Dim r As Integer

        Try
            r = CInt(CStr(m.Mul(CInt(NumOne.Text), CInt(NumTwo.Text))))

            Result.Text = CStr(r)

            ListBox1.Tag = "x"

        Catch ex As Exception

        End Try
    End Sub

    Private Sub ButtonDiv_Click(sender As Object, e As EventArgs) Handles ButtonDiv.Click
        'Divides the numbers, with a divide by 0 exception

        If NumOne.Text = ("") Then
            MessageBox.Show("Please enter the first number")
            NumOne.Focus()
        ElseIf NumTwo.Text = ("") Then
            MessageBox.Show("Please enter the second number")
            NumTwo.Focus()
        End If

        Dim m As New MathOp2()
        Dim r As Integer

        Try
            r = CInt(CStr(m.Div(CInt(NumOne.Text), CInt(NumTwo.Text))))

            Result.Text = CStr(r)

            ListBox1.Tag = "/"

        Catch ex As Exception

        End Try

        'outputs a message box telling the user to correct the division by 0, also displays a blank result box instead of NaN

        Try

            If CDbl(NumTwo.Text) = Val(0) Then
                Result.Text = ""
                MessageBox.Show("You cannot divide by 0, please input another number")
                NumTwo.Focus()
            End If

        Catch ex As Exception

        End Try

    End Sub

    Private Sub ButtonExit_Click(sender As Object, e As EventArgs) Handles ButtonExit.Click
        'Simply closes the form
        Me.Close()
    End Sub

    Private Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click

        If NumOne.Text = ("") Then
            MessageBox.Show("Please enter some numbers")
            NumOne.Focus()
        ElseIf NumTwo.Text = ("") Then
            MessageBox.Show("Please enter another number")
            NumTwo.Focus()
        End If

        Try
            If ListBox1.Items.Count < 10 Then
                ListBox1.Items.Add(NumOne.Text & ListBox1.Tag.ToString() & NumTwo.Text & ("=") & Result.Text)
            End If
        Catch ex As Exception

        End Try
    End Sub

    Private Sub ButtonClear_Click(sender As Object, e As EventArgs) Handles ButtonClear.Click

        'ListBox1 will not clear after database items have been retrieved

        ListBox1.Items.Clear()

    End Sub

    Private Sub Buttonreset_Click_1(sender As Object, e As EventArgs) Handles Buttonreset.Click
        NumOne.Text = ""
        NumTwo.Text = ""
        Result.Text = ""
    End Sub

    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim toolTip1, toolTip2, toolTip3, toolTip4, toolTip5, toolTip6, toolTip7, toolTip8 As New ToolTip()

        toolTip1.SetToolTip(Me.m3, "Addition")
        toolTip2.SetToolTip(Me.ButtonSub, "Subtraction")
        toolTip3.SetToolTip(Me.ButtonMul, "Multiplication")
        toolTip4.SetToolTip(Me.ButtonDiv, "Division")
        toolTip5.SetToolTip(Me.ButtonSave, "Save operation")
        toolTip6.SetToolTip(Me.Buttonreset, "Reset operation")
        toolTip7.SetToolTip(Me.ButtonExit, "Exit application")
        toolTip8.SetToolTip(Me.ButtonClear, "Clear list")

    End Sub

    Private Sub ListBox1_DoubleClick(sender As Object, e As EventArgs) Handles ListBox1.DoubleClick

        'Get the text of the selected item
        Dim selectedtext As String = ListBox1.Items(ListBox1.SelectedIndex).ToString()

        'Split the item by the operator and the = into an array of strings
        Dim parts As String() = selectedtext.Split("+"c, "-"c, "x"c, "/"c, "="c)

        If parts.Length > 2 Then
            'Define a variable for each part
            Dim part1 As String = parts(0).Trim()
            Dim part2 As String = parts(1).Trim()

            NumOne.Text = part1
            NumTwo.Text = part2

        End If

    End Sub
 
    Private Sub cmdInsertData_Click(sender As Object, e As EventArgs) Handles cmdInsertData.Click
       
        Dim x As Integer
        For x = 0 To ListBox1.Items.Count - 1
            If ListBox1.GetSelected(x) = False Then
                ListBox1.SetSelected(x, True)
            End If
        Next
        Try
            For Each Operation As String In ListBox1.SelectedItems


                Dim Builder As New OleDb.OleDbConnectionStringBuilder With
                    {
                        .Provider = "Microsoft.ACE.OLEDB.12.0",
                        .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "CalcDB.accdb"),
                        .PersistSecurityInfo = False
                    }

                Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDb.OleDbCommand With {.Connection = cn}

                        cmd.CommandText =
                            <SQL>
                    INSERT INTO CalcOps 
                    (
                       Operation
                    ) 
                    VALUES 
                    (
                        @Operation
                    )
                </SQL>.Value

                        With cmd.Parameters

                            .AddWithValue("@Operation", Operation)

                        End With

                        Try
                            cn.Open()
                            cmd.ExecuteNonQuery()

                        Catch ex As Exception
                            MessageBox.Show("Problem:" & Environment.NewLine & ex.Message)
                        End Try

                    End Using
                End Using

            Next
        Catch ex As Exception
            MessageBox.Show("Problem:" & Environment.NewLine & ex.Message)
        End Try

        'This causes the insert done to pop up as many times as there are items inserted into the database
        Dim c As Integer
        For c = 0 To ListBox1.Items.Count - 1
            If ListBox1.GetSelected(c) = True Then
                MessageBox.Show("Insert done")
            End If
        Next

    End Sub


    Private Sub ButtonRet_Click(sender As Object, e As EventArgs) Handles ButtonRet.Click
      
        Dim Builder As New OleDb.OleDbConnectionStringBuilder With
                    {
                        .Provider = "Microsoft.ACE.OLEDB.12.0",
                        .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "CalcDB"),
                        .PersistSecurityInfo = False
                    }

        Try

           Using cn As New OleDb.OleDbConnection
                cn.ConnectionString = " Provider=Microsoft.ACE.OLEDB.12.0;" +
                      "Data Source=|DataDirectory|\CalcDB.accdb"
                cn.Open()
                Dim comm As New OleDbCommand("SELECT TOP 10 Operation FROM CalcOps", cn)
                comm.CommandType = CommandType.Text

                Dim da As New OleDbDataAdapter(comm)
                Dim ds As New DataSet

                da.Fill(ds)
                ListBox1.Items.Clear()
       
                If ds.Tables.Count > 0 And ds.Tables(0).Rows.Count > 0 Then
                    ListBox1.DataSource = ds.Tables(0)
                    ListBox1.DisplayMember = "Operation"
                End If

            End Using
      
        'Shows an error message with the problem message
        Catch ex As Exception
            MessageBox.Show("Error: " + ex.Message)
        End Try

    End Sub

    Private Sub ButtonDelete_Click(sender As Object, e As EventArgs) Handles ButtonDelete.Click
     
        Dim Builder As New OleDb.OleDbConnectionStringBuilder With
                    {
                        .Provider = "Microsoft.ACE.OLEDB.12.0",
                        .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "CalcDB"),
                        .PersistSecurityInfo = False
                    }

        Using cn As New OleDb.OleDbConnection
            cn.ConnectionString = " Provider=Microsoft.ACE.OLEDB.12.0;" +
                  "Data Source=|DataDirectory|\CalcDB.accdb"
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}

                cmd.CommandText =
                    <SQL>
                        
                    DELETE FROM CalcOps
                   
                </SQL>.Value

                Try
                    cn.Open()
                    cmd.ExecuteNonQuery()

                Catch ex As Exception

                End Try

            End Using
        End Using
        MessageBox.Show("Database has been deleted")

    End Sub

End Class


This post has been edited by WarMacheen: 09 January 2014 - 08:31 AM

Was This Post Helpful? 0
  • +
  • -

#36 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9059
  • View blog
  • Posts: 34,018
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 09 January 2014 - 08:42 AM

Quote

Save operation button doesn't work after the Retrieve button populates ListBox1 with database items


If your listbox1 is being backed by a dataset perhaps - add the value to the dataset and NOT to the items list. Those are two different things. Yes.. this means you'll probably need your dataset to be a global variable.



Quote

Clear button doesn't work after Retrieve button populates ListBox1. Error = Items collection cannot be modified when the DataSource property is set. I understand this error, but am not sure how to go about correcting it


Same thing as above - you need to clear the dataset. So, again, have the dataset be a global variable and interact with it that way.


Quote

ListBox1 double click event doesn't work after Retrieve button populates the list. The list was supposed to be populated, then an operation could be double clicked and it would send the first and second number to the corresponding text boxes. This event does work as long as the Retrieve button is never used.

Same as above - you are interacting with the wrong thing.


Quote

Save to DB button doesn't work after Retrieve button is used. Error = "Conversion from type DataRowView to type String is not valid"

Put a breakpoint on line 213 and see what the for each data is.
Was This Post Helpful? 1
  • +
  • -

#37 WarMacheen  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 51
  • Joined: 08-February 11

Re: Populating list box from database problems

Posted 09 January 2014 - 10:33 AM

Thank you, great explanation. I have made these changes so far. Seemed to work. I have this as my module

Imports System.Data.OleDb
Module Module1

    'global public variable
    Public myDataSet As DataSet
    'global public method
    Public Sub GetData()
        Dim cn As OleDbConnection = New OleDbConnection(" Provider=Microsoft.ACE.OLEDB.12.0;" +
                      "Data Source=|DataDirectory|\CalcDB.accdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT TOP 10 Operation FROM CalcOps", cn)
        cn.Open()
        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "CalcOps")

        cn.Close()
        cn = Nothing
    End Sub
End Module


And this as the Retrieve button now

Private Sub ButtonRet_Click(sender As Object, e As EventArgs) Handles ButtonRet.Click
      
        Try
            Call Module1.GetData()

            If myDataSet.Tables.Count > 0 And myDataSet.Tables(0).Rows.Count > 0 Then
                ListBox1.DataSource = myDataSet.Tables(0)
                ListBox1.DisplayMember = "Operation"
            End If

        'Shows an error message with the problem message
        Catch ex As Exception
            MessageBox.Show("Error: " + ex.Message)
        End Try

    End Sub


Going to work on using your suggestions to figure out the rest

This post has been edited by andrewsw: 09 January 2014 - 10:41 AM
Reason for edit:: Removed previous quote

Was This Post Helpful? 0
  • +
  • -

#38 WarMacheen  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 51
  • Joined: 08-February 11

Re: Populating list box from database problems

Posted 09 January 2014 - 11:33 AM

Working on the insert module, getting Error = ExecuteNonQuery: Connection property has not been established.??

Imports System.Data.OleDb

Module Module2
    Public Sub InsertData()
        Dim cn As OleDbConnection = New OleDbConnection(" Provider=Microsoft.ACE.OLEDB.12.0;" +
                    "Data Source=|DataDirectory|\CalcDB.accdb")
        Dim comm As OleDbCommand = New OleDbCommand("INSERT INTO [CalcOps](Operation) VALUES (@Operation)")

        cn.Open()

        comm.ExecuteNonQuery()

        cn.Close()
        cn = Nothing
    End Sub
End Module


Main code as

Private Sub cmdInsertData_Click(sender As Object, e As EventArgs) Handles cmdInsertData.Click
       
        Dim x As Integer
        For x = 0 To ListBox1.Items.Count - 1
            If ListBox1.GetSelected(x) = False Then
                ListBox1.SetSelected(x, True)
            End If
        Next
        Try
            For Each Operation As String In ListBox1.SelectedItems

                Call Module2.InsertData()

            Next
        Catch ex As Exception
            MessageBox.Show("Problem:" & Environment.NewLine & ex.Message)

        End Try

    End Sub

Was This Post Helpful? 0
  • +
  • -

#39 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9059
  • View blog
  • Posts: 34,018
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 09 January 2014 - 11:49 AM

For the love of pete.. really?

Here's your code that is not working:
07	        Dim comm As OleDbCommand = New OleDbCommand("INSERT INTO [CalcOps](Operation) VALUES (@Operation)")


Here's an example of your code that was working from a few posts up:
286	                Dim comm As New OleDbCommand("SELECT TOP 10 Operation FROM CalcOps", cn)


Notice anything different? I don't know - perhaps the connection object being added to the command object?

This worries me that you are still just flailing around and not understanding the steps.
Was This Post Helpful? 0
  • +
  • -

#40 thecoat  Icon User is offline

  • D.I.C Regular

Reputation: 75
  • View blog
  • Posts: 264
  • Joined: 07-December 13

Re: Populating list box from database problems

Posted 22 January 2014 - 04:09 PM

Edit: NM was looking at the 2nd page thinking it was the third.

This post has been edited by thecoat: 22 January 2014 - 04:13 PM

Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3