A .NET app uses a stored proceedure to query a table.
I get this error. —
Procedure SCGAdd_RptGetAllStreets has no parameters and arguments were supplied.
Thing is, neither the stored proceedure OR the sqlCommand object has ANY arguments or parameters specified.
sqlCommand code in .NET ----
Dim objCommand As New SqlCommand
Try
With objCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "SCGAdd_RptGetAllStreets"
End With
Return QueryWorkOrders(objCommand)
Finally
If objCommand IsNot Nothing Then
objCommand.Dispose()
End If
End Try
Here is the Stored Procedure –
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
– =============================================
– Author:
– Create date: 8/6/08
– Description: Return all values from street look up table
– =============================================
ALTER PROCEDURE [dbo].[SCGAdd_RptGetAllStreets]
– Add the parameters for the stored procedure here
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * from gisv_streets
RETURN
END
What is in the QueryWorkOrders() method? Might there be code within that method that adds a parameter to the command object?
Also, make sure that your connection string is pointing where you think it is. I mention this in case you recently changed the stored proc, perhaps in a local copy of a database, but you are using a connection string that points to some other database with a different version of the stored proc.
Idea 1) Are you absolutely sure the stored proc is in the same database your code is running against? This looks like a classic “change the stored proc in the QA database but test the code against the dev database” problem.
Idea 2) What is QueryWorkOrders() do?
Nothing you’ve shown us defines that method. If that’s some auto-generated Data Access Layer code, often those drive extra status parameters into the SQLCommand object ahead of the embedded .Execute() call. They assume you’ve built your SQL stored proc to match the way they would if they were generating it too.
ETA great minfds seem to think alike. Sanity’s post was NOT there when I wrote mine.
Not to pile on, but the last two posters seem to be on track to me. Your parameters collection may be starting out empty, but it’s evidently not empty when the command reaches the database, because that’s what the database is freaking out about. (Notice my clever use of technical terms!) At what step in the process are you checking the parameters.count value?