Solved

OleDbException was unhandled by user code

Posted on 2013-10-23
12
367 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

786 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