如何使用asp从访问数据库中删除特定的记录?

So I have a page called BrowseAllItems.asp which displays all my database information along with individual edit buttons that redirect to EditItem.asp (partially shown below) for the option of either editing or deleting the database info for that specific record. The part of editing the data works fine, but I could use some help with deleting the record. Here is what I have:

我有一个页面叫做，browseallitem。asp显示了我所有的数据库信息和个人编辑按钮，重定向到EditItem。用于选择编辑或删除该特定记录的数据库信息。编辑数据的部分工作得很好，但是我可以使用一些帮助来删除记录。以下是我的资料:

Sub DeleteRecord

ItemCode=request.form("item_code")


'Create an ADO connection object
Dim Connection
Set Connection = Server.CreateObject("ADODB.Connection")

'To open an Access database our string would look like the following:
Dim ConnectionString
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
                "DBQ=" & Server.MapPath("\") & "\" & FolderName & "\items.mdb;DefaultDir=;UID=;PWD=;"

'Then to open the database we (optionally) set some of the properties of the Connection and call Open
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Open ConnectionString

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

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM items WHERE ITEM_CODE='" & ItemCode & "' "

I know it has to do with the above line -- This code works if I replace "ItemCode" with the actual item code for the record but I need a way to take the item code from the selected record and apply it where it says ItemCode.

我知道这与上面的代码有关——如果我将“ItemCode”替换为记录的实际项目代码，那么这段代码就可以工作，但我需要一种方法，从选中的记录中获取项目代码，并将其应用到它所指定的ItemCode中。

rs.LockType = 3
'Open the recordset with the SQL query
rs.Open strSQL, Connection
'Tell the recordset we are deleting a record 
rs.Delete 

rs.Close
'Reset server objects
Set rs = Nothing
Set Connection = Nothing

Response.Write "Record Deleted"

End Sub

2 个解决方案

#1

Perhaps I misunderstood something, but it looks like an ADODB.Command with a parameter query could be useful here. I don't see why you should need a recordset to delete a record with a given ITEM_CODE.

也许我误解了什么，但它看起来像一个ADODB。使用参数查询的命令在这里可能很有用。我不明白为什么需要一个记录集来删除一个带有给定ITEM_CODE的记录。

It seems you already have a useable ADODB.Connection. In this example, I used cnn instead of Connection as the name of the object variable.

看来你已经有了一个可用的ad . connection。在本例中，我使用cnn而不是连接作为对象变量的名称。

Dim cmd ' As ADODB.Command
Dim lngRecordsAffected ' As Long
strSQL = "PARAMETERS which_item Text(255);" & vbCrLf & _
    "DELETE FROM items WHERE ITEM_CODE = [which_item];"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandType = 1 ' adCmdText
Set cmd.ActiveConnection = cnn
cmd.CommandText = strSQL
'cmd.Parameters.Append cmd.CreateParameter("which_item", _
'    adVarChar, adParamInput, 255, ItemCode)
cmd.Parameters.Append cmd.CreateParameter("which_item", _
    200, 1, 255, ItemCode)
cmd.Execute lngRecordsAffected
Set cmd = Nothing
' lngRecordsAffected is the number of records deleted in case you 
' need it for anything ... perhaps you'd like to do this ...
Response.Write lngRecordsAffected & " Record(s) Deleted"

#2

Seems to me that you are loosing "ItemCode" value. Do you post your request? request.form relates to post actions. On the other hand, request.querystring recieves data from the querystring (a get action), and request("ItemCode") catches both cases.

在我看来，你正在失去“ItemCode”的价值。你把你的要求贴出来了吗?请求。表单与post操作相关。另一方面，请求。querystring从querystring (get操作)接收数据，request(“ItemCode”)捕获这两种情况。

in any case you must sanitize!!! before a concatenation in order to prevent SQL injection, in your case ausuming that ItemCode is an integer you can use cint like this

无论如何你必须消毒!!为了防止SQL注入，在进行连接之前，在您的示例中，项目代码是一个整数，您可以像这样使用cint

ItemCode = cint(request("item_code"))

I guess your query is not throwing errors because the presence of the single quotes, which with the null value of "ItemCode" gives a valid SQL sentence that returns no data. While debugging, you can always response.write strSQL previous to execute (or open) the sentence.

我猜您的查询不会抛出错误，因为存在单引号，而单引号的空值为“ItemCode”，这将给出一个有效的SQL语句，该语句不返回任何数据。在调试时，您始终可以响应。在执行(或打开)句子之前编写strSQL。

#1