Doubt making sql statement

[Paulo]

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!

 

[Guest]

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("'", "''")

 

[Ross Culver]

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]

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!

 

[Guest]

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.

 

[Guest]

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;