Apologies if my post doesn't follow common practice/rules for coding forums as this is my second question.

I have a button that once clicked, performs a search in an MS Access database for items based on the selected values in combo boxes and adds these items to a data grid. It creates a parameter for each value and adds the relevant combo box item to that parameter.

The CreateSQLStr() function forms the SQL command text based on what combo boxes have a selected value (i.e. if a combo box doesn't have a selected value then it won't specify a condition for that field in the table but if it does it will add AND (TableField = @Parameter) onto the SQL command text).

I've tried changing the "ACTIVE" to a pre defined string and same error and I can't think of anything else.

My error is `System.Data.OleDb.OleDbException: 'Parameter @Status has no default value.'

Details:

System.Data.OleDb.OleDbException
  HResult=0x80040E10
  Message=Parameter @Status has no default value.
  Source=System.Data
  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 WindowsApplication1.frmEmployee.vehiclesearch_Click(Object sender, EventArgs e) in C:\Users\aawad\OneDrive\Documents\QHP\QHP\QHP\frmEmployee.vb:line 79
   at System.EventHandler.Invoke(Object sender, EventArgs e)
   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(IntPtr 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 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at WindowsApplication1.My.MyApplication.Main(String[] Args) in :line 81

  This exception was originally thrown at this call stack:
    [External Code]
    WindowsApplication1.frmEmployee.vehiclesearch_Click(Object, System.EventArgs) in frmEmployee.vb
    [External Code]

`

