使用Classic ASP写入SQL数据库

I'm trying to pull some data down into my database. I use Microsoft Webmatrix for easy development, with SQL database setup and working.

我正在尝试将一些数据下载到我的数据库中。我使用Microsoft Webmatrix进行简单的开发，使用SQL数据库设置和工作。

** UPDATE ** Tags and variables was mixed - sorry - now i get this error:

**更新**标签和变量混合 - 抱歉 - 现在我收到此错误：

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'ConnectDB'

/writenote.asp, line 17 

Please don't look into security as this will run on a standalone unit with no internet access and only operator access.

请不要考虑安全性，因为它将在没有互联网访问且只有操作员访问的独立单元上运行。

Below is what i use for input to database.

下面是我用于输入数据库的内容。

"Connect.asp":

“Connect.asp”：

<% 
dim strConnect
strConnect = "Provider=SQLOLEDB;Data Source=(local);" & _
         "Database=HISTORICDATA;User ID=userid;Password=pw"
%>
<!-- METADATA TYPE="typelib"
 FILE="C:\Program Files (x86)\Common Files\System\ado\msado15.dll" -->

My form:

我的表格：

<form action="writenote.asp" method="post">
<fieldset>
<legend>New note for <% response.write(tagname) %></legend>

Time:<br />
<input type="datetime-local" name="timestamp" value="<% Response.Write Now %>" required><br />

Object:<br />
<input type="text" name="object" value="<% response.write(tagname) %>" required><br />

User:<br />
<input type="text" name="user" value="<% response.write(loginuser) %>" required><br />

Note:<br />
<textarea name="note" rows="10" cols="30" required></textarea>
<br /><br />

<input type="submit" value="Submit">
<input type="reset">
</fieldset>
</form>

Below here is the content of "writenote.asp":

下面是“writenote.asp”的内容：

<%

dim username,object,time,note,objConn,objs,query

username = Request.Form("user")
tag      = Request.Form("object")
time     = Request.Form("timestamp")
note     = Request.Form("note")

Set objConn = ConnectDB()
query       = "INSERT INTO notes (username,object,time,note) VALUES ('"& username &"','"& object &"','"& time &"','"& note &"')"
Set objs    = objConn.Execute(query)

Response.Redirect("notes.asp")
%>

Old problem:

老问题：

When i fill in my form it keeps saying:

当我填写表格时，它一直说：

![Http 500 error]https://cdn2.hubspot.net/hub/501326/file-2730129857-png/blog-files/01-sharepoint-2013-the-website-cannot-display-the-page-http-500-500x249.png?t=1482420346935

！[Http 500错误] https://cdn2.hubspot.net/hub/501326/file-2730129857-png/blog-files/01-sharepoint-2013-the-website-cannot-display-the-page-http- 500-500x249.pngΔT= 1482420346935

I can't make it insert any data in my database and it keeps coming with http 500 error.

我不能让它在我的数据库中插入任何数据，它不断出现http 500错误。

2 个解决方案

#1

In writenote.asp you can do like this

在writenote.asp中你可以这样做

    <%
    dim username,object,time,note,dbInsert,dbOpen

    Set connect = Server.CreateObject("ADODB.Connection")
    dbOpen = "PROVIDER=SQLOLEDB;Data Source=(local);UID=username;PWD=password;Database=HISTORICDATA "

    username = Request.Form("user")
    tag      = Request.Form("object")
    time     = Request.Form("timestamp")
    note     = Request.Form("note")

    connect.open(dbOpen)
    dbInsert       = "INSERT INTO notes (username,object,time,note) VALUES ('"& username &"','"& object &"','"& time &"','"& note &"')"
    connect.Execute(dbInsert)
    connect.Close
    Set Connect = Nothing

    Response.Redirect("notes.asp")
    %>

If you are doing db queries in lots of pages, Do as you have in your example. Put this in a 'connect.asp' or a config.asp':

如果您在许多页面中进行数据库查询，请按照示例中的操作进行操作。把它放在'connect.asp'或config.asp'中：

Set connect = Server.CreateObject("ADODB.Connection")
        dbOpen = "PROVIDER=SQLOLEDB;Data Source=(local);UID=username;PWD=password;Database=HISTORICDATA "

You can than include it into any page you are doing db queries with this code snippet:

您可以将其包含在使用此代码段进行数据库查询的任何页面中：

<!-- #include file="connect.asp" -->

I must be included outside the <% %>-tags.

我必须包含在<%%> - 标记之外。

And when doing queries use this syntax:

在进行查询时使用以下语法：

    connect.open(dbOpen)
    dbQuery = "put your query here" 
    connect.execute(dbQuery)    
    connect.Close
    Set Connect = Nothing

#2

I found a solution which worked. Thank you very much for your contribution and help.

我找到了一个有效的解决方案。非常感谢您的贡献和帮助。

In "Connect.asp" i connect to database and create recordset for which i use later on.

在“Connect.asp”中，我连接到数据库并创建我稍后使用的记录集。

The document "notes.asp" gets the info needed through a cookie coming from elsewhere. This data is handled same place and not shown as this contains info not for public display :)

