Stored Proceedure Question

[moolie]

I am trying to create a stored proceedure that drops a table from the database.

code

CREATE PROCEDURE SP_DROP
@InputTable as varchar(50)

AS

DROP TABLE InputTable
GO

seems straight forward. I get an error message when I try to pass the parameter to the SP. "Item cannot be found in the corresponding ordinal."

the code in VBA looks like:

Code:

Dim delTable As String
delTable = InputBox("Enter table to delete from the database", "Delete Table")

With DropTable

.ActiveConnection = cn
.CommandText = "SP_DROP"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters(1) = delTable
.Execute
MsgBox "Table Dropped"

End With


error and break on .Parameters(1) = delTable

breaks even if I hard code the table name as well. Any suggestions?

 

[SQLSister]

CREATE PROCEDURE usp_test2 (@tablename varchar(50))
AS

DECLARE @InsertString NVARCHAR(500)
Set @InsertString = 'drop Table ' + @TAbleNAme
execute sp_executesql @InsertString

GO

Questions about posting. See faq183-874

 

[moolie]

Edited the SP and I still am getting the same error message on the .parameter(1) = delTable line of VBA code.

Although, the SP now runs well on the server, I just need to be able to pass the parameter.

 

[maswien]

Use SQL Sister's SP, then try following in ASP code.

.Parameters(0) = delTable

 

[moolie]

Same. It seems no matter what I try the parameter will not go. I'm using the exact code for the SP, maybe I'm missing something in VBA to launch the SP?

Stumped

 

[SQLSister]

You could try asking inthe vba forum. Maybe they will see it better than we will.

Questions about posting. See faq183-874