Populating list box from database problems

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

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

#1 WarMacheen  Icon User is offline

  • D.I.C Head

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

Populating list box from database problems

Posted 06 January 2014 - 09:42 AM

I current have this code, when the button is clicked nothing happens. I suspect I'm either missing a connections or it's just entirely wrong. The DB is CalcDB, table is CalcOps, column is Operation. Needs to pull all of the rows into the list box.

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}
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}

                    cn.Open()

                    Dim comm As New OleDbCommand("SELECT Operation FROM CalcOps", cn)
                    comm.CommandType = CommandType.StoredProcedure
                    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
                    End If

                End Using
            End Using


        Catch ex As Exception

        End Try

           
    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Populating list box from database problems

#2 Wrath_014  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 49
  • Joined: 19-July 13

Re: Populating list box from database problems

Posted 06 January 2014 - 09:56 AM

If you're using Try and Catch, why won't you put a message box that will pop up in case of an error in the Catch section?


           

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try




This post has been edited by Wrath_014: 06 January 2014 - 09:58 AM

Was This Post Helpful? 1
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 2,999
  • Joined: 12-January 10

Re: Populating list box from database problems

Posted 06 January 2014 - 09:57 AM

i dont see where you set the path of the connection
Was This Post Helpful? 0
  • +
  • -

#4 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 06 January 2014 - 10:03 AM

View PostWrath_014, on 06 January 2014 - 09:56 AM, said:

If you're using Try and Catch, why won't you put a message box that will pop up in case of an error in the Catch section?


           

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try




The error message indicates that the Db couldn't be found, though the same code here worked in another section of my code...something missing
Was This Post Helpful? 0
  • +
  • -

#5 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 06 January 2014 - 10:11 AM

Command text was not set for command object

 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}
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                      "Data Source=|DataDirectory|\CalcDB.accdb"
                    cn.Open()
                    cmd.ExecuteNonQuery()

                    Dim comm As New OleDbCommand("SELECT Operation FROM CalcOps", cn)
                    comm.CommandType = CommandType.StoredProcedure
                    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
                    End If

                End Using
            End Using


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

    End Sub

This post has been edited by WarMacheen: 06 January 2014 - 10:12 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,503
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 06 January 2014 - 10:16 AM

Put a breakpoint on line 12 and step through your code. Does any of that make sense?

1. make a variable 'cn' for oledbconnection
2. make a variable 'cmd' with the connection being 'cn'
3. set cn's connection string to some string
4. open the 'cn' connection.
5. cmd executes a stored procedure/sql statement.


Perhaps.. your are missing something in those steps? Like a SQL statement or stored procedure name?
Was This Post Helpful? 0
  • +
  • -

#7 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 06 January 2014 - 10:33 AM

View Postmodi123_1, on 06 January 2014 - 10:16 AM, said:

Put a breakpoint on line 12 and step through your code. Does any of that make sense?

1. make a variable 'cn' for oledbconnection
2. make a variable 'cmd' with the connection being 'cn'
3. set cn's connection string to some string
4. open the 'cn' connection.
5. cmd executes a stored procedure/sql statement.


Perhaps.. your are missing something in those steps? Like a SQL statement or stored procedure name?


I realize something is missing, I'm just not sure what or where.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,503
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 06 January 2014 - 10:37 AM

... you cannot ".ExecuteNonQuery" if you never specified a query!

In line 19 you seem to know where to add a query to a command object.. why not apply that up above it?
Was This Post Helpful? 0
  • +
  • -

#9 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 06 January 2014 - 10:45 AM

View Postmodi123_1, on 06 January 2014 - 10:37 AM, said:

... you cannot ".ExecuteNonQuery" if you never specified a query!

In line 19 you seem to know where to add a query to a command object.. why not apply that up above it?


I get Command Text was not set for Command Object no matter where execute is positioned after cn.open()
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,503
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 06 January 2014 - 10:51 AM

Why are you throwing it around randomly? Connecting to a database is a straight forward series of steps.

Have your sql statement.
create your connection object
open your connection.
set your adapter to teh sql statement and your connection object
create a dataset
us the adapter to .Fill the dataset.
dispose of your objects and connections....

... or

create your sql statement.
create your connection object
open your connection.
create a command object using the sql statement and your connection object
execute non query on your command object.
dispose of your objects and connections....


OleDb Basics in VB.Net

A Really Simple Database Create a Database using Access & VB.net Express 2008
Was This Post Helpful? 1
  • +
  • -

#11 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 06 January 2014 - 12:28 PM

updated some things, no errors showing up but the listbox populates with "System.Data.DataViewManager"

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}

                Dim returnValue As New Object

                Using cmd As New OleDb.OleDbCommand
                    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CalcDB.accdb"
                    cmd.CommandText = "SELECT Operation FROM CalcOps"
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = cn
                    cn.Open()
                    
                    returnValue = cmd.ExecuteNonQuery()

                    Dim strSQL As String = "SELECT Operation FROM CalcOps"
                    Dim da As New OleDbDataAdapter
                    Dim ds As New DataSet
  

                    ListBox1.DataSource = ds


                End Using
            End Using


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



    End Sub

Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,503
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 06 January 2014 - 12:42 PM

What is a dataset? It's a collection of tables. Perhaps setting the datasource to a table from the dataset would be a good call.
Was This Post Helpful? 0
  • +
  • -

#13 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 06 January 2014 - 01:33 PM

View Postmodi123_1, on 06 January 2014 - 12:42 PM, said:

What is a dataset? It's a collection of tables. Perhaps setting the datasource to a table from the dataset would be a good call.


Thanks, though none of this really helps me. I do understand what your saying, just not how to implement. Thanks for tyring anyway!

This post has been edited by WarMacheen: 06 January 2014 - 01:33 PM

Was This Post Helpful? 0
  • +
  • -

#14 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,503
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 06 January 2014 - 01:41 PM

How do you reference a table from a dataset? Perhaps use the 'tables' collection inside the dataset.

http://msdn.microsof...=vs.110%29.aspx
http://msdn.microsof...=vs.110%29.aspx
Was This Post Helpful? 0
  • +
  • -

#15 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 06 January 2014 - 02:10 PM

View Postmodi123_1, on 06 January 2014 - 01:41 PM, said:

How do you reference a table from a dataset? Perhaps use the 'tables' collection inside the dataset.

http://msdn.microsof...=vs.110%29.aspx
http://msdn.microsof...=vs.110%29.aspx


Thanks, I've actually looked at those pages and many others.
Was This Post Helpful? 0
  • +
  • -

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