By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,978 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,978 IT Pros & Developers. It's quick & easy.

Problem Calling Stored Procedure

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.