We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Updating an Access database from VB .Net 2008 Express with a class

Medium Priority
722 Views
Last Modified: 2012-05-06
I searched the site and found what I think is my answer.  So I tried to plagerize the solution found at http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_21978492.html, but got stuck.  My snippet is what I created based on the solution given.  It blows on the line "cmd.ExecuteNonQuery()" with the error "No value given for one or more required parameters."

Specifics (using the Northwind Access database):
* The value of the connection string is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Nwind.mdb"
* The value of the SQL string is "Update Customers Set CompanyName = 'Around the Horn' where CustomerID = 'AROUT'"
* "strConnectionString" and "strCommandString" are being passed in successfully.  
* The connection (conn) is opening successfully; conn.State = ConnectionState.Open (value of "1").
* The Immediate Window is giving me the message: "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll."  

The "ConnectAndFill()" sub works fine to populate the dataset on a retrieval, but the update statement blows up.  What am I missing?

The solution is probably straight forward, but it's late and I'm tired.  Thanks in advance.

StateGuy

Imports System.Data.OleDb
 
Public Class OleDataManipulation
 
    Private dtDataTable As DataTable = New DataTable
    Private strCommandString, strConnectionString As String
 
    ReadOnly Property RetrievedTable() As DataTable
        Get
            Return dtDataTable
        End Get
    End Property
 
    WriteOnly Property CommandString() As String
        Set(ByVal value As String)
            strCommandString = value
        End Set
    End Property
 
    WriteOnly Property ConnectionString() As String
        Set(ByVal value As String)
            strConnectionString = value
        End Set
    End Property
 
    Sub New(ByVal intSQLType As Integer, _
	ByVal CommandString As String, _
	ByVal ConnectionString As String)
 
        Try
            Me.CommandString = CommandString
            Me.ConnectionString = ConnectionString
            Select Case intSQLType
                Case 1 ' Retreival
                    ConnectAndFill() 
                Case 2 ' Update
                    ConnectAndUpdate()
            End Select
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
 
    Private Sub ConnectAndFill()
        Try
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(strCommandString, strConnectionString)
            da.Fill(dtDataTable)
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
 
    Private Sub ConnectAndUpdate()
        Dim conn As OleDbConnection = New OleDbConnection(strConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(strCommandString, conn)
        Try
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
            conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        Finally
            conn.Close()
        End Try
    End Sub
 
End Class

Open in new window

Comment
Watch Question

Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
"No value given for one or more required parameters" generally means that you've misspelled a column name. Check your strCommandString value and compare that against the datasource (i.e. table) that you're querying.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
what is the value of strCommandString?

Author

Commented:
* The value of the connection string is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Nwind.mdb"
* The value of the SQL string is "Update Customers Set CompanyName = 'Around the Horn' where CustomerID = 'AROUT'"
* "strConnectionString" and "strCommandString" are being passed in successfully.  
* The connection (conn) is opening successfully; conn.State = ConnectionState.Open (value of "1").
* The Immediate Window is giving me the message: "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll."

Author

Commented:
I should have known better and double-checked my column names.  This was the answer.  Thanks much and enjoy the points!

Randy
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.