Updating database records in VB

[simonfoley]

Hi there

More noob questions from yours truly!

As part of the application I'm writing in Visual Basic, I have written code to display records in text boxes and cycle through each of them one record at a time. I'm having trouble getting the code I've written to update records to work however. I am new to this however, so I'm sure I've made an obviously glaring error that you more experienced people can point out for me:

Public Class frmRecords

    ' VARIABLES TO AID NAVIGATION THROUGH RECORDS
    Dim moverow As Integer
    Dim maxrows As Integer

    ' DEFINES NEW DATA TABLE AND ESTABLISHES CONNECTION PATH TO DATA
    Dim dataCity As New DataTable()
    Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\globexdata\globex.mdb"
    Dim sqlString As String = "SELECT * FROM cities"
    Dim dataAdapt As New OleDb.OleDbDataAdapter(sqlString, connectionString)
    Dim command As New OleDb.OleDbCommandBuilder(dataAdapt)


    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        
        ' POPULATES DATA TABLE & SET VARIABLES FOR RECORD NAVIGATION
        dataAdapt.Fill(dataCity)
        dataAdapt.Dispose()

        maxrows = dataCity.Rows.Count
        moverow = -1

    End Sub

    ' SUBROUTINE TO DISPLAY / NAVIGATE RECORDS
    Private Sub Navigate()
        txtCity.Text = dataCity.Rows(moverow).Item(0)
        txtCountry.Text = dataCity.Rows(moverow).Item(1)
        txt2010pop.Text = dataCity.Rows(moverow).Item(2)
        txt2015pop.Text = dataCity.Rows(moverow).Item(3)

    End Sub

[code for moving thru records here]

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

        dataCity.Rows(moverow).Item(0) = txtCity.Text
        dataCity.Rows(moverow).Item(1) = txtCountry.Text
        dataCity.Rows(moverow).Item(2) = txt2010pop.Text
        dataCity.Rows(moverow).Item(3) = txt2015pop.Text

        command = New OleDb.OleDbCommandBuilder(dataAdapt)

        dataAdapt.Update(dataCity)
        MsgBox("Data updated")
    End Sub

At the present time I get an error that says "The DataAdapter.SelectCommand property needs to be initialized"

[Unhnd_Exception]

I wonder if you have more code than this.

You dispose of the dataAdapter in the load event.

Of course I don't know where your error is.

Seems like you would get an Object reference not set to an instance of an object within your brnUpdate sub.

command.Commandtext = "Update cities Set City1 = 'Jacksonville'
dataAdapt.SelectCommand = command

[ShahanDev]

Hi!

As an alternative you can use this for your btnUpdate code:

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

        dataCity.Rows(moverow).Item(0) = txtCity.Text
        dataCity.Rows(moverow).Item(1) = txtCountry.Text
        dataCity.Rows(moverow).Item(2) = txt2010pop.Text
        dataCity.Rows(moverow).Item(3) = txt2015pop.Text

        dataAdapt = New OleDb.OleDbDataAdapter(sqlUPDATEString, connectionString)

        dataAdapt.Update(dataCity)
        MsgBox("Data updated")
    End Sub

or check this link for a proper methodology and clarification:
http://msdn.microsoft.com/en-us/library/z1z2bkx2.aspx