Query based on user input

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • nemo

    #1

    Query based on user input

    Try to explain this as simple as I can -

    User provides a list (names, ID's, or ....) into a text box on a GUI
    application...

    Based on which options they select I have to look up the corresponding
    values from our sql databases.

    For example:

    user provides:
    adamsj
    nimmow
    dandekp

    I need to return
    nimmow 1234
    dandekp 2341
    adamsj <>

    where nimmow and dandekp are actual employees and adamsj does not have
    an ID

    I need to do this for thousands of rows at a time, so I want to avoid
    doing a for each with a select statement.

    Whats the best way to get this data from SQL? Ive thought about doing
    a for each through the list the user provides and appending on a "where
    username in ('x','y','z')" to the end of a sql query and then just
    getting the results back in a datatable, but then I would not get the
    users who were not found in the list..

    ideas?

    thanks

    will

  • Tim Patrick

    #2
    Re: Query based on user input

    There are a few different ways to do this. The method you suggested works
    well. You would need to keep track of those results that weren't returned
    by the query. You could do that using a Generic.List or one of the other
    Generic collections. Add all of the search strings to the list.

    Dim allSearch As New Generic.List(Of String)
    allSearch.Add(" adamsj")
    allSearch.Add(" nimmow")

    Then as you parse the results from your SQL query, delete the ones you find.

    allSearch.Remov e(CStr(returned Table!ShortName ))

    Anything left in the list will be your "<>" elements.

    For Each oneEntry As String IN allSearch
    myStringBuild.A ppendLine(oneen try & " <>")
    Next oneEntry

    You could also create a temporary SQL Server table with your search strings,
    and join this table to the real table.

    SELECT TT.SearchText, RT.ID
    FROM ##MyTempTable AS TT LEFT JOIN RealTable AS RT
    ON TT.SearchText = RT.ShortName

    Any record that comes back with a NULL ID field is a "<>" entry. Don't forget
    to remove the temporary table when you're finished with it.

    -----
    Tim Patrick
    Start-to-Finish Visual Basic 2005
    Try to explain this as simple as I can -
    >
    User provides a list (names, ID's, or ....) into a text box on a GUI
    application...
    >
    Based on which options they select I have to look up the corresponding
    values from our sql databases.
    >
    For example:
    >
    user provides:
    adamsj
    nimmow
    dandekp
    I need to return
    nimmow 1234
    dandekp 2341
    adamsj <>
    where nimmow and dandekp are actual employees and adamsj does not have
    an ID
    >
    I need to do this for thousands of rows at a time, so I want to avoid
    doing a for each with a select statement.
    >
    Whats the best way to get this data from SQL? Ive thought about doing
    a for each through the list the user provides and appending on a
    "where username in ('x','y','z')" to the end of a sql query and then
    just getting the results back in a datatable, but then I would not get
    the users who were not found in the list..
    >
    ideas?
    >
    thanks
    >
    will
    >

    Comment

    • nemo

      #3
      Re: Query based on user input

      I agree using a temp table would be preferrable.

      Unfortunately that would require inserting all 10K rows into the
      database, which would require 10K sql insert statements. Kinda defeats
      the intention of reducing the number of SQL calls..

      Your idea of removing systems as they are found is not bad.. I may look
      into that.

      Im just kinda surprised .net doesnt have some kind of method where you
      could get all of the user's input into a single datatable and get the
      other information into another datatable from SQl and then do some sort
      of merge on the two datatables.

      Im looking into the joinview class right now, but frankly it confuses
      me.

      What makes this even more complicated is that I have simplified the
      problem.. The data I need to relate the input to actually resides on
      two seperate servers that cannot be linked and have no common ID with
      permissions to both..

      so whatever I end up doing, I actually have to do twice..

      fun stuff..

      any more suggestions?

      Comment

      • nemo

        #4
        Re: Query based on user input

        also - not using .net 2.0 yet.. is there a 1.1 equivalent of
        generic.list?
        nemo wrote:
        I agree using a temp table would be preferrable.
        >
        Unfortunately that would require inserting all 10K rows into the
        database, which would require 10K sql insert statements. Kinda defeats
        the intention of reducing the number of SQL calls..
        >
        Your idea of removing systems as they are found is not bad.. I may look
        into that.
        >
        Im just kinda surprised .net doesnt have some kind of method where you
        could get all of the user's input into a single datatable and get the
        other information into another datatable from SQl and then do some sort
        of merge on the two datatables.
        >
        Im looking into the joinview class right now, but frankly it confuses
        me.
        >
        What makes this even more complicated is that I have simplified the
        problem.. The data I need to relate the input to actually resides on
        two seperate servers that cannot be linked and have no common ID with
        permissions to both..
        >
        so whatever I end up doing, I actually have to do twice..
        >
        fun stuff..
        >
        any more suggestions?

        Comment

        • Tim Patrick

          #5
          Re: Query based on user input

          The classes in the System.Collecti ons namespace provide similar functionality
          to the Generic collections, although they are not strongly typed.

          -----
          Tim Patrick
          Start-to-Finish Visual Basic 2005
          also - not using .net 2.0 yet.. is there a 1.1 equivalent of
          generic.list?
          nemo wrote:
          >I agree using a temp table would be preferrable.
          >>
          >Unfortunatel y that would require inserting all 10K rows into the
          >database, which would require 10K sql insert statements. Kinda
          >defeats the intention of reducing the number of SQL calls..
          >>
          >Your idea of removing systems as they are found is not bad.. I may
          >look into that.
          >>
          >Im just kinda surprised .net doesnt have some kind of method where
          >you could get all of the user's input into a single datatable and get
          >the other information into another datatable from SQl and then do
          >some sort of merge on the two datatables.
          >>
          >Im looking into the joinview class right now, but frankly it confuses
          >me.
          >>
          >What makes this even more complicated is that I have simplified the
          >problem.. The data I need to relate the input to actually resides on
          >two seperate servers that cannot be linked and have no common ID with
          >permissions to both..
          >>
          >so whatever I end up doing, I actually have to do twice..
          >>
          >fun stuff..
          >>
          >any more suggestions?
          >>

          Comment

          • Tim Patrick

            #6
            Re: Query based on user input

            You might want to ask a SQL Server newsgroup if there is a way to build a
            table on the fly from a chunk of single-column data. I kind of doubt it--Oracle
            doesn't have a feature like that in its standard DDL commands. But SQL Server
            Data Transformation Services (DTS) is able to import tab-delimited data into
            tables quick quickly, so there may be some advanced or back-door way to do
            it.

            -----
            Tim Patrick
            Start-to-Finish Visual Basic 2005
            I agree using a temp table would be preferrable.
            >
            Unfortunately that would require inserting all 10K rows into the
            database, which would require 10K sql insert statements. Kinda defeats
            the intention of reducing the number of SQL calls..
            >
            Your idea of removing systems as they are found is not bad.. I may
            look into that.
            >
            Im just kinda surprised .net doesnt have some kind of method where you
            could get all of the user's input into a single datatable and get the
            other information into another datatable from SQl and then do some
            sort of merge on the two datatables.
            >
            Im looking into the joinview class right now, but frankly it confuses
            me.
            >
            What makes this even more complicated is that I have simplified the
            problem.. The data I need to relate the input to actually resides on
            two seperate servers that cannot be linked and have no common ID with
            permissions to both..
            >
            so whatever I end up doing, I actually have to do twice..
            >
            fun stuff..
            >
            any more suggestions?
            >

            Comment

            • Spam Catcher

              #7
              Re: Query based on user input

              "nemo" <miaminemo@gmai l.comwrote in news:1162835614 .928309.289380
              @f16g2000cwb.go oglegroups.com:
              Unfortunately that would require inserting all 10K rows into the
              database, which would require 10K sql insert statements. Kinda defeats
              the intention of reducing the number of SQL calls..
              You could bulk load the data using DTS or BCP.

              Or just do the SELET * FROM USERS WHERE UserName in ('user1', 'user2', etc)
              method - perhaps chunk the select into ~100 - 1000 users? Once you get the
              datatable back - merge the data into a large datatable containing the
              complete results?
              Im just kinda surprised .net doesnt have some kind of method where you
              could get all of the user's input into a single datatable and get the
              other information into another datatable from SQl and then do some sort
              of merge on the two datatables.
              MS Linq will allow you to dynamically query data tables and join tables
              together...but it's in alpha or beta.



              Comment

              • Andrew Morton

                #8
                Re: Query based on user input

                nemo wrote:
                Whats the best way to get this data from SQL? Ive thought about doing
                a for each through the list the user provides and appending on a
                "where username in ('x','y','z')" to the end of a sql query and then
                Don't forget to escape apostrophes somehow (or use a stored procedure)
                because otherwise that's going to fall over as soon as you get an employee
                with an apostrophe in their name.

                Andrew


                Comment

                Working...