Question export list box data to database

[ljvictorio]

I have a simple calculator app. The user enters numbers into two separate text boxes. The user can press one of four buttons for each arithmetic operand. The result is displayed in the result label. If the user presses the Display button, the entire operation is displayed in a list box, which can store 10 math problems. This is my code, which works fine.

Public Class Calculator

    Dim strEquation As String
    Dim myMathClass As New MathOp2


    Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click


        lblResult.Text = myMathClass.Add(CSng(txtFirstNumber.Text), CSng(txtSecondNumber.Text)).ToString
        strEquation = myMathClass.EquationUsed
        btnSave.Focus()
    End Sub


    Private Sub btnSubtract_Click(sender As Object, e As System.EventArgs) Handles btnSubtract.Click


        lblResult.Text = myMathClass.Subtract(CSng(txtFirstNumber.Text), CSng(txtSecondNumber.Text)).ToString
        strEquation = myMathClass.EquationUsed
        btnSave.Focus()
    End Sub


    Private Sub btnDivide_Click(sender As Object, e As System.EventArgs) Handles btnDivide.Click


        lblResult.Text = myMathClass.Divide(CSng(txtFirstNumber.Text), CSng(txtSecondNumber.Text)).ToString
        strEquation = myMathClass.EquationUsed
        btnSave.Focus()
    End Sub


    Private Sub btnMultiply_Click(sender As Object, e As System.EventArgs) Handles btnMultiply.Click


        lblResult.Text = myMathClass.Multiply(CSng(txtFirstNumber.Text), CSng(txtSecondNumber.Text)).ToString
        strEquation = myMathClass.EquationUsed
        btnSave.Focus()
    End Sub


    Private Sub btnSave_Click(sender As Object, e As System.EventArgs) Handles btnSave.Click
        'The save button concatenates variables into the arithmetic operation


        'Set the focus to Display
        btnDisplay.Focus()
    End Sub


    Private Sub btnDisplay_Click(sender As Object, e As System.EventArgs) Handles btnDisplay.Click


        lstResultsBox.Items.Add(myMathClass.EquationUsed)
        txtFirstNumber.Focus()


        If lstResultsBox.Items.Count > 10 Then
            MessageBox.Show("You have saved 10 equations. Please clear the list to save additional problems.",
                            "Maximum number of saved problems", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
    End Sub


    Private Sub btnClear_Click(sender As Object, e As System.EventArgs) Handles btnClear.Click
        'Clear all saved operations in the listbox
        lstResultsBox.Items.Clear()
        txtFirstNumber.SelectAll()
        txtFirstNumber.Focus()
    End Sub
    Private Sub txtFirstNumber_GotFocus(sender As Object, e As System.EventArgs) Handles txtFirstNumber.GotFocus
        'Select all text when this textbox receives focus
        txtFirstNumber.SelectAll()
    End Sub


    Private Sub txtSecondNumber_GotFocus(sender As Object, e As System.EventArgs) Handles txtSecondNumber.GotFocus
        'Select all text when this textbox receives focus
        txtSecondNumber.SelectAll()
    End Sub


    Private Sub lstResultsBox_SelectedValueChanged(sender As Object, e As System.EventArgs) Handles lstResultsBox.SelectedValueChanged


        'Declare variables
        Dim strSelected As String
        Dim strSplitSelected() As String


        'Use Split method to turn the selected result into an array and used the 
        'Array index to place the user selected number into the proper textbox. 


        strSelected = lstResultsBox.SelectedItem.ToString
        strSplitSelected = strSelected.Split(" "c)
        txtFirstNumber.Text = strSplitSelected(0)
        txtSecondNumber.Text = strSplitSelected(2)
        lblResult.Text = String.Empty


    End Sub


End Class

What I want to do now is to include an Import and Export button on the form. The export button should save the 10 equations currently in the list box into an Access database file. Similarly, the Import button should bring the equations saved in the database file into the listbox. I've read up on using a DataGridView control, but I'm not sure this is my answer. Can I make this work using the current list box. Does anyone have any advice on what approach I should take? Any help would be greatly appreciated.

 

[IanRyder]

Hi,

Before I suggest anything, do you have any experience with using an Access Database and also using Connection and DataAdapter objects to interact with a database?

Cheers,

Ian

 

[ljvictorio]

Ian,

Yes, I do have experience with Access and basic SQL commands. I have been reading about data access components in VB.NET, and tried experimenting with the available data access wizards. I am aware of the objConnection and objDataAdapter objects, and that they need to be declared along with a DataSet object, but overall this is new to me.

Thanks,

Larry

 

[IanRyder]

Hi,

In that case, this example should be easy to get your head round.

1) If not already done so, create the Formula table in your Access Database

2) Open up your Data Sources and use the "Add New Data Source" wizard to add the Access table to your Project. This will create 3 objects being, a DataSet, a TableAdapterManager and a specific TableAdapter for your Formula Table, i.e tblFormulasTableAdapter.

3) Now drag the DataSet object and the SpecificTableAdapter object from your toolbox to the form.

3) Add a ListView to your project and set the DataSource to the Formula table in your DataSet, its ValueMember property to an IDNo, for instance, and the DisplayMember property to the Formula field in the table.

4) Now drop Buttons onto the project to allow you to Add Formulas to the DataTable in the DataSet and Save the Table bask to the Database.

5) Now incorporate the following code into the corresponding events of your own project.

