Display Query Results in Message Box

I have the below code working fine but I'd like to add a "LIKE" to the SQL statement so users can search by ID or user name, ex:

strSQL = "SELECT * FROM Table1 WHERE USER ID or User Name LIKE '%" & strKey & '%" ...

I've been able to accomplish the Like for the user name but the msgbox only lists one value and doesn't list all the possible values for the user to see/choose. I think I need a do or for loop but would like some input. Thanks!



Const adOpenStatic = 3
Const adLockOptimistic = 3


strKey = InputBox("Enter User ID")

strConnection = "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=<server>;Database=<database>;User ID=<doman\userid>;[Password]=<password>;"
[b]strSQL = "SELECT * FROM Table1 WHERE Key1 = '" & strKey & "'"
[/b]
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adOpenStatic, adLockOptimistic, adLockReadOnly


If(Not(objRecordSet.EOF )) Then

Field1 = strKey
Field2 = objRecordSet("User Name")

objRecordSet.MoveFirst

Else

msgbox "No records found."

End If 

objRecordSet.Close
Set objRecordSet=Nothing
objConnection.Close
Set objConnection = Nothing

Open in new window


Tracy
LVL 1
tracymsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Your final query which will hit the DB Server should be like below-

SELECT * FROM Table1 WHERE USERID LIKE '%12%' or UserName LIKE '%Pawan%'

SELECT * FROM Table1 WHERE USERID LIKE '%12%'

This is even better it will have the ability to use the index.
SELECT * FROM Table1 WHERE USERID LIKE '12%'
0
tracymsAuthor Commented:
Thanks! I don't know what the number 12 represents? The "Like" is based on what the user enters in the text box of the script (strKey). Also, will all results be show in message box?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

tracymsAuthor Commented:
DUH! Sorry, I think you were giving me examples - LOL.
0
Pawan KumarDatabase ExpertCommented:
strKey is that number/text that user enters,, .. 12 is an example.
0
tracymsAuthor Commented:
strSQL = "SELECT * FROM Table1 where Key1 LIKE '%" & strKey & "%' or User Name LIKE '%" & strKey & "%'"...that's working and takes care of the first part. Thanks! It still only shows one value in the msgbox - getting that to work will involve vbscript.
1
Fabrice LambertFabrice LambertCommented:
You'll need a loop to retrieve all the values, and build a string to show them all.

Warning !!
I do not advice using such technique, unless you know that you have very limited data to display.
Users won't like having a gigantic message box poping with buttons out of the screen's bound.
0
tracymsAuthor Commented:
Makes sense Fabrice, thanks. I suppose I can modify the SQL OR statement to say "equal to" instead of "Like". I wanted to give users a way to look up either, which this will and return only a single value...unless you have another suggestion?
0
Ryan ChongCommented:
but the msgbox only lists one value and doesn't list all the possible values for the user to see/choose. I think I need a do or for loop but would like some input.
yes, so you need to do a looping. something like:

...
Dim yourOutput
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adOpenStatic, adLockOptimistic, adLockReadOnly

do while objRecordSet.EOF = false
  Field1 = objRecordSet("Key1")
  Field2 = objRecordSet("User Name")
  yourOutput = Field1 & ": " & Field2 & vbcrlf
  objRecordSet.MoveNext
loop

if yourOutput <> "" then
    msgbox yourOutput
Else
   msgbox "No records found."
End If 
...

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tracymsAuthor Commented:
Thanks Ryan. I stripped down your code as it was still only pulling a single value in the message box. Once I got it working, I found the message box was looping through each value - no way to cancel.

strKey = InputBox("Enter Person ID","Information Box")
strConnection = "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=;Database=;User ID=;Password="
strSQL = "SELECT * FROM Table1 where Key1 LIKE '%" & strKey & "%' or User Name LIKE '%" & strKey & "%'"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adLockReadOnly
do while objRecordSet.EOF = False
msgbox objRecordSet("User Name") & ": " & strKey & vbcrlf
objRecordSet.MoveNext
loop
objRecordSet.Close
Set objRecordSet=Nothing
objConnection.Close
Set objConnection = Nothing

Open in new window


I was looking to show users the possible values in a single window/message box so they can see their options...I don't know. Perhaps a drop down to select?
0
Ryan ChongCommented:
are you doing all these in pure vbscript by running a .vbs file? where you actually run these scripts?

do while objRecordSet.EOF = False

msgbox objRecordSet("User Name") & ": " & strKey & vbcrlf
objRecordSet.MoveNext
loop

I found the message box was looping through each value - no way to cancel.
yes, you can't since you put the messagebox in the loop, if you got 10 records, it will prompt for 10 times, likewise.

