Answered by:
Cannot delete db row classic asp application IIS7 Sql Server 2008 (can add, edit, list)

Question
-
User515267028 posted
Hello,
I am having trouble deleting a record in a 2008 SQL server database from a .asp page (classic asp). From my web based application, I can connect to the DB, list records, add records, and edit records, but can't DELETE any records.
I suspect it's a "permission" type of issue, but I can't for the life of me figure this out. Using a System DSN to connect. Reproduce this behavior using multiple SQL server accounts setup & have attempted to assign appropriate permissions.
I can delete records from MS-Management Studio when I login as administrator.
Any thoughts?
Thanks
Tuesday, December 29, 2009 7:46 PM
Answers
-
User989702501 posted
Mmm... well that's what the sql provider error msgs.
Can you try response.write sql and the copy out the syntax and run it directly in query analyzer. if it's works, then something really fishy from the provider end.- Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
Thursday, December 31, 2009 2:46 AM -
User744767459 posted
Hi,
It is most possible that this issue caused by sql provider. You can follow qbernard's suggestion to isolate this issue. Also, you can simply remove the "*" from the sql query statement to see if it works.
- Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
Sunday, January 3, 2010 11:04 PM -
User515267028 posted
Hi, thanks so much for the suggestions.
Removing the * from the SQL did the trick.
Works:
strSQL = "DELETE from tbl_some_table WHERE some_id=" & varSomeId & ";"
Doesn't work:
strSQL = "DELETE * from tbl_some_table WHERE some_id=" & varSomeId & ";"
Is this the common convention for DELETE w/ SQL Server 2008? (no * used)? I'm moving from Access to SQL server (classic ASP application). Appreciate your help.
- Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
Wednesday, January 6, 2010 8:17 PM
All replies
-
User989702501 posted
Well, what's the error msgs when you delete from ASP?
Tuesday, December 29, 2009 9:05 PM -
User515267028 posted
The catch all 500 error... unless of course I add "ON ERROR RESUME NEXT" (and nothing essentually happens). Also get the same behavior using a DSN Less connection string. I've uninstalled & reinstalled SQL server. No luck using the sa account either(cannot delete, but can add, edit, & list records).
Thanks.
Tuesday, December 29, 2009 9:13 PM -
User989702501 posted
Disable IE friendly error msgs, take out resume next first. then post the error msgs here.
Tuesday, December 29, 2009 9:35 PM -
User515267028 posted
Per website logs get the following error:
auto_id=1 (the record I'm trying to delete) and then:
|20|8004e14|incorrect_syntax_near_'*'
*Same "delete_record.asp" code works fine w/ an Access Database with a different website running on the same server (only difference is the connections between Access & SQL).
W/ debugging for website set to true get the following:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near '*'.
line 30...
Tuesday, December 29, 2009 9:35 PM -
User989702501 posted
So the syntax is not accepted here in this setup as Access don't recognize the query. change it and test.
Tuesday, December 29, 2009 9:40 PM -
User515267028 posted
Thanks for all your suggestions.
The exact same delete_record.asp code works fine on an Access Database.
I encounter the problem using the same code with SQL Server 2008 (of course changed the connection string).
Here's my delete sub_procedure (if you wouldn't mind looking at)
sub delete_record
dim objCmd, strSQL
' Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")
' Build the SQL string
strSQL = "DELETE * from tbl_contact WHERE auto_id=" & varAutoId & ";"
' Set the command object properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strSQL
objCmd.CommandType = adCmdText
' Execute the command
objCmd.Execute <----------This is the line that gives the error
' Close & dereference
Set objCmd = Nothing
end subcall delete_record
I don't understand why this exact code works on the Access Database & not the SQL Server (presuming the connection string is correct for the SQL Server)...
Tuesday, December 29, 2009 9:47 PM -
User989702501 posted
Mm... i don't know why it could be SQL got picky on the query :)
Try - strSQL = "DELETE * from tbl_contact WHERE auto_id='" & varAutoId & "'"
A single quote before and after the variable.Tuesday, December 29, 2009 10:17 PM -
User515267028 posted
Tried the single quote before and after the variable. Still same error message.
Thanks again for your assistance. I will be very happy if this is as simple as a syntax/bad_user/me_error.
Still getting the same error and I've tried a different flavor of the the delete code:
Dim Conn
ERROR below:
Dim Rs
Dim sql
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "Provider=SQLOLEDB; Data Source=2008server; Initial Catalog=database_name; User Id=user_id; Password=password_id"
sql= "Delete * FROM tbl_contact WHERE auto_id='" & varAutoId & "';"
Rs.Open sql, Conn
Conn.Close
Set Conn = NothingMicrosoft OLE DB Provider for SQL Server
error '80040e14'Incorrect syntax near '*'.
Can edit the record, add new records, and list records without a problem. Something about the delete just isn't working.
Tuesday, December 29, 2009 10:24 PM -
User989702501 posted
Mmm... well that's what the sql provider error msgs.
Can you try response.write sql and the copy out the syntax and run it directly in query analyzer. if it's works, then something really fishy from the provider end.- Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
Thursday, December 31, 2009 2:46 AM -
User744767459 posted
Hi,
It is most possible that this issue caused by sql provider. You can follow qbernard's suggestion to isolate this issue. Also, you can simply remove the "*" from the sql query statement to see if it works.
- Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
Sunday, January 3, 2010 11:04 PM -
User515267028 posted
Hi, thanks so much for the suggestions.
Removing the * from the SQL did the trick.
Works:
strSQL = "DELETE from tbl_some_table WHERE some_id=" & varSomeId & ";"
Doesn't work:
strSQL = "DELETE * from tbl_some_table WHERE some_id=" & varSomeId & ";"
Is this the common convention for DELETE w/ SQL Server 2008? (no * used)? I'm moving from Access to SQL server (classic ASP application). Appreciate your help.
- Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
Wednesday, January 6, 2010 8:17 PM -
User989702501 posted
ha! didn't notice that. I checked back my sql2k statement, all delete statement is just 'delete from' and I think this is the correct syntax.
Thursday, January 7, 2010 1:50 AM