Question Querying an Access table using the value selected in a combo

[Darius]

Dear all,

I have started to build the application where I can navigate through a table in Access and bring in three text boxes the ProjectID, ProjectName and ProjectManager. I have also added 4 buttons (MoveNext, MoveBack, MoveFirstRecord and MoveLastRecord) that work.At the moment, when I load the application I am able to navigate through the records and display in the text boxes the Project Name, Project ID and Project Manager. I can also move next, move back, move to the first record and move the last one without problems.I have also managed to add a combo that displays all the Projects ID, which are always unique.What I need to do is, by choosing a Project ID from that combo box and then clicking on a new button called "Search Project", to fill the text boxes with the appropriate information for the Project ID chosen in the combo.I am quite lost and I don't know to approach this in a efficiente way.

This is the code I currently have:

Public Class Form2
Dim connection As New OleDb.OleDbConnection
Dim DataSet As New DataSet
Dim DataAdapter As OleDb.OleDbDataAdapter
Dim sql1 As String
Dim sql2 As String
Dim inc As Integer
Dim MaxRows As Integer
​

​

PrivateSub Form2_Load(ByVal sender AsObject,ByVal e As System.EventArgs)HandlesMe.Load
Try
connection.ConnectionString ="PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Corporate Reporting\2014_15\Corporate\QuarterlyReporting.accdb"
connection.Open()

'--For the text boxes--
sql1 ="SELECT * FROM Projects_Application"
DataAdapter =New OleDb.OleDbDataAdapter(sql1, connection)
DataAdapter.Fill(DataSet,"Projects")

'--For the combo--
sql2 ="SELECT ProjectID FROM Projects_Application ORDER BY ProjectID"
Dim cm AsNew OleDb.OleDbCommand(sql2, connection)
Dim dr As OleDb.OleDbDataReader = cm.ExecuteReader
While dr.Read
cmbSearch.Items.Add(dr(0).ToString)
EndWhile

dr.Close()
connection.Close()

MaxRows = DataSet.Tables("Projects").Rows.Count
inc =-1

Catch ex As Exception
MsgBox(ex.Message)
EndTry
EndSub

PrivateSub NavigateRecords()
txtProjectName.Text = DataSet.Tables("Projects").Rows(inc).Item(0)
txtProjectID.Text = DataSet.Tables("Projects").Rows(inc).Item(1)
txtProjectManager.Text = DataSet.Tables("Projects").Rows(inc).Item(2)
EndSub

PrivateSub btnNext_Click(ByVal sender As System.Object,ByVal e As System.EventArgs)Handles btnNext.Click
If inc <>(MaxRows -1)Then
inc = inc +1
NavigateRecords()
EndIf
EndSub

PrivateSub btnPrevious_Click(ByVal sender As System.Object,ByVal e As System.EventArgs)Handles btnPrevious.Click
If inc >0Then
inc = inc -1
NavigateRecords()
EndIf
EndSub

PrivateSub btnFirst_Click(ByVal sender As System.Object,ByVal e As System.EventArgs)Handles btnFirst.Click
If inc >0Then
inc =0
NavigateRecords()
EndIf
EndSub

PrivateSub bntLastR_Click(ByVal sender As System.Object,ByVal e As System.EventArgs)Handles bntLastR.Click
If inc <>(MaxRows -1)Then
inc = MaxRows -1
NavigateRecords()
EndIf
EndSub

PrivateSub btnSearch_Click(ByVal sender As System.Object,ByVal e As System.EventArgs)Handles btnSearch.Click
??????
EndSub

End Class


Can someone give a hint of how to proceed?

Thanks,

Darius​

 

[jmcilhinney]

For future reference, please post your code as plain text inside appropriate formatting tags, i.e.

[xcode=vb]your code here[/xcode]

As you can see, the code is far more readable when posted that way:

