Solved

OleDbException was unhandled by user code

Posted on 2013-10-23
12
369 Views
Last Modified: 2013-10-28
Greetings everyone,
I'm currently working in Visual Studios 2010 creating a webapplication and trying to update a gridview with a connection to an Access database. However when i try to update the gridview it errors out with:

OleDbException was unhandled by user code
No value given for one or more required parameters

i've looked everywhere and found a few that are close to the problem i'm having but nothing really to help. Any help anyone can give would be appreciated. My code follows.

Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Public Class _Default
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim strQuery As String
        If Not Page.IsPostBack Then
            Dim connAccess As New OleDbConnection

            Dim Path As String = MapPath("~/App_Data/Database5.accdb")
            connAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path
            connAccess.Open()
            ' Dim ads As New AccessDataSource
            Dim ds As New DataSet
            Dim da As New OleDbDataAdapter

            'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\testsite\WebApplication1\WebApplication1\App_Data\Database31.accdb
            ds.AcceptChanges()
            strQuery = "select * from Employee"
            Try
                If Me.GridView1.SortExpression = "" Then
                    da.SelectCommand = New OleDbCommand(strQuery)
                Else
                    da.SelectCommand = New OleDbCommand(strQuery & " order by " & Me.GridView1.SortExpression)
                End If
                'da.SelectCommand = New OleDbCommand(strQuery)
                da.SelectCommand.Connection = connAccess
                da.Fill(ds)

                GridView1.DataSource = ds
                GridView1.DataBind()
            Catch ex As Exception
                'da.SelectCommand.Connection = connAccess
                'da.Fill(ds)
                ' GridView1.Columns(0).Visible = Session("Edit")
                ' GridView1.Columns(1).Visible = Session("Edit")
                ' GridView1.DataSource = ds
                'GridView1.DataBind()
            End Try

            connAccess.Close()
        End If
    End Sub

    Private Sub GridView1_RowCancelingEdit(sender As Object, e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
        GridView1.EditIndex = -1
        Session("CatchIt") = False
    End Sub

    Private Sub GridView1_RowCommand(sender As Object, e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand

    End Sub

    'Private Sub GridView1_RowDeleting(sender As Object, e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
    '    Dim connAccess As New OleDbConnection
    '    Dim dataset As New DataSet
    '    Dim Path As String = MapPath("~/App_Data/Database5.accdb")
    '    connAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path
    '    connAccess.Open()
    '    Dim adapter As New OleDbDataAdapter
    '    adapter.DeleteCommand = New OleDbCommand("delete from employee where last_name='" & Me.GridView1.Rows(e.RowIndex).Cells(3).Text & "'")
    '    adapter.DeleteCommand.Connection = connAccess
    '    adapter.DeleteCommand.ExecuteNonQuery()
    '    connAccess.Close()
    '    bind_grid()
    'End Sub

    Protected Sub GridView1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles GridView1.SelectedIndexChanged

    End Sub

    Private Sub GridView1_RowEditing(sender As Object, e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
        GridView1.EditIndex = e.NewEditIndex
        bind_grid()
    End Sub

    Private Sub GridView1_RowUpdated(sender As Object, e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView1.RowUpdated
    End Sub

    Private Sub GridView1_RowUpdating(sender As Object, e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
        Dim txtEmployee_ID, txtLast_Name, txtFirst_Name, txtWork_Site, txtZip_Code, txtTitle As TextBox
        Dim rowUpdate As GridViewRow = GridView1.Rows(e.RowIndex)
        txtEmployee_ID = CType(rowUpdate.Cells(2).Controls(0), TextBox)
        txtLast_Name = CType(rowUpdate.Cells(3).Controls(0), TextBox)
        txtFirst_Name = CType(rowUpdate.Cells(4).Controls(0), TextBox)
        txtWork_Site = CType(rowUpdate.Cells(5).Controls(0), TextBox)
        txtZip_Code = CType(rowUpdate.Cells(6).Controls(0), TextBox)
        txtTitle = CType(rowUpdate.Cells(7).Controls(0), TextBox)
        Dim connAccess As New OleDbConnection
        Dim ds As New DataSet
        Dim Path As String = MapPath("~/App_Data/Database5.accdb")
        connAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path
        Dim da As New OleDbDataAdapter
        da.UpdateCommand = New OleDbCommand("update Employee set Employee.[Last_Name]=@pLast_Name, Employee.[First_Name] = @pFirst_Name, Employee.[Work_Site]=@pWork_Site, Employee.[Zip_Code]=@pZip_Code, Employee.[Title]=@pTitle where Employee_ID='" & txtEmployee_ID.Text & "'")
        ' @pEmployeeID")
        '& Me.GridView1.Rows(e.RowIndex).Cells(2).Text)
        da.UpdateCommand.Parameters.AddWithValue("@pEmployee_ID", txtEmployee_ID.Text)
        da.UpdateCommand.Parameters.AddWithValue("@pLast_Name", txtLast_Name.Text)
        da.UpdateCommand.Parameters.AddWithValue("@pFirst_Name", txtFirst_Name.Text)
        da.UpdateCommand.Parameters.AddWithValue("@pWork_Site", txtWork_Site.Text)
        da.UpdateCommand.Parameters.AddWithValue("@pZip_Code", txtZip_Code.Text)
        da.UpdateCommand.Parameters.AddWithValue("@pTitle", txtTitle.Text)
        connAccess.Open()
        da.UpdateCommand.Connection = connAccess
        da.UpdateCommand.ExecuteNonQuery()
        connAccess.Close()
        GridView1.EditIndex = -1
        bind_grid()
        'Session("CatchIt") = False
    End Sub

    Private Sub bind_grid()
        'Throw New NotImplementedException
        Dim connAccess As New OleDbConnection
        Dim ds As New DataSet
        Dim path As String = MapPath("~/App_Data/Database5.accdb")
        connAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path
        connAccess.Open()
        Dim da As New OleDbDataAdapter
        da.SelectCommand = New OleDbCommand("select * from Employee")
        da.SelectCommand.Connection = connAccess
        da.Fill(ds)
        GridView1.DataSource = ds
        GridView1.DataBind()
        connAccess.Close()
    End Sub

End Class

Open in new window

0
Comment
Question by:calibreinc
  • 6
  • 5
12 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39593938
The usual reason for this error is a missing or misspelled value. It seems likely that any one parameter is Null or a zero-length string.
0
 

Author Comment

by:calibreinc
ID: 39594002
i've checked the database and everything has a value and doesn't allow zero length stings
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594042
did you try to run your procedure from backend?
0
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

Author Comment

by:calibreinc
ID: 39594209
Thank you Padas
0
 

Author Comment

by:calibreinc
ID: 39594308
Brichsoft:
Yes the query runs in Access
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594344
hi,
by debugging, can you tell us exact line where error is coming.?
0
 

Author Comment

by:calibreinc
ID: 39594373
Oh yes sorry i forgot to mention that. it's failling on:

        da.UpdateCommand.ExecuteNonQuery()
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594529
hi,

you are updating 6 columns.

For debugging purpose can you update the field individually.?

i mean just update First_Name and if its work then update last_name

it will help you to sort out the thing
0
 

Accepted Solution

by:
calibreinc earned 0 total points
ID: 39595324
i figured out why it was breaking. rebuilt the Database and found that if i commented out:

txtEmployee_ID = CType(rowUpdate.Cells(2).Controls(0), TextBox)

Then it works fine
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39596335
means
you are getting blank value in CType(rowUpdate.Cells(2).Controls(0), TextBox)

right?
0
 

Author Closing Comment

by:calibreinc
ID: 39605117
Worked it out on my own through hours of staring at the code and it finally came to me
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question