problem with simple stored procedure execution

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

on SQL Server i have the following sample stored procedure:

CREATE PROC owner.procname (
@id int,
@desc varchar(50) output)
AS

select @desc = description
from table1 where id = @id


in a class library, i have the following two methods:

public string GetDescription(int nID, string sConnectString)
{
SqlConnection oConn = new SqlConnection(sConnectString);
oConn.Open();
SqlCommand oCommand = oConn.CreateCommand();
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.CommandText = "procname";

AddParam(oCommand, "@id", SqlDbType.Int,
ParameterDirection.Input, nID);
AddParam(oCommand, "@desc", SqlDbType.VarChar,
ParameterDirection.Output, null);

oCommand.ExecuteNonQuery();
string sReturn = (string) oCommand.Parameters["@desc"].Value;

oCommand.Dispose();
oConn.Close();
oConn.Dispose();
return sReturn;
}

public void AddParam(SqlCommand oCommand, string sName, SqlDbType
eType, ParameterDirection eDirection, object oValue)
{
SqlParameter oParam = oCommand.Parameters.Add(sParamName, eType);
oParam.Direction = eDirection;
if (oValue == null)
{
switch (eType)
{
case SqlDbType.VarChar:
oParam.Value = "";
oParam.Size = 0;
break;
case SqlDbType.Bit:
case SqlDbType.Int:
case SqlDbType.Money:
oParam.Value = 0;
break;
case SqlDbType.DateTime:
oParam.Value = DateTime.Now;
break;
}
}
else if (eType == SqlDbType.VarChar)
{
oParam.Value = oValue;
string sValue = (string) oValue;
oParam.Size = sValue.Length;
}
}


the problem is when i instatiate the class, and run the GetDescription
method, i receive the following error:

Procedure 'procname' expects parameter '@id', which was not supplied.

but the @id parameter IS supplied, unless i'm doing it incorrectly with
the Add method of the parameter collection?

thanks for any help figuring out why the stored procedure thinks i
haven't supplied the @id parameter,

jason
 
ah, sorry, i modified the code to get rid of some issue-irrelevant
bloat, and missed one of my renames. sParamName should read sName,
which is passed in as the 2nd AddParam argument.

some additional data suggests that it definitley has something to do
with the AddParam method. when i add the parameter with the following
line in the GetDescription body:

oCommand.Parameters.Add("@id", (object) nID).Direction =
ParameterDirection.Input

it seems to get recognized as added. something about passing the
arguments to a function that handles the parameter addition seems to be
the source of the problem, but i still haven't found it.
 
Jason,
AddParam(oCommand, "@id", SqlDbType.Int,
ParameterDirection.Input, nID);
AddParam(oCommand, "@desc", SqlDbType.VarChar,
ParameterDirection.Output, null);
Click to expand...

Here's the problem, you've setup your parameters correctly but after that
you're executing your query without setting @id to something
You need to set @id:
oCommand.Parameters["@id"].Value = ???

Then you can execute your command
oCommand.ExecuteNonQuery();
string sReturn = (string) oCommand.Parameters["@desc"].Value;
Click to expand...

jason said:
on SQL Server i have the following sample stored procedure:

CREATE PROC owner.procname (
@id int,
@desc varchar(50) output)
AS

select @desc = description
from table1 where id = @id


in a class library, i have the following two methods:

public string GetDescription(int nID, string sConnectString)
{
SqlConnection oConn = new SqlConnection(sConnectString);
oConn.Open();
SqlCommand oCommand = oConn.CreateCommand();
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.CommandText = "procname";

AddParam(oCommand, "@id", SqlDbType.Int,
ParameterDirection.Input, nID);
AddParam(oCommand, "@desc", SqlDbType.VarChar,
ParameterDirection.Output, null);

oCommand.ExecuteNonQuery();
string sReturn = (string) oCommand.Parameters["@desc"].Value;

oCommand.Dispose();
oConn.Close();
oConn.Dispose();
return sReturn;
}

public void AddParam(SqlCommand oCommand, string sName, SqlDbType
eType, ParameterDirection eDirection, object oValue)
{
SqlParameter oParam = oCommand.Parameters.Add(sParamName, eType);
oParam.Direction = eDirection;
if (oValue == null)
{
switch (eType)
{
case SqlDbType.VarChar:
oParam.Value = "";
oParam.Size = 0;
break;
case SqlDbType.Bit:
case SqlDbType.Int:
case SqlDbType.Money:
oParam.Value = 0;
break;
case SqlDbType.DateTime:
oParam.Value = DateTime.Now;
break;
}
}
else if (eType == SqlDbType.VarChar)
{
oParam.Value = oValue;
string sValue = (string) oValue;
oParam.Size = sValue.Length;
}
}


the problem is when i instatiate the class, and run the GetDescription
method, i receive the following error:

Procedure 'procname' expects parameter '@id', which was not supplied.

but the @id parameter IS supplied, unless i'm doing it incorrectly with
the Add method of the parameter collection?

thanks for any help figuring out why the stored procedure thinks i
haven't supplied the @id parameter,

jason
Click to expand...
 
haha, how silly of me. there are so many other things to set, i
completely forgot about the value i'm passing in. thanks so much for
noticing that.
 
oh nice. entirely data driven parameter settings. i will definitely
have to play with that, thanks for the link!
 
wow. i just implemented a version of what you describe, and it is the
sexiest thing i've seen in a while. thank you very much for sharing
your discovery! the margin for error in typing data from sql to the
business objects i'm writing has just gotten about as small as i could
realistically hope for, and with lots of time saving automation. this
is GREAT

jason
 
I think the thing that's really cool is the T-SQL that creates that XML
file.
It's original use was to create a C++ class for OleDb.
Once I got farther into .NET, I modified that T-SQL again to create that XML
definition file for building a SqlCommand.
You're right, typing errors went to zero once I implemented that.
I'm glad you saw the concept. Good Luck.
 
Back
Top