vbscript + update Sql

kingmike
kingmike used Ask the Experts™
on
I would like to create a vbscript with the windows powershell that will as the user a question and then the user can make a response and it will update a sql table cell. Then another question will popup and the user will enter this number and it will update in sql.. And so on until all of the cells I want updated are updated.

I am a vbscript newbie so any help is greatly appreciated.

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
kingmike,

"vbscript with the windows powershell"

Firstly, VBScript is a different language from Windows PowerShell.  Therefore, you will want to decide which language you are using first as approaches may be drastically different in a cmdlet than in a vbs.

VBScript Reference:
http://msdn.microsoft.com/en-us/library/t0aew7h6%28VS.85%29.aspx

PowerShell Reference:
http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx


Secondly, I am not sure you want to update a record in this manner given you are making several trips to and from the database --- however, guess I can't really say I have done this to really say there are negatives associated --- I have used VB to work with SQL through ADO so it is possible:

http://msdn.microsoft.com/en-us/library/ms676116%28VS.85%29.aspx


Again, I don't want to discourage you, just thinking if you are having to learn a new language anyway -- why would you go with VBScript for an interface to update a database row.  You can look at VB.NET and some of the nice features of .NET's SQL Server Provider, LINQ to SQL and other newer technologies.


Hope that helps get your started.

Author

Commented:
The company is trying to stick mostly with vbscript to minimize required knowledge.. if that makes any since.. WSH is what I am going for with this.
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Ah, I got you.  Windows Scripting Host.  PowerShell is the new command line scripting language and so wasn't sure why you were correlating the two.

Anyway, the reference above should help, but here is another with more sample code on using ADO(DB) to connect to SQL Server.

http://msdn.microsoft.com/en-us/magazine/cc301942.aspx

You can basically do :

UPDATE your_table
SET column_name = 'some value'
WHERE id = 'some id'

You would just replace the 'some value' literal with the response from user and specify the column_name based on what question you are up to in your sequence of update.  You will also have to determine if you are always updating same table and record (based on ID) or if user is choosing this also at some point.

Regards,

--isa
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
any idea how i tell it which row?
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
UPDATE your_table
SET column_name = 'some value'
WHERE id = 'some id'

The row will be selected by the unique identifier for that row.

If you don't have a primary key field, then you would have to use some logical ones that would make a row unique (e.g., if combination of columns A, B and C are guaranteed unique when looking at all three together then you can have a filter like WHERE A = 'a value' AND B = 'b value' AND C = 'c value')

Hope that helps.

Author

Commented:
here is what i have so far but it will  not work.
'************************************************
' File:    Input.vbs (WSH test in VBScript) 
' Author:  Jason Walters
'
' Retrieving user input in VBScript and pushing it to SQL
'************************************************
Option Explicit

Dim Message, result
Dim Title, Text1, Text2

' Define dialog box variables.
Message = "Please enter the first number"           
Title = "WSH SQL user input"
Text1 = "User input canceled"
Text2 = "You entered:" & vbCrLf

' Ready to use the InputBox function
' InputBox(prompt, title, default, xpos, ypos)
' prompt:    The text shown in the dialog box
' title:     The title of the dialog box
' default:   Default value shown in the text box
' xpos/ypos: Upper left position of the dialog box 
' If a parameter is omitted, VBScript uses a default value.

result = InputBox(Message, Title, "", 100, 100)


Dim DataConn 
DataConn = Server.CreateObject("ADODB.Connection") 
DataConn.ConnectionTimeout = 15 
DataConn.CommandTimeout = 30 
  

Dim strSQLIP 
Dim strSQLDatabase 
Dim strSQLUsername 
Dim strSQLPassword 
Dim strSQLTable 
  
strSQLIP       = "***.**.***.**,****" 
strSQLDatabase = "********" 
strSQLUsername = "****" 
strSQLPassword = "*****" 
strSQLTable    = "dbo.tbl_campaign" 
  
DataConn.Open ("Provider=SQLOLEDB;Data Source=" & strSQLIP & ";Initial Catalog=" & strSQLDatabase & ";User Id=" & strSQLUsername & ";Password=" & strSQLPassword & ";Connect Timeout=15;Network Library=dbmssocn;")
  

UPDATE tbl_campaign
SET campaignGoal = result


' Evaluate the user input.
If result = "" Then    ' Canceled by the user
    WScript.Echo Text1
Else 
    WScript.Echo Text2 & result
End If

'*** End

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Before I dive into the code, please elaborate on "will not work".  Are you getting and error, incorrect results at command line or in SQL table or both, ...

Without having looked at the rest of the code for errors, the update statement has no WHERE clause and so you will probably end up changing the campaignGoal for every row in tbl_campaign if the remainder of the code syntax is correct.  You will want to read what I indicated before on setting the row to update.

Author

Commented:
variable is undefined; 'server'
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Server.CreateObject("ADODB.Connection") would be used in ASP.  Since this is a command line application (VBScript) then it would be CreateObject("ADODB.Connection").

Author

Commented:
k i changed that. now on line 31 it says object required: "
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Sorry, I should have caught this before, but you need to "Set" objects in VBScript.

Dim DataConn
DataConn = Server.CreateObject("ADODB.Connection")

Should be:

Dim DataConn
Set DataConn = Server.CreateObject("ADODB.Connection")

Sorry been distracted and haven't look through the code thoroughly yet, but may work in your favor to work through it this way so you can learn a little about VBScript.  I would highly suggest though you invest in some reference books.  At least for me personally, I learn by diving in and it is just handy to have something around -- but guess these days the Internet is filled with information -- I recall back in the day I had a VBScript Bible -- think I saw they have a book now that is PowerShell, VBScript and JScript Bible (reference).

Author

Commented:
yeh I am trying to learn.. but vbscript and the powershell seem to be more confusing than most languages I have tried. I am making some progress. I took one short class on it.

Now I  am getting an error that reads tbl_campaign not defined. It is coming from this stmt i think.

 
DataConn.Open ("Provider=SQLOLEDB;Data Source=" & strSQLIP & ";Initial Catalog=" & strSQLDatabase & ";User Id=" & strSQLUsername & ";Password=" & strSQLPassword & ";Connect Timeout=15;Network Library=dbmssocn;")
 
UPDATE tbl_campaign
SET campaignGoal = result
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Okay, so getting further as the issue there is you just have the SQL command sitting directly in code as if it were VBScript.  That code should be in a variable called something like "sql" or following some convention "strSql" that you then must execute using your ADODB.Connection to yield an ADODB.ResultSet for a select query or in this case you can use an execute non query call that will result in an integer number of rows impacted.

Author

Commented:
this is the recordset i have for the ASP part of this that displays on the web.


Dim Recordset1 
Dim Recordset1_numRows 
Recordset1 = Server.CreateObject("ADODB.Recordset") 
Recordset1.ActiveConnection = DataConn 
Recordset1.Source = "SELECT title,campaignGoal,toDate,toGoal FROM " & strSQLTable 
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open() 
  
Recordset1_numRows = 0 
  
Dim Repeat1__numRows 
Dim Repeat1__index 
  
Repeat1__numRows   = -1 
Repeat1__index     = 0 
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows 

Dim dsTitle, dsGoal, dsToDate, dsToGoal
  
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))  
        dsTitle = trim(Recordset1.Fields.Item("title").Value)
        dsGoal = trim(Recordset1.Fields.Item("campaignGoal").Value) 
        dsToDate = trim(Recordset1.Fields.Item("toDate").Value) 
        dsToGoal = trim(Recordset1.Fields.Item("toGoal").Value) 
                 
        response.write ("<tr>" )
        response.write ("<td align=center>" & dsTitle & "</td>")
        response.write ("<td align=center>" & dsGoal & "</td>")
        response.write ("<td align=center>" & dsToDate & "</td>") 
        response.write ("<td align=center>" & dsToGoal & "</td>") 
        response.write ("</tr>") 
         
        Repeat1__index=Repeat1__index+1 
        Repeat1__numRows=Repeat1__numRows-1 
        Recordset1.MoveNext() 
End While 
Recordset1.Close() 
Recordset1 = Nothing

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Okay, now I am confused on what you are looking to do.  I should have been clearer above having re-read my comments.  The use of VBScript with ADODB isn't that strange to me as I have done it before many times from ASP where would use web forms to update a database row or set of rows from admin portion of a web site.

However, sounded like you were trying to run a command line script that would update a row but one field at a time as a prompt and response type application from the command prompt.

If you are doing this via ASP, then that makes a little more sense to me.

Here is a set of tutorials on building dynamic pages (ASP) with ADO.
http://www.w3schools.com/ado/default.asp

You should have enough from my above comments on what needs to get done, I think you just need to get more familiar with the syntax.  You seem to have a good bit of code already in your environment to do so given the right reference.  You will see from the display ASP that the SQL code is in string passed to ADO and not sitting as VB code on its own.  You can also see that connection is needed to query which is needed to get recordset.

Regards,

Kevin

Author

Commented:
I am trying to use the WSH to update the SQL and the ASP webpage is where all users can view what is in the database.

Author

Commented:
I think i am very close to what I am going for with this.
'************************************************
' File:    Input.vbs (WSH test in VBScript) 
' Author:  Jason Walters
'
' Retrieving user input in VBScript and pushing it to SQL
'************************************************
Option Explicit

Dim Message, result
Dim Title, Text1, Text2

' Define dialog box variables.
Message = "Please enter the first number"           
Title = "WSH SQL user input"
Text1 = "User input canceled"
Text2 = "You entered:" & vbCrLf

' Ready to use the InputBox function
' InputBox(prompt, title, default, xpos, ypos)
' prompt:    The text shown in the dialog box
' title:     The title of the dialog box
' default:   Default value shown in the text box
' xpos/ypos: Upper left position of the dialog box 
' If a parameter is omitted, VBScript uses a default value.

result = InputBox(Message, Title, "", 100, 100)


Dim DataConn 
Set DataConn = CreateObject("ADODB.Connection") 
DataConn.ConnectionTimeout = 15 
DataConn.CommandTimeout = 30 
  

Dim strSQLIP 
Dim strSQLDatabase 
Dim strSQLUsername 
Dim strSQLPassword 
Dim strSQLTable 
  
strSQLIP       = "***.***.***.**,****" 
strSQLDatabase = "*******" 
strSQLUsername = "****" 
strSQLPassword = "****" 
strSQLTable    = "dbo.tbl_campaign" 
  
DataConn.Open ("Provider=SQLOLEDB;Data Source=" & strSQLIP & ";Initial Catalog=" & strSQLDatabase & ";User Id=" & strSQLUsername & ";Password=" & strSQLPassword & ";Connect Timeout=15;Network Library=dbmssocn;")
  
Dim Recordset1 
Dim Recordset1_numRows 
Set Recordset1 = CreateObject("ADODB.Recordset") 
Recordset1.ActiveConnection = DataConn 
Recordset1.Source = "SELECT title,campaignGoal,toDate,toGoal FROM " & strSQLTable 
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open() 
  
Recordset1_numRows = 0 
  
Dim Repeat1__numRows 
Dim Repeat1__index 
  
Repeat1__numRows   = -1 
Repeat1__index     = 0 
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows 



Recordset1.Close() 

' Evaluate the user input.
If result = "" Then    ' Canceled by the user
    WScript.Echo Text1
Else 
    WScript.Echo Text2 & result
End If

'*** End

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Just execute UPDATE query instead of SELECT and you should be fine.

Author

Commented:
I am getting an error that says "current recordset does not support updating. This may be a limitation of the provider, or of the selected locktype"

this is the code i am trying to use.
'************************************************ 
' File:    Input.vbs (WSH test in VBScript)  
' Author:  Jason Walters 
' 
' Retrieving user input in VBScript and pushing it to SQL 
'************************************************ 
Option Explicit 
 
Dim Message, result 
Dim Title, Text1, Text2 
 
' Define dialog box variables. 
Message = "Please enter the first number"            
Title = "WSH SQL user input" 
Text1 = "User input canceled" 
Text2 = "You entered:" & vbCrLf 
 
' Ready to use the InputBox function 
' InputBox(prompt, title, default, xpos, ypos) 
' prompt:    The text shown in the dialog box 
' title:     The title of the dialog box 
' default:   Default value shown in the text box 
' xpos/ypos: Upper left position of the dialog box  
' If a parameter is omitted, VBScript uses a default value. 
 
result = InputBox(Message, Title, "", 100, 100) 
 
 
Dim DataConn  
Set DataConn = CreateObject("ADODB.Connection")  
DataConn.ConnectionTimeout = 15  
DataConn.CommandTimeout = 30  
   
 
Dim strSQLIP  
Dim strSQLDatabase  
Dim strSQLUsername  
Dim strSQLPassword  
Dim strSQLTable  
   
strSQLIP       = "**********"  
strSQLDatabase = "********"  
strSQLUsername = "*********"  
strSQLPassword = "******"  
strSQLTable    = "dbo.tbl_campaign"  
   
DataConn.Open ("Provider=SQLOLEDB;Data Source=" & strSQLIP & ";Initial Catalog=" & strSQLDatabase & ";User Id=" & strSQLUsername & ";Password=" & strSQLPassword & ";Connect Timeout=15;Network Library=dbmssocn;") 
   
Dim Recordset1  
Dim Recordset1_numRows  
Set Recordset1 = CreateObject("ADODB.Recordset")  
Recordset1.ActiveConnection = DataConn  
Recordset1.Source = "SELECT title,campaignGoal,toDate,toGoal FROM " & strSQLTable  
Recordset1.CursorType = 0  
Recordset1.CursorLocation = 2  
Recordset1.LockType = 1  
Recordset1.Open()  
   
Recordset1_numRows = 0  
   
Dim Repeat1__numRows  
Dim Repeat1__index  
   
Repeat1__numRows   = -1  
Repeat1__index     = 0  
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows  

Recordset1.Fields.Item("title").value = "test"
 
 
 
Recordset1.Close()  
 
' Evaluate the user input. 
If result = "" Then    ' Canceled by the user 
    WScript.Echo Text1 
Else  
    WScript.Echo Text2 & result 
End If 
 
'*** End

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
You have locked the recordset to being read only with :

Recordset1.LockType = 1

I would go through the tutorial again on ADODB.RecordSet:
http://www.w3schools.com/ADO/ado_ref_recordset.asp

Click on LockType and it will give you the different options.  You can see which works best for your needs like adLockOptimistic or:

Recordset1.LockType = 3


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial