Exception on ExecuteReader() using OleDbCommand and Access
- by Shane Fagan
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:
