select query

Question

User-1383778792 posted

 plz tell me query

i have multiple textbox to searchin which one of "id" field which have 1 to 40 numbers. if i blank so query excute with respect of 40 id.mean all id shown. if give perticular so result give with respect of this id.

i use like this

if (id =="")

{ id = "2" }

else { id = id.Tostring() }

if i blank so  it give result but only respect of 2 not to all.how can i store all 1 to 40 in default.

Answer 1

User-1060328564 posted

 Can u elobrate it more. Can't get what exactly you are trying to ask.

 

 

Answer 2

User-1383778792 posted

 sir want that when select query xcute when user didnt put any data so the query excute interm of (select *) mean all . but specific so it display only specific record as input. mean if input 4 so print only 4 if leave so print 1,2,3,4,....40. ok now u got it.

Answer 3

User-40550723 posted

 You can use dynamic query for this condition

declare @sqlquery as varchar(8000)

set @sqlquery ='SELECT  * from table1 where 1=1  ' ;

 if(@input1 <>'')

begin 

      set    @sqlquery = @sqlquery +  '  AND columnName = ' + @input1 ;

end  

 exec(@sqlquery)

 

note @input1 is textboxvalue use appropiate cast for it

Answer 4

User-1087602695 posted

string Q; 

if (Textbox1.Text != "")

{

        Q = "select *from table1";

}

else

{

       Q = "select *from table1 where id =' " + Textbox1.Text + " ' ";

}

/// here your execution code....................

 

Answer 5

User-1383778792 posted

 i want to say that if input is 4 so query is select <id > from<table> where id ='4'

 

 and if there is blank input so query is select <id > from<table> where id ='1','2','3'...........'40'

 

so want this query how can write in right way?

Answer 6

User-102753425 posted

Hi Please user belo code for your requriement.

 

protected void executeQuery(string filterValue)

{

if (string.IsNullOrEmpty(filterValue))

{

string strValue = string.Equals;for (int intCounter = 1; intCounter < 41; intCounter++)

{

if (strValue.Equals(string.Empty))

strValue = intCounter.ToString;

else

strValue += "' , '" + intCounter.ToString;

}

}

else

strValue = filterValue;

string strSQlSquery = "select * From [tablename] where [columnname] in ('" + strValue + "')";

 

Answer 7

User-1383778792 posted

 thanku s much dude.u tooo gud.

Answer 8

User-1383778792 posted

 Dear is not working.only can run if user give input.other wise exception dear.

n tell me more that can we more attractive like if user give coma( , ) seprate number .so can query excute?

Answer 9

User-102753425 posted

Yes you can bit before passing that input in the where clause then you have to reverify that wether inout number is correct formatte or not. For validati9on you can do form JavaScript as well in code level also.

hint: Treat the input as string and split by ",". So it return you array and then cerated the wel formatted string. Else you query thow execption.

Answer 10

User-40550723 posted
 

If you want it to be handle in stored  procedure then here it is 

declare @sqlquery as varchar(8000)
declare @input1 as varchar(10) 
set @input1 = '258'

set @sqlquery ='SELECT  * from Table1 where 1=1  ' ;

if(@input1 <>'')
begin 
      set @sqlquery = @sqlquery +  '  AND ColumnID = '+ @input1;
end
else
begin
	  set @sqlquery = @sqlquery +  '  AND ColumnID >=1 AND ColumnID <=40';
end
exec(@sqlquery)

Just pass textbox value to stored procedure (perform validations that user can only input integer in that textfield) 

 

 

Answer 11

User-40550723 posted

 Or if you want to do it from front end then  

 string addedString = string.Empty;
            if (string.IsNullOrEmpty(TextBox1.Text.Trim()))
            {
                addedString = " AND ColumnID>=1 AND ColumnID<=40 ";
            }
            else
            {
                addedString = " AND ColumnID ='" + TextBox1.Text.Trim() + "'";
            }

            string sqlquery = " Select * from table1 where 1=1 ";
            sqlquery  =sqlquery + addedString;

 
but I will preferred the store procedure approach.

Answer 12

User-1199946673 posted

 

If you want it to be handle in stored  procedure then here it is 

This is an Access Forum, Access doesn't support stored procedures!!!

Answer 13

User-1383778792 posted

 how can i do please explaine interm of my senrio.

Answer 14

User-1199946673 posted

Although a really do not understand what you're asking, I think you're problem is how to dela with Optional Parameters

An ASP.NET Search Engine with MS Access for optional search criteria

If I understand you correctly, you've multiple (40) textboxes, if a textbox remains blank, it should be ignored in the serach criteria. If this is you're question, that the above link provides the answer!

Answer 15

User-1383778792 posted

i did dear. this is the coion to excute comma spreate input

 

cli = Regex.Replace(cli, "'", ""); qry = qry + " And CLI = '" + cli + "'";