asp连SqlServer数据库示例

原创

Windows技术交流

修改于 2023-10-10 17:46:40

1.9K1

文章被收录于专栏：Windows技术交流Windows技术交流

conn.asp代码示例

DATABASE=TutorialDB，TutorialDB是你的数据库名

'打头是注释

SERVER=127.0.0.1,1433比SERVER=127.0.0.1兼容性高

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%Response.Buffer = True
On Error Resume Next
'i = 1/0

servername="127.0.0.1"
user="sa"
pw="你的密码"
set conn=server.CreateObject("adodb.connection")

'conn.open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER="&servername&";UID="&user&";PWD="&pw&";DATABASE=TutorialDB"
conn.open "DRIVER={SQL Server};SERVER="&servername&";UID="&user&";PWD="&pw&";DATABASE=TutorialDB"
Response.Write "Success!"

If Err.Number <> 0 Then
Response.Clear
Response.Write "Error:"
Response.Write Err.Description

%>

<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<br/>
<%= Err.Source %><br/>
Err.Number:<%= Err.Number %><br/>

</BODY>
</HTML>
<%End If%>

或者

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%Response.Buffer = True
On Error Resume Next
'i = 1/0

set conn=server.createobject("adodb.connection")
'conn.connectionstring="PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=127.0.0.1;UID=sa;PWD=你的密码;DATABASE=TutorialDB;"
'conn.open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=127.0.0.1,1433;UID=sa;PWD=你的密码;DATABASE=TutorialDB;"
conn.open "DRIVER={SQL Server};SERVER=127.0.0.1,1433;UID=sa;PWD=你的密码;DATABASE=TutorialDB;"
Response.Write "Success!"

If Err.Number <> 0 Then
Response.Clear
Response.Write "Error:"
Response.Write Err.Description
%>

<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<br/>
<%= Err.Source %><br/>
Err.Number:<%= Err.Number %><br/>

</BODY>
</HTML>
<%End If%>

连上了就报Success!

没连上就报错如下

比如创建数据库的时候用了如下SQL语句

https://learn.microsoft.com/zh-cn/sql/azure-data-studio/quickstart-sql-server?view=sql-server-ver15

创建数据库
USE master;
GO

IF NOT EXISTS (
      SELECT name
      FROM sys.databases
      WHERE name = N'test_db'
      )
   CREATE DATABASE [test_db];
GO

IF SERVERPROPERTY('ProductVersion') > '12'
   ALTER DATABASE [test_db] SET QUERY_STORE = ON;
GO


创建表
-- Create a new table called 'Customers' in schema 'dbo'
-- Drop the table if it already exists
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
   DROP TABLE dbo.Customers;
GO

-- Create the table in the specified schema
CREATE TABLE dbo.Customers (
   CustomerId INT NOT NULL PRIMARY KEY, -- primary key column
   [Name] NVARCHAR(50) NOT NULL,
   [Location] NVARCHAR(50) NOT NULL,
   [Email] NVARCHAR(50) NOT NULL
   );
GO

插入数据
-- Insert rows into table 'Customers'
INSERT INTO dbo.Customers (
   [CustomerId],
   [Name],
   [Location],
   [Email]
)
VALUES
   (1, N'Orlando', N'Australia', N''),
   (2, N'Keith', N'India', N'keith0@adventure-works.com'),
   (3, N'Donna', N'Germany', N'donna0@adventure-works.com'),
   (4, N'Janet', N'United States', N'janet1@adventure-works.com')
GO



查询
-- Select rows from table 'Customers'
USE master;
SELECT * FROM dbo.Customers;

是按上面的SQL语句创建数据库、建立表、插入数据的，现在就想通过.asp把SqlServer里插入的数据读出来，可是总是报

Error executing query: [Microsoft][ODBC SQL Server Driver][SQL Server]对象名 'dbo.Customers' 无效。 (Error Number: -2147217865)

有的资料提到是命名实例和默认实例的区别，但我试了，并非这个导致。

我确实是命名实例MSSQLSERVER_M，但我试了，这2句都可以

connectionString = "DRIVER={SQL Server};SERVER=10.255.5.4\MSSQLSERVER_M,1433;UID=sa;PWD=myPassword;DATABASE=test_db;"
connectionString = "DRIVER={SQL Server};SERVER=10.255.5.4,1433;UID=sa;PWD=myPassword;DATABASE=test_db;"

真正有问题的是查询语句，由于创建的时候使用了USE master;在查询时也得加它，并非要在dbo.Customers前加数据库名变成sql = "SELECT * FROM test_db.dbo.Customers;"，这是不对的

正确的是把sql = "SELECT * FROM dbo.Customers;"改成sql = "USE master;SELECT * FROM dbo.Customers;"

接下来我给出完整的.asp代码

<!DOCTYPE html>
<html>
<head>
    <title>SQL Server Query Example</title>
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th, td {
            border: 1px solid black;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
    </style>
</head>
<body>
    <h1>Customers</h1>
    <%
    On Error Resume Next

    Set conn = Server.CreateObject("ADODB.Connection")
   ' connectionString = "DRIVER={SQL Server};SERVER=10.255.5.4\MSSQLSERVER_M,1433;UID=sa;PWD=QYv_lr6dx6cj;DATABASE=test_db;"
    connectionString = "DRIVER={SQL Server};SERVER=10.255.5.4,1433;UID=sa;PWD=myPassword;DATABASE=test_db;"
    conn.Open connectionString

    If Err.Number <> 0 Then
        Response.Write "Error opening connection: " & Err.Description & " (Error Number: " & Err.Number & ")"
        Err.Clear
    Else
        sql = "USE master;SELECT * FROM dbo.Customers;"
        Set rs = conn.Execute(sql)

        If Err.Number <> 0 Then
            Response.Write "Error executing query: " & Err.Description & " (Error Number: " & Err.Number & ")"
            Err.Clear
        Else
            Response.Write "<table>"

            ' 输出表头
            Response.Write "<tr>"
            For Each field In rs.Fields
                Response.Write "<th>" & field.Name & "</th>"
            Next
            Response.Write "</tr>"

            ' 输出数据
            While Not rs.EOF
                Response.Write "<tr>"
                For Each field In rs.Fields
                    Response.Write "<td>" & field.Value & "</td>"
                Next
                Response.Write "</tr>"
                rs.MoveNext
            Wend

            Response.Write "</table>"
        End If

        rs.Close
        conn.Close
    End If
    %>
</body>

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云服务器