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

Answer 1

User989702501 posted

Well, what's the error msgs when you delete from ASP?

Answer 2

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.

Answer 3

User989702501 posted

Disable IE friendly error msgs, take out resume next first. then post the error msgs here.

Answer 4

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...

 

 

Answer 5

User989702501 posted

So the syntax is not accepted here in this setup as Access don't recognize the query. change it and test.

Answer 6

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 sub

call 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)...

 

Answer 7

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.

Answer 8

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
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 = Nothing

ERROR below:

Microsoft 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.

Answer 9

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.

Answer 10

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.

Answer 11

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.

Answer 12

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.