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

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


Please assist me:

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

Procedure AutomateMatterNumber has no parameters and arguments were supplied."


MS SQL 2000stored procedure:
*/
CREATE PROCEDURE dbo.AutomateMatterNumber AS

DECLARE @nextMtr AS BIGINT
DECLARE @dtToday AS DATETIME

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

SELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,
@dtToday=CONVERT(DATETIME,GETDATE() ,102)
FROM tblCoversheet WHERE
MatterNumber IN (SELECT MatterNumber FROM tblCoversheet
WHERE ISNUMERIC(MatterNumber)>0) ORDER BY (CONVERT(BIGINT, MatterNumber)) DESC

/*
Insert into the temptable, first record for the day
*/
INSERT INTO tempMatter(MatterNumber,DateSet)
VALUES(@nextMtr,@dtToday)
END
ELSE
BEGIN
--select tempmatternumber,convert(datetime,dateset,101) from tempMatter
-- Increment data from the temp table
SELECT @nextMtr=MAX(MatterNumber) + 1,@dtToday= GETDATE() FROM tempMatter
INSERT INTO tempMatter(MatterNumber,DateSet)
VALUES(@nextMtr,@dtToday)

END

-- Return the result
RETURN @nextMtr
GO

VB.NET 2003 Function:
Public Function GetMatterNumber()
Try
objADO = New clsADO
cnCPSS = New SqlConnection(objADO.CxnStr)
cnCPSS.Open()
cmdCPSS = New SqlCommand("AutomateMatterNumber", cnCPSS)
cmdCPSS.CommandType = CommandType.StoredProcedure
cmdCPSS.Parameters.Add("@nextMtr", "")
cmdCPSS.Parameters(0).SqlDbType = SqlDbType.BigInt
cmdCPSS.Parameters(0).Size = 8
cmdCPSS.Parameters(0).Direction = ParameterDirection.Output
Return cmdCPSS.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
cnCPSS.Close()
End Try
End Function

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

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

Dim cmd As New Command
Dim PARAMS As ADODB.Parameters

Set PARAMS = cmd.Parameters

PARAMS.Append cmd.CreateParameter("NewMatterNumber", adVariant, adParamReturnValue, 1)

With cmd
.CommandText = "cpss.dbo.AutomateMatterNumber"
.CommandType = adCmdStoredProc
.ActiveConnection = cnDBase
.Execute
End With

getNewMatter = PARAMS("NewMatterNumber")
Exit Function

errH:
MsgBox "Error Generating MatterNumber", vbCritical + vbOKOnly, "Matter Number"
End Function
++++++++++++++++++++++++++++++++++++++++++++++++
Where did I go wrong.
Thanks so much for your help.
0
mnarewec
11/16/2007 9:29:31 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
1999 Views

Similar Articles

[PageSpeed] 36

The parameter direction must be set to ReturnValue and its name to @RETURN_VALUE.


Regards

Dave
0
DMW
11/16/2007 9:53:16 AM

mnarewec:
-- Return the result
RETURN @nextMtr
 

Here you are returning the value of @nextMtr, but you're trying to add this as a parameter while calling the stored procedure from code.  See below,

mnarewec:
cmdCPSS.Parameters.Add("@nextMtr", "")
cmdCPSS.Parameters(0).SqlDbType = SqlDbType.BigInt
cmdCPSS.Parameters(0).Size = 8
cmdCPSS.Parameters(0).Direction = ParameterDirection.Output
 

There is a difference between return value and an output type parameter.  You can do one of 2 things.

Either modify your procedure definition and add the @netMtr as an output type parameter,

Or modify your code and remove the parameter adding code.

Hope this will help.
 


Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."

"Mark as Answer" the post(s) which helped you solve the problem
0
dhimant
11/16/2007 11:36:29 AM

I suppose that your procedure should start this way:

CREATE PROCEDURE dbo.AutomateMatterNumber

 @nextMtr AS BIGINT,
 @dtToday AS DATETIME

AS

 


Thanks

JPazgier
0
jpazgier
11/17/2007 1:18:29 AM
Reply:

Similar Artilces:

SQL Server Stored Procedures/VB.Net
IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example..... Most likely the easiest way, is, when you retrieve the data from the sProc, put it in a DataView.. That way, you can sort The DataView by any field, ASC or DESC that you wantDavid WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on th...