文档“notes.asp”通过来自其他地方的cookie获取所需的信息。此数据处理相同的地方，但未显示，因为这包含不公开显示的信息:)

In the left section i create input for the SQL database. Most fields are readonly for the user as the input comes from the cookie and time. The notes for the same tagname is shown in the right sections textarea. This data can also be printed (in an unordered way).

在左侧部分，我为SQL数据库创建输入。对于用户来说，大多数字段都是只读的，因为输入来自cookie和时间。相同标记名的注释显示在textarea的右侧部分中。也可以打印此数据（以无序方式）。

I'll post my code below, but not CSS. The VBs might be unordered but works like a charm.

我将在下面发布我的代码，但不是CSS。 VB可能是无序的，但就像一个魅力。

Connect.asp:

Connect.asp：

<% 'include file
dim strConnect,objConn,objRecordSet

Set objConn = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConn.Open = "Provider=SQLOLEDB;Server=.\SQLEXPRESS;" & _
           "Database=HISTORICDATA;User ID=sa;Password=PW;"

%>
<!-- METADATA TYPE="typelib"
 FILE="C:\Program Files (x86)\Common Files\System\ado\msado15.dll" -->

From my "notes.asp":

从我的“notes.asp”：

<%@ Language="VBScript" %>
<!-- #include file="connect.asp" -->
<!-- Cookie handling -->
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <link rel="stylesheet" href="css/styles.css" type="text/css" />
        <!--[if lt IE 9]>
        <script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
        <![endif]-->
<script language="javascript">
<!--
function printTextArea(notetxtarea)
{
 var elementRef = document.getElementById(notetxtarea);
 var windowUrl = 'about:blank';
 var uniqueName = new Date();
 var windowName = 'Print' + uniqueName.getTime();
 var printWindow = window.open(windowUrl, windowName, 'left=50000,top=50000,width=0,height=0');

 printWindow.document.write(elementRef.value);
 printWindow.document.close();
 printWindow.focus();
 printWindow.print();
 printWindow.close();
}
// -->
</script>
        <title>Notes for <% response.write(tagname) %></title>
    </head>
    <body onload="window.resizeTo(1000,900)">

<header>
<h1>Notes for <% response.write(tagname) %></h1>
<p>For use is now:</p>

<%
response.write("Object: " & tagname)
response.write("<br />")
response.write("User: " & loginuser)
%>

</header>

<section>

<form action="writenote.asp" method="post">
    <fieldset>
        <legend>New note for <% response.write(tagname) %></legend>

<label for="tid">Time:</label>
<input type="datetime-local" id="tid" name="timestamp" value="<% Response.Write Now %>" readonly><br />

<label for="objekt">Object:</label>
<input type="text" id="objekt" name="object" value="<% response.write(tagname) %>" readonly><br />

<label for="bruger">User:</label>
<input type="text" id="bruger" name="user" value="<% response.write(loginuser) %>" readonly><br />

<p></p>Note (max. 300 chars):</p>
<textarea name="note" rows="11" cols="30" maxlength="300" required></textarea>
<br /><br />

<input type="submit" value="Submit">
<input type="reset">
        </fieldset>
</form>

</section>

<section>
<fieldset>
<legend>Notes for <% response.write(tagname) %></legend>
    <input type="button" value="Print Notes" onclick="JavaScript:printTextArea('notetxtarea');">
<textarea name="note" rows="23" cols="40" id="notetxtarea" readonly>
<%
dim strSQL,printStr
'Open the recordset object executing the SQL statement and return records
strSQL = "SELECT * FROM dbo.notes WHERE tag = '" & tagname & "' ORDER BY time DESC"

set objRecordSet = objConn.Execute (strSQL)

'first of all determine whether there are any records
If objRecordSet.EOF Then
Response.Write("No records of " & tagname)

Else

Do While NOT objRecordSet.EOF  

Response.write(objRecordSet("time") & vbCrLf)
Response.write(objRecordSet("tag") & vbCrLf)
Response.write(objRecordSet("username")& vbCrLf)
Response.write(objRecordSet("note")& vbCrLf)
Response.write(vbCrLf)
Response.write("-----------------------")
Response.write(vbCrLf)
objRecordSet.MoveNext
Loop
End If

objRecordSet.Close
Set objRecordSet=nothing
objConn.Close
Set objConn=nothing

%>
</textarea>


</fieldset>

</section>



<div class="clearer"></div>
<footer>
<p>Things in my footer</p> 
</footer>


</body>
</html>

From "writenote.asp":

来自“writenote.asp”：

<%@ Language="VBScript" %>
<!-- #include file="connect.asp" -->
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Execute write to file</title>
    </head>
    <body>
<%
dim username,tag,time,note,query,objs

username = Request.Form("user")
tag      = Request.Form("object")
time     = Request.Form("timestamp")
note     = Request.Form("note")


objConn.Execute = "INSERT INTO dbo.notes (tag,username,time,note) VALUES ('"+ tag +"','"+ username +"','"+ time +"','"+ note +"')"


Response.Redirect("notes.asp")

objRecordSet.Close
Set objRecordSet=nothing
objConn.Close
Set objConn=nothing

%>
    </body>
</html>