locked
Cannot delete db row classic asp application IIS7 Sql Server 2008 (can add, edit, list) RRS feed

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

     

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

    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