[RESOLVED] [2005] Parameter Problem - Need to have sometimes and sometimes not

[JAKSupport]

Hi,

I have this

Dim FindRecordDA As New OleDbDataAdapter("SELECT * FROM tblSSOPVerificationFormRecords WHERE AutoNumber = ? AND Technician = ? AND Location = ? AND VerificationDate >= ? AND VerificationDate <= ?", cn)
 
        Dim FindRecordDS As New DataSet
 
 
 
        FindRecordDS.Clear()
 
        FindRecordDA.SelectCommand.Parameters.Clear()
 
 
 
        If recordNumberTextBox.Text = "" Then
 
            FindRecordDA.SelectCommand.Parameters.Add("@AutoNumber", OleDbType.VarChar, 80).Value = "*"
 
        Else
 
            FindRecordDA.SelectCommand.Parameters.Add("@AutoNumber", OleDbType.VarChar, 80).Value = recordNumberTextBox.Text
 
        End If
 
 
 
        FindRecordDA.SelectCommand.Parameters.Add("@Technician", OleDbType.VarChar, 80).Value = technicianComboBox.Text
 
        FindRecordDA.SelectCommand.Parameters.Add("@Location", OleDbType.VarChar, 80).Value = locationComboBox.Text
 
        FindRecordDA.SelectCommand.Parameters.Add("@VerificationDate1", OleDbType.Date, 80).Value = SSOPStartDatePicker.Text
 
        FindRecordDA.SelectCommand.Parameters.Add("@VerificationDate2", OleDbType.Date, 80).Value = SSOPEndDatePicker.Text
 
 
 
        FindRecordDA.FillSchema(FindRecordDS, SchemaType.Source, "tblSSOPVerificationFormRecords")
 
        FindRecordDA.Fill(FindRecordDS, "tblSSOPVerificationFormRecords")

I know the "*" isn't correct but how can I make it to where that parameter is nothing and pulls everything?

Definately change your code back to the original code above...but you will need to modify it. Try always using named parameters even if the RDBMS doesn't support em, at least on your client end you know what and where everything is. If you need to include an all, you will need to make use of the DBNull class and pass DBNull.Value. Make sure you do not pass "*" like you did, and definately avoid Nothing keyword, this keyword is unpredictable when dealing with databases and nulls.

So now you should have:

        If recordNumberTextBox.Text.Length = 0 Then

            FindRecordDA.SelectCommand.Parameters.Add("@AutoNumber", OleDbType.VarChar, 80).Value = DBNull.Value

        Else

            FindRecordDA.SelectCommand.Parameters.Add("@AutoNumber", OleDbType.VarChar, 80).Value = recordNumberTextBox.Text

        End If

So now you're done with your code page...you will need to do one more thing in your db side of things to make sure this works...
In your db you need to write your procedure so that the parameter has a default value of null and use some boolean logic...

In the db side of things:

CREATE PROCEDURE foo_bar @AutoNumber int=NULL
AS
BEGIN
SET NOCOUNT ON
SELECT blah FROM YourTable WHERE
(@AutoNumber IS NULL OR YourTable.AutoNumber = @AutoNumber)
...

That's all