indeed a messagebox is not a good way to show options. (you also can't make selection from a messagebox)
0
tracymsAuthor Commented:
Thanks Ryan,

I'm pasting the code in an application window that uses vbscript and it gets finicky with how its used/written. I did manage to get things working but had to account for users clicking the cancel button and re-set fields in the application to empty as they default to a random number and I was getting a mismatch error.

I've posted the working code below but would like to ask one more thing - is there a way to "remember" the last thing the user entered in the message box? I've researched the "Default" value option for input boxes but it doesn't work when the input box is invoked/brought up again.

strKey = InputBox("Enter Person ID","Search")
If strKey = "" then
Field1 = ""
Field2 = ""
on error resume next
Else
strConnection = "Provider=SQLOLEDB;Server=;Database=;User ID=;Password="
strSQL = "SELECT * FROM Table1 where ID LIKE '%" & strKey & "%' or User_Name LIKE '%" & strKey & "%'"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adLockReadOnly
do while objRecordSet.EOF = false
Field1 = objRecordSet("User_Name")
Field2 = objRecordSet("ID")
yourOutput = objRecordSet("User_Name")  & " - " & objRecordSet("ID") & vbcrlf
objRecordSet.MoveNext
loop
if yourOutput <> "" then
msgbox yourOutput
Else
msgbox "No records found."
Field1 = ""
Field2 = ""
End If
End If

Open in new window

0
Ryan ChongCommented:
i guess you want to prevent user to enter blank or cancel the input box?

perhaps you can let user to try 3 times to enter the values, else you may exit your program, something like this should work:

tryCnt = 0
strKey = ""
do while strKey = ""
	strKey = trim(InputBox("Enter Person ID","Search"))
	tryCnt = tryCnt + 1
	if tryCnt = 3 then exit do
loop

'on error resume next

Field1 = ""
Field2 = ""
yourOutput = ""

strConnection = "Provider=SQLOLEDB;Server=blalbla;Database=;User ID=;Password=blalbla"
strSQL = "SELECT * FROM Table1 where ID LIKE '%" & strKey & "%' or User_Name LIKE '%" & strKey & "%'"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adLockReadOnly

do while objRecordSet.EOF = false
	Field1 = objRecordSet("User_Name")
	Field2 = objRecordSet("ID")
	yourOutput = objRecordSet("User_Name")  & " - " & objRecordSet("ID") & vbcrlf
	objRecordSet.MoveNext
loop

objRecordSet.close
set objRecordSet = nothing
objConnection.close
set objConnection = nothing

if yourOutput <> "" then
	msgbox yourOutput
Else
	msgbox "No records found."
End If

Open in new window


see if this meet your requirement?
0
tracymsAuthor Commented:
Its ok for them to click the cancel button but thank you for the code. The problem was it threw an error when it was clicked so I used on error resume next to prevent the error from popping up.

I was asking about the remember last thing entered in the text box because sometimes the same ID is associated with a document and the user doesn't have to keep manually entering the same ID. If it remembers the last value on refresh then the same ID will pop up in the input box...makes sense what I'm asking? Thanks!
0
tracymsAuthor Commented:
Sort of like this that I found here - https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/inputbox-function. The thing is when I refresh the input box it's blank again and not storing the previous value in the input box.

Dim Message, Title, Default, MyValue
Message = "Enter a value between 1 and 3"    ' Set prompt.
Title = "InputBox Demo"    ' Set title.
Default = "1"    ' Set default. <--- what user enters in input box
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

I realize this is an additional question - I can ask/post a separate question to address this if you prefer, thank you.
0
Ryan ChongCommented:
The problem was it threw an error when it was clicked so I used on error resume next to prevent the error from popping up.
what error you encountered here? and I think on most of the scenarios we try to avoid to use on error resume next in our codes, unless we are so sure the impact of applying that code.

If it remembers the last value on refresh then the same ID will pop up in the input box...makes sense what I'm asking?
do you mean... after the whole execution of your vbscript (the vbscript program run successfully to the end), you try to store a "cache" (aka default value) somewhere and re-use it on next occasion?
0
tracymsAuthor Commented:
- The error would say type mismatch: Field1 when I clicked the Cancel button before I added the on error resume next.

- Yes, that's what I mean. I want to cache what the user entered after vbscript is run to the end and re-use it again on the next occasion (have it show in the inputbox).
0
Ryan ChongCommented:
I want to cache what the user entered after vbscript is run to the end and re-use it again on the next occasion (have it show in the inputbox).
in that case, we will need to save that value somewhere and then retrieve it again when your vbscript is run again.

vbscript is purely a piece of codes and can't really be "cached".
0
tracymsAuthor Commented:
Thanks all!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.