We help IT Professionals succeed at work.

VBScript against SQL database

I have an SQL database that I need to access using a VBscript/  I need tohe script to prompt the end user with 3 variables when executed then take the variables entered and use them as conditions on an SQL query.  I need the user to enter location, department and resource.  These values are in a drop down box presented by the vbscript and chosen by the end user.  They are then used to display infomation from database fields and can be saved to a delimited file.  Can anyone help?
Comment
Watch Question

CERTIFIED EXPERT
Commented:
basically there's two questions..

1. how to capture a users response.
as you want drop down options you would have to look at doing a form..
i suggest HTML. (see the sniplet)



2. how to get a query from sql server via vbscript

Dim mConnection, sql

Set mConnection = CreateObject("ADODB.Connection")

mConnection.Open "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=DBNAME","db_user","password"

 sql = "select * from table where location = '"& strUserCountry

 mConnection.Execute sql

then return it anyway you like.

Set WshShell = WScript.CreateObject("WScript.Shell") 
Set WshNetwork = WScript.CreateObject("WScript.Network") 
Set WshProcessEnvironment = WshShell.Environment("Process")
Set WshFileSystem = WScript.CreateObject("Scripting.FileSystemObject")

dim strUserInput, strQuestion, strOptions, vMsg

strOptions = "<div align=""center""><p>Select your Country:" _
                                  & "</p>" & vbCrLf _
                                  & "<p><Select name=""UserInput""  " _
                                  & "id=""UserInput""></p>" & vbCrLf _
                                  & " <option value=""NL"">NL</option>" _
								  & " <option value=""FR"">FR</option>" _
								  & " <option value=""BE"">BE</option>" _
								  & " <option value=""ES"">ES</option>" _
								  & " <option value=""IT"">IT</option>" _
								  & " <option value=""A"">A</option>" _
								  & " <option value=""CH"">CH</option>" _
								  & "</select>" _
								  & "<p>" _
								  & "<input type=""hidden"" id=""OK"" " _
                                  & "name=""OK"" value=""0"">" _
                                  & "<input type=""submit"" value="" OK "" " _
                                  & "OnClick=""VBScript:OK.Value=1""></p></div>"


strUserCountry = GetUserInput( strOptions )

Open in new window

CERTIFIED EXPERT
Top Expert 2011
Commented:
as per the above , however i'd create the actual sql as a stored procedure and pass the parameters to that...

i'd then run the stored procedure via bcp

and use ms excell to display the results... allowing the user to decide if they want to keep the result file
via standard ms excell save processing

Explore More ContentExplore courses, solutions, and other research materials related to this topic.