Update SINGLE field of a table in Access db.

Question

How do you update a single field in an Access database using an asp.net website in VisualStudio08. Assuming that the connection has been established, give step to step instructions of what to do in the design view.

Answer 1

Assuming you want this done on some postback event like a button click, you need to (this is from memory, I don't have it in front of me!):

1. click on the button, show its properties, then the events tab.
2. double-click in the OnClick space to create a new event handler (or enter a name directly)

3. In the event handler in the code window, invoke some SQL something like

   update table set field=value where field2=identifier

(or you could use the LINQ equivalent) Where table, field, field2, value and identifier should be replaced with specific names to suit your database.

You could create a SqlCommand::ExecuteNonQuery instance to run the SQL.

Answer 2

Here is a console app that shows you how to use ADO.NET to update an Access DB. An alternate is to use Linq. You could add a method to your CodeBehind that does something like this, and call it from your OnClick event handler.

Option Explicit On Option Strict On

Imports System Imports System.Data Imports System.Data.OleDb

Public Class Program Public Shared Sub Main()

    Dim connectionString As String = GetConnectionString()
    Dim queryString As String = _
        "UPDATE Categories Set CategoryName = 'ABC' WHERE CategoryID = 1;"

    Using connection As New OleDbConnection(connectionString)
        Dim command As OleDbCommand = connection.CreateCommand()
        command.CommandText = queryString
        Try
            connection.Open()

            Dim rowsAffected As Integer = command.ExecuteNonQuery()


        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using
End Sub

Private Shared Function GetConnectionString() As String
    ' To avoid storing the connection string in your code,  
    ' you can retrieve it from a configuration file.
    ' Assumes Northwind.mdb is located in c:\Data folder.
    Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
       & "c:\Data\Northwind.mdb;User Id=admin;Password=;"
End Function

End Class