Hello all, I just joined, and (is often the case) I have a problem perhaps someone could give me a pointer towards fixing. I've tried to keep this simple...
I have a stored procedure that has been working fine for years, but won't work on a different server, under certain circumstances. After pulling my hair out for a considerable amount of time I went back to basics and created a single, standalone page that called the SP.
Now, I get the same error everywhere and can't get this page to work at all. Perhaps someone can suggest what I'm doing wrong?
The error is:
Code:
Microsoft OLE DB Provider for SQL Server error '80040e10'
Procedure or function 'sp_CategorySearch' expects parameter '@keywords', which was not supplied.
sp_test.asp, line 20
This is the top of the stored procedure:
Code:
USE [MY_DATABASE]
GO
/****** Object: StoredProcedure [dbo].[sp_CategorySearch] Script Date: 10/28/2009 15:20:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_CategorySearch] (
@keywords varchar(255),
@dbfields varchar(255),
@withinCategoryID int
) AS...etc
And this is the ASP calling page that throws the error:
Code:
<%
oDB = Null
db_username = "myusername"
db_pass = "mypassword"
db_server = "mydbserver"
db_network = "dbmssocn"
adOpenStatic = 3
Call db_connect(oDB, "MY_DATABASE")
Set cm = Server.CreateObject("ADODB.Command")
cm.ActiveConnection = oDB
cm.CommandText = "sp_CategorySearch"
cm.Parameters.Append cm.CreateParameter("@keywords", 129, &H0001, 1000) ' 129 = adChar (string), &H0001 = adParamInput
cm.Parameters.Append cm.CreateParameter("@dbfields", 129, &H0001, 255)
cm.Parameters.Append cm.CreateParameter("@withinCategoryID", 3, &H0001) ' 3 = adInteger (int)
cm.Parameters("@keywords") = "water"
cm.Parameters("@dbfields") = "CName"
cm.Parameters("@withinCategoryID") = 0
Set rsC = cm.Execute
db_disconnect(oDB)
%>
Where the db_connect() and dbdisconnect() are simple functions to open and close the connection.
Anyone any ideas about why this SP complains?! I'm willing to try just about anything!
