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.

Were none of the solutions here: http://stackoverflow.com/questions/575821/how-do-you-insert-new-entries-into-an-access-db-table-through-an-asp-net-website of any help? — BIBD, Feb 23 '09 at 15:30
The replies were quite insightful, however the question remains valid. The problem is more complex than I explain... I have an unnormalised field with 155 entries that I need to update. When I try to update I get the error "Too many fields". — CGF, Feb 23 '09 at 17:52
This is not an MS Access question -- you are not using anything but the Jet database engine. — David-W-Fenton, Feb 24 '09 at 02:01
You have an non-normalized FIELD or TABLE? Sounds to me like you have 155 fields in a non-normalized table. — HardCode, Feb 24 '09 at 04:43
Its pretty normalised! Just need all fields due to the type of info being collected. To establish optimal Insurance Supplier dealing with many constraints. — CGF, Mar 01 '09 at 14:06
@David W. Fenton: "you are not using anything but the Jet database engine": how do you know they are not using the Access QBE to write their SQL joins for them? Even most folk who prefer to hand crank SQL DML still use the Access table designer in preference to SQL DDL. — onedaywhen, Mar 03 '09 at 09:07

score 0 · Answer 1 · answered Feb 25 '09 at 21:18

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!):

click on the button, show its properties, then the events tab.
double-click in the OnClick space to create a new event handler (or enter a name directly)
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.

score 0 · Accepted Answer · edited Mar 11 '11 at 23:09

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

Update SINGLE field of a table in Access db

2 Answers2