Query based on user input

[nemo]

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]

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

[nemo]

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?

[nemo]

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?

[Tim Patrick]

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?

[Tim Patrick]

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?

[Spam Catcher]

"nemo" <mi*******@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.

[Andrew Morton]

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