Public Class Form2

    Dim connection As New OleDb.OleDbConnection
    Dim DataSet As New DataSet
    Dim DataAdapter As OleDb.OleDbDataAdapter
    Dim sql1 As String
    Dim sql2 As String
    Dim inc As Integer
    Dim MaxRows As Integer

    Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            connection.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Corporate Reporting\2014_15\Corporate\QuarterlyReporting.acc db"
            connection.Open()

            '--For the text boxes--
            sql1 = "SELECT * FROM Projects_Application"
            DataAdapter = New OleDb.OleDbDataAdapter(sql1, connection)
            DataAdapter.Fill(DataSet, "Projects")

            '--For the combo--
            sql2 = "SELECT ProjectID FROM Projects_Application ORDER BY ProjectID"
            Dim cm As New OleDb.OleDbCommand(sql2, connection)
            Dim dr As OleDb.OleDbDataReader = cm.ExecuteReader
            While dr.Read
                cmbSearch.Items.Add(dr(0).ToString)
            End While

            dr.Close()
            connection.Close()

            MaxRows = DataSet.Tables("Projects").Rows.Count
            inc = -1

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub NavigateRecords()
        txtProjectName.Text = DataSet.Tables("Projects").Rows(inc).Item(0)
        txtProjectID.Text = DataSet.Tables("Projects").Rows(inc).Item(1)
        txtProjectManager.Text = DataSet.Tables("Projects").Rows(inc).Item(2)
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If inc <> (MaxRows - 1) Then
            inc = inc + 1
            NavigateRecords()
        End If
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        End If
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc > 0 Then
            inc = 0
            NavigateRecords()
        End If
    End Sub

    Private Sub bntLastR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntLastR.Click
        If inc <> (MaxRows - 1) Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        '??????
    End Sub

End Class

 

[jmcilhinney]

As for the question, I would suggest that you start by abandoning your method of navigation and use data-binding. It will reduce your code considerably and actually remove the need for a Search feature at all. Also, note that there's no point performing your second query because you already have that information from the first query. Just use that same data. Here's what you should do:

1. Add a BindingSource to your form.
2. Create a DataTable instead of a DataSet and Fill that with your data adapter. You're already using a DataTable in the DataSet so what point does the DataSet serve?
3. Bind your DataTable to the BindingSource.
4. Bind the BindingSource to your controls.

Here's what the binding would look like:

projectBindingSource.DataSource = projectDataTable

With projectIDComboBox
    .DisplayMember = "ProjectID"
    .ValueMember = "ProjectID"
    .DataSource = projectBindingSource
End With

projectNameTextBox.DataBindings.Add("Text", projectBindingSource, "ProjectName")
projectManagerTextBox.DataBindings.Add("Text", projectBindingSource, "ProjectManager")

Your ComboBox will now display the ProjectID of the current record. Presumably that is read-only so putting it in a TextBox isn't really a good idea anyway. You can then navigate simply by calling MoveFirst, MovePrevious, MoveNext or MoveLast on the BindingSource. You can also select a ProjectID in the ComboBox and the other fields for that record will automatically populate the TextBoxes.

 

[Darius]

Hi jmcillhinney!

Your last reply was excellent and I've followed your tips, having got very good results.

As I'm not familiarised with the BindingSource thing, I don't know how to move to the next step of this application. I've added four new buttons: Add a New Project (needs to create an empty row), Commit Changes (for the new project added), Update Record (when changing the project name or manager) and Delete Record (when project has dissapeared and need deletion).





Could please give me some hints of how to proceed? That'd be great

Public Class Form3
    Dim connection As New OleDb.OleDbConnection
    Dim Datatable As New DataTable
    Dim DataAdapter As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim ProjectBindingSource As New BindingSource

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            connection.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Corporate Reporting\2014_15\Corporate\QuarterlyReporting.accdb"
            connection.Open()
            sql = "SELECT * FROM Projects_Application ORDER BY ProjectID"
            DataAdapter = New OleDb.OleDbDataAdapter(sql, connection)
            DataAdapter.Fill(Datatable)
            ProjectBindingSource.DataSource = Datatable

            With cmbSearch
                .DisplayMember = "ProjectID"
                .ValueMember = "ProjectID"
                .DataSource = ProjectBindingSource
            End With

            NavigateRecords()
            connection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub NavigateRecords()
        txtProjectName.DataBindings.Add("Text", ProjectBindingSource, "ProjectName")
txtProjectName.DataBindings.Add("Text", ProjectBindingSource, "ProjectName")

        txtProjectManager.DataBindings.Add("Text", ProjectBindingSource, "ProjectManager")
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        ProjectBindingSource.MoveNext()
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        ProjectBindingSource.MovePrevious()
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        ProjectBindingSource.MoveFirst()
    End Sub

    Private Sub bntLastR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntLastR.Click
        ProjectBindingSource.MoveLast()
    End Sub

    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        ???


    End Sub

    Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        ???


    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        ???


    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim MessageOkCancel As Boolean

        MessageOkCancel = MsgBox("Are you sure you want to delete this record?", MsgBoxStyle.OkCancel)

        Select Case MessageOkCancel
            Case True
                ???


            Case False
                NavigateRecords()
        End Select
    End Sub
