Hi, how can I use any logic to mount a search sql statement based on the 
fields the user fills, example:

Name:
SomeCode:
Birth:

if name <> empty the sql will be: select fields from table where Name like 
'%valueFromField%'
if somecode <> empty sql will be: select fields from table where (Name like 
'%valueFromTextBox%') Or (SomeCode='valueFromTextBox')

how will I concatenating with the OR keyword?

Thanks a lot!

On Aug 14, 9:06 pm, "Paulo"  wrote:

> Hi, how can I use any logic to mount a search sql statement based on the
> fields the user fills, example:
>
> Name:
> SomeCode:
> Birth:
>
> if name <> empty the sql will be: select fields from table where Name like
> '%valueFromField%'
> if somecode <> empty sql will be: select fields from table where (Name like
> '%valueFromTextBox%') Or (SomeCode='valueFromTextBox')
>
> how will I concatenating with the OR keyword?
>
> Thanks a lot!


Not really clear what you need

string sql = String.Format("select fields from table where Name like
'%{0}%' Or SomeCode='{1}',
SomeCodeTextBox.Text,
BirthTextBox.Text
)

and use replace() to get rid of single quotes (') :-)

e.g. BirthTextBox.Text.replace("'", "''")

This isn't exactly answering your question, but I prefer to put the logic in 
an SQL stored procedure and leave the html side simple:

Exec spSelectTableValues Name, SomeCode

Then in the procedure:

create procedure spSelectTableValues
    @Name varchar(?), @SomeCode varchar(?)
as
Set @Name = '%' + isnull(@Name, '') + '%'
Set @SomeCode = '%' + isnull(@SomeCode, '') + '%'

Select * from Table where Name like @Name and SomeCode like @SomeCode

Ross


"Paulo"  wrote in message 
news:%23TNvmZq3HHA.5164@TK2MSFTNGP05.phx.gbl...

> Hi, how can I use any logic to mount a search sql statement based on the 
> fields the user fills, example:
>
> Name:
> SomeCode:
> Birth:
>
> if name <> empty the sql will be: select fields from table where Name like 
> '%valueFromField%'
> if somecode <> empty sql will be: select fields from table where (Name 
> like '%valueFromTextBox%') Or (SomeCode='valueFromTextBox')
>
> how will I concatenating with the OR keyword?
>
> Thanks a lot!
> 

What I trying to do is some logic... example:

strSQL = "Select NOME,CPF,DATANASC From CLIENTE Where"

if (txtName.Text.Trim().ToString() <> "")
{
strSQL += "(NOME like txtName.Text ...)"
}

if (txtCode.Text.Trim().ToString() <> "")
{
strSQL += "OR (CPF like etc.. etc..)" bla bla
}

.... ... and so on to all the search condition fields

but for example if it enters just on the second condition, the OR will give 
a error because will concatenate with "WHERE"

Can you understand me... ??

I hope u can help me!

Thanks!

"Ross Culver"  escreveu na mensagem 
news:eR5CXsq3HHA.536@TK2MSFTNGP06.phx.gbl...

> This isn't exactly answering your question, but I prefer to put the logic 
> in an SQL stored procedure and leave the html side simple:
>
> Exec spSelectTableValues Name, SomeCode
>
> Then in the procedure:
>
> create procedure spSelectTableValues
>    @Name varchar(?), @SomeCode varchar(?)
> as
> Set @Name = '%' + isnull(@Name, '') + '%'
> Set @SomeCode = '%' + isnull(@SomeCode, '') + '%'
>
> Select * from Table where Name like @Name and SomeCode like @SomeCode
>
> Ross
>
>
> "Paulo"  wrote in message 
> news:%23TNvmZq3HHA.5164@TK2MSFTNGP05.phx.gbl...
>> Hi, how can I use any logic to mount a search sql statement based on the 
>> fields the user fills, example:
>>
>> Name:
>> SomeCode:
>> Birth:
>>
>> if name <> empty the sql will be: select fields from table where Name 
>> like '%valueFromField%'
>> if somecode <> empty sql will be: select fields from table where (Name 
>> like '%valueFromTextBox%') Or (SomeCode='valueFromTextBox')
>>
>> how will I concatenating with the OR keyword?
>>
>> Thanks a lot!
>>
>
> 

On Aug 14, 9:50 pm, "Paulo"  wrote:

> What I trying to do is some logic... example:
>
> strSQL = "Select NOME,CPF,DATANASC From CLIENTE Where"
>
> if (txtName.Text.Trim().ToString() <> "")


string strSQL="";

if (txtName.Text.Trim().ToString() <> "") {
strSQL += "(NOME like txtName.Text ...)"
}

if (strSQL <> "") {
strSQL += " OR ";
}

if (txtCode.Text.Trim().ToString() <> "")
{
strSQL += "(CPF like etc.. etc..)" bla bla
}

if (strSQL <> "") {
strSQL += " OR ";
}

....

and finally

strSQL = "Select NOME,CPF,DATANASC From CLIENTE Where" + strSQL;

Hope this helps you.

On Aug 14, 10:22 pm, Alexey Smirnov  wrote:

> On Aug 14, 9:50 pm, "Paulo"  wrote:
>
> > What I trying to do is some logic... example:
>
> > strSQL = "Select NOME,CPF,DATANASC From CLIENTE Where"
>
> > if (txtName.Text.Trim().ToString() <> "")
>
> string strSQL="";
>
> if (txtName.Text.Trim().ToString() <> "") {
> strSQL += "(NOME like txtName.Text ...)"
>
> }
>
> if (strSQL <> "") {
> strSQL += " OR ";
>
> }
>
> if (txtCode.Text.Trim().ToString() <> "")
> {
> strSQL += "(CPF like etc.. etc..)" bla bla
>
> }
>
> if (strSQL <> "") {
> strSQL += " OR ";
>
> }
>
> ...
>
> and finally
>
> strSQL = "Select NOME,CPF,DATANASC From CLIENTE Where" + strSQL;
>
> Hope this helps you.



Nope, some changes


string strSQL="";

if (txtName.Text.Trim().ToString() <> "") {
strSQL += "(NOME like txtName.Text ...)";
}


if (txtCode.Text.Trim().ToString() <> "")
{
if (strSQL <> "") {
strSQL += " OR ";
}

strSQL += "(CPF like etc.. etc..)";
}


....

and finally

strSQL = "Select NOME,CPF,DATANASC From CLIENTE Where" + strSQL;