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 cmdCleanUncheck edSuites
.Connection = cnSalesWiz
.CommandType = CommandType.Sto redProcedure
.CommandText = "prCleanUncheck edSuites"
.Parameters.Add ("@SuiteList ", SqlDbType.VarCh ar)
.Parameters.Add ("@Presentation ID", SqlDbType.Int)
.Parameters.Add ("@CheckString" , SqlDbType.VarCh ar)
.Parameters("@S uiteList").Valu e = Replace("'" & SQLEncode
(Request.Form(" SuiteList")) & "'", vbCrLf, ",")
.Parameters("@P resentationID") .Value = CInt(Request.Qu eryString
("ID"))
.Parameters("@C heckString").Va lue = Replace("'" & SQLEncode
(Request.Form(" SuiteID")) & "'", vbCrLf, ",")
'These next three lines run, so I know it's making it to here
Response.Write( .Parameters("@S uiteList").Valu e & "<br>")
Response.Write( .Parameters("@P resentationID") .Value & "<br>")
Response.Write( .Parameters("@C heckString").Va lue & "<br>")
.ExecuteNonQuer y()
End With
CREATE PROCEDURE prCleanUnchecke dSuites (
@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(Val ue))
FROM Common.dbo.spli t
(@SuiteList,',' )
) AND
SuiteID NOT IN (
SELECT LTRIM(RTRIM(Val ue))
FROM Common.dbo.spli t
(@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("@n ame",sqldbtype. Varchar,50,Para meterdirection. Input)
objParam.value = "yourvalue"
cmdCleanUncheck edSuites.parame ters.add(objPar am)

objParam = new SqlParameter
_("@name2",sqld btype.Varchar,5 0,Parameterdire ction.Input)
objParam.value = "yourvalue"
cmdCleanUncheck edSuites.parame ters.add(objPar am)

hth

greetz Peter
"Scott McNair" <sc**********@s fmco.takethispa rtout.com> wrote in message
news:Xn******** ***********@207 .46.248.16...

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 cmdCleanUncheck edSuites
.Connection = cnSalesWiz
.CommandType = CommandType.Sto redProcedure
.CommandText = "prCleanUncheck edSuites"
.Parameters.Add ("@SuiteList ", SqlDbType.VarCh ar)
.Parameters.Add ("@Presentation ID", SqlDbType.Int)
.Parameters.Add ("@CheckString" , SqlDbType.VarCh ar)
.Parameters("@S uiteList").Valu e = Replace("'" & SQLEncode
(Request.Form(" SuiteList")) & "'", vbCrLf, ",")
.Parameters("@P resentationID") .Value = CInt(Request.Qu eryString ("ID"))
.Parameters("@C heckString").Va lue = Replace("'" & SQLEncode
(Request.Form(" SuiteID")) & "'", vbCrLf, ",")
'These next three lines run, so I know it's making it to here
Response.Write( .Parameters("@S uiteList").Valu e & "<br>")
Response.Write( .Parameters("@P resentationID") .Value & "<br>")
Response.Write( .Parameters("@C heckString").Va lue & "<br>")
.ExecuteNonQuer y()
End With
CREATE PROCEDURE prCleanUnchecke dSuites (
@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(Val ue))
FROM Common.dbo.spli t
(@SuiteList,',' )
) AND
SuiteID NOT IN (
SELECT LTRIM(RTRIM(Val ue))
FROM Common.dbo.spli t
(@CheckString,' ,')
)
) AND
PresentationID = @PresentationID
GO

[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.

"Scott McNair" <sc**********@s fmco.takethispa rtout.com> wrote in message
news:Xn******** ***********@207 .46.248.16...

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 cmdCleanUncheck edSuites
.Connection = cnSalesWiz
.CommandType = CommandType.Sto redProcedure
.CommandText = "prCleanUncheck edSuites"
.Parameters.Add ("@SuiteList ", SqlDbType.VarCh ar)
.Parameters.Add ("@Presentation ID", SqlDbType.Int)
.Parameters.Add ("@CheckString" , SqlDbType.VarCh ar)
.Parameters("@S uiteList").Valu e = Replace("'" & SQLEncode
(Request.Form(" SuiteList")) & "'", vbCrLf, ",")
.Parameters("@P resentationID") .Value = CInt(Request.Qu eryString
("ID"))
.Parameters("@C heckString").Va lue = Replace("'" & SQLEncode
(Request.Form(" SuiteID")) & "'", vbCrLf, ",")
'These next three lines run, so I know it's making it to here
Response.Write( .Parameters("@S uiteList").Valu e & "<br>")
Response.Write( .Parameters("@P resentationID") .Value & "<br>")
Response.Write( .Parameters("@C heckString").Va lue & "<br>")
.ExecuteNonQuer y()
End With
CREATE PROCEDURE prCleanUnchecke dSuites (
@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(Val ue))
FROM Common.dbo.spli t
(@SuiteList,',' )
) AND
SuiteID NOT IN (
SELECT LTRIM(RTRIM(Val ue))
FROM Common.dbo.spli t
(@CheckString,' ,')
)
) AND
PresentationID = @PresentationID
GO