End Class

 

[jmcilhinney]

To add a new record you call AddNew on the BindingSource. To edit the current record, the user simply types new values into the controls. Data-binding takes care of two-way data flow between the data source and the control. To delete the current record you call RemoveCurrent on the BindingSource. Note that all those operations affect only the local copy of the data. To save the changes from your DataTable back to the database, you call Update on the same data adapter you used to retrieve the data in the first place.

Note that you should also call EndEdit on the BindingSource before saving the changes. That will ensure that any pending edit is committed to the DataTable. I'm not sure whether calling AddNew and/or RemoveCurrent will implicitly call EndEdit or not. You can test that and, if it doesn't, add a call yourself.

 

[Darius]

Hi illhinney!

Thanks for you tips. I have done a start and implemented some code but the application breaks quite a lot. I was wondering whether you have some time to keep giving me a hand.


-Update record. When I make changes in the text boxes, two things happen. First, then the information gets changed in the application but not in the database and then I get an error message saying: "InvalidOperationException was unhandled. Update requires a valid UpdateCommand when passed DataRow collection with modified rows". The table in the Access database has a primary key (which is an autonumber) so my initial research seemed to appoint to a lack of primary key as the main reason of the error, but it really does have one.

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        DataAdapter.Update(Datatable)
        ProjectBindingSource.EndEdit()

-Delete record. When I click on the Detele Record button, I get the same error message as indicated above. The error points to the line DataAdapter.Update(Datatable)

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim MessageOkCancel As Boolean
        MessageOkCancel = MsgBox("Are you sure you want to delete this record?", MsgBoxStyle.OkCancel)

        Select Case MessageOkCancel
            Case True
                Try
                    ProjectBindingSource.RemoveCurrent()
                    ProjectBindingSource.EndEdit()
                    DataAdapter.Update(Datatable)
                    NavigateRecords()
                Catch ex As Exception
                    MsgBox(ex.message)
                End Try
            Case False
                Exit Sub
        End Select
    End Sub

- Add new project works and clears up the text boxes in order to allow me to enter new parameters.
-Commit changes. When I try to commit the changes to add the new record to the database, it happens the same that in Update record. The new project is added to the combo and then a error message crops up saying "InvalidOperationException was unhandled. Update requires a valid UpdateCommand when passed DataRow collection with modified rows".

]
    Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        If txtProjectName.Text = "" Or IsNothing(txtProjectID.Text) Or txtProjectManager.Text = "" Then
            MsgBox("All the fields are compulsary")
            Exit Sub
        Else
            DataAdapter.Update(Datatable)
            ProjectBindingSource.EndEdit()
        End If
    End Sub

[/FONT] I would appreciate any more help you can provide.

Thanks!

Darius

 

[jmcilhinney]

Firstly, here's what I said earlier:

To save the changes from your DataTable back to the database, you call Update on the same data adapter you used to retrieve the data in the first place.

Note that you should also call EndEdit on the BindingSource before saving the changes.

Are you doing that?

Also, I would question the fact that you're calling Update repeatedly. Normally, you let the user make all their changes and then save all those changes in one batch with a single call to Update. Generally speaking, the only reason not to do that is that there are other users who need to be able to see the changes immediately.

As for the issue, if you're going to save changes to the database then you need SQL code to do it. Just as you have a SQL SELECT statement in the data adapter's SelectCommand that gets executed when you call Fill, so you need INSERT, UPDATE and DELETE statements in the InsertCommand, UpdateCommand and DeleteCommand that get executed when you call Update. You can either create those yourself or, if circumstances support it, let a command builder do it for you. Check this out for an example of each:

Retrieving and Saving Data in Databases

 

[Darius]

Sorry jmcilhinney, but I thought I was doing what you said. I am calling Update using the same DataAdapter I used to fill the text boxes. If what I am doing it is not right, so then I do not understand what you mean. Sorry

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
DataAdapter.Update(Datatable)
ProjectBindingSource.EndEdit() 
End sub

If you say that by doing this I am not doing what you said, I do not really understand your statement.

I will have a look at the examples you mention.

Thanks for your help!

 

[jmcilhinney]

I said that Update is what saves the changes and you need to call EndEdit before you save the changes, i.e. you need to call EndEdit BEFORE you call Update. You're calling EndEdit AFTER you call Update. There's no use ending the current edit after the changes have already been saved. That means that the current edit will not be saved.

 

