CODEWITHSUNDEEP
databaseformsms-accessasp-classicvbscript
Traug
Traug

Reputation: 59

How to delete a specific record from an access database with 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:

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.

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

Upvotes: 1

Views: 2115

Answers (2)

Saic Siquot
Saic Siquot

Reputation: 6513

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.

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

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.

Upvotes: 0

HansUp
HansUp

Reputation: 97101

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.

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.

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"

Upvotes: 2

Related Questions