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

Hi,

I have this

VB Code:

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?

I did a work around with this:

Dim queryString As String
queryString = """SELECT * FROM tblSSOPVerificationFormRecords WHERE VerificationDate >= ? AND VerificationDate <= ?"

If Not recordNumberTextBox.Text = "" Then

queryString = queryString & " AND AutoNumber = ?"

End If

If Not locationComboBox.Text = "" Then

queryString = queryString & " AND Location = ?"

End If

If Not technicianComboBox.Text = "" Then

queryString = queryString & " AND Technician = ?"

End If

queryString = queryString & """"

Dim FindRecordDA As New OleDbDataAdapter(queryString, cn)
Dim FindRecordDS As New DataSet

FindRecordDA.SelectCommand.Parameters.Add("@VerificationDate1", OleDbType.Date, 80).Value = SSOPStartDatePicker.Text

FindRecordDA.SelectCommand.Parameters.Add("@VerificationDate2", OleDbType.Date, 80).Value = SSOPEndDatePicker.Text

If Not recordNumberTextBox.Text = "" Then

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

End If

If Not locationComboBox.Text = "" Then

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

End If

If Not technicianComboBox.Text = "" Then

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

End If

FindRecordDA.FillSchema(FindRecordDS, SchemaType.Source, "tblSSOPVerificationFormRecords")

FindRecordDA.Fill(FindRecordDS, "tblSSOPVerificationFormRecords")



But now it gives me this error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.


Any ideas? Possibly one that would make my first post work without this extra code.
Thanks!

Anyone?

Quote:

---

Originally Posted by jre1229

Hi,

I have this

VB Code:

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:

Code:

---

        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 :)

JAKSupport,
When you say:

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)


What are you referring to? I don't really understand this. All I have in my DB is a table which contains my field. My data adapter pulls the information from the table and the parameter narrow the data down. So wouldn't the DBNull only pull NULL records? Aren't Null records blank records? I may be totally wrong on this so tell me if I am. What I am trying to do is, if a textbox is blank then set the parameter to EVERYTHING, which means A B C or D or blank or anything for that matter. And if the textbox isn't blank then set the parameter value to the text.
Thanks in advance for the help.

You don't have to use a stored procedure but the SQL code is sound. You should use:

SELECT * FROM tblSSOPVerificationFormRecords WHERE (@AutoNumber IS Null OR AutoNumber = @AutoNumber) AND Technician = @Technician AND Location = @Location AND VerificationDate BETWEEN @VerifcationStartDate AND @VerificationEndDate

This is the important part:

@AutoNumber IS Null OR AutoNumber = @AutoNumber

Think about how that works. If you supply Null as the parameter value then that becomes:

Null IS Null OR AutoNumber = Null

which will be true for every row. If you supply a numerical value, e.g. 10 for the parameter then that becomes:

10 IS Null OR AutoNumber = 10

which is true only for the rows where AutoNumber is 10. That's exactly what you want. It's important that you use named parameters rather than just "?" so that the one parameter can be used in more than one location in the SQL code. Otherwise you'd have to supply the same parameter value twice.

Worked like a charm! Thanks guys!