locked
Why Won't My Database Update? RRS feed

  • Question

  • Moderators:  Please move this if it is posted in the wrong forum.

     

    I am extremely new to programing and am trying to teach myself Visual Basic.  I wrote a program that connects to a database.  It successfully reads the table, however it will not update.  Can someone look at m y code below and try to see what's wrong?  Thanks.

    Code Snippet

     

    Public Class frmCalls

        'database declarations

        Dim MyConnection As OleDbConnection

        Dim callsCommand As OleDbCommand

        Dim callsAdapter As OleDbDataAdapter

        Dim callsTable As DataTable

        Dim callsManager As CurrencyManager

        'variable declarations

        Dim maxrows As Integer

        Dim inc As Integer

        Dim countrows As Integer

        Dim lowestdate As Integer

        Dim testdate As Integer

       

       

        Private Sub frmCalls_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

           

            'connect to books database

            MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = c:\leads.mdb")

            'open the connection

            MyConnection.Open()

            'establish command object

            read = "select * " + "from " + Selection

            callsCommand = New OleDbCommand(read, MyConnection)

            'establish data adapter/data table

            callsAdapter = New OleDbDataAdapter()

            callsAdapter.SelectCommand = callsCommand

            callsTable = New DataTable()

            callsAdapter.Fill(callsTable)

            'bind controls to data table

            txtFirst.DataBindings.Add("Text", callsTable, "Borrower First Name")

            txtLast.DataBindings.Add("Text", callsTable, "Borrower Last Name")

            txtCofirst.DataBindings.Add("Text", callsTable, "Co-Borrower First Name")

            txtColast.DataBindings.Add("Text", callsTable, "Co-borrower Last Name")

            txtpropStreet.DataBindings.Add("Text", callsTable, "Property Address")

            txtpropUnit.DataBindings.Add("Text", callsTable, "Property Unit")

            txtpropCity.DataBindings.Add("Text", callsTable, "Property City")

            txtpropState.DataBindings.Add("Text", callsTable, "Property State")

            txtpropZip.DataBindings.Add("Text", callsTable, "Property Zip")

            txtMailStreet.DataBindings.Add("Text", callsTable, "Mailing Address")

            txtMailUnit.DataBindings.Add("Text", callsTable, "Mailing Unit")

            txtMailCity.DataBindings.Add("Text", callsTable, "Mailing City")

            txtMailState.DataBindings.Add("Text", callsTable, "Mailing State")

            txtMailZip.DataBindings.Add("Text", callsTable, "Mailing Zip")

            txthome.DataBindings.Add("Text", callsTable, "Home Phone")

            txtwork.DataBindings.Add("Text", callsTable, "Work Phone")

            txtCell.DataBindings.Add("Text", callsTable, "Cell Phone")

            txtEmail.DataBindings.Add("Text", callsTable, "Email Address")

            txtcurlender.DataBindings.Add("Text", callsTable, "Current Lender")

            txtCuramount.DataBindings.Add("Text", callsTable, "Current Loan Amount")

            txtCurRate.DataBindings.Add("Text", callsTable, "Current Rate")

            txtCurPayment.DataBindings.Add("Text", callsTable, "Current Payment")

            txtCurType.DataBindings.Add("Text", callsTable, "Current Rate Type")

            txtCurDate.DataBindings.Add("Text", callsTable, "Current Origination Date")

            txtBesttime.DataBindings.Add("Text", callsTable, "Best Time")

            txtPurpose.DataBindings.Add("Text", callsTable, "Purpose of Loan")

            txtLasttime.DataBindings.Add("Text", callsTable, "Last Call")

            txtLastdisposition.DataBindings.Add("Text", callsTable, "Last Disposition")

            txtLastlo.DataBindings.Add("Text", callsTable, "Last LO")

            'establish currency manager

            callsManager = DirectCast(Me.BindingContext(callsTable), CurrencyManager)

     

            'find the oldest date

            maxrows = callsTable.Rows.Count

            countrows = 0

            lowestdate = 0

            testdate = 1

            While countrows <> maxrows - 1

                If (callsTable.Rows(lowestdate).Item(27) < callsTable.Rows(testdate).Item(27)) And (callsTable.Rows(lowestdate).Item(30).ToString = "0") And (callsTable.Rows(lowestdate).Item(31).ToString = "0") And (callsTable.Rows(lowestdate).Item(32).ToString = "0") Then

                    testdate = testdate + 1

                    countrows = countrows + 1

                Else

                    If callsTable.Rows(testdate).Item(30) = 0 And callsTable.Rows(testdate).Item(31) = 0 And callsTable.Rows(testdate).Item(32) = 0 Then

                        lowestdate = testdate

                        testdate = testdate + 1

                        countrows = countrows + 1

                    Else

                        testdate = testdate + 1

                        countrows = countrows + 1

                    End If

                End If

            End While

     

    callsManager.Position = lowestdate

      'update last call time

      callsAdapter.Update(callsTable)

           

            'close the connection

            MyConnection.Close()

            'dispose of the connection object

            MyConnection.Dispose()

            callsCommand.Dispose()

            callsAdapter.Dispose()

            callsTable.Dispose()

        End Sub

     

    Thursday, April 19, 2007 12:52 PM