Public Class Form1
 
  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    'Fill the Typed DataSet with the Formula Table From the Data Source
    TblFormulasTableAdapter1.Fill(TempDataSet1.tblFormulas)
  End Sub
 
  Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
    'When you want to Save the Forumlas just call Update on the Table Adapter
    TblFormulasTableAdapter1.Update(TempDataSet1.tblFormulas)
  End Sub
 
  Private Sub btnAddFormula_Click(sender As System.Object, e As System.EventArgs) Handles btnAddForumla.Click
    'To add a new formula to the ListBox just add a new row the the Forumla table in the Dataset
    If Not TextBox1.Text = String.Empty Then
      TempDataSet1.tblFormulas.AddtblFormulasRow(TextBox1.Text)
    End If
  End Sub
End Class

Hope that helps.

Cheers,

Ian

 

[ljvictorio]

Ian,

Your example explained a few things to me more clearly than a couple of the books I am reading.
I followed your code example pretty close, but I'm still coming up with errors.

Private Sub Calculator_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'SavedOperationsDataSet1.Forumla' table. You can move, or remove it, as needed.
        Me.ForumlaTableAdapter.Fill(Me.SavedOperationsDataSet1.Forumla)


    End Sub


    Private Sub btnRetrieve_Click(sender As Object, e As System.EventArgs) Handles btnRetrieve.Click
        'Retrieve button gets values from database to insert back into listbox
        SavedOperationsDataSet1.Forumla.AddForumlaRow(lstResultsBox.Items)
    End Sub


    Private Sub btnUpdate_Click(sender As Object, e As System.EventArgs) Handles btnUpdate.Click
        'Update button saves current listbox equations to database 
        ForumlaTableAdapter.Update(SavedOperationsDataSet1.Forumla)
    End Sub

First, "Forumla" is correctly misspelled, which I mistakenly titled the table.

SavedOperations database
Forumla table
Formula column
(in case this is of any importance)

In regards to the Retrieve button, which should pull items out of the database, I am getting an error
concerning a listbox item that cannot be converted to a FormulaRow. If I understand the error correctly,
it makes sense that a list of equations obviously cannot be converted to a single equation. I was attempting
to take each equation (or row in the database) and bring it into the ListBox, but where did I go wrong. I know you
mentioned adding a ListView to the project, but I wanted to keep the ListBox because of other code attached to it.

As far as the Update button, which should save the current listbox items back to the Access database, I get the
classic error that an object reference is not set to an instance of an object.
This seems pretty simple, but I am clearly overlooking quite a bit.


Here's a corny picture of my project just in case.

It is quite refreshing to discover that there are people willing to help others. Thanks for taking the time to read
my questions and respond with such eloquence.

Cheers,

Larry

 

[IanRyder]

Hi Larry,

I know you mentioned adding a ListView to the project, but I wanted to keep the ListBox because of other code attached to it.

My apologies, I meant ListBox and not ListView. I would then suggest that you re-try from my point No.3 since you are misunderstanding a couple of points which is probably my fault for saying ListView - Doh!!:-

1) In the Form Load event, you FILL the Formula table in the DataSet which should be applied to the DataSoure of the ListBox. This then automatically populates the ListBox with the rows of the DataTable once you have correctly set the DisplayMember and ValueMember properties of the ListBox.

2) Once you have got point 1 correct, and being done in the Load event, then the PRINCIPAL of this "btnRetrieve" section of code becomes totally redundant:-

Private Sub btnRetrieve_Click(sender As Object, e As System.EventArgs) Handles btnRetrieve.Click
  'Retrieve button gets values from database to insert back into listbox
  SavedOperationsDataSet1.Forumla.AddForumlaRow(lstResultsBox.Items)
End Sub

As I said the, the above principal in not necessary, however the code you are trying to use in this code block is incorrect for retrieving information from the Database since this is explained in point 1 above.

However, this item of code:-

SavedOperationsDataSet1.Forumla.AddForumlaRow(lstResultsBox.Items)

is to allow you to add NEW SINGLE ROWS to the DataTable in the DataSet. Therefore this should be adding the new formulas, from the Label or TextBox which your project has generated. i.e:-

SavedOperationsDataSet1.Forumla.AddForumlaRow(TextBox1.Text)

Since the DataTable has been bound to the ListBox as its DataSource as soon as you add the new Formula to the DataTable it is automatically visible in the ListBox.

3) Your update code looks correct so once the above is done you should be sorted.

Hope that helps.

Cheers,

Ian

 

[ljvictorio]

Thanks for the clarification. Quick question. I am still getting an InnerException stating that the
Object reference is not set to an instance of an object. Do I still need to declare the Connection
and DataAdapter objects in code when using the data access wizard, or is that taken care of
for me through the wizard?

 

[IanRyder]

Hi,

I am still getting an InnerException stating that the Object reference is not set to an instance of an object. Do I still need to declare the Connection and DataAdapter objects in code when using the data access wizard, or is that taken care of for me through the wizard?

No, this is taken care of for you since when you use the Data Source Wizard it creates the objects you need, which are then placed in your ToolBox, and the connection string is saved in the app.config file.

You typically get this error when you have declared a variable of an object type but you have NOT actually created that object in memory using the New Keyword.

If you are still struggling then post the relevant code that is causing the error and we should be able to help you further.

Hope that helps.

Cheers,

Ian