Passing Parameters to SP on SQL server 1

[moolie]

I have a stored proceedure on my SQL server called SP_DROP. Basically, it drops a table from the database. the SP requires a parameter containing the name of the table to drop. SP looks like this:

code:

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

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

GO

the Code in access looks like this:

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


I get an error "Item cannot be found in the corresponding ordinal." on the .Parameter(1) = deltable. If I comment out that line I get an error message stating the @tablename is an expected parameter.

Help.

 

[cmmrfrds]

I believe the parameters collection is Zero based not 1 based.

.Parameters(0) = delTable

 

[moolie]

I tried that as well - same error message.

 

[PHV]

Just a though (typed not tested):
.Parameters(0) = delTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[moolie]

Typed and tested, unfortunately.

here is the full code from the app:

Code:
Private Sub Command6_Click()

Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TestStride;Data Source=IT2"
cn.Open

Dim DropTable As ADODB.Command
Set DropTable = New ADODB.Command

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

If delTable = "" Then

MsgBox "Enter a table name to drop the table."

Else

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

End If

If cn.State = 1 Then
cn.Close
End If
Set cn = Nothing

End Sub

 

[PHV]

Again typed, not tested:
DropTable.ActiveConnection = cn
DropTable.CommandType = adCmdStoredProc
DropTable.CommandText = "SP_DROP"
DropTable.Execute , Array(delTable)
MsgBox "Table Dropped"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[moolie]

DropTable.Execute , Array(delTable)


Killer - Did the trick.

2 days and 2 forums later.

Star for you!