none
Visual basic Script to update an SQL Table RRS feed

  • Question

  • 'Hello. I am trying to write a Visual Basic Script that will update a table. Basically a program I use to activate a user account is in SQL. I am trying to save myself some time to automate this process as I do this hundreds times a day and the program normally used is really slow. I have confirmed changing the SQL table does indeed activate the account as needed.
    
    'Here is what I have so far. (Please excuse unused variables as I have been playing around)


    Dim CN 'Computer Name
    Dim Active
    Dim Connection
    Dim ConnString
    Dim Recordset
    Dim SQL
    Dim tableName, connectionString, recNo, arrayColumns, arrayValues
    
    Set wshShell = CreateObject( "WScript.Shell" )
    strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
    
    CN = strComputerName & "\SQLEXPRESS"
    strComputers = CN 'The computer name changes for each server I do this on
    arrComputers = SPLIT(strComputers, ",")
    MsgBox "Test " & CN
    
    Active = InputBox("Do you want to Activate Tech Account? Y for Yes, N to Deactivate TechAccount")
    
    'This is for tech to log in to the server
    if Active = "Y" or Active = "y" then
       set WshShell = CREATEOBJECT("WScript.Shell")
       WshShell.Run("net user TechAccount /active:yes")
       MsgBox "Activated Tech Account"
    
    'The SQL is for the Tech account in the software
    'ConnString="DRIVER={SQL Server};SERVER=" & CN &  ";UID=;" & "PWD=;DATABASE=Directory"
    ConnString = "Driver={SQL Server Native Client 11.0};Server=" & CN & "; Database=Directory;"'Trusted_Connection=yes;"
    arrayColumns = Array("Name", "Info1")
    
    SQL = "SELECT * users"
    
    'This is where I am stuck. I am not sure how to connect to SQL Server. The SQL Servers are Express versions So I know below is wrong.
    Set connection = Sys.OleObject("ADODB.Connection")
    connection.ConnString = connString
    Call connection.Open
    
    Set Recordset = Server.CreateObject("ADODB.Recordset")
    
    Connection.Open ConnString
    Recordset.Open SQL,Connection
    
    If Recordset.EOF Then 
       Response.Write("No records returned.") 
    Else 
    
       Do While NOT Recordset.Eof   
          Response.write Recordset("TechAccount")
          Response.write "<br>"    
          Recordset.MoveNext     
       Loop
    
    End If
    
    Recordset.Close
    Set Recordset=nothing
    
    Connection.Close
    Set Connection=nothing
    
    End IF
    
    'This part is for deactivating tech accounts
    if Active = "N" or Active = "n" then
       set WshShell = CREATEOBJECT("WScript.Shell")
       WshShell.Run("net user TechAccount /active:no")
       mgBox "Dectivated TechAccount"
    End IF

    Looking for any help I can get. Thank you in advance


    • Edited by Broggy69 Tuesday, February 12, 2019 7:31 AM bad Formatting
    • Moved by Bill_Stewart Friday, March 15, 2019 8:04 PM This is not "fix/debug/rewrite my script for me" forum
    Tuesday, February 12, 2019 7:28 AM

Answers

  • You should not be using VBScript for this. All of this can be done in one line in PowerShell.

    $sql = 'update users Set Enabled = 1 Where username = 'myusertoupdate'
    Invoke-SQlCmd -Instance .\SQLExpress -Query $sql

    Since you do not know VBScript or SQL I recommend learning and using PowerShell.  VBScript is obsolete.

    If you are trying to do this in a web page then it will not work and you will need to post in the http://forums.asp.net site for help with ASP.


    \_(ツ)_/



    • Edited by jrv Tuesday, February 12, 2019 10:54 AM
    • Marked as answer by Broggy69 Wednesday, April 10, 2019 4:20 PM
    Tuesday, February 12, 2019 10:47 AM