VB.net connect to SQL Express Stored Procedure
Dear Experts, i am writing a VB.net window application. I created a SQL Express database inside the project. Inside the SQLExpress database, i created a stored procedure. I would like to know how to call that SP. Below is what i did and it raised a error "Missing provider" I tried to add "'Provider=SQLOLEDB;'", the missing provider error message gone but another error raised as below.. No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21). Sub PushtoDB() Dim con As OleDbConnection Dim cmd As OleDbCommand = New OleD...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...

How to execute stored procedure from vb.net CodeBehind instead of a sql statement?
I've learned how to exeucte a simple sql statement from my vb.net codebehind (see sample below), but I want to go a step further and learn how to execute a stored procedure from my code behind.  I've already written the stored proc, just don't know how to reference it and the parameters in my codebehind..Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxx").ConnectionStringconn = New SqlConnection(connectionString) comm = New SqlCommand("INSERT into xxx (a...

how to pass vb.net 2 dimensional array to a sql stored procedure
I am developing a online shoing cart for a ISP company. according to the business when we add some products we keep set of records for products such as domain name, address , tel, hosting option and so on so my basket contains a list of items as follow i would prefer to convert these in to datatable and pass to a stored procedure item     tel           name        add1      add2       add3      postcode   &n...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

Problem with DBD::ODBC : -DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL Server]Procedure has no parameters and arguments were supplied. (SQL-42000)(DBD: st_execute/SQLExecute err=-1)
When I try to run a perl script that uses ODBC drivers to insert data more than 70000 rows I am getting the following error.... DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL Server]Procedure has no parameters and arguments were supplied. (SQL-42000)(DBD: st_execute/SQLExecute err=-1) [for Statement "INSERT INTO db.guest.portfolio_event_1_310_3560_3(col1,col2,col3,col4,col4,col5) VALUES (?,?,?,?,?)" with ParamValues: 4=' ', 1='1754079', 2=' ', 3='0', 5=' '] at /usr/local/lib/perl5/site_perl/5.8.6/DBIx/ ContextualFetch.pm lin...

SQL Stored Procedure Issue
This is the Stored Procedure below ->  SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/ /****** Object:  Stored Proc...

SQL Stored Procedure to MySQL Stored Procedure Conversion
Hi, I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN , but I don't know what to do with the rest of the code. The following is the sql stored procedure that I am trying to convert to msql stored procedure: CREATE PROCEDURE Register_User (@userName Varchar(50), (@PassWord Varchar(50), (@FirstName Varchar(50), (@LastName Varchar(50) ) AS IF EXISTS(SELECT u_ID FROM User_ID Where u_UserName=@UserName) RETURN -1 ELSE INSERT User_ID( u_User...

Store SQL return parameter in VB.NET variable
I am working with inserting customer complaints and want to get the complaint id back when the insert is complete.  So far I have the complaint being inserted without problem but I am not getting the complaint id back in the program. SQL Insert procedure:ALTER PROCEDURE [dbo].[InsertComplaint] @fcomp_assign CHAR(30), @fcomp_bu CHAR(25), -- required @fcomp_cntct_id SMALLINT, -- required @fcomp_create_by CHAR(30), -- required @fcomp_cust CHAR(8), -- required @fcomp_desc CHAR(8000), -- required @fcomp_ftr BIT, -- required @fcomp_inv CHAR(8), @fcomp_mor CHA...

regarding sql stored procedures debugging from VC# .Net
i have done all the required changes for debugging the sql stored procedure from VC# .net application.On debugging , the debugger goes into stored procedure from VC# code , but after debugging the stored procedure debugger donot return back to VC# coding even when i have used some checkpoints in VC# application....

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

How to convert Sql Server Stored Procedures into Oracle Stored Procedures
Hi All, I am migrating from sql server2000 to oracle. I have moved all table into oracle manually. Now I need to move stored procedure. I don't know how to convert sql server stored procedure into oracle stored procedure form. Is there any tool which will convert directly. Please some one help me. Thank you.    Hi there,Please use the tool bellow, which does the job you want. http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html thanks sharp guy...

Web resources about - VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied - asp.net.sql-datasource

Resources last updated: 1/21/2016 5:39:50 AM