?
Solved

OleDbException was unhandled by user code

Posted on 2013-10-23
12
Medium Priority
?
377 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
11 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

598 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