• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1405
  • Last Modified:

Updating access database using buttons in vb.net

I am trying to update a database record when a user amends it. I have a textbox called 'txtColsTextBox' which stores the value and a button called 'btnSaveExit'. On the button click, I need to update the db with new value. How would I do this based on my code. I am thinking , i need to use me.validate function but not sure how to code. Thanks

        Dim connetionString As String
        Dim oledbCnn As OleDbConnection
        Dim oledbCmd As OleDbCommand
        Dim sql As String

        connetionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\csm\storage.accdb"
        sql = "SELECT Cols FROM Racks Where [Rack code] = '" & buttonName & "'"
            oledbCnn = New OleDbConnection(connetionString)
        Try
            oledbCnn.Open()
            oledbCmd = New OleDbCommand(sql, oledbCnn)
            Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()
            While oledbReader.Read
                'MsgBox(oledbReader.Item(0))
                txtColsTextBox.Text = oledbReader.Item(0)
            End While
            oledbReader.Close()
            oledbCmd.Dispose()
            oledbCnn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Open in new window

0
peter-cooper
Asked:
peter-cooper
  • 3
  • 3
1 Solution
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I would recommend using an Update query.

Execute something like this:

Update Racks SET Cols = " & txtColsTextBox.Text  & " Where [Rack code] = '" & buttonName & "'"

Open in new window


** Aircode - Not tested
0
 
peter-cooperAuthor Commented:
@TheHiTechCoach

Would I place this in my button click event? Also, do I not need to reference an adapter of sorts to perform the update? Thanks
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You would use this in the click event in place of your code using oledbReader.

try something like this:

Dim myConnection As New OleDb.OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\csm\storage.accdb")

Dim SQLCmd As New OleDbCommand
Dim SQLStr As String = "Update Racks SET Cols = " & txtColsTextBox.Text  & " Where [Rack code] = '" & buttonName & "'"

myConnection.Open()
SQLCmd.Connection = myConnection
SQLCmd.CommandText = SQLStr
SQLCmd.ExecuteNonQuery()

myConnection.Close()

Open in new window


also see:
http://www.codeproject.com/Articles/26801/Insert-Update-Delete-Search-Values-in-MS-Access-20

Insert, Update, Delete & Search Values in MS Access 2003 with VB.NET 2005

Introduction

This article and sample covers that "How to Insert, Update, Delete and Search Values to/from MS Access database 2003 with VB.NET 2005", Although VB.NET 2005 gives lot more methodologies to do this same job but I find this more easier, simplest and understandable for a beginner.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
peter-cooperAuthor Commented:
Thanks for the informative link and your help in solving my problem.
0
 
peter-cooperAuthor Commented:
Thank you
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You're welcome. Glad we could assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now