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.
3 6353
What are the value of your parameters and where are you getting them from?
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.
I resolved problem me selft
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("@nextMtr").SqlDbType = SqlDbType.BigInt
cmdCPSS.Parameters("@nextMtr").Size = 8
cmdCPSS.Parameters("@nextMtr").Direction = ParameterDirection.ReturnValue
cmdCPSS.ExecuteNonQuery()
Return cmdCPSS.Parameters("@nextMtr").Value
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
cnCPSS.Close()
End Try
End Function
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bilbo |
last post by:
I have a a headscratcher here:
I have a form that when submitted should do 2 things when a user enters
data and then clicks the Add button.
Here goes:
1. Call a stored procedure called...
|
by: Justin |
last post by:
I am trying to query two tables with a stored procedure but I get the
following error:
"Procedure Details has no parameters and arguments were supplied."
Here is my stored procedure as created...
|
by: Child |
last post by:
Hi, I am getting the error: "Procedure or function spAddActivity has too
many arguments specified. " on a stored procedure insert. I compared the
number of parameters in the function and the SP...
|
by: cj |
last post by:
I need to call a stored procedure that passes back 2 strings. I don't
think I need a data adapter or a select command or a command builder.
So after I create the connection what do I do?
Dim...
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function...
|
by: Siv |
last post by:
Hi,
I have a stored procedure that I want to execute and then wait in a loop
showing a timer whilst it completes and then carry on once I get
notification that it has completed. The main reason...
|
by: mahajanvit |
last post by:
Hi one and all
I got this problem during my project. So in order to solve this I made
a very small application. I am trying to insert using SP and
sqldatasource control. I know that while using...
|
by: neenaprasad |
last post by:
I am trying to pass parameters through form ....................
Getting the following error...........
ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range,...
|
by: priyamtheone |
last post by:
I'm trying to create a stored procedure in MSSQL Server 2005 that'll perform the following jobs:
1) Create a login.
2) Create an user in TestDB database for the login created in step 1.
3) Assign...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |