Hello, and thanks in advance for any help that you may provide.

I am fairly new to ASP (I've been doing it for about 4 months off and on). I have an ASP page (deleteselect.asp) that is set up to query an Access database, list the records in the table, and then create a link for each record that calls on another asp page (deletepublication.asp) and deletes the record from the database using the primary key (ID) field.

I got this exact same code to work for another page to delete users from the database. I copied the code exactly to create the delete publication page but then I started getting the error (the delete user page still works, just not the deletepublication page). Basically I have 2 tables in the dB, one called "users" and one called "publications", so I just changed the code accordingly and started getting the error.

This sounds quite complicated, but basically I can't figure out why one works, and the exact same code for another page doesn't work and generates this error:

"Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8.
/admin/deletepublication.asp, line 29"

Line 29 of my code (from deletepublication.asp) is : "rsDeleteEntry2.Delete"


Here is all of my code:

"deleteselect.asp" (this is used to create the list and delete link - works fine until i click on the delete link that is
generated, that's when i get the error).
<%
'Dimension variables
Dim adoCon2 'Holds the Database Connection Object
Dim rsPublications 'Holds the recordset for the records in the database
Dim strSQL2 'Holds the SQL query for the database

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

'Set an active connection to the Connection object using a DSN-less connection
adoCon2.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=MY CONNECTION LOCATION (OBVIOUSLY THE REAL CODE DOESN"T SAY THIS)"

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

'Initialise the strSQL variable with an SQL statement to query the database
strSQL2 = "SELECT * FROM publications ORDER BY Authors ASC;"

'Open the recordset with the SQL query
rsPublications.Open strSQL2, adoCon2

Response.Write ("<table width='100%' border='1' cellpadding='3' cellspacing='0' bordercolor='#CBCBBD'>")
Response.Write ("<tr>")
Response.Write ("<td colspan='2' class='tableBanner'>Delete a Publication: <font color='red'> WARNING, This will permanently

delete the publication</font></td>")
Response.Write ("</tr><tr>")
Response.Write ("<td colspan='2' bgcolor='#E6E6DC'><strong>To delete a record, click on the delete link to the left of the

publication</strong></td>")
Response.Write ("</tr><tr>")
Response.Write ("<td width='10%'>")
Response.Write ("<td width='90%'>")
Response.Write ("</tr>")

'Loop through the recordset
Do While not rsPublications.EOF
'Write the HTML to display the current record in the recordset
Response.Write ("<tr>")
Response.Write ("<td>")
Response.Write ("<a href=""deletepublication.asp?ID=" & rsPublications("ID") & """>")
Response.Write ("Delete</a>")
Response.Write ("</td>")
Response.Write ("<td>")
Response.Write (rsPublications("Authors"))
Response.Write (". ")
Response.Write (rsPublications("Year"))
Response.Write (". ")
Response.Write (rsPublications("Title"))
Response.Write (". ")
Response.Write (rsPublications("Publication"))
Response.Write (". ")
Response.Write (rsPublications("Description"))
Response.Write ("</td>")
Response.Write ("</tr>")

'Move to the next record in the recordset
rsPublications.MoveNext

Loop

Response.Write ("</table>")

'Reset server objects
rsPublications.Close
Set rsPublications = Nothing
Set adoCon2 = Nothing
%>


"deletepublication.asp" (the code that actually deletes the record from the dB)
<% 'Dimension variables
Dim adoCon2 'Holds the Database Connection Object
Dim rsDeleteEntry2 'Holds the recordset for the record to be deleted
Dim strSQL2 'Holds the SQL query to query the database
Dim lngRecordNo2 'Holds the record number to be deleted

'Read in the record number to be deleted
lngRecordNo2 = CLng(Request.QueryString("ID"))

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

'Set an active connection to the Connection object using a DSN-less connection
adoCon2.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=MY CONNECTION LOCATION"

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

'Initialise the strSQL variable with an SQL statement to query the database
strSQL2 = "SELECT * FROM publications WHERE ID=" & lngRecordNo2

'Set the lock type so that the record is locked by ADO when it is deleted
rsDeleteEntry2.LockType = 3

'Open the recordset with the SQL query
rsDeleteEntry2.Open strSQL2, adoCon2

'Delete the record from the database
rsDeleteEntry2.Delete

'Reset server objects
rsDeleteEntry2.Close
Set rsDeleteEntry2 = Nothing
Set adoCon2 = Nothing

'Return to the delete select page in case another record needs deleting
Response.Redirect "index.asp"
%>

I changed the DBQ to "MY CONNECTION LOCATION" on purpose so please don't mention that.

I read that this type of error usually means a syntax error, that one of the field names or fields called in the code is misspelled, but i have gone and checked each one. The only field called in the deletepublication.asp is "ID" and that's exactly how it is in my Access database ("ID").

If you need any other information please just let me know. Thank you very much.

Nikbone

I'm not sure why you are getting the error, but why doing you just delete the record using straight sql?

Instead of selecting and then deleting just delete straight away by changing the select * from blah blah to delete from blah blah...?

Sounds like you may have an issue with the LockType. Try 2 instead of 3.

rokslide is right though, you'll have better performance by doing:

strSQL2 = "DELETE FROM publications WHERE ID=" & lngRecordNo2

Thank you very much for your help. I changed it to from the select to just delete in the SQL statement, and also deleted the line "rsDeleteEntry2.Close" and finally got it to work.

Thanks again!

Nikbone

I love it when a plan comes together...

<insert A-Team theme music here>

Lol, thats funny.