Professional Web Applications Themes

dynamically build sql based on text in textbox and dropdownbox - ASP.NET Building Controls

I have a web form that contains textboxes for first name and last name. I also have a dropdown list that contains companies. I would like the user to enter criteria in any manner in the control and get results from the database. For example if the user enters information in the firstname field a dataset be returned. If the user selects all three controls then a dataset should also be returned and the text of the controls will be the search criteria. The base sql is : select * from Name where where" if the txtID contains a value ...

  1. #1

    Default dynamically build sql based on text in textbox and dropdownbox

    I have a web form that contains textboxes for first name and last name. I
    also have a dropdown list that contains companies.

    I would like the user to enter criteria in any manner in the control and get
    results from the database.

    For example if the user enters information in the firstname field a dataset
    be returned. If the user selects all three controls then a dataset should
    also be returned and the text of the controls will be the search criteria.

    The base sql is : select * from Name where where"

    if the txtID contains a value then the sql string will be "select * FROM
    Name where ID = TXTID.TEXT"

    if txtid and txtfirstname have values the sql string will be "select * FROM
    Name where ID = TXTID.TEXT and FIRST_NAME = txtfirstname.text"


    if all three are selected txtid,txtfirstname and txtlastname and have values
    the sql string will be "select * FROM Name where ID = TXTID.TEXT and
    FIRST_NAME = txtfirstname.text and LAST_NAME = txtlastname.txt"
    Also, is it possible to loop through all the controls find the textboxes
    that contains values and build the controls that way.

    Pls help my job is on the line.








    I understand how to use datasets, grid, data adapter and connection objects.

    would like to determine the user


    Edward F Guest

  2. #2

    Default Re: dynamically build sql based on text in textbox and dropdownbox

    Edward,

    A good way to do this would be to encapsulate the search logic into
    a stored procedure with default parameters. some thing like

    Create procedure GetResults(
    FirstName VarChar(255) = null,
    LastName VarChar(255) = null,
    Id int = null
    )
    As
    Begin
    Select *
    From <table>
    Where (firstname = FirstName or FirstName is null)
    And (LastName = LastName or LastName is null)
    And (Id = Id or Id is null)
    End

    Now in your data access layer, you call the stored procedure with only
    the parameters that have a value.

    Hope this sets you on track.
    -Souri

    souri challa Guest

Similar Threads

  1. Capturing Data of Dynamically Created TextBox's
    By Jack Johnston in forum ASP.NET General
    Replies: 6
    Last Post: August 4th, 10:37 AM
  2. Dynamically Build a Tag
    By grndvl1 in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: June 30th, 08:54 PM
  3. Replies: 3
    Last Post: August 11th, 05:17 PM
  4. Replies: 4
    Last Post: May 31st, 12:58 AM
  5. Dynamically build RangeValidation control for text box
    By Ibrahim Shameeque in forum ASP.NET Building Controls
    Replies: 0
    Last Post: December 12th, 01:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139