Private Sub vehiclesearch_Click(sender As Object, e As EventArgs) Handles VehicleSearch.Click
    If DbConnect() Then 'This fucntions returns boolean for whether or not database is connecyed
        Dim SQLcmd As New OleDbCommand
        With SQLcmd
            .Connection = cn
            .CommandText = CreateSQLStr() 'Creates command text based on selected combo box items, see below
            .Parameters.AddWithValue("@Make", MakeCBox.SelectedItem)
            .Parameters.AddWithValue("@Model", ModelCBox.SelectedItem)
            .Parameters.AddWithValue("@ClassV", ClassCBox.SelectedItem)
            .Parameters.AddWithValue("@VSeats", SeatsCBox.SelectedItem)
            .Parameters.AddWithValue("@GrBox", GrboxCBox.SelectedItem)
            .Parameters.AddWithValue("@Branch", BranchCBox.SelectedItem)
            .Parameters.AddWithValue("@Status", "ACTIVE")
            Dim rs As OleDbDataReader = .ExecuteReader 'Error occurs at this point
            While rs.Read 'Adding data to data grid
                Dim make, model, year, fuel, vclass, body As String
                make = rs("VMake")
                model = rs("VModel")
                year = rs("VRegYear")
                fuel = rs("VFuel")
                vclass = rs("VClass")
                body = rs("VBody")
                VehDGrid.Rows.Add(make, model, year, fuel, vclass, body)
            End While
            rs.Close()
            cn.Close()
        End With
    Else 'Error message when no vehicle found
        MessageBox.Show("Could not find any vehicles with the selected details. ", "Find vehicle", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    End If
End Sub

Function CreateSQLStr() As String
    Dim SQLStr As String
    SQLStr = "Select * FROM Vehicles WHERE (VStatus = @Status)"
    If MakeCBox.SelectedItem IsNot Nothing Then
        SQLStr = SQLStr   " AND (VMake = @Make)"
    End If
    If ModelCBox.SelectedItem IsNot Nothing Then
        SQLStr = SQLStr & " AND (VModel = @Model)"
    End If
    If ClassCBox.SelectedItem IsNot Nothing Then
        SQLStr = SQLStr & " AND (VClass = @ClassV)"
    End If
    If SeatsCBox.SelectedItem IsNot Nothing Then
        SQLStr = SQLStr & " AND (VSeats = @Seats)"
    End If
    If GrboxCBox.SelectedItem IsNot Nothing Then
        SQLStr = SQLStr & " AND (VGearbox = @GrBox)"
    End If
    If BranchCBox.SelectedItem IsNot Nothing Then
        SQLStr = SQLStr & " AND (BranchID = @Branch)"
    End If
    Return SQLStr
End Function

CodePudding user response：

With MS Access, the order of the parameters is important not the names. I use the ? placeholder within my SQL command when using parameters. I also specify the data type so consider using the OleDbParameter Constructor (String, OleDbType) constructor. In my example I have used VarChar which you may want to change accordingly.

I would also consider implementing Using:

Managed resources are disposed of by the .NET Framework garbage collector (GC) without any extra coding on your part. You do not need a Using block for managed resources. However, you can still use a Using block to force the disposal of a managed resource instead of waiting for the garbage collector.

Lastly, if you want to conditional update the WHERE based on a value being selected, you should do the same for your parameters to ensure they too are not being added when they are not required:

If MakeCBox.SelectedItem IsNot Nothing Then
    SQLcmd.Parameters.AddWithValue("@Make", MakeCBox.SelectedItem)
End If

This should help to ensure your SQL command is what you want it to be.

As a courtesy note it would also be good to look into taking the time to name your variables, methods, and anything else to be a bit more descriptive, for example CreateSQLStr becomes GetSelectVehicleCommandString.

Also I try to abstract away repetitive code behind a method so that you can call that and pass in parameters. It makes it a little easier to maintain and if you wanted to, you could write a unit test for those methods to ensure they do exactly what you expect them to do. These are just my thoughts. I've thrown together an example to help you on your way. Hopefully this has all been useful to you:

Private Sub vehiclesearch_Click(sender As Object, e As EventArgs) Handles VehicleSearch.Click
    Dim selectVehicleCommandString = "SELECT * FROM Vehicles WHERE (VStatus = @Status)"

    Using connection As New OleDbConnection(connectionString),
          command As New OleDbCommand(selectVehicleCommandString, connection) 

        command.Parameters.Add(CreateParameter("@Status", OleDbType.VarChar, "ACTIVE"))

        If MakeCBox.SelectedItem IsNot Nothing Then
            selectVehicleCommandString = AppendToWhereClause(selectVehicleCommandString, "VMake")
            command.Parameters.Add(CreateParameter("@Make", OleDbType.VarChar,MakeCBox.SelectedItem))
        End If

        If ModelCBox.SelectedItem IsNot Nothing Then
            selectVehicleCommandString = AppendToWhereClause(selectVehicleCommandString, "VModel")
            command.Parameters.Add(CreateParameter("@Model", OleDbType.VarChar,ModelCBox.SelectedItem))
        End If

        If ClassCBox.SelectedItem IsNot Nothing Then
            selectVehicleCommandString = AppendToWhereClause(selectVehicleCommandString, "VClass")
            command.Parameters.Add(CreateParameter("@ClassV", OleDbType.VarChar,ClassCBox.SelectedItem))
        End If

        If SeatsCBox.SelectedItem IsNot Nothing Then
            selectVehicleCommandString = AppendToWhereClause(selectVehicleCommandString, "VSeats")
            command.Parameters.Add(CreateParameter("@VSeats", OleDbType.VarChar,SeatsCBox.SelectedItem))
        End If

        If GrboxCBox.SelectedItem IsNot Nothing Then
            selectVehicleCommandString = AppendToWhereClause(selectVehicleCommandString, "VGearbox")
            command.Parameters.Add(CreateParameter("@GrBox", OleDbType.VarChar,GrboxCBox.SelectedItem))
        End If

        If BranchCBox.SelectedItem IsNot Nothing Then
            selectVehicleCommandString = AppendToWhereClause(selectVehicleCommandString, "BranchID")
            command.Parameters.Add(CreateParameter("@Branch", OleDbType.VarChar,BranchCBox.SelectedItem))
        End If

        command.CommandText = selectVehicleCommandString

        connection.Open()

        Using reader As OleDbDataReader = command.ExecuteReader()
            While reader.Read
                VehDGrid.Rows.Add(reader("VMake"), reader("VModel"), reader("VRegYear"), reader("VFuel"), reader("VClass"), reader("VBody"))
            End While
        End Using
    End Using
End Sub

Private Function AppendToWhereClause(commandString As String, columnName As String) As String
    Return commandString   $" AND ({columnName} = ?)"
End Function

Private Function CreateParameter(parameterName As String, dataType As OleDbType, value As String)
    Dim parameter As New OleDbParameter(parameterName, dataType) With {
        .Value = value
    }
    Return parameter
End Function

Please note that this code is completely untested as I haven't got the capabilities of running this against an MS Access database. if I've missed something, let me know and I'll look to correct accordingly

My attempt here is to try and give you some thoughts on how to approach the code:

1. Implement "Using" when given the opportunity
2. If conditionally appending to the WHERE clause if a value exists, do the same with the parameter itself
3. Provide more descriptive names to your variable names, methods, form objects
4. Remove the use of unnecessary variables (when rows to VehDGrid for example)

CodePudding user response：

You don't need a loop to file out the data grid.

And I would not mess if the conneciton is open or closed. All code written will have closed the connection, so code based on that assumpiton.

While the "@" name parameters for oleDB does not matter (for Access data engine), however, you might as well use them, since that code can be used in the future for sql server etc.

I would also NOT separate add where clause and THEN separate add parameters. ALWAYS add them both at the SAME time.

That way, you can change the order of the code, or even cut paste more parts in, and it will always work. Since Access is sensitive to order of parameters, then this advice is EVEN MORE valuable (add sql, and parameters as a group in your code - not in two separate places. That way, you really can't mess up the order, since you pair this process in code.

As noted, you don't need a loop to add the rows to the data grid - it is a data aware code, and you can thus just send it the data table.

eg this:

    Using conn As New OleDbConnection(My.Settings.AccessDB)

        conn.Open()

        Using cmdSQL As New OleDbCommand("", conn)

            cmdSQL.CommandText = "Select * FROM Vehicles  WHERE "
            pAdd(cmdSQL, "VMake", MakeCBox)
            pAdd(cmdSQL, "Vmodel", ModelCBox)
            pAdd(cmdSQL, "VClass", ClassCBox)
            pAdd(cmdSQL, "VSeats", SeatsCBox)
            pAdd(cmdSQL, "VGearBox", GrboxCBox)
            pAdd(cmdSQL, "BranchID", BranchCBox)

            cmdSQL.CommandText &= " AND (VStatus = @VStatus)"
            cmdSQL.Parameters.AddWithValue("@VStatus", "ACTIVE")

            Dim rstData As New DataTable
            rstData.Load(cmdSQL.ExecuteReader)

            If rstData.Rows.Count = 0 Then
                MsgBox("no data")
            Else
                VehDGrid.DataSource = rstData
            End If

        End Using
    End Using

End Sub

Sub pAdd(cmd As OleDbCommand, sColumn As String,  c As ComboBox)

    If c.SelectedItem IsNot Nothing Then

        Dim sP As String = "@" & sColumn

        cmd.CommandText &= " AND (" & sColumn & " = " & sP & ")"
        cmd.Parameters.AddWithValue(sP, c.SelectedItem)

    End If

End Sub