[chanmmn]

Perhaps you want to take a look of this again:

http://support.microsoft.com/default...b;en-us;308049

chanmm
"Scott McNair" <sc**********@s fmco.takethispa rtout.com> wrote in message
news:Xn******** ***********@207 .46.248.16...

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 cmdCleanUncheck edSuites
.Connection = cnSalesWiz
.CommandType = CommandType.Sto redProcedure
.CommandText = "prCleanUncheck edSuites"
.Parameters.Add ("@SuiteList ", SqlDbType.VarCh ar)
.Parameters.Add ("@Presentation ID", SqlDbType.Int)
.Parameters.Add ("@CheckString" , SqlDbType.VarCh ar)
.Parameters("@S uiteList").Valu e = Replace("'" & SQLEncode
(Request.Form(" SuiteList")) & "'", vbCrLf, ",")
.Parameters("@P resentationID") .Value = CInt(Request.Qu eryString
("ID"))
.Parameters("@C heckString").Va lue = Replace("'" & SQLEncode
(Request.Form(" SuiteID")) & "'", vbCrLf, ",")
'These next three lines run, so I know it's making it to here
Response.Write( .Parameters("@S uiteList").Valu e & "<br>")
Response.Write( .Parameters("@P resentationID") .Value & "<br>")
Response.Write( .Parameters("@C heckString").Va lue & "<br>")
.ExecuteNonQuer y()
End With
CREATE PROCEDURE prCleanUnchecke dSuites (
@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(Val ue))
FROM Common.dbo.spli t
(@SuiteList,',' )
) AND
SuiteID NOT IN (
SELECT LTRIM(RTRIM(Val ue))
FROM Common.dbo.spli t
(@CheckString,' ,')
)
) AND
PresentationID = @PresentationID
GO

[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
"Scott McNair" <sc**********@s fmco.takethispa rtout.com> wrote in message
news:Xn******** ***********@207 .46.248.16...

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 cmdCleanUncheck edSuites
.Connection = cnSalesWiz
.CommandType = CommandType.Sto redProcedure
.CommandText = "prCleanUncheck edSuites"
.Parameters.Add ("@SuiteList ", SqlDbType.VarCh ar)
.Parameters.Add ("@Presentation ID", SqlDbType.Int)
.Parameters.Add ("@CheckString" , SqlDbType.VarCh ar)
.Parameters("@S uiteList").Valu e = Replace("'" & SQLEncode
(Request.Form(" SuiteList")) & "'", vbCrLf, ",")
.Parameters("@P resentationID") .Value = CInt(Request.Qu eryString
("ID"))
.Parameters("@C heckString").Va lue = Replace("'" & SQLEncode
(Request.Form(" SuiteID")) & "'", vbCrLf, ",")
'These next three lines run, so I know it's making it to here
Response.Write( .Parameters("@S uiteList").Valu e & "<br>")
Response.Write( .Parameters("@P resentationID") .Value & "<br>")
Response.Write( .Parameters("@C heckString").Va lue & "<br>")
.ExecuteNonQuer y()
End With
CREATE PROCEDURE prCleanUnchecke dSuites (
@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(Val ue))
FROM Common.dbo.spli t
(@SuiteList,',' )
) AND
SuiteID NOT IN (
SELECT LTRIM(RTRIM(Val ue))
FROM Common.dbo.spli t
(@CheckString,' ,')
)
) AND
PresentationID = @PresentationID
GO

[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
"Scott McNair" <sc**********@s fmco.takethispa rtout.com> wrote in message
news:Xn******** ***********@207 .46.248.16...

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 cmdCleanUncheck edSuites
.Connection = cnSalesWiz
.CommandType = CommandType.Sto redProcedure
.CommandText = "prCleanUncheck edSuites"
.Parameters.Add ("@SuiteList ", SqlDbType.VarCh ar)
.Parameters.Add ("@Presentation ID", SqlDbType.Int)
.Parameters.Add ("@CheckString" , SqlDbType.VarCh ar)
.Parameters("@S uiteList").Valu e = Replace("'" & SQLEncode
(Request.Form(" SuiteList")) & "'", vbCrLf, ",")
.Parameters("@P resentationID") .Value = CInt(Request.Qu eryString
("ID"))
.Parameters("@C heckString").Va lue = Replace("'" & SQLEncode
(Request.Form(" SuiteID")) & "'", vbCrLf, ",")
'These next three lines run, so I know it's making it to here
Response.Write( .Parameters("@S uiteList").Valu e & "<br>")
Response.Write( .Parameters("@P resentationID") .Value & "<br>")
Response.Write( .Parameters("@C heckString").Va lue & "<br>")
.ExecuteNonQuer y()
End With
CREATE PROCEDURE prCleanUnchecke dSuites (
@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(Val ue))
FROM Common.dbo.spli t
(@SuiteList,',' )
) AND
SuiteID NOT IN (
SELECT LTRIM(RTRIM(Val ue))
FROM Common.dbo.spli t
(@CheckString,' ,')
)
) AND
PresentationID = @PresentationID
GO