使用asp连接到SQL Server 2008数据库

I tried a lot but I am not able to deal with this problem. I wasted last 2 days without any significant result. Hope I will get some help here.

我尝试了很多，但我无法处理这个问题。我浪费了最近2天没有任何重大结果。希望我能在这里得到一些帮助。

I wanted to connect to a SQL Server 2008 database using ASP. I installed SQL Server 2008, created a database, but I am not able to connect to that database using the asp code. I can see the database in Visual Web Developer, I can also connect it through asp.net using the add connection wizard of visual web developer but I don't want to use add connection wizard. I want to write my asp code to connect to SQL Server database in notepad. My IIS is working and I am able to run asp code to access other database like ms access database but I can't access SQL Server db.

我想使用ASP连接到SQL Server 2008数据库。我安装了SQL Server 2008，创建了一个数据库，但我无法使用asp代码连接到该数据库。我可以在Visual Web Developer中看到数据库，我也可以使用可视化开发人员的添加连接向导通过asp.net连接它，但我不想使用添加连接向导。我想编写我的asp代码来连接到记事本中的SQL Server数据库。我的IIS正在工作，我能够运行asp代码来访问其他数据库，如ms访问数据库，但我无法访问SQL Server数据库。

The object explorer of SQL Server Management Studio shows:

SQL Server Management Studio的对象资源管理器显示：

localhost\SQLSERVER3(SQL Server 10.0.1600-RAJ-PC\raj)

Databases

数据库

examples
  tables
   System Tables
      dbo.cars
      columns
          id(PK,int,not null)
          name(varchar(50),null)

You can see the SQL Server and its databases in the attached jpg image. I want to connect to example database and then want to access cars table.

您可以在附加的jpg图像中看到SQL Server及其数据库。我想连接到示例数据库，然后想要访问汽车表。

Please help me.

请帮帮我。

UPDATED

更新

here is my code :

这是我的代码：

<html>
<head>
<title>Guestbook</title>
</head>

<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsGuestbook         'Holds the recordset for the records in the database
Dim strSQL          'Holds the SQL query for the database

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "ODBC;Driver={SQL Native Client};" & _
           "Server=localhost\SQLSERVER3;" & _
           "Database=examples;" & _
           "Uid=raj;" & _
           "Pwd=love1987"

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"

'Create an ADO recordset object
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT name FROM dbo.cars;"

'Open the recordset with the SQL query 
rsGuestbook.Open strSQL, adoCon

'Loop through the recordset
Do While not rsGuestbook.EOF
    'Write the HTML to display the current record in the recordset
    Response.Write ("<br>")
    Response.Write (rsGuestbook("Name"))
    'Response.Write ("<br>")
    'Response.Write (rsGuestbook("Comments"))
    'Response.Write ("<br>")

    'Move to the next record in the recordset
    rsGuestbook.MoveNext
Loop

'Reset server objects
rsGuestbook.Close

Set rsGuestbook = Nothing
Set adoCon = Nothing
%>

</body>
</html>

2 个解决方案

#1

I am able to connect using the following connection string on my local dev machine (with SQL 2008 R2 Express):

我可以使用本地开发机器上的以下连接字符串进行连接（使用SQL 2008 R2 Express）：

Driver={SQL Server}; Server=hostname\instancename; Database=dbname; Uid=user; Pwd=password

One thing I noticed in your code: you are trying to establish a DSN-less connection, then you run a query on it without USE dbname or anything. That may be the issue, or a least an issue.

我在您的代码中注意到的一件事：您正在尝试建立无DSN连接，然后在没有USE dbname或任何内容的情况下对其运行查询。这可能是问题，或者至少是一个问题。

#2

Try creating a DSN, and then refer to it by name in your connection string.

尝试创建DSN，然后在连接字符串中按名称引用它。

Open the ODBC icon in your Control Panel.
在控制面板中打开ODBC图标。
Choose the System DSN tab.
选择“系统DSN”选项卡。
Click on Add in the System DSN tab.
单击“系统DSN”选项卡中的“添加”。
Select the Microsoft Access Driver. Click Finish.
选择Microsoft Access驱动程序。单击完成。
In the next screen, click Select to locate the database.
在下一个屏幕中，单击“选择”以查找数据库。
Give the database a Data Source Name (DSN).
为数据库提供数据源名称（DSN）。
Click OK.

单击确定。

set conn=Server.CreateObject("ADODB.Connection")

set conn = Server.CreateObject（“ADODB.Connection”）

conn.Open "northwind"

conn.Open“northwind”

http://www.w3schools.com/ado/ado_connect.asp

#1