Drop table error in C# sqldatasource

[hx009]

Any C# / SQL Server experts on here? I have a rather unusual problem that Google hasn't been of much help on. I have a web app that maintains it's own tables (CREATE/DROP) in a SQL Express 2005 database. All interaction is done through stored procedures. The problem is that the stored procedure that does a "DROP TABLE <thetable>" blows up in the web interface with the following exception thrown on the web page:

Cannot drop the table 'thetable', because it does not exist or you do not have permission.

Simple problem, right? Wrong. The table in fact did get dropped. For debugging purposes, I've been running as the SA user. Also, if I run the uspDropUserTable procedure from within management studio as the SA user, the procedure runs flawlessly with no errors. The only thing I can figure is that there is some weirdness in SqlDataSources or GridViews that I'm not aware of for doing deletes. Here is the basic relevant page code being used:

<asp:GridView
ID="DocTypesGridView"
AutoGenerateColumns="false"
DataSourceID="SqlDataSource1"
DataKeyNames="TableName"
runat="server">
<Columns>
<asp:ButtonField ButtonType="Image" CommandName="Delete" ImageUrl="~/Images/delete.png" Text="Delete" />
<asp:TemplateField HeaderText="Document Type">
<ItemTemplate>
<%# Eval("FriendlyName")%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>


<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:AppConnectionString %>"
SelectCommand="uspGetUserTableList"
DeleteCommandType="StoredProcedure"
DeleteCommand="uspDropUserTable"
/>


and the stored procedure:

CREATE PROCEDURE [dbo].[uspDropUserTable]
@TableName VARCHAR(250)
AS

SET NOCOUNT ON

EXECUTE('DROP TABLE [' + @TableName + ']')

 

[dishawbr]

The spr might be getting executed 2x.. do you have auto event wireup enabled maybe?

 

[UCJefe]

The DeleteCommand gets executed for every ROW that needs to be deleted and not once per table. If you 2 rows that are marked as deleted (with the same tablename), the sp will be executed twice and will bomb the second time. Chances are very likely that your sp is being executed twice with the same tablename. I know that you are probably expecting every row to have a distinct TableName but maybe they don't.

Your error message a very specific one and it almost certaintly not lying to you. Either you are trying to drop a table that doesn't exist or you don't have permission. Since you say you're using SA, the table really must not exist.

And I won't even get into exposing a DROP TABLE command through the web that takes in a table name. A user could drop any table in your DB with that page. Hope they're not important.

 

[hx009]

Originally posted by: UCJefe
And I won't even get into exposing a DROP TABLE command through the web that takes in a table name. A user could drop any table in your DB with that page. Hope they're not important.

I don't have a choice on that. This is an add-on to an app I didn't write that was written in Delphi. Unfortunately, it manages it's categories by creating and deleting new tables rather than a properly normalized schema.

In any case, I'm doing what I can to secure it: It's being done in a stored proc, checking that the user has rights to drop the table, and that the table is not a system or app critical table before doing so.

 

[hx009]

Originally posted by: dishawbr
The spr might be getting executed 2x.. do you have auto event wireup enabled maybe?

That's a good point. I forgot to check that.

 

[GoatMonkey]

If those above suggestions don't work. I would not want to do this as just a bound control. You should have a codebehind that you can step through and put in a try...catch, to see exactly what is happening.

 

[hx009]

Originally posted by: GoatMonkey
If those above suggestions don't work. I would not want to do this as just a bound control. You should have a codebehind that you can step through and put in a try...catch, to see exactly what is happening.

I haven't had a chance to get back to it, but I'm fairly sure the double execute was happening. For a quick fix I wrapped the drop like:

TRY BEGIN
EXECUTE('DROP TABLE [' + @TableName + ']')
TRY END
CATCH BEGIN
CATCH END

The drop happens without a hitch now.