Answers

  • Also, I know your learning, the data adapter will open and close your database connection automatically for you. You do not need to explicitly open and close your connection.

     

    Keeping a database connection open for a long time is generally a bad practise. It's recommend that you open the connection, work the data, and then close the connection. In your code above you do a lot of work on data thats held in the data table however your connection is left open while you loop the table and data bind, this is a long time.

     

    The data adapter actually leaves the connection the way it was found. So for example

     

    if your connection is closed and you call Fill() the data adapter opens the connection and then closes it.

    if your connection is open and you call Fill() the data adapter doesn't open the connection and it also doesn't close it.

     

    Hope that was also helpful

    Thursday, April 19, 2007 2:22 PM

All replies

  • Hi there,

     

    It's a very simple thing you have missed out. In your data adapter you set a SelectCommand like this...

     

            callsAdapter.SelectCommand = callsCommand

     

    This SelectCommand is used by the adapter to select the data, which is working. The data adapter also has an UpdateCommand (it also has DeleteCommand and InsertCommand) that you must set to the Commands that are called to perform the update action. Your database isn't updating because you don't have an UpdateCommand specified.

     

    There is an object called a CommandBuilder which you can use to build an update sql statement from a select sql statement, it is fairly easy to use and I'll let you look into that yourself (I don't have a code example handy).

     

    The reason why the commands have to be specified seperately and not automatically generated by the data adapter is because you may, for example, select the data using a query (or view) and then you may update the data (or a subset of the data) using the table directly (or using a stored procedure), it really depends on the type of database you use, whether it's Access, Oracle or SQL Server, and also the requirements of the software.

     

    Hope that was helpful.

    Thursday, April 19, 2007 2:13 PM
  • Also, I know your learning, the data adapter will open and close your database connection automatically for you. You do not need to explicitly open and close your connection.

     

    Keeping a database connection open for a long time is generally a bad practise. It's recommend that you open the connection, work the data, and then close the connection. In your code above you do a lot of work on data thats held in the data table however your connection is left open while you loop the table and data bind, this is a long time.

     

    The data adapter actually leaves the connection the way it was found. So for example

     

    if your connection is closed and you call Fill() the data adapter opens the connection and then closes it.

    if your connection is open and you call Fill() the data adapter doesn't open the connection and it also doesn't close it.

     

    Hope that was also helpful

    Thursday, April 19, 2007 2:22 PM
  • Derek, thanks for the help.  That fixed it!  Quck question regarding what you are saying about opening and closing the connection.  So I don't have to have the line of code saying MyConnection.open() ?  Basically you are saying that all I have to do is create the command that the adapter will use and specify the connection in that command, and when the adapter runs it, it will open and close it?   Hope I got that correctly.  Thanks again!
    Monday, April 23, 2007 2:47 AM
  • Hi,

     

    Yeah thats correct, data adapters are 'tidy' so to speak.

     

    When you use an adapter and the connection is closed it opens it, does it's stuff, and then closes the connection automatically. You don't need to call MyConnection.Open(), or MyConnection.Close().

     

    If the connection is already open, then the adapter does it's stuff using the open connection and when it's finished it leaves the connection open. 

     

    The reason an adapter leaves the connection open is this.... say you had three adapters and you call each adapters Fill() method one after the other. If the connection is closed then each individual adapter opens and closes the connection resulting in three opens and three closes. If you open the connection just before calling the three adapters then the connection remains open for all adapters, meaning the connection is only opened and closed once.

     

    Database Connections are considered expensive resourses so how you use them directly affects your applications performance. You generally don't see it stand alone applications but once you go multi-user you need to think about them some more.

     

    Hope that helped some more.

     

     

    Monday, April 23, 2007 8:02 AM