How do I execute an sql query using a vbscript inputbox?

How would I go about creating a vbscript that asks the user for a username, then when entered, it runs the following sql query:

             select username, granted_role
             from   all_users a,
             dba_role_privs b
             where  a.username = b.grantee
             and   ( b.granted_role like 'WV%'
             or b.granted_role like 'SV%')
             and a.username = UPPER('&1');

The query is searching to see if a user has a specific Oracle role, so if the user does have a role (beginning with WV, or SV), a dialogue will popup and say "user has the following roles" and then list the associated roles.

I have NO idea how to do this, but it is something that I'd really like. I imagine there would need to be a connection string involved, which is WV_PROD with a schema owner of PROD1 and password pf PROD1.

I hope what I'm asking for makes sense. Thanks in advance to those who can help!!    
Who is Participating?
sirbountyConnect With a Mentor Commented:
This should work for you...
Dim con : Set con = CreateObject("ADODB.Connection")
con.Open "Provider=SQLOLEDB.1;Initial Catalog=WV_PROD","PROD1","PROD1"
strUser = InputBox ("Enter user name:")
strSQL="select username, granted_role from all_users a, dba_role_privs b where a.username = b.grantee and (b.granted_role like 'WV%'              or b.granted_role like 'SV%') and a.username = UPPER('" & strUser & "');
Dim objRS : set objRS=con.execute(strSQL)
If objRS.EOF Then
  wscript.echo "No records found"
  Wscript.echo "user has the following roles:"
  Do While Not objRS.EOF
    wscript.echo objRS.Fields(1)
End If

Open in new window

mskittenAuthor Commented:
I am getting an Unterminated string constant error on the 5th line. I'm thinking there is a problem with this part:
and a.username = UPPER('" & strUser & "');

I tried adding a quote on the end, but it seemed that didn't work for me. After taking that part out, I now get this error (see attached). I'm thinking the provider or connection?

This is some connection info that we use in scheduled task. Maybe it will help.
 /opendb=provider=msdaora;data source=wv_prod;password=prod1;user id=prod1;
strSQL="select username, granted_role from all_users a, dba_role_privs b where a.username = b.grantee and (b.granted_role like 'WV%'              or b.granted_role like 'SV%') and a.username = UPPER('" & strUser & "');"

What do you get with the quote on the end?
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

mskittenAuthor Commented:
I might have been mistaken with the quote it looks like (long day yesterday).

I now get this error. See attached.
sirbountyConnect With a Mentor Commented:
Something wrong with the connection string then...
mskittenAuthor Commented:
Hello, sorry for they delay. I've been waiting for the connection string info. I'll let you know once they have gotten back to me.

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.

All Courses

From novice to tech pro — start learning today.