prompt user to select from recordset.

  • Thread starter Thread starter Kevin O'Brien
  • Start date Start date
K

Kevin O'Brien

Hey guys,

I am querying a sql database for a matches using wild cards. If I return
more than one record I want to prompt the user to choose the best match.
What is the best way to do this? I thought I could put the matching records
into a label and have the user select the item that they want but when using
that method I am only getting the values in the label item and not the
recordID. Any help is greatly appreciated. My code is below.

Thanks,

Kevin





thisConnection.Open()

Dim scalarCommand As New SqlCommand("Select Count(*) FROM Users where
FirstName like '" & TextBox2.Text & "%' OR LastName like '" & TextBox2.Text
& "%'", thisConnection)

Dim intCount = scalarCommand.ExecuteScalar()

TextBox99.Text = intCount.ToString()

If intCount > 1 Then

End If
 
If you want to use a Label - you can store it in the Tag property - or if
you want to keep it a little more slick, subclass Label and add a RecordId
property to it.

From a UI point of view, i'd recommend using some control that supports
complex binding. If this is Winforms, you can use a Combobox or a ListBox to
name two. You can set the DisplayMember to the LastName or whatever it
currently is (whatever value you want the users to see) and set the
ValueMember to the RecordID. You can then access it using the
ListBox/Combbox.SelectedValue property.

If I understand your issue correctly, one of these should work whichever
control you decide on. if I misunderstood , was vague or was unclear, just
let me know and I'll try again.

Cheers,

bill
 
Hey Bill,

Thanks for the quick response. I'll try out the listbox. I haven't really
used it so I'll give it a shot and let you know how I make out.

thank you,
Kevin
 
Of course, your application will be subject to SQL injection attacks. Before
you finish be sure to replace the concatenated SQL with a Parameter
query--better yet a stored procedure.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Kevin,

If you want to use the listbox is it better to use the DataTable (in or
outside a DataSet) with AdoNet instead of the now very classic recordset.

In my idea is it impossible to bind a Listbox to a recordset.

Cor
 
Bill,

As far as I know is it impossible to use the IN or an infinit string of OR's
with parameters.
(in fact a not limited by length array of parameters).

Do you have a direct solution to that. Otherwise maybe a good target for you
to realise that for us.

Cor
 
Miha

I think that it a very lot of work and will work slow
IN (@Param1, @Param2, ...)
Click to expand...
IN (@Param1, @Param2, ..........................@Param10000000000000)

(In C# or VB.Net I can do this in a loop, that is not the problem, however I
would be more glad if there was a parameterArray in SQL for that, or a
solution like that)

Cor
 
Cor Ligthert said:
Miha

I think that it a very lot of work and will work slow
Click to expand...

If you have a better idea, tell us.
The other variation is to process [comma] delimited strings on the server
which involves t-sql code.
IN (@Param1, @Param2, ..........................@Param10000000000000)

(In C# or VB.Net I can do this in a loop, that is not the problem, however
I would be more glad if there was a parameterArray in SQL for that, or a
solution like that)
Click to expand...

Everybody would be glad. But this is the current state of the affairs.
 
I always use the T-Sql parsing b/c well, I'm good like that and gotta
practice what I preach
http://support.microsoft.com/default.aspx?scid=kb;en-us;555167

But I'm liking the client side approach you recommend. Might have to give
that a try.
Miha Markic said:
Cor Ligthert said:
Miha

I think that it a very lot of work and will work slow
Click to expand...

If you have a better idea, tell us.
The other variation is to process [comma] delimited strings on the server
which involves t-sql code.
IN (@Param1, @Param2, ..........................@Param10000000000000)

(In C# or VB.Net I can do this in a loop, that is not the problem,
however I would be more glad if there was a parameterArray in SQL for
that, or a solution like that)
Click to expand...

Everybody would be glad. But this is the current state of the affairs.
Click to expand...
 
or... LOL, use LINQ ;-)
William Vaughn said:
Of course, your application will be subject to SQL injection attacks.
Before you finish be sure to replace the concatenated SQL with a Parameter
query--better yet a stored procedure.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
Click to expand...
 
Miha,

Is this a kind of joke or somehting from you, I did not recognise it as
that.

This was my message related to the answer from Bill

This is the results from you after your interceptions .
If you have a better idea, tell us.
The other variation is to process [comma] delimited strings on the server
which involves t-sql code.
Click to expand...
Do you think that I ask a question to Bill as I have the answer? The
solution you gave was in fact explicit in my question.

Cor
 
You always know how to push my buttons... ;(

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Ah, this is a well documented (outside of the MS doc) issue that has a
number of clean solutions.
First, write a TSQL delimited string to Table value function to pass to the
IN as in
... WHERE X IN (SELECT yy FROM MyTSQLDelimitedStringToTVF)
Or... write the code in a CLR function. I have examples of both in my book.
While the CLR function takes about 5 lines of code (and the TSQL takes about
40 lines), the CLR function is no faster.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Only in the same way a yapping Chihuahua can push a champion Rottweiler's
buttons. Although being a cat lover, you might not appreciate the Rotty
reference and I'm about 70 lbs to fat for the Chihuahua analogy to fit ;-)
 
When i was first learning ADO.NET, the DBA at my company busted me using
concatenated sql. He got all over me about it and I countered that there
was no elegant way to do it as far as I knew. he responded that "There's no
elegant way for me to restore the database without looking like a complete
jacka33 after some hacker decides to have some fun with it" . That pretty
much settled it for me ;-)
 
Back
Top