CLR-Stored Procdures

Visual Basic .NET » Database Programming

CLR-Stored Procdures -- MiladAzimi --

Hi I have a CLR-Stored Procdure I've compiled it and deployed it but it
doesn't work.
The error message says that Subqueries are not allowed in this context,I
wonder if you could help me.

by the way I've already reconfigured the Data Base and it's ready to
run the CLR-Stored Procdures.

here is the code
==========================CODE=========================

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{

public static void AddGoods(SqlString GoodsID,SqlString TypeName,
SqlString Unit,SqlString GoodsName,SqlString Amount,SqlString Price,
SqlString Description)
{
using ( SqlConnection cnn = new SqlConnection("context
connection=true") )
{
SqlCommand cmd = new SqlCommand();
string strType = " (SELECT TypeID FROM Tbl_TypesOfGoods
WHERE TypeName='" + TypeName.ToString() + "')";
string strUnit = " (SELECT UnitID FROM Tbl_Units WHERE
UnitName='" + Unit.ToString() + "')";

cmd.CommandText = "INSERT INTO Tbl_Goods(GoodsID, TypeID,
UnitID, GoodsName, " +
"Amount, Price, Description)" + " VALUES('" +
GoodsID.ToString() + "'," +
strType + "," + strUnit + ", '" + GoodsName.ToString() +
"', '" +
Amount.ToString() + "', '" + Price.ToString() + "', '" +
Description.ToString() + "');";

cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
}
};

==========================END OF CODE==================

Thanks alot.

-- ChrisCottrell --

Why don't you just change the insert statement to use a from and a join. The syntax using the join is much clearer than what you are doing anyway. It has a problem with the way you are nesting a select statement as a value.
Don't think you can do that with regular sql.. Try running in Mgmt. Studio and see if it has a problem with the syntax. My guess is that it will not work as coded.

On 7/21/06, Milad Azimi <milad.a.p> wrote:

Hi
I have a CLR-Stored Procdure I've compiled it and deployed it but it
doesn't work.
The error message says that Subqueries are not allowed in this context,I
wonder if you could help me.

by the way I've already reconfigured the Data Base and it's ready to run
the CLR-Stored Procdures.

here is the code
==========================CODE=========================

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{

public static void AddGoods(SqlString GoodsID,SqlString TypeName,
SqlString Unit,SqlString GoodsName,SqlString Amount,SqlString
Price,
SqlString Description)
{
using ( SqlConnection cnn = new SqlConnection("context
connection=true") )
{
SqlCommand cmd = new SqlCommand();
string strType = " (SELECT TypeID FROM Tbl_TypesOfGoods WHERE
TypeName='" + TypeName.ToString() + "')";
string strUnit = " (SELECT UnitID FROM Tbl_Units WHERE
UnitName='" + Unit.ToString() + "')";

cmd.CommandText = "INSERT INTO Tbl_Goods(GoodsID, TypeID,
UnitID, GoodsName, " +
"Amount, Price, Description)" + " VALUES('" +
GoodsID.ToString() + "'," +
strType + "," + strUnit + ", '" + GoodsName.ToString() +
"', '" +
Amount.ToString() + "', '" + Price.ToString() + "', '" +
Description.ToString() + "');";

cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
}
};

==========================END OF CODE==================

Thanks alot.
>

Home