OleDbException was unhandled by user code

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

calibreincAsked:
Who is Participating?
 
calibreincAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
calibreincAuthor Commented:
i've checked the database and everything has a value and doesn't allow zero length stings
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bhavesh ShahLead AnalysistCommented:
did you try to run your procedure from backend?
0
 
calibreincAuthor Commented:
Thank you Padas
0
 
calibreincAuthor Commented:
Brichsoft:
Yes the query runs in Access
0
 
Bhavesh ShahLead AnalysistCommented:
hi,
by debugging, can you tell us exact line where error is coming.?
0
 
calibreincAuthor Commented:
Oh yes sorry i forgot to mention that. it's failling on:

        da.UpdateCommand.ExecuteNonQuery()
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
means
you are getting blank value in CType(rowUpdate.Cells(2).Controls(0), TextBox)

right?
0
 
calibreincAuthor Commented:
Worked it out on my own through hours of staring at the code and it finally came to me
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.