Optional Paramter

  • Thread starter Thread starter Gene Ariani
  • Start date Start date
G

Gene Ariani

Is there an elegant way to have an optional parameter in sql string like

Select * from Name where (mytextbox is empty select everyone else select
lastname=mytextbox)


Any direction is appreciated it.


Thanks


Gene
 
Assuming you're embedding this in a string, you could say:
sSQL = "Select * from Name " & _
iif(MyTextBox.Text = "", _
"Where Name like '" & MyTextBox.Text & "%'")
 
Gene,
This sounds more like a SQL Server or Access question.

You may want to ask this "down the hall" in an SQL Server, Access or the
ado.net newsgroup.

Hope this helps
Jay
 
Sg;

Thank you very much for taking the time to response. But I can’t seem to
concatenate your string correctly to make it work.


Thanks


Gene
 
Hi Gene,
Select * from Name where (mytextbox is empty select everyone else select
lastname=mytextbox)
Click to expand...

What is the benefit of that above
If mytexbox.text ="" then
selectstring = "select * from name"
else
selectstring = "select * from name where lastname"' _
trim(mytextbox.text) & "'"
end if

Or do you want some more processing on your database computer?

I hope this helps a little bit?

Cor
 
Well I was trying to avoid writing multiple select statement and fit
everthing into one clean sql statement
 
Hi Gene,

I find this a very clean statement and when it is build it is only one thru
step in your computer tested by one digit.

When you put it in a SQL it has to be everytime evaluated and compiled on
your SQL server side.

And this statement shows direct what you are doing.

My choise would be easy.

Cor
 
Gene,
Then use a variation of Cor's example:

selectstring = "select * from name"

If mytexbox.text <> "" then
selectstring &= " where lastname""' _
trim(mytextbox.text) & """
end if

As Cor stated his original and my modified version are both very easy.

Hope this helps
Jay
 
Back
Top