Problem Calling Stored Procedure

  • Thread starter Thread starter Scott McNair
  • Start date Start date
S

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
 
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
 
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
 
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.
 
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
 
Back
Top