• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Cannot delete from SQL Server database

I'm trying to delete entries from my SQL Server database but I get the error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near '*'.
/AcSec/delete.asp, line 19

I am using classic ASP. Please see my 'delete.asp' attached.
I'm doing it like this:
<a href="Update.asp?key=<%= rs("ID")%>">Delete</a>
This method worked fine when I was using MS Access database, but since I switched to SQL Server it doesn't work. Would you please let me know how to change the code in the 'delete.asp' in order to be able to delete entries? Thank you very much for your help.
<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim strSQL              'Holds the SQL query for the database
Dim lngRecordNo         'Holds the record number to be deleted

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

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=sqloledb;SERVER=DASTVMSQLCX01;DATABASE=AcSec;UID=AcSecUser;PWD=acsec102611;"

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "DELETE * FROM Results WHERE ID=" & lngRecordNo

adoCon.Execute strSQL

'Reset server objects
adoCon.Close
Set adoCon = Nothing

'Return to the delete select page incase another record needs deleting
Response.Redirect "All.asp"
Response.End
%>

Open in new window

0
baxtalo
Asked:
baxtalo
1 Solution
 
lcohanDatabase AnalystCommented:
SQL Server syntax is:

DELETE  FROM Results WHERE ID=" ...

and not

DELETE * FROM Results WHERE ID="
0
 
baxtaloAuthor Commented:
Thank you very much, perfect solution.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now