468,242 Members | 1,601 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Problem Calling Stored Procedure

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").Valu e & "<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
Nov 21 '05 #1
6 1257
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,Parameter direction.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
"Scott McNair" <sc**********@sfmco.takethispartout.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 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").Valu e & "<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

Nov 21 '05 #2
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

Nov 21 '05 #3
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**********@sfmco.takethispartout.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 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").Valu e & "<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

Nov 21 '05 #4
Perhaps you want to take a look of this again:

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

chanmm
"Scott McNair" <sc**********@sfmco.takethispartout.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 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").Valu e & "<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

Nov 21 '05 #5
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**********@sfmco.takethispartout.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 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").Valu e & "<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

Nov 21 '05 #6
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**********@sfmco.takethispartout.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 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").Valu e & "<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

Nov 21 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Jarrod Morrison | last post: by
2 posts views Thread by Kent Lewandowski | last post: by
reply views Thread by Roman Prigozhin | last post: by
2 posts views Thread by Woody Splawn | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.