VB.net - Why does adding a return parameter to command cause the execution of the command to fail with "has too many arguments specified"

Question

1.00/5 (1 vote)

See more:

This is driving me nuts!

In a VB.Net program I have an SQL command object to call a stored procedure as follows:

VB

With SQLCmdTSUpdate
    .CommandText = "xxxxx_xxxxxxx_xxxxx_xxxxxx"
    .CommandType = CommandType.StoredProcedure
    .Connection = conn
    .Parameters.Add(New SqlClient.SqlParameter("@p_employee_login";, SqlDbType.VarChar, 10, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_start_date", SqlDbType.DateTime, 8, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, Now))
    .Parameters.Add(New SqlClient.SqlParameter("@p_ts_job_contracts", SqlDbType.VarChar, 220, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_mondays", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_tuesdays", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_wednesdays", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_thursdays", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_fridays", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_saturdays", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_sundays", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_ts_categories", SqlDbType.VarChar, 140, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_disb_uniques", SqlDbType.VarChar, 220, ParameterDirection.InputOutput, False, 30, 0, "";, DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_disb_actions", SqlDbType.VarChar, 40, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_disb_dates", SqlDbType.VarChar, 220, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_disb_job_contracts", SqlDbType.VarChar, 220, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_claim_values", SqlDbType.VarChar, 160, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_vat_elements", SqlDbType.VarChar, 160, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_claim_backs", SqlDbType.VarChar, 40, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_disb_categories", SqlDbType.VarChar, 140, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
    .Parameters.Add(New SqlClient.SqlParameter("@p_details", SqlDbType.VarChar, 1020, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, ""))
End With

Only one of the parameters is inputoutput

Having set the input parameters and then using:

SQLCmdTSUpdate.ExecuteNonQuery()

The SQL stored procedure is executed fine and has the desired results (and returns some value in the inputoutput parameter as expected). However the stored procedure uses the return value to notify whether the actions within the sp were done OK, so I need to access this return value.

As soon as I add the extra parameter as the first parameter as follows:

.Parameters.Add(New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue))

and then use the ExecuteNonQuery, I get an error about too many parameters

It is as if the dot net framework is not recognising that the extra parameter is for the return value.

Although I could add another output parameter to return the return code, the stored procedures in question have been used for years in VB6 programs with return values with no problems and I have no desire to re-write the sps just because VB.Net is being used.

Any pointers would be hugely appreciated. Thanks

Posted 26-Aug-13 13:38pm

Martin@eg

Updated 26-Aug-13 17:38pm

Dave Kreskowiak

v3

Add a Solution

Comments

Foothill 26-Aug-13 19:46pm

IIRC, the return parameter is added automatically, so you're basically duplicating it.

2 solutions

Solution 1

IIRC, you just need to add a parameter, with any name you want (it doesn't need a "@" on the beginning of the name), but make sure that your setting it's direction to ParameterDirection.ReturnValue. That's it!

What's with the semicolon at the end of the empty string constant you defined, in the definition of the "@p_disb_uniques" parameter?? Get rid of it.

Posted 26-Aug-13 17:49pm

Dave Kreskowiak

Comments

Martin@eg 27-Aug-13 19:47pm

Tks for responding. The semicolon got added when I pasted the code in so it is not really there. I know that I do not need the @ in front of the parameter name, but it has never really been a problem.

As explained above, when I add the return value parameter, it is no longer possible to execute the command without an error. The exact line that I am adding is:

.Parameters.Add(New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue))

As you can see, it does indeed specify that it is a returnvalue.

It is absolutely clear that no one else seems to have this problem after searching on google for days on end. Can you think why this doesn't work in my case?

Dave Kreskowiak 27-Aug-13 22:02pm

Move the ReturnValue parameter to the top of the list and see what happens.

Martin@eg 28-Aug-13 4:16am

I have put as the first parameter and the last and both cause the same error. Any other ideas?

Dave Kreskowiak 28-Aug-13 8:09am

Double-check the parameters against what the SP is expecting, for name, type, size and try it?

Remove all other parameters and try reading them one-by-one?

Martin@eg 28-Aug-13 12:11pm

I really appreciate your help, but as stated, the stored procedure works fine until the returnvalue parameter is added. Here is the stored procedure header itself:

ALTER PROCEDURE [dbo].[ukgas_timesheets_disbs_update]
@p_employee_login varchar(10),
@p_start_date datetime,
--Timesheet fields
@p_ts_job_contracts varchar(220),
@p_mondays varchar(100),
@p_tuesdays varchar(100),
@p_wednesdays varchar(100),
@p_thursdays varchar(100),
@p_fridays varchar(100),
@p_saturdays varchar(100),
@p_sundays varchar(100),
@p_ts_categories varchar(140),
--Disbursement fields
@p_disb_uniques varchar(220) output,
@p_disb_actions varchar(40),
@p_disb_dates varchar(220),
@p_disb_job_contracts varchar(220),
@p_claim_values varchar(160),
@p_vat_elements varchar(160),
@p_claim_backs varchar(40),
@p_disb_categories varchar(140),
@p_details varchar(1020)
AS

I must be doing something wrong since there are no other similar questions out on the internet, but perhaps it is not a widely used facility. I did see some comment that Microsoft are trying to discourage its use, but I have used this facility for many years with no issues.

Thank you

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Martin@eg · Accepted Answer · 2013-08-29T07:13:00

There was nothing wrong with the existing parameters but it seems that it is necessary to provide all the silly options even when defining the returnvalue parameter.

.Parameters.Add(New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue))

doesn't work, but

.Parameters.Add(New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 30, 0, "", DataRowVersion.Current, 0))

does work.

Thanks Microsoft!

Wish someone could clarify what parameters are actually needed beyond name, datatype, size & direction

------
It now appears that it is all down to the DataRowVersion value. If you do not specify this option then no output parameters (and returnvalue) work properly. Why this option should even exist for parameters of a stored procedure is a mystery and even more annoying is why the default prevents output parameters from working. Does anyone actually understand this?