2 Replies - 4785 Views - Last Post: 22 January 2012 - 10:38 AM Rate Topic: -----

#1 shoucostin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-January 12

How To Update Data in MS Access Database in VB 2008

Posted 22 January 2012 - 06:23 AM

Okay, so I'm actually making a library system and I'm having problems with my data not saving in the database.
It does appear in the DataGrid but after I close the application and check the database, nothing's there. What to do? Help would be GREATLY appreciated. http://cdn2.dreaminc...fault/laugh.gif

Here's the code.

Imports System.Data.OleDb
Imports System.Object

Public Class AddBook
    Dim CN As New System.Data.OleDb.OleDbConnection
    Dim CNString As String

    Public Sub Database()
        CNString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=THLibraryDatabase.mdb"
        CN.ConnectionString = CNString
        CN.Open()
    End Sub

    Private Sub getBookNumber()
        Dim SQL As String = "SELECT * FROM tableBooks " & _
        "ORDER BY BookNumber DESC"

        Dim DBR As OleDbDataReader
        Dim DBC As OleDbCommand = New OleDbCommand(SQL, CN)
        DBR = DBC.ExecuteReader()
        If DBR.Read() = True Then
            BookNumberTextBox.Text = Val(DBR.Item(0)) + 1
            BookNumberTextBox.Enabled = False
        End If
    End Sub

    Private Sub AddBook()
        If MessageBox.Show("Are you sure you want to add book?", "Add Book Notification", _
                               MessageBoxButtons.YesNo, MessageBoxIcon.Question) = _
                               Windows.Forms.DialogResult.Yes Then
            Dim SQL As String = "INSERT INTO tableBooks " & _
            "VALUES ('" & BookNumberTextBox.Text & _
            "','" & SubjectComboBox.Text & _
            "','" & TitleTextBox.Text & _
            "','" & AuthorTextBox.Text & _
            "','" & PublisherTextBox.Text & _
            "','" & DatePublishedPicker.Text & _
            "','" & ISBNTextBox.Text & _
            "','" & EditionTextBox.Text & _
            "','" & QuantityTextBox.Text & "')"

            Dim DBC As OleDbCommand = New OleDbCommand(SQL, CN)
            DBC.ExecuteNonQuery()

            MessageBox.Show("Book successfully added!", "Add Book Notification", _
                           MessageBoxButtons.OK, MessageBoxIcon.Information)
            Call getBookNumber()
            Call Clear()
            CN.Close()
            Me.Close()
        End If
    End Sub

    Private Sub Clear()
        BookNumberTextBox.Clear()
        SubjectComboBox.SelectedItem = Nothing
        TitleTextBox.Clear()
        AuthorTextBox.Clear()
        PublisherTextBox.Clear()
        DatePublishedPicker.Text = ""
        ISBNTextBox.Clear()
        EditionTextBox.Clear()
        QuantityTextBox.Clear()
    End Sub

    Private Sub AddBook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call Database()
        Call getBookNumber()
    End Sub

    Private Sub AddButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddButton.Click
        If BookNumberTextBox.Text = "" Then
            MessageBox.Show("Please provide required information.", "Required Field", _
                            MessageBoxButtons.OK, MessageBoxIcon.Information)
            BookNumberTextBox.Focus()
        ElseIf SubjectComboBox.SelectedItem = Nothing Then
            MessageBox.Show("Please provide required information.", "Required Field", _
                            MessageBoxButtons.OK, MessageBoxIcon.Information)
            SubjectComboBox.Focus()
        ElseIf TitleTextBox.Text = "" Then
            MessageBox.Show("Please provide required information.", "Required Field", _
                            MessageBoxButtons.OK, MessageBoxIcon.Information)
            TitleTextBox.Focus()
        ElseIf AuthorTextBox.Text = "" Then
            MessageBox.Show("Please provide required information.", "Required Field", _
                            MessageBoxButtons.OK, MessageBoxIcon.Information)
            AuthorTextBox.Focus()
        Else
            If Not CN.State = ConnectionState.Open Then
                CN.Open()
            End If
            Call AddBook()
        End If
    End Sub

    Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelButton.Click
        Call Clear()
        CN.Close()
    End Sub
End Class



Is This A Good Question/Topic? 0
  • +

Replies To: How To Update Data in MS Access Database in VB 2008

#2 VIPER5646  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 90
  • Joined: 01-March 09

Re: How To Update Data in MS Access Database in VB 2008

Posted 22 January 2012 - 07:12 AM

We need to know which version of Microsoft Access are you using.
In the Addbook() I would use the following when assigning the command object .

 Dim DBC As OleDbCommand = New OleDbCommand(Sql, CN)
        With DBC
            .CommandType = CommandType.Text
            .CommandText = Sql
            .ExecuteNonQuery
        End With
 

This post has been edited by VIPER5646: 22 January 2012 - 07:31 AM

Was This Post Helpful? 0
  • +
  • -

#3 shoucostin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-January 12

Re: How To Update Data in MS Access Database in VB 2008

Posted 22 January 2012 - 10:38 AM

I'm using MS Access 2003 Format.

In AddBook(), I've actually added this one:
Dim DBC As OleDbCommand = New OleDbCommand(SQL, CN)
044
            DBC.ExecuteNonQuery()




instead of this one:

Dim DBC As OleDbCommand = New OleDbCommand(Sql, CN)
       With DBC
           .CommandType = CommandType.Text
           .CommandText = Sql
           .ExecuteNonQuery
       End With


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1