SQL Parameter question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am writing some code that passes two parameters to a store procedure. One
of the parameters is an output parameter. I am querying the DB using the
input value and returning a field from the result using the output parameter.
The issue is that when I run the ExecuteNonquery command I get the following
error:

'@shortLinkUrl' of type: String, the property Size has an invalid size: 0


Any ideas? Please help. I am a beginner programmer using C# and ASP.NET, so
please be as detailed as possible in your help. Thanks.

Hector
 
Did you add it as an output parameter to the parameters collection of
the command you are executing? You can do so by setting the parameter's
Direction to ParameterDirection.Output.
 
First of all, thank you for answering my query. To answer your question, yes
I did add it to the command object as an output parameter. I also coded the
T-SQL of the store procedure as an output parameter. I genuinely believed I
did everything I had to do to ensure everything works as it's supposed to. I
even had similar code, that works the same way, that works fine. I compared
the code to make sure I did not make any mistakes along the way and
everything seems fine. Here is some sample code that I am using:


SqlParameter[] Parameters;
Parameters = new SqlParameter[2];
Parameters[0] = new SqlParameter("@shortLinkId", SqlDbType.VarChar);
Parameters[0].Value = shortLinkId;
Parameters[1] = new SqlParameter("@shortLinkUrl", SqlDbType.VarChar);
Parameters[1].Direction = ParameterDirection.Output;
SqlHelper.ExecuteCommand(connectionString, "spGetShortLinkUrl", Parameters);
shortLinkUrl = Parameters[1].Value.ToString();


The following is some code from the ExecuteCommand method:

SqlCommand Command = new SqlCommand();
Command.Connection = new SqlConnection(connection);
Command.CommandText = commandText;
Command.CommandType = commandType;
foreach (SqlParameter parameter in commandParameters)
Command.Parameters.Add(parameter);

Command.Connection.Open();
int recordsAffected;
try
{
recordsAffected = Command.ExecuteNonQuery();
}
catch (Exception ex)
..
..
..
Thanks,
Hector
 
Ok. Did you try to specify the length parameter (check the overloads
for the SqlParameter constructor)? If not, try to set it to the length
you specified in your stored procedure.
 
It's very weird. I have code that works the same way as the one I am having
problems with, but your suggestion did the trick. The code works fine now.
Thank you.

Hector
 
I came across the same issue as well. Its strange how the exact code works
in one situtation and then don't work in the other
 
Back
Top