VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied

[mnarewec]

Please assist me:

This erorr message is produce when calling the stored procedure in vb.net

Procedure AutomateMatterN umber has no parameters and arguments were supplied."


MS SQL 2000stored procedure:
*/[code=sql]
CREATE PROCEDURE dbo.AutomateMat terNumber AS

DECLARE @nextMtr AS BIGINT
DECLARE @dtToday AS DATETIME

IF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,Dat eSet,GETDATE()) =0 )
BEGIN
DELETE FROM tempMatter-- incase there are some old records

SELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,
@dtToday=CONVER T(DATETIME,GETD ATE() ,102)
FROM tblCoversheet WHERE
MatterNumber IN (SELECT MatterNumber FROM tblCoversheet
WHERE ISNUMERIC(Matte rNumber)>0) ORDER BY (CONVERT(BIGINT , MatterNumber)) DESC

/*
Insert into the temptable, first record for the day
*/
INSERT INTO tempMatter(Matt erNumber,DateSe t)
VALUES(@nextMtr ,@dtToday)
END
ELSE
BEGIN
--select tempmatternumbe r,convert(datet ime,dateset,101 ) from tempMatter
-- Increment data from the temp table
SELECT @nextMtr=MAX(Ma tterNumber) + 1,@dtToday= GETDATE() FROM tempMatter
INSERT INTO tempMatter(Matt erNumber,DateSe t)
VALUES(@nextMtr ,@dtToday)

END

-- Return the result
RETURN @nextMtr
GO
[/code]
VB.NET 2003 Function:[code=vbnet]
Public Function GetMatterNumber ()
Try
objADO = New clsADO
cnCPSS = New SqlConnection(o bjADO.CxnStr)
cnCPSS.Open()
cmdCPSS = New SqlCommand("Aut omateMatterNumb er", cnCPSS)
cmdCPSS.Command Type = CommandType.Sto redProcedure
cmdCPSS.Paramet ers.Add("@nextM tr", "")
cmdCPSS.Paramet ers(0).SqlDbTyp e = SqlDbType.BigIn t
cmdCPSS.Paramet ers(0).Size = 8
cmdCPSS.Paramet ers(0).Directio n = ParameterDirect ion.Output
Return cmdCPSS.Execute NonQuery()
Catch ex As Exception
Console.WriteLi ne(ex.Message)
Finally
cnCPSS.Close()
End Try
End Function[/code]

'++++++++++++++ +++++++++++++++ +++++++++++
Eventhought changing ParameterDirect ion to ReturnValue and changing ExecuteScalar it produce nothing or error.

NB. SQL procedure works well in VB6 code:
[code=vb]
Public Function getNewMatter() As String
On Error GoTo errH

Dim cmd As New Command
Dim PARAMS As ADODB.Parameter s

Set PARAMS = cmd.Parameters

PARAMS.Append cmd.CreateParam eter("NewMatter Number", adVariant, adParamReturnVa lue, 1)

With cmd
.CommandText = "cpss.dbo.Autom ateMatterNumber "
.CommandType = adCmdStoredProc
.ActiveConnecti on = cnDBase
.Execute
End With

getNewMatter = PARAMS("NewMatt erNumber")
Exit Function

errH:
MsgBox "Error Generating MatterNumber", vbCritical + vbOKOnly, "Matter Number"
End Function[/code]
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++
Where did I go wrong.
Thanks so much for your help.

[kenobewan]

What are the value of your parameters and where are you getting them from?

[mzmishra]

Hey It seems your procedure is wrong.
You are trying to provide a parameter to procedure but the procedure does not accept any.
Can you check that.

[mnarewec]

I resolved problem me selft


Public Function GetMatterNumber ()
Try
objADO = New clsADO
cnCPSS = New SqlConnection(o bjADO.CxnStr)
cnCPSS.Open()
cmdCPSS = New SqlCommand("Aut omateMatterNumb er", cnCPSS)
cmdCPSS.Command Type = CommandType.Sto redProcedure
cmdCPSS.Paramet ers.Add("@nextM tr", "")
cmdCPSS.Paramet ers("@nextMtr") .SqlDbType = SqlDbType.BigIn t
cmdCPSS.Paramet ers("@nextMtr") .Size = 8
cmdCPSS.Paramet ers("@nextMtr") .Direction = ParameterDirect ion.ReturnValue
cmdCPSS.Execute NonQuery()
Return cmdCPSS.Paramet ers("@nextMtr") .Value
Catch ex As Exception
Console.WriteLi ne(ex.Message)
Finally
cnCPSS.Close()
End Try
End Function