By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,116 Members | 1,319 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,116 IT Pros & Developers. It's quick & easy.

VBScript-Unable to get connected to database

BezerkRogue
P: 68
I have created a script that automates the launching of an application based upon a value in the registry. For some reason, it is not connecting to the database to read what application it's supposed to launch. I have combed over this script and can't figure out what the issue is. Please help.

Expand|Select|Wrap|Line Numbers
  1.  
  2. ' VBScript File for synchronizing error proofing applications and launching the correct EP application on station start up.
  3.  
  4. 'Error handling
  5. On error resume next
  6.  
  7. 'Create variables
  8.  
  9. Dim objShell 'WScript Shell object
  10. Dim strReg1  'StationName
  11. Dim objConn  'Database Connection
  12. Dim strSQL   'SQL Command String
  13. Dim objRec   'Recordset
  14. Dim strConn  'Connection String
  15. Dim strEPApp 'Error Proofing Application String
  16. Dim appExec  'SyncToy executable variable
  17. Dim appExec2 'EP executable variable
  18.  
  19. 'Decalare and open connection string and recordset and set objects
  20. Set objConn = CreateObject("ADODB.Connection")
  21. strConn="Provider=SQLOLEDB; Data Source=XXXXXX;Trusted_Connection=Yes;Database=XXX;UID=XXX; PWD=XXX;"
  22.  
  23. Set objRec = CreateObject("ADODB.Recordset")
  24. Set objShell = WScript.CreateObject("Wscript.Shell")
  25.  
  26. 'Declare File and Reg variables
  27.  
  28. strReg1 = "HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Error Proofing Station\Properties\StationName"
  29.  
  30. 'Declare variable for database records by reading registry values into recordset.
  31. Dim rec1
  32.  
  33. rec1 = objShell.RegRead(strReg1)
  34.  
  35. If rec1 = "" then
  36.     strReg1 = "HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Prepick Error Proofing Station\Properties\StationName"
  37.     rec1 = objShell.RegRead(strReg1)
  38. End If
  39. wscript.echo rec1
  40.  
  41. 'Declare SQL Statements
  42. strSQL = "Select * from EPApps where StaName ='" & rec1 &"'"
  43. wscript.echo strSQL
  44. objRec.Open strSQL, strConn
  45. strEPApp = objRec.fields(2).value
  46.  
  47. 'Launch Synctoy App
  48. Dim WShell
  49. Set WShell = WScript.CreateObject("WScript.Shell")
  50. Set appExec = WShell.Exec("C:\SyncToy\SyncToy.exe -R")
  51. Wscript.Sleep 5000
  52. Set appExec2 = WShell.Exec("C:\Program Files\OnlineErrorProofing\" & strEPApp )
  53.  
At this point any help would be appreciated.

Thanks
Jul 14 '08 #1
Share this Question
Share on Google+
1 Reply


jeffstl
Expert 100+
P: 432
You have to open the connection to your database before you execute SQL on it.

Expand|Select|Wrap|Line Numbers
  1. 'Declare SQL Statements
  2. objConn.Open strConn
  3.  
  4. strSQL = "Select * from EPApps where StaName ='" & rec1 &"'"
  5. wscript.echo strSQL
  6. objRec.Open strSQL, strConn
  7. strEPApp = objRec.fields(2).value
  8.  
  9. objConn.Close
  10.  
  11.  
  12.  
I also notice you don't specify the path within the object so your going ot need to do something like this
Jul 14 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.