Problem Calling Stored Procedure

[Scott McNair]

Hi all,

I'm having problems calling a stored procedure from within my code. If I
execute the SP manually from a Query Analyzer window it runs without a
hitch, but it seems it doesn't even fire when I try to run it from my code.

Anyway, here's both the VB code and the stored procedure:

With cmdCleanUncheckedSuites
.Connection = cnSalesWiz
.CommandType = CommandType.StoredProcedure
.CommandText = "prCleanUncheckedSuites"
.Parameters.Add("@SuiteList", SqlDbType.VarChar)
.Parameters.Add("@PresentationID", SqlDbType.Int)
.Parameters.Add("@CheckString", SqlDbType.VarChar)
.Parameters("@SuiteList").Value = Replace("'" & SQLEncode
(Request.Form("SuiteList")) & "'", vbCrLf, ",")
.Parameters("@PresentationID").Value = CInt(Request.QueryString
("ID"))
.Parameters("@CheckString").Value = Replace("'" & SQLEncode
(Request.Form("SuiteID")) & "'", vbCrLf, ",")
'These next three lines run, so I know it's making it to here
Response.Write(.Parameters("@SuiteList").Value & "<br>")
Response.Write(.Parameters("@PresentationID").Value & "<br>")
Response.Write(.Parameters("@CheckString").Value & "<br>")
.ExecuteNonQuery()
End With


CREATE PROCEDURE prCleanUncheckedSuites (
@SuiteList varchar(200),
@PresentationID varchar(50),
@CheckString varchar(8000) = ''
) AS
DELETE FROM tblItems
WHERE SuiteID IN (
SELECT SuiteID
FROM MasterSuites
WHERE SuiteNum In (
SELECT LTRIM(RTRIM(Value))
FROM Common.dbo.split
(@SuiteList,',')
) AND
SuiteID NOT IN (
SELECT LTRIM(RTRIM(Value))
FROM Common.dbo.split
(@CheckString,',')
)
) AND
PresentationID = @PresentationID
GO

 

[Peter Proost]

I don't work a lot with stored procedures but maybe if you declare your
parameters like this, it helps.

Dim objParam as SqlParameter
objParam = new
SqlParamete("@name",sqldbtype.Varchar,50,Parameterdirection.Input)
objParam.value = "yourvalue"
cmdCleanUncheckedSuites.parameters.add(objParam)

objParam = new SqlParameter
_("@name2",sqldbtype.Varchar,50,Parameterdirection.Input)
objParam.value = "yourvalue"
cmdCleanUncheckedSuites.parameters.add(objParam)

hth

greetz Peter

 

[Chris Dunaway]

I don't know why your sproc is not running but I had a problem with a
sproc not apparently running and it turned out that there was an
exception generated and my code swallowed up the exception.

Is your code wrapped in a Try Catch? Or is there a try catch somewhere
that could be hit and the exception is not logged? Or if it is logged,
did you check wherever you log exceptions to see if there was one?
Just a thought

 

[JohnFol]

Scott, you could also put a SQL trace on the server (presuming this is SQL).
That will confirm the procedure is being called, and that he parameters and
their values are being correctly sent.

 

[chanmmn]

Perhaps you want to take a look of this again:

http://support.microsoft.com/default.aspx?scid=kb;en-us;308049

chanmm

 

[Ron Allen]

Scott,
In your parameter list you define @PresentationID as an SqlDbType.Int
but in the SPROC definition it is a varchar(50). This will cause an error
in the SPROC.

Ron Allen