Question Updating a Database

[windymiller]

I have been trying for ages to get VB to give the option to Update the database on Form Close. The database is a single table Access Database.The Binding Navigator works fine, the text boxes and labels are updated as you navigate through the records.Hopefully someone can show me where my Update code is going wrong

Imports System.Object
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.DataSet
Imports System.ComponentModel
Imports System.Collections.Generic



Public Class Form1
Dim FileDuplcateBindingNavigator As New BindingNavigator(True)
Dim FileDuplicatesBindingSource As New BindingSource()
Dim dwg As String
Dim fswMessage As String
Dim fswPath As String
Dim RowIndex As Integer = 0
Dim response As MsgBoxResult
Dim ds As New DataSet
Dim dt As New DataTable
Dim oledbDataAdapter1 As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(oledbDataAdapter1)



Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Book visual basic 2005 page 1056
ds = New DataSet("FileDuplicate")
dt = New DataTable("FileDuplicate")
Dim ConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Peter\Databases\Database1.mdb"
Dim Connection1 As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConnectionString)
Dim command1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM FileDuplicate")
command1.CommandType = CommandType.Text
Connection1.Open()
command1.Connection = Connection1
oledbDataAdapter1.SelectCommand = command1
oledbDataAdapter1.TableMappings.Add("Table", "FileDuplicate")
oledbDataAdapter1.Fill(ds)
oledbDataAdapter1.Fill(dt)
Call Populate()

'Binding navigator class printed from help
Me.FileDuplcateBindingNavigator.BindingSource = Me.FileDuplicatesBindingSource
Me.FileDuplcateBindingNavigator.Dock = DockStyle.Top
Me.Controls.Add(Me.FileDuplcateBindingNavigator)
Me.FileDuplicatesBindingSource.DataSource = ds.Tables("FileDuplicate")
Try
Me.IDTextBox.DataBindings.Add(New Binding("Text", Me.FileDuplicatesBindingSource, "ID", True))
Me.Drawing_NumberTextBox.DataBindings.Add(New Binding("Text", Me.FileDuplicatesBindingSource, "Drawing Number", True))
Me.Created_DateLabel1.DataBindings.Add(New Binding("Text", Me.FileDuplicatesBindingSource, "Created Date", True))
Me.Last_AccessedLabel2.DataBindings.Add(New Binding("Text", Me.FileDuplicatesBindingSource, "Last Accessed", True))
Me.LocationTextBox.DataBindings.Add(New Binding("Text", Me.FileDuplicatesBindingSource, "Location", True))
Me.txtDescription.DataBindings.Add(New Binding("Text", Me.FileDuplicatesBindingSource, "Description", True))
Me.cboType.DataBindings.Add(New Binding("Text", Me.FileDuplicatesBindingSource, "Type", True))
Catch ex As Exception
MsgBox("Update Failed" & ex.Message.ToString)
End Try


'Book visual basic 2005 page 1057
If Connection1.State = ConnectionState.Open Then
lblstatus.Text = "Database connection is Open"
Else
lblstatus.Text = "Database connection failed"
End If

End Sub

Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click

If Me.ds.HasChanges Then

response = MsgBox("Changes have been made do you wish to save changes", vbYesNo, )


If response = MsgBoxResult.Yes Then
'On closing form can update database if needed
Try
Me.Validate()
Me.FileDuplicatesBindingSource.EndEdit()
builder.GetUpdateCommand()
Me.oledbDataAdapter1.Update(Me.ds)
Me.oledbDataAdapter1.Update(Me.ds.Tables("FileDuplicate"))

MsgBox("UpdateSuccesful")
Close()

Catch ex As Exception
MsgBox("Update Failed" & ex.Message)
End Try
End If

End If
Close()
End Sub
Public Sub populate()
Try

IDTextBox.Text = CStr(dt.Rows(RowIndex)("ID"))
Drawing_NumberTextBox.Text = CStr(dt.Rows(RowIndex)("drawing Number"))
Created_DateLabel1.Text = CStr(dt.Rows(RowIndex)("created date"))
Last_AccessedLabel2.Text = CStr(dt.Rows(RowIndex)("Last Accessed"))
LocationTextBox.Text = CStr(dt.Rows(RowIndex)("location"))
txtDescription.Text = CStr(dt.Rows(RowIndex)("Description"))
cboType.Text = CStr(dt.Rows(RowIndex)("Type"))
Catch ex As Exception
MsgBox("Update Failed" & ex.Message.ToString)
End Try
End Sub
End Class

 

[Stonkie]

This is pretty hard to read without the code blocks, but here some suggestion.

First, I didn't find where you close your connection when you fill the dataset. You seem to open it before filling but never close it. Actually, you don't need to open the connection before calling the Fill() method, it opens it automatically.

Second, the table adapter is your all purpose tool for data manipulation. You should not need to use a command builder except for very advanced cases that I don't think apply here.

Third, is there a space in the table name in your code or is it a copy/paste mistake?

Me.oledbDataAdapter1.Update(Me.ds.Tables("FileDupl icate"))

And last, you should be able to use strongly typed features on the dataset like Me.ds.FileDuplicate to get the FileDuplicate DataTable without using an evil string literal (better to find a typing mistake at compile time than runtime).

One last thing, you did not precise what is not working. What is the actual behavior as opposed to the expected behavior? Does it throw an exception, does it simply not save or won't it even compile? What exception/message/stack trace do you get? etc.

 

[cjard]

This is pretty hard to read without the code blocks, but here some suggestion.

By this, stonkie means he wants you to read this page:Visual Basic .NET Forums - BB Code List
and use CODE tags in future

Third, is there a space in the table name in your code or is it a copy/paste mistake?

No, its the forum software inserting a space to present nuisance people deliberately inserting long liens with no spaces to break the layout. It doesnt happen inside code tags

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

The OP should change the way he does his data access; this seems to be gotten from a not-very-well-written book.
For a better way of doing data access, read the DW2 link in my signature, section Creating a Simple Data App.
All your code there can be reduced to about 3 lines

 

[windymiller]

Sorry about the code being hard to read

If I remove the If Me.ds.HasChanges and the Try statement from the Close Button

this is the Error Statement that I get if changes have been made to a textbox

Update FailedSyntax error (missing operator) in query expression '((ID =?) AND ((?
=1 AND Location IS NULL) OR (Location = ?)) AND ((? = 1 AND Drawing Number
IS NULL) OR (Drawing Number = ?)) AND((? = 1 Last Accessed IS NULL) OR
(Last Accessed =?)) AND ((? = 1 AND Created Date IS NULL) OR (Created Date =
?)) AND'.

 

[MattP]

Your expression ends with AND; It's looking for another operator there.

 

[cjard]

I remove the ... Try statement from the Close Button

That does kinda imply that you're Trying, Catching an erro and then not doing anything with the error other than hiding it. Not the best coding practice, IMV

 

[windymiller]

Thankyou for all your comments I purchased another book re-built the database and vb form from scratch it will now update the database from the dataadapter update command

 

[cjard]

Sorry about the code being hard to read

ps; Don't be sorry; fix the problem! Learn what a CODE block is and use it. Thousands of other people on the internet have managed it fine

Visual Basic .NET Forums - BB Code List