Show Data From A DataBase

[paulthepaddy]

Hey Guys I Was Wondering If Any1 Could Help me.

I Want To Show This Data In A ListBox but all i am getting is a blank listbox and no errors

Public Sub RecentWork_Display()
        Dim connection As New OleDb.OleDbConnection
        connection.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DB.accdb;Persist Security Info=True")
        connection.Open()
        Dim command As New OleDb.OleDbCommand("SELECT * FROM RecentWork", connection)
        With ListBox_Un
            .DataSource = command.ExecuteNonQuery
            .DisplayMember = "InvoiceName"
        End With
        'ListBox_Un.DataSource = command.ExecuteNonQuery
        connection.Close()
        connection.Dispose()
    End Sub

any help will be much appreicated

 

[jmcilhinney]

You are assigning the result of ExecuteNonQuery to the DataSource of the ListBox. Have you looked to see what ExecuteNonQuery actually returns? It's definitely not anything that you can bind to a ListBox. You need to populate a DataTable with the results of the query bind that.

On a side note, you should be setting the DisplayMember (and ValueMember if you're using it) before setting the DataSource.

 

[paulthepaddy]

Hi, Thanks For The Reply.

Their Is Definatly Nothing Coming From The Database, And Their Is Definatly Data In The DataBase, And I Have Checked All Spellings Just In Case, And No Spelling Errors.

With ListBox_Un             
.DataSource = command.ExecuteNonQuery             
.DisplayMember = "InvoiceName"         
End With

Im guessing This Code Doesn't Bind It To The Listbox.

what i am trying to achive if you know a better way of doing it.

The database is 3 coloms

i only want to display 1 coloum But i do need the values of the other 2 coloums.

im thinking it might eb better if i create a class of the database, then use a list(of class)

whats you opinion on this?

Thanks

 

[paulthepaddy]

Hi, I Made A Silly Mistake, The DB Was In My Project Folder BUT NOT In My Bin Folder LOL

But Now I Am Getting Diffrent Results Than Expected

For Some Reason My Code Is Giving The Following In The List Box Instead Of What I Wanted



Instead of eg

invoice name bla bla
invoice name bla bla
invoice name bla bla

Any Ideas On Why This Is Happening, Also Can You Tell Me What This Kind Of Error Is So I Know For NExt Time I Get It

Public recentWork As New BindingList(Of recent)

Public Sub RecentWork_Display()
        Dim connection As New OleDbConnection
        connection.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DB.accdb;Persist Security Info=True")
        connection.Open()

        Dim command As New OleDbCommand("SELECT * FROM RecentWork", connection)
        Dim reader As OleDbDataReader
        Dim recent As New recent
        reader = command.ExecuteReader
        If reader.HasRows Then
            Do While reader.Read()
                recent.InvoiceName = reader("InvoiceName").ToString
                recent.DateCreated = reader("DateCreated").ToString
                recent.DeleteDate = reader("DeleteDate").ToString
                recentWork.Add(recent)
            Loop
        End If
        reader.Close()

        With ListBox_Recent
            .DataSource = recentWork
            .DisplayMember = recent.InvoiceName
        End With

        connection.Close()
        connection.Dispose()

Public Class recent
    Public InvoiceName As String
    Public DateCreated As String
    Public DeleteDate As String

End Class

 

[jmcilhinney]

The problem is that you are only creating one 'recent' object and you're adding it to the list multiple times. There's only one object so it will always have the last values you assigned to it. If you want to add three objects to the list then you need to create three objects, not one object and change its property values.

By the way, you have a load of pointless code there. You could have simply populated a DataTable and bound it, as I suggested earlier. What's the point of the 'recent' class and the BindingList?

 

[paulthepaddy]

ok thanks for the info on only creating 1 object. the idea was, that i have a database with a table of recent work. invoices that have not yet been completed.

the idea was that i would get a row from the database then pass data from the row into the class then add it intot eh binding list(bindinglist as it would refresh when things where added to the list)

i only need 1 value to be displayed. as this list wouldn't get very big, i wanted to try and keep this part of coding small in terms of resources and speed of running as this runs at the form load. if you still think that a datatable would be better then can you give em a rough idea on what to do with it as i dont think i have used one before

many thanks

 

[jmcilhinney]

Given that you have all your data access code right there in your form anyway, I just don't see the point of an extra class and a BindingList. You've already got a data reader so simply create a DataTable object and then call its Load method and pass the data reader. You then bind the DataTable just as you are the BindingList. You can can add rows to the DataTable and the UI will update automatically. You can add data in the UI and the DataTable will update automatically. If you intend to save data then I'd do away with the data reader and use a data adapter instead, which can retrieve and save.

Retrieving and Saving Data in Databases

 

[paulthepaddy]

thank you, that has cleared alot up.

I Haven't Looked At The Link Yet But Will Do in 10 Mins. The Data Reader And DataTable Will Probably Work Better As I Already Have The Code For Updating And Delete The Rows I Have

Got Alot Of This Code From Reading One Of Your Blog Posts

I Have Done This, This Way Because This Is Done Automaticly and not user input.
Do you Think I Should Use The DataAdapter Or Just Stick With This Way And The Data Reader & Table.

Public Sub RecentWork_AddNew(Name As String, Type As String)
            Dim connection As New OleDb.OleDbConnection
            connection.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DB.accdb;Persist Security Info=True")
            connection.Open()
            Dim command As New OleDb.OleDbCommand("INSERT INTO RecentWork (InvoiceName, DateCreated, DeleteDate) " & "VALUES (@InvoiceName, @DateCreated, @DeleteDate)", connection)
            With command.Parameters
                .AddWithValue("@InvoiceName", Name)
                .AddWithValue("@DateCreated", Format(Date.Today, "dd:MM:yyyy"))
                .AddWithValue("@DeleteDate", Format(DateAdd(DateInterval.Day, 10, Date.Today), "dd:MM:yyyy"))
                .AddWithValue("@Type", Type)
            End With
            command.ExecuteNonQuery()
            connection.Close()
        End Sub

again thanks for help, The Opinion From A Pro Means Alot

 

[jmcilhinney]

What you do is up to you but I think that you should go with the data adapter. Use it to populate a DataTable and bind that to the UI. Edit the data through the UI and then use the same adapter to save the changes when you're done. You're just complicating things unnecessarily otherwise.

 

[paulthepaddy]

Hi, I Am having Problems Getting The Listbox To Bind With The DataTable.

i have tried quite a few diffrent bits of code, far to many to list them all, so just an example of what i mean

ListBox_Recent.DisplayMember = "InvoiceName"
ListBox_Recent.DataSource = table

again im not getting any errors but i am getting this agian


could you please give me the proper Code To Bind The Datatable To The Listbox, I Have Googled And All examples Are not Working

Here Is All Involved Code

#Region "Recent Work"
    Private connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DB.accdb;Persist Security Info=True")
    Private table As New DataTable

    Public Sub RecentWork_Display()
        Using connection
            connection.Open()
            Using command As New OleDbCommand("SELECT * FROM RecentWork", connection)
                Using reader As OleDbDataReader = command.ExecuteReader()
                    table.Load(reader)
                End Using
            End Using
        End Using

    End Sub

    Public Sub RecentWork_add(InvoiceName As String)
        Dim insert As New OleDbCommand("INSERT INTO RecentWork (InvoiceName, DateCreated, DeleteDate) " & "VALUES (@InvoiceName, @DateCreated, @DeleteDate)", Me.connection)
        connection.Open()
        With insert.Parameters
            .AddWithValue("@InvoiceName", InvoiceName)
            .AddWithValue("@DateCreated", Format(Date.Today, "dd:MM:yyyy"))
            .AddWithValue("@DeleteDate", Format(DateAdd(DateInterval.Day, 10, Date.Today), "dd:MM:yyyy"))
        End With
        insert.ExecuteNonQuery()
        connection.Close()
    End Sub

    Public Sub RecentWork_Delete(InvoiceName As String)
        Dim delete As New OleDbCommand("DELETE FROM RecentWork WHERE InvoiceName = @InvoiceName", Me.connection)
        connection.Open()
        delete.Parameters.AddWithValue("@InvoiceName", InvoiceName)
        delete.ExecuteNonQuery()
        connection.Close()
    End Sub

    Public Sub RecentWork_Keep(InvoiceName As String)
        Dim update As New OleDbCommand("UPDATE RecentWork SET DeleteDate='00:00:0000', InvoiceName = @InvoiceNameK WHERE InvoiceName= @InvoiceName'", connection)
        connection.Open()
        With update.Parameters
            .AddWithValue("@InvoiceNameK", InvoiceName & " (K)")
            .AddWithValue("@InvoiceName", InvoiceName)
        End With
        update.ExecuteNonQuery()
        connection.Close()
    End Sub

 

[jmcilhinney]

I don't see where you're binding in the code you posted.

 

[paulthepaddy]

hi sorry about that, my mistake lol

 Private Sub StartForm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'DataGridView1.DataSource = table
        ListBox_Recent.DisplayMember = "InvoiceName"
        ListBox_Recent.DataSource = table

this code is giving the result in the image above

 

[jmcilhinney]

I would think that it's because you're binding the DataTable before creating it's schema. When you bind there is no InvoiceName column so it is ignored. Try binding after you call Fill or else call FillSchema or manually build the schema before binding.

 

[paulthepaddy]

.... i cant believe it, thanks i was way over complecating that, got it sorted now thanks, my only other question now is.

is their away to keep the table then the list updated everytime something happens with that datatable or adapter

thanks so much

 

[jmcilhinney]

Nothing is going to happen with the adapter and the only time something will change in the DataTable is when you change it. You're using a ListBox so the user can't edit the data directly, so the only way to make changes is in code. If you want to save the changes after making them then do so.