Exception on ExecuteReader() using OleDbCommand and Access

Question

Hi again all, I'm getting the error below for this SQL statement in VB.Net

'Fill in the datagrid with the info needed from the accdb file

    'to make it simple to access the db
    connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
    connstring += "Source=" & Application.StartupPath & "\AuctioneerSystem.accdb"
    'make the new connection
    conn = New System.Data.OleDb.OleDbConnection(connstring)


    'the sql command
    SQLString = "SELECT AllPropertyDetails.PropertyID, Street, Town, County, Acres, Quotas, ResidenceDetails, Status, HighestBid, AskingPrice FROM AllPropertyDetails "
    SQLString += "INNER JOIN Land ON AllPropertyDetails.PropertyID = Land.PropertyID "
    SQLString += "WHERE Deleted = False "
    If PriceRadioButton.Checked = True Then
        SQLString += "ORDER BY AskingPrice ASC"
    ElseIf AcresRadioButton.Checked = True Then
        SQLString += "ORDER BY Acres ASC"
    End If
    'try to open the connection
    conn.Open()
    'if the connection is open
    If ConnectionState.Open.ToString = "Open" Then
        'use the sqlstring and conn to create the command
        cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
        'read the db and put it into dr
        dr = cmd.ExecuteReader

        If dr.HasRows Then

            'if there is rows in the db then make sure the list box is clear
            'clear the rows and columns if there is rows in the data grid
            LandDataGridView.Rows.Clear()
            LandDataGridView.Columns.Clear()
            'add the columns
            LandDataGridView.Columns.Add("PropertyNumber", "Property Number")
            LandDataGridView.Columns.Add("Address", "Address")
            LandDataGridView.Columns.Add("Acres", "No. of Acres")
            LandDataGridView.Columns.Add("Quotas", "Quotas")
            LandDataGridView.Columns.Add("Details", "Residence Details")
            LandDataGridView.Columns.Add("Status", "Status")
            LandDataGridView.Columns.Add("HighestBid", "Highest Bid")
            LandDataGridView.Columns.Add("Price", "Asking Price")
            While dr.Read
                'output the fields into the data grid
                LandDataGridView.Rows.Add( _
                dr.Item("PropertyID").ToString _
                , dr.Item("Street").ToString & " " & dr.Item("Town").ToString & ", " & dr.Item("County").ToString _
                , dr.Item("Acres").ToString _
                , dr.Item("Quota").ToString _
                , dr.Item("ResidenceDetails").ToString _
                , dr.Item("Status").ToString _
                , dr.Item("HighestBid").ToString _
                , dr.Item("AskingPrice").ToString)
            End While
        End If
        'close the data reader
        dr.Close()
    End If
    'close the connection
    conn.Close()

Any ideas why its not working? The fields in the DB and the table names seem ok but its not working :/

The tables are

AllPropertyDetails
ProperyID:Number
Street: text
Town: text
County: text
Status: text
HighestBid: Currency
AskingPrice: Currency
Deleted: Boolean


Land
PropertyID: Number
Acres: Number
Quotas: Text 
ResidenceDetails: text

error is:

System.InvalidOperationException was unhandled
  Message="An error occurred creating the form. See Exception.InnerException for details.  The error is: No value given for one or more required parameters."
  Source="AuctioneerProject"
  StackTrace:
       at AuctioneerProject.My.MyProject.MyForms.Create__Instance__[T](T Instance) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 190
       at AuctioneerProject.My.MyProject.MyForms.get_LandReport()
       at AuctioneerProject.ReportsMenu.LandButton_Click(Object sender, EventArgs e) in C:\Users\admin\Desktop\Auctioneers\AuctioneerProject\AuctioneerProject\ReportsMenu.vb:line 4
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at AuctioneerProject.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.OleDb.OleDbException
       ErrorCode=-2147217904
       Message="No value given for one or more required parameters."
       Source="Microsoft Office Access Database Engine"
       StackTrace:
            at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
            at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
            at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
            at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
            at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
            at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.OleDb.OleDbCommand.ExecuteReader()
            at AuctioneerProject.LandReport.load_Land() in C:\Users\admin\Desktop\Auctioneers\AuctioneerProject\AuctioneerProject\LandReport.vb:line 37
            at AuctioneerProject.LandReport.PriceRadioButton_CheckedChanged(Object sender, EventArgs e) in C:\Users\admin\Desktop\Auctioneers\AuctioneerProject\AuctioneerProject\LandReport.vb:line 79
            at System.Windows.Forms.RadioButton.OnCheckedChanged(EventArgs e)
            at System.Windows.Forms.RadioButton.set_Checked(Boolean value)
            at AuctioneerProject.LandReport.InitializeComponent() in C:\Users\admin\Desktop\Auctioneers\AuctioneerProject\AuctioneerProject\LandReport.designer.vb:line 40
            at AuctioneerProject.LandReport..ctor() in C:\Users\admin\Desktop\Auctioneers\AuctioneerProject\AuctioneerProject\LandReport.vb:line 5
       InnerException: 

Answer 1

maybe it should be Deleted = 0 not Deleted = False

But I do agree with JonH.. you should wrap this up in a stored procedure

Answer 2

Can you post the rest of the ADO.NET related code? It appears you're not sending an argument value for an expected param.

No value given for one or more required parameters.

Answer 3

it is probably how you are running the query string. From your "error message":

The error is: No value given for one or more required parameters.

you only show the code to build the string, but the error is most likely in how you are havig VB call the database with that string, but you don't show that.

EDIT after OP posted table definitions.
the column Deleted is not within the either table:

WHERE Deleted = False

Answer 4

Could it be that this line:

, dr.Item("Quota").ToString _

should be

, dr.Item("Quotas").ToString _

because Quotas (plural) is the column name coming back from the SQL statement?