Solved

OleDbException was unhandled by user code

Posted on 2013-10-23
12
364 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
 

Author Comment

by:calibreinc
ID: 39594209
Thank you Padas
0
 

Author Comment

by:calibreinc
ID: 39594308
Brichsoft:
Yes the query runs in Access
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now