VBScript如何设置连接字符串

Question

我不确定之前是否已经讨论过，但我有以下代码（我从 Access 数据库的示例中进行了更改。）

我不知道要在 'Provider' 中放入什么内容 和“数据源”。我正在使用 MS SQL Server 2005，如何在我的计算机中查找此信息？拜托，即使我尝试从这里开始http://www.connectionstrings.com/< /em>，但我还是不知道该怎么做。

Dim conn, sql
sql = "SELECT * FROM tblOutbox"
Set conn = CreateObject("ADODB.Connection")
With conn
      .Provider = "myProvider"
      .Mode = adModeReadWrite
   .ConnectionString = "Data Source=mysource;" & _
          "database=myDbase.mdf;   "
      .Open 
End With
If conn.State = adStateOpen Then
      WScript.Echo "Connection was established."
End If
conn.Close
Set conn = Nothing

这里使用access数据库：

Dim conn 
Dim sql
sql = "SELECT * FROM tblOutbox"
Set conn = CreateObject("ADODB.Connection")
   With conn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Mode = adModeReadWrite
      .ConnectionString = "Data Source= f:/Status.mdb"
      .Open 
   End With
   WScript.Echo "Connection was opened." 
conn.Close
Set conn = Nothing
WScript.Echo "Connection was closed."

Answer 1

尝试在连接字符串中设置提供程序

conn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=XXX;Initial Catalog=XXX;User ID=<XXX>;Password=<XXX>;"

注意：我还没有测试过它，但它应该可以工作

Answer 2

满足您所有的连接字符串需求：http://www.connectionstrings.com/

Answer 3

感谢您的帮助，我终于找到了解决方案。
在“管理工具”、“数据源 (ODBS)”、“驱动程序”选项卡下，我使用提供程序：SQLNCLI、我的服务器：数据源，并将 Recordset rs 添加到代码中。看来，没有这个，连接就建立不起来。我不知道发生了什么事。

Dim conn , rs, sql, ConnString
sql = "SELECT * FROM tblOutbox"
Set rs = CreateObject("ADODB.Recordset")
Set conn = CreateObject("ADODB.Connection")
With conn
      .Provider = "SQLNCLI"
      .Mode = adModeReadWrite
      .ConnectionString = "SERVER=.\SQLExpress;AttachDbFilename=F:\Test2.mdf;Database=Test2.mdf; Trusted_Connection=Yes;"
      .Open
      WScript.Echo "Connection was established."
End With
rs.Open sql,conn
If conn.State = adStateOpen Then
      WScript.Echo "Connection was established."
Else
  WScript.Echo "No Connection ."
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Answer 4

使用 SQL Server，您不直接连接到该文件。 Access 作为基于文件的 RDBMS 略有不同。

按照你的例子，它看起来像这样：

Dim conn, sql
sql = "SELECT * FROM tblOutbox"
Set conn = CreateObject("ADODB.Connection")
With conn
      .Mode = adModeReadWrite
      .ConnectionString = "Provider=SQLOLEDB;server=[servername];database=[databasename]uid=[insertuser];pwd=[insertpassword];"
      .Open 
End With
If conn.State = adStateOpen Then
      WScript.Echo "Connection was established."
End If
conn.Close
Set conn = Nothing

通常有一种更紧凑的方法来做到这一点，但如果没有上下文，很难给出更好的例子