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

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

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

0
StateGuy
Asked:
StateGuy
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
"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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
what is the value of strCommandString?
0
 
StateGuyAuthor 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."
0
 
StateGuyAuthor Commented:
I should have known better and double-checked my column names.  This was the answer.  Thanks much and enjoy the points!

Randy
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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