All replies

  • Why are you putting your question in a code box.  It is not code and it is not readable when it is in one.  No line wrap.


    \_(ツ)_/

    Tuesday, February 12, 2019 10:45 AM
  • You should not be using VBScript for this. All of this can be done in one line in PowerShell.

    $sql = 'update users Set Enabled = 1 Where username = 'myusertoupdate'
    Invoke-SQlCmd -Instance .\SQLExpress -Query $sql

    Since you do not know VBScript or SQL I recommend learning and using PowerShell.  VBScript is obsolete.

    If you are trying to do this in a web page then it will not work and you will need to post in the http://forums.asp.net site for help with ASP.


    \_(ツ)_/



    • Edited by jrv Tuesday, February 12, 2019 10:54 AM
    • Marked as answer by Broggy69 Wednesday, April 10, 2019 4:20 PM
    Tuesday, February 12, 2019 10:47 AM
  • Why are you putting your question in a code box.  It is not code and it is not readable when it is in one.  No line wrap.


    \_(ツ)_/


    Because when I enter regular text extra coding appears.
    Wednesday, February 13, 2019 6:28 AM
  • You should not be using VBScript for this. All of this can be done in one line in PowerShell.

    $sql = 'update users Set Enabled = 1 Where username = 'myusertoupdate'
    Invoke-SQlCmd -Instance .\SQLExpress -Query $sql

    Since you do not know VBScript or SQL I recommend learning and using PowerShell.  VBScript is obsolete.

    If you are trying to do this in a web page then it will not work and you will need to post in the http://forums.asp.net site for help with ASP.


    \_(ツ)_/



    PowerShell is blocked. So I can run regular command line or visual basic script. So that is why I was trying this with Visual basic script

    Wednesday, February 13, 2019 6:30 AM
  • Why are you putting your question in a code box.  It is not code and it is not readable when it is in one.  No line wrap.


    \_(ツ)_/


    Because when I enter regular text extra coding appears.

    Turn off Grammarly for this site or it will screw up everything. Contact Grammarly and upgrade or patch your copy.


    \_(ツ)_/

    Wednesday, February 13, 2019 6:40 AM
  • You should not be using VBScript for this. All of this can be done in one line in PowerShell.

    $sql = 'update users Set Enabled = 1 Where username = 'myusertoupdate'
    Invoke-SQlCmd -Instance .\SQLExpress -Query $sql

    Since you do not know VBScript or SQL I recommend learning and using PowerShell.  VBScript is obsolete.

    If you are trying to do this in a web page then it will not work and you will need to post in the http://forums.asp.net site for help with ASP.


    \_(ツ)_/



    PowerShell is blocked. So I can run regular command line or visual basic script. So that is why I was trying this with Visual basic script

    How is PowerShell blocked?  Current systems require PowerShell.


    \_(ツ)_/

    Wednesday, February 13, 2019 6:41 AM
  • You should not be using VBScript for this. All of this can be done in one line in PowerShell.

    $sql = 'update users Set Enabled = 1 Where username = 'myusertoupdate'
    Invoke-SQlCmd -Instance .\SQLExpress -Query $sql

    Since you do not know VBScript or SQL I recommend learning and using PowerShell.  VBScript is obsolete.

    If you are trying to do this in a web page then it will not work and you will need to post in the http://forums.asp.net site for help with ASP.


    \_(ツ)_/



    PowerShell is blocked. So I can run regular command line or visual basic script. So that is why I was trying this with Visual basic script

    How is PowerShell blocked?  Current systems require PowerShell.


    \_(ツ)_/

    We cannot run powershell. When anyone tries we get. "Powershell has been blocked by your IT admin." Then bit 9 blocks it as well

    • Edited by Broggy69 Thursday, February 14, 2019 2:58 AM extra code
    Thursday, February 14, 2019 2:56 AM
  • Ok.  It s really odd to block PowerShell and not VBScript.

    Have you taken the time to learn VBScript? It seems that the code you are using is not intended to be used for the purpose you are trying to use it for. It is also quite incorrectly written for any purpose.  It is code that was copied from coed for an ASP web page.  It cannot update anything and will not run as a local script.  It can only be run on an IIS server.

    You also need to realize that only a dbo can update the logins for a user in SQLServer.  To update logins requires the use of a stored procedure to enable a user login. 

    I would suggest that you post questions about SQLServer and logins/accounts in the SQLServer forum.  They can show you how to manage this using TSQL.


    \_(ツ)_/

    Thursday, February 14, 2019 5:09 AM
  • Ok.  It s really odd to block PowerShell and not VBScript.

    Have you taken the time to learn VBScript? It seems that the code you are using is not intended to be used for the purpose you are trying to use it for. It is also quite incorrectly written for any purpose.  It is code that was copied from coed for an ASP web page.  It cannot update anything and will not run as a local script.  It can only be run on an IIS server.

    You also need to realize that only a dbo can update the logins for a user in SQLServer.  To update logins requires the use of a stored procedure to enable a user login. 

    I would suggest that you post questions about SQLServer and logins/accounts in the SQLServer forum.  They can show you how to manage this using TSQL.


    \_(ツ)_/

    I am trying to learn it. I am better with Visual Basic .Net and have that working, but we are not allowed to add .exe files to the servers. So VB Script is the only option. 

    Code is poorly written because going from .Net to VBS is very different. 

    So the SQL is SQL Express it is used to control one of our software. The account I am updating is not an SQL account, but rather the software reads the SQL data. There is only 1-5 records and the "Users" is an an actual table in the SQL Database. In that table there is a field named info1. That is what I need to update.

    I have been working with the Admin teams to allow us to use powershell but they do not want that.

    Hopefulley that makes more sense.

    Thursday, February 14, 2019 6:42 AM
  • To update a table you will need to use an "update" sql statement.  If you search you will find many examples of using vbscript to update a table.  You can also search for VB6 examples as they are partly useful.

    I would suggest that just using TSQL at an OSQL prompt would be the easiest for you.

    osql -?

    This would eliminate the need to learn VB or VBScript.


    \_(ツ)_/

    Thursday, February 14, 2019 6:48 AM
  • Here is an example of how to do a table update with VBScript:

    Public Function Update()
    
    	Dim adoCMD
    	Dim adoConn
    	Dim strSQL
    	
    	'Define a query to Update a new record into the FE temp table
    	strSQL = "UPDATE [" & Me.FETempTableName & "]" & vbCrLf & _
    	         "SET [partnumber] = ?," & vbCrLf & _
    	         "[title] = ?," & vbCrLf & _
    	         "[qtyper] = ?," & vbCrLf & _
    	         "[oldqtyper] = ?," & vbCrLf & _
    	         "[addpartrecordflg] = ?," & vbCrLf & _
    	         "[doneflg] = ?" & vbCrLf & _
    	         "WHERE [aid] = ?;"
    	
    	Set adoConn = CreateObject(ADODB.Connection)
    	strCnxn="Provider='sqloledb';Data Source=SERVER_NAME;Integrated Security=SSPI;Initial Catalog=Northwind;"  
    	adoConn.Open  strCnxn  
    	
    	Set adoCMD = CreateObject(ADODB.Command)
    	
    	With adoCMD
    		.ActiveConnection = adoCnn
    		.CommandType = adCmdText
    		.CommandText = strSQL
    		.Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)
    		.Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Me.title)
    		.Parameters.Append .CreateParameter("p3", adSmallInt, adParamInput, 2, Me.qtyper)
    		.Parameters.Append .CreateParameter("p4", adSmallInt, adParamInput, 2, Me.oldqtyper)
    		.Parameters.Append .CreateParameter("p5", adBoolean, adParamInput, 2, Me.addpartrecordflg)
    		.Parameters.Append .CreateParameter("p6", adBoolean, adParamInput, 2, Me.doneflg)
    		.Parameters.Append .CreateParameter("p7", adInteger, adParamInput, 4, Me.aid)
    		Set adoRS = .Execute(lRecordsAffected)
    	End With
    	
    	If lRecordsAffected = 0 Then
    		Update = False
    	Else
    		'Return a good return code
    		Update = True
    	End If
    
    
    End Function


    \_(ツ)_/

    Thursday, February 14, 2019 7:05 AM
  • Ok, So I have my code connecting to the SQL server.

    But my query isn't working. I am not getting a file.

    Here is the code.

    Dim CN 'Computer Name Dim SQL dim dbconn dim ConnectionString Set wshShell = CreateObject( "WScript.Shell" ) strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" ) CN = strComputerName & "\SQLEXPRESS" strComputers = CN MsgBox "Test " & CN Active = InputBox("Do you want to Activate Snowbird? Y for Yes, N to Deactivate Snowbird") if Active = "Y" or Active = "y" then set WshShell = CREATEOBJECT("WScript.Shell") WshShell.Run("net user snowbird /active:yes") MsgBox "Activated Snowbird" ConnectionString = "Provider=SQLOLEDB;Server=" & CN & "; DataBase=Directory; Trusted_Connection=yes;" Set dbconn = CreateObject("ADODB.Connection") dbconn.Open ConnectionString SQL = "SELECT Info INTO [Text;HDR=No;DATABASE=D:\Rent\TycOAdmin_Y.txt] FROM [User]" dbconn.Execute(SQL)

    'I am trying to export the field "Info" to a Text file. It is in a table called "User"

    MsgBox "Connection Worked?" End IF if Active = "N" or Active = "n" then set WshShell = CREATEOBJECT("WScript.Shell") WshShell.Run("net user snowbird /active:no") mgBox "Dectivated Snowbird" End IF


    Thursday, February 14, 2019 8:25 AM
  • Why are you asking how to update a SQL table then posting the code that tries to set a Windows user account?  That makes no sense.

    I cannot tell what you are trying to doo and your question and explanation are not helpful.  ALso your SQL has nothing to do with querying a database.  It is an internal only expression used to create file output.

    You need to learn how to use SQL and you have to explain what you are doing better than you are.  You seem to be just copying things and guessing at how to use them.


    \_(ツ)_/


    • Edited by jrv Thursday, February 14, 2019 8:32 AM
    Thursday, February 14, 2019 8:29 AM
  • Why are you asking how to update a SQL table then posting the code that tries to set a Windows user account?  That makes no sense.

    I cannot tell what you are trying to doo and your question and explanation are not helpful.  ALso your SQL has nothing to do with querying a database.  It is an internal only expression used to create file output.

    You need to learn how to use SQL and you have to explain what you are doing better than you are.  You seem to be just copying things and guessing at how to use them.


    \_(ツ)_/


    Sorry. So this reason is the field I am trying to update is an XML file. So I cannot simply update the entire thing.

    So my plan was to export the file, change it, then re-import it. I work late hours. I am using one of my .Net programs as a road map. But learning very quickly VBS and .Net are different. 

    Thursday, February 14, 2019 6:08 PM