locked
select query RRS feed

  • 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.


    Thursday, April 9, 2009 8:48 AM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2009 2:56 AM
  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2009 4:25 AM
  • 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 + "')";

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2009 4:42 AM
  • 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!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 11, 2009 8:55 AM
  • User-1383778792 posted

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

     

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 13, 2009 1:50 AM

All replies

  • User-1060328564 posted

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

     

     

    Thursday, April 9, 2009 8:58 AM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2009 2:56 AM
  • 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

    Friday, April 10, 2009 3:23 AM
  • 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....................

     

    Friday, April 10, 2009 3:34 AM
  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2009 4:25 AM
  • 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 + "')";

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2009 4:42 AM
  • User-1383778792 posted

     thanku s much dude.u tooo gud.

    Friday, April 10, 2009 6:06 AM
  • 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?

    Friday, April 10, 2009 7:32 AM
  • 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.

    Friday, April 10, 2009 8:23 AM
  • 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) 
     

     

    Friday, April 10, 2009 8:52 AM
  • 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.
    Friday, April 10, 2009 9:13 AM
  • 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!!!
    Friday, April 10, 2009 9:43 PM
  • User-1383778792 posted

     how can i do please explaine interm of my senrio.

    Saturday, April 11, 2009 12:28 AM
  • 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!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 11, 2009 8:55 AM
  • User-1383778792 posted

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

     

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 13, 2009 1:50 AM