Updating Access file from VB 2010 - ALevel project - HELP :)

[James.M.Dallimore]

Hi all,
Had alot of problems with writing this code to update an access database from vb 2010 - even my computing teacher is at a dead end as what we havent tried. - Da.update(ds, "stafftable")

Basicallly i can navigate records fine - but as soon as i try to update from user inputted data VB gives me olbexceptions. Im at a loss as what to do now. - Code is below - everything declared in a seperate module

Thanks james

Public Class frmMain

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        NavigateRecords("First")
    End Sub

    Private Sub frmMain_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        con.Close()
    End Sub

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

        intCounter = 0
    End Sub

    Sub AddRecordsToForm()
        txtsurname.Text = ds.Tables("stafftable").Rows(0).Item(1)
        txtname.Text = ds.Tables("stafftable").Rows(0).Item(2)
        txtemail.Text = ds.Tables("stafftable").Rows(0).Item(3)
        txtjobtitle.Text = ds.Tables("stafftable").Rows(0).Item(4)
        txthomephone.Text = ds.Tables("stafftable").Rows(0).Item(5)
        txtmobilephone.Text = ds.Tables("stafftable").Rows(0).Item(6)
        txtaddress.Text = ds.Tables("stafftable").Rows(0).Item(7)
    End Sub

    Sub NavigateRecords(ByVal MyWay As String)


        intMaxRows = ds.Tables("stafftable").Rows.Count


        Select Case MyWay
            Case "First"
                If intCounter <> 0 Then
                    intCounter = 0
                End If
            Case "Last"
                If intCounter <> intMaxRows Then
                    intCounter = intMaxRows - 1
                End If
            Case "Next"
                If intCounter <> intMaxRows - 1 Then
                    intCounter = intCounter + 1
                Else
                    MsgBox("No more records available")
                End If
            Case "Previous"
                If intCounter > 0 Then
                    intCounter = intCounter - 1
                Else
                    MsgBox("First Record")
                End If
        End Select

        txtsurname.Text = ds.Tables("stafftable").Rows(intCounter).Item(1)
        txtname.Text = ds.Tables("stafftable").Rows(intCounter).Item(2)
        txtemail.Text = ds.Tables("stafftable").Rows(intCounter).Item(3)
        txtjobtitle.Text = ds.Tables("stafftable").Rows(intCounter).Item(4)
        txthomephone.Text = ds.Tables("stafftable").Rows(intCounter).Item(5)
        txtmobilephone.Text = ds.Tables("stafftable").Rows(intCounter).Item(6)
        txtaddress.Text = ds.Tables("stafftable").Rows(intCounter).Item(7)


    End Sub



    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        NavigateRecords("Previous")
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        NavigateRecords("Next")
    End Sub

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

    Sub UpdateRecord()

        Dim cb As New OleDb.OleDbCommandBuilder(da) 'The command builder is needed to update the table

        'Set the dynaset to the amended text in the text boxes
        ds.Tables("stafftable").Rows(intCounter).Item(1) = txtsurname.Text
        ds.Tables("stafftable").Rows(intCounter).Item(2) = txtname.Text
        ds.Tables("stafftable").Rows(intCounter).Item(3) = txtemail.Text
        ds.Tables("stafftable").Rows(intCounter).Item(4) = txtjobtitle.Text
        ds.Tables("stafftable").Rows(intCounter).Item(5) = txthomephone.Text
        ds.Tables("stafftable").Rows(intCounter).Item(6) = txtmobilephone.Text
        ds.Tables("stafftable").Rows(intCounter).Item(7) = txtaddress.Text
        'This does not update the actual database
        'The dynaset is a copy of the table
        'This code will update the table

        da.Update(ds, "stafftable")

        MsgBox("Record has been updated")

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        ClearForm()
    End Sub

    Sub ClearForm()
        'Clear the text boxes in the form
        txtsurname.Clear()
        txtname.Clear()
        txtjobtitle.Clear()
        txtemail.Clear()

    End Sub

    Sub CommitNewRecord()

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        'Add a new row to your dynaset
        dsNewRow = ds.Tables("stafftable").NewRow()

        dsNewRow.Item("Surname") = txtsurname.Text
        dsNewRow.Item("Forename") = txtname.Text
        dsNewRow.Item("email") = txtemail.Text
        dsNewRow.Item("Job Title") = txtjobtitle.Text
        dsNewRow.Item("Home Phone") = txthomephone.Text
        dsNewRow.Item("Mobile Phone") = txtmobilephone.Text
        dsNewRow.Item("Address") = txtaddress.Text

        ds.Tables("stafftable").Rows.Add(dsNewRow)
        da.Update(ds, "stafftable")

    End Sub


    Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        CommitNewRecord()
        'Reconnect to database and fill form
        'DisconnectFromDatabase()
        'ConnectToDatabase()
        'AddRecordsToForm()
        MsgBox("Record has been added to the database")

    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        NavigateRecords("Last")
    End Sub

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

        'Reconnect to database and fill form
        'DisconnectFromDatabase()
        'ConnectToDatabase()
        'AddRecordsToForm()
        MsgBox("Record has been deleted from the database")
    End Sub

    Sub DeleteCurrentRecord()
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("stafftable").Rows(intCounter).Delete()
        da.Update(ds, "stafftable")
    End Sub

    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
        ClearForm()
    End Sub

    Sub SearchRecord(ByVal strSurname As String)
        'Search by surname
        Dim x As Integer
        Dim strCurrent As String
        Dim bolFound As Boolean

        bolFound = False

        intMaxRows = ds.Tables("stafftable").Rows.Count
        For x = 0 To intMaxRows - 1
            strCurrent = ds.Tables("stafftable").Rows(x).Item(2)
            If UCase(strCurrent) = UCase(strSurname) Then
                txtsurname.Text = ds.Tables("stafftable").Rows(x).Item(1)
                txtname.Text = ds.Tables("stafftable").Rows(x).Item(2)
                txtemail.Text = ds.Tables("stafftable").Rows(x).Item(3)
                txtjobtitle.Text = ds.Tables("stafftable").Rows(x).Item(4)
                bolFound = True
            End If
        Next x

        If bolFound = False Then
            MsgBox("Sorry, surname not found")
        End If
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim strSurname As String

        strSurname = InputBox("Please enter Surname")
        SearchRecord(strSurname)
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Con.Close()
        Me.Close()
    End Sub