[Darius]

Hi again jmcilhinney,

I have had a go with delete in the first instance following the examples you passed me and it seems it isn't doing anything and no errors are thrown, although it reaches the message box.

All the other code is the same and the only thing I have changed is the code within the Delete button.

 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim Project_To_Delete As String
Project_To_Delete = txtProjectID.Text
Dim Delete As New OleDb.OleDbCommand("DELETE FROM Projects_Application WHERE ProjectID = " & Project_To_Delete, connection)
Delete.Parameters.Add(Project_To_Delete, OleDb.OleDbType.Integer, 3, "ProjectID")
DataAdapter.DeleteCommand = Delete
Msgbox("Record deleted")
End sub

Do you have an idea of what I may be doing wrong?

Thanks,

Darius

 

[jmcilhinney]

Hi again jmcilhinney,

I have had a go with delete in the first instance following the examples you passed me and it seems it isn't doing anything and no errors are thrown, although it reaches the message box.

All the other code is the same and the only thing I have changed is the code within the Delete button.

 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim Project_To_Delete As String
Project_To_Delete = txtProjectID.Text
Dim Delete As New OleDb.OleDbCommand("DELETE FROM Projects_Application WHERE ProjectID = " & Project_To_Delete, connection)
Delete.Parameters.Add(Project_To_Delete, OleDb.OleDbType.Integer, 3, "ProjectID")
DataAdapter.DeleteCommand = Delete
Msgbox("Record deleted")
End sub

Do you have an idea of what I may be doing wrong?

Thanks,

Darius

That code doesn't resemble any of the examples I provided. Firstly, you use a data adapter to save changes from a DataTable. If you're not using a DataTable that may have multiple changes in it then you don't use a data adapter. If you do use a data adapter then you need to call its Update method to actually save the changes, as all my data adapter examples do. If you're changing one record directly then you need to call ExecuteNonQuery on your command. Also, if you're going to add parameters to your command then you have to have parameters in your SQL code.

So, you first need to determine which example to follow based on the descriptions and then follow that example. Don't mix and match.

 

[Darius]

Sorry, I don't understand a single word of what you say and even your examples don't differentiate clearly what you try to prove in your previous reply.

Everything may be obvious for you, but not for me that I am not a programmer. I was hoping to get more practical help from you in terms of giving me a hand with the code, instead of a theory lecture, for which I could buy a book or look at online resources.

Not really satisfied with your help since your words are harsh and scornful and I only wanted a little bit of practical assistance with my code.

 

[jmcilhinney]

One of the examples I provided is described thusly:

Retrieving multiple records for display and editing, then saving the changes.

Does that not describe what you're trying to do? If so then logic dictates that that is the example to follow. It demonstrates clearly how to retrieve and save the data. It's then just a matter of binding the DataTable to a BindingSource and the BindingSource to your controls.

Questions like this one get asked a hundred times a day. So that I and others don't have to provide the same answers those hundred times, I wrote those examples. Programmer or not, you should be able to match your scenario to one of them.

 

[Darius]

Sorry if I was a little bit harsh, I am just a little bit frustated I can't work it out for myself.

I have done some more research and looked at your example. This is the final I code I have got but it does not do anything, although it does not throw any errors neither.

Can you spot please what is missing? I just don't know what I may be doing wrong.

connection.Open()
Dim Project_To_Delete As String
Project_To_Delete = txtProjectID.Text
sql_delete = "DELETE FROM Projects_Application WHERE ProjectID = " & Project_To_Delete
Dim Delete As New OleDb.OleDbCommand(sql_delete, connection)
Delete.Parameters.Add("Project_To_Delete", OleDb.OleDbType.Integer, 3, "ProjectID")
DataAdapter.DeleteCommand = Delete
Datatable.AcceptChanges()
ProjectBindingSource.EndEdit()
DataAdapter.Update(Datatable)
ProjectBindingSource.DataSource = Datatable
connection.Close()

Thanks.

 

[jmcilhinney]

That still doesn't really resemble my example all that much. You seem to be changing things unnecessarily and thereby complicating things. For a start, why are you waiting until it's time to save to create the DeleteCommand? That can still work but why change from the example?

Anyway, there are two glaring issues there. Firstly, compare the SQL code for your DELETE statement with mine. Secondly, you won't find a call to AcceptChanges anywhere in any of my examples, so that part you just made up.