VBScript连接到SQLServer2005并更新表-VBScripttoconnecttoSQLServer2005andupdateatable

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.

使用集成Windows安全性进行测试,未使用SQL登录进行测试。

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.

  Provider:您正在建立的连接类型,在本例中为SQL Server。

• Data Source: The server you are connecting to.

  数据源:您要连接的服务器。

• Initial Catalog: The name of the database.

  初始目录:数据库的名称。

• user id: your username.

  用户ID:您的用户名。

• 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:

请注意,如果要使用Windows登录凭据并在本地运行脚本,则可以将以下内容替换为用户名和密码字段:

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查询,并将返回的数据捕获为数组。

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.

请记住,数据是作为一个数组返回的(通常是二维的,你想要的结果实际上是在数组的第二维中!),你可能需要修剪以在结果结束时删除空格或使用像Left这样的字符串函数解析结果。就个人而言,我总是修剪()将结果分配给变量,因为我被隐藏的空白咬了几次,而不是我可以计算。