End Class

[john.knapp]

What exceptions do you get?
Can you provide the code for the external module?

[James.M.Dallimore]

oledbexception was unhandled
Syntax error in INSERT INTO statement

Module code -

Module Module1
    Public intMaxRows As Integer
    Public intCounter As Integer
    Public con As New OleDb.OleDbConnection
    Public Const dbProvider As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    Public Const dbSource As String = "Data Source = F:\A2 Computing\StaffDatabase.mdb"

    Public ds As New DataSet
    Public da As OleDb.OleDbDataAdapter

    Public Const sql As String = "select * from staff"

    Sub ConnectToDatabase()
        'Connect to 
        con.ConnectionString = dbProvider & dbSource
        con.Open()

        'This takes the data from the Access table and puts it into the form
        'It will show the first record in the form
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "stafftable")

    End Sub

    Sub DisconnectFromDatabase()
        'clear the dynaset
        ds.Clear()
        con.Close()
    End Sub


End Module

thanks

[john.knapp]

Ok, I've managed to recreate that error now. I don't believe you have an INSERT or UPDATE statement in your DataAdapter (looking now) - see this thread for details on a similiar situation.

[john.knapp]

Did you set the datasource in Visual Studio? When I recreated the project, I added the datasource and the designer created datatables, commands, etc. - do you have anything corresponding to that?

[john.knapp]

You're missing some functionality.

1. The dataset is not bound to an actual table in the database, it just retrieves the data
2. The INSERT INTO command is being called even when it should be an UPDATE statement
3. Item Three

How did you create this project and add the datasource, did you follow a tutorial, textbook, or ?

Can you zip and email the project to me? Email in PM

[James.M.Dallimore]

Followed a tutorial - first time coding in VB 2010 and before that only basic pascal ( old i know ) email being sent now :)

[john.knapp]

You need to code the statements for the DataAdapter's Update, Insert, Delete, and Search methods. You can get the statements themselves by using the OleDb.OleDbCommandBuilder to generate your statements, and then all you need to do is add the parameters.

da = New OleDb.OleDbDataAdapter(sql, con)
Dim cb As New OleDb.OleDbCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.UpdateCommand = cb.GetUpdateCommand
da.DeleteCommand = cb.GetDeleteCommand
' haven't figured out parameters yet
' but they should probably go here
' then you would set their values per
' the respective textbox
da.Fill(ds, "stafftable")

Try this link for example code - I'll "fix" your Insert, but then you're on your own... :)

ps. I don't understand why the teachers couldn't see this issue... granted, it did take me some hardcore "googling" to figure it out