Populating list box from database problems

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

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

#16 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9258
  • View blog
  • Posts: 34,738
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 06 January 2014 - 02:12 PM

Then you should know how to access a specific table in the ".Tables" collection inside a given dataset.
Was This Post Helpful? 0
  • +
  • -

#17 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:26 PM

If I knew how to do it, I wouldn't be here. This wasn't my first stop. I can read those pages all day long, and I have, but I don't know what I'm looking for, or how to implement it for my situation. I have this at the moment, it does nothing

 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 TOP 10 Operation FROM CalcOps"
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = cn
                    cn.Open()

                    returnValue = cmd.ExecuteNonQuery()

                    Dim da As New OleDbDataAdapter("CalcOps", cn)
                    Dim dt As New DataTable
                    Dim ds As New DataSet
                    da.SelectCommand.CommandType = CommandType.TableDirect
                    da.Fill(ds, "CalcDB")
                    dt = ds.Tables("CalcDB")




                End Using
            End Using


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


This post has been edited by WarMacheen: 06 January 2014 - 02:28 PM

Was This Post Helpful? 0
  • +
  • -

#18 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9258
  • View blog
  • Posts: 34,738
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 06 January 2014 - 02:32 PM

Why would you have that? It seems to be a step back or two into more of a mess than your last chunk of code?

Then things like line 22 - you use 'execute non query', but the command text in line 17 is a query!




As for my previous questions - collections, do you know how to use them? Like an array or a list?
Was This Post Helpful? 1
  • +
  • -

#19 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3515
  • View blog
  • Posts: 11,998
  • Joined: 12-December 12

Re: Populating list box from database problems

Posted 06 January 2014 - 02:33 PM

If the code runs without error then it might populate your DataSet, but you no longer have any code that attempts to populate the ListBox. But see modi's comments above as well.

This post has been edited by andrewsw: 06 January 2014 - 02:34 PM

Was This Post Helpful? 0
  • +
  • -

#20 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 - 07:19 AM

Getting expected query name after execute error

am I any closer here

 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"
                   


                    Dim objDataAdapter As New OleDbDataAdapter()

                    objDataAdapter.SelectCommand = New OleDbCommand()


                    objDataAdapter.SelectCommand.Connection = cn
                    objDataAdapter.SelectCommand.CommandText = "SELECT Operation FROM CalcOps"
                    objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure


                    Dim objDataSet As DataSet = New DataSet()

                    objDataAdapter.Fill(objDataSet, "CalcOps")
                    Dim objDataView As New DataView(objDataSet.Tables("CalcOps"))

                    cn.Open()
                    cmd.ExecuteNonQuery()

                End Using
            End Using


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


    End Sub

Was This Post Helpful? 0
  • +
  • -

#21 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3515
  • View blog
  • Posts: 11,998
  • Joined: 12-December 12

Re: Populating list box from database problems

Posted 07 January 2014 - 07:22 AM

Post the full error message and confirm which line it refers to in your posted code.
Was This Post Helpful? 0
  • +
  • -

#22 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 - 07:30 AM

View Postandrewsw, on 07 January 2014 - 07:22 AM, said:

Post the full error message and confirm which line it refers to in your posted code.


Error: expected query name after EXECUTE
Was This Post Helpful? 0
  • +
  • -

#23 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3515
  • View blog
  • Posts: 11,998
  • Joined: 12-December 12

Re: Populating list box from database problems

Posted 07 January 2014 - 07:58 AM

And what is the line number?

But you have set the CommandType to StoredProcedure, which it isn't.
Was This Post Helpful? 1
  • +
  • -

#24 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9258
  • View blog
  • Posts: 34,738
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 07 January 2014 - 07:59 AM

Are you just randomly throwing lines around or is there some sort of directed effort?

Again - post #10 notice how I said you use a command object _OR_ an adapter? Why are you using both in your code?

Literally if you take (one or the other) written out lines from that post of mine and put it to code you should have a functioning call to your database.. then again your post #11 seemed to have most of the working connection parts but your lack of understanding collections held you back getting the results in a listview's datasource.
Was This Post Helpful? 1
  • +
  • -

#25 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 - 08:56 AM

I actually went back and tried to rework some things using a book, obviously failed.

Reworked the code again, getting "object reference not set to an instance of the object", at least it's an error I haven't seen lol

 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"



                    Dim comm As New OleDbCommand("SELECT Operation FROM CalcOps", cn)
                    comm.CommandType = CommandType.Text
                    comm.Connection.Open()
                    comm.ExecuteNonQuery()


                    Dim ds As New DataSet

                    If ds.Tables.Count > 0 And ds.Tables("CalcOps").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 andrewsw: 09 January 2014 - 10:42 AM
Reason for edit:: Removed previous quote

Was This Post Helpful? 0
  • +
  • -

#26 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9258
  • View blog
  • Posts: 34,738
  • Joined: 12-June 08

Re: Populating list box from database problems

Posted 07 January 2014 - 09:00 AM

Quote

object reference not set to an instance of the object

That means you are attempting to use a variable with out declaring it 'new'. A simple visual once over should point out where.. or barring that a simple exercise of deubugging by putting a breakpoint at the top of the code and track line by line as it executes.uote]
Was This Post Helpful? 1
  • +
  • -

#27 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 - 09:41 AM

View Postmodi123_1, on 07 January 2014 - 09:00 AM, said:

Quote

object reference not set to an instance of the object

That means you are attempting to use a variable with out declaring it 'new'. A simple visual once over should point out where.. or barring that a simple exercise of deubugging by putting a breakpoint at the top of the code and track line by line as it executes.uote]


Looks like this line, maybe new table?

If ds.Tables.Count > 0 And ds.Tables("CalcOps").Rows.Count > 0 Then
Was This Post Helpful? 0
  • +
  • -

#28 thecoat  Icon User is offline

  • D.I.C Regular

Reputation: 89
  • View blog
  • Posts: 324
  • Joined: 07-December 13

Re: Populating list box from database problems

Posted 07 January 2014 - 11:51 AM

The 'And' Operator evaluates both sides of the conditional statement. You either need 2 If statements, one nested within the other or Use 'AndAlso', so that if the left side evaluates to false, the right side is never evaluated. This will solve your error condition, but the root of the problem is that you are never populating your dataset (no tables are added hence the error when you try and reference a table that doesn't exist).
Was This Post Helpful? 1
  • +
  • -

#29 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 - 12:08 PM

View Postthecoat, on 07 January 2014 - 11:51 AM, said:

The 'And' Operator evaluates both sides of the conditional statement. You either need 2 If statements, one nested within the other or Use 'AndAlso', so that if the left side evaluates to false, the right side is never evaluated. This will solve your error condition, but the root of the problem is that you are never populating your dataset (no tables are added hence the error when you try and reference a table that doesn't exist).


Thank you thecoat, and everyone else in this thread. I actually got the code working and it finally hit me to have ds set to the CalcDBDataSet. Once this and the "And" statement was corrected, the list populated. I appreciate you guy's patience with this.

This post has been edited by WarMacheen: 07 January 2014 - 12:09 PM

Was This Post Helpful? 0
  • +
  • -

#30 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 - 12:28 PM

Ug, while the code is indeed pulling data from the database, it is also updating the datbase with the information pulled or stored. I closed the application and restarted, hit retrieve and the same list of operations came up, even though I deleted them from the DB manually.

  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

                    ds = CalcDBDataSet


                    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
  • +
  • -

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