VBScript to connect to SQL Server 2005 and update a table

Question

I am new to VBScript. Can someone please help me to connect to SQL Server 2005 (OLEDB) using VBScript and update a table in the database.

My server: sql14\qw
My database: fret
User id: admin
Pasword: pass
Table name: lookup

Answer 1

Please check out this article :

http://www.cruto.com/resources/vbscript/vbscript-examples/misc/database/Connect-to-a-SQL-Server-Database.asp

Answer 2

Easy stuff, actually. First, you have to define the connection and recordset that you'll be using:

Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")

After that, it's all about the connection string:

connstr="Provider=SQLOLEDB.1;Data Source=" & server & ";Initial Catalog=" & database & ";user id = '" & uid & "';password='" & pwd & "'"

The string consists of a few parts:

• Provider: the type of connection you are establishing, in this case SQL Server.

• Data Source: The server you are connecting to.

• Initial Catalog: The name of the database.

• user id: your username.

• password: um, your password. ;)

Note that if you want to use your Windows login credentials and are running the script locally then you can substitute the following for the username and password fields:

Integrated Security=SSPI

Of course, this won't work if you're using your script on a website, so you'll have to explicitly use username and password. Then you just open the recordset, hand over the SQL query, and capture the returned data as an array.

SQL="Select @@version as name"
AdRec.Open SQL, AdCn,1,1
queryReturn=Adrec("name")

Just remember that the data is being returned as an array (often two dimensional, where the results you want are actually in the second dimension of the array!) and that you may need to either Trim to kill blank spaces at the end of results or parse the results with string functions like Left. Personally, I always Trim() a result while assigning it to a variable as I've been bitten by hidden blanks more times than I can count.

Answer 3

Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=sql14\qw;Initial Catalog=fret;user id ='admin';password='pass'"
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "UPDATE lookup SET Col1 = 'Hello'"
myCommand.Execute
myConn.Close

Tested using Integrated Windows Security, did not test with SQL Login.