Wednesday, March 21, 2012

Problems with output variables when using varchar

I am using c# in Visual Studio 2005 to run a stored procedure in a sql 2000 databse. this is the stored procedure

CREATE procedure dbo.returnVendor4
@.vendorID varchar(10),
@.name nvarchar output
as
select @.name = vendor_Name
from vendors
where Code = @.vendorID

I steped through the c# code, it gives me an error when the procedure is ran (at the ExecuteNonQuery() part). this is the error message

"String[0]: the Size property has an invalid size of 0."

I have a similar procedure that returns an int, and it works okay. My c# code that calls the procedure is below

string retVal ;
SqlConnection con = new SqlConnection(
"server = lissadell; database = s00048271; uid = s00048271; pwd = 'chocolate'");
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "returnVendor4";

cmd.Parameters.Add("name", SqlDbType.NVarChar);
cmd.Parameters.Add("vendorID", SqlDbType.VarChar);

cmd.Parameters["name"].Direction = ParameterDirection.Output;
cmd.Parameters["vendorID"].Value = "G/03/I";
cmd.ExecuteNonQuery();

retVal = (string)(cmd.Parameters["name"].Value);
textBox1.Text = retVal.ToString() ;

just an idea - have you tried switching the order of your Parameters.Add statements? I think SQL will want the input parameter first. No idea if this will make a difference, just something off the top of my head.

Another guess - can you specify the size of the parameters when you add them? i.e. varchar(10)?

|||i tried both ideas, they didn't work|||

IN your Stored Procedure you need to add a length to your @.name parameter

@.name nvarchar(50) output.

Also, I think your C# Parameter.Add syntax may be a little off (Been a while since I coded in C#), try

cmd.Parameters.Add("@.name", SqlDbType.NVarChar,50);
cmd.Parameters.Add("@.vendorID", SqlDbType.VarChar,10);

sql

No comments:

Post a Comment