十进制数据类型输出用于从sql表问题进行操作 [英] decimal datatype output for manipulation from sql table question
问题描述
我有一个包含productCode和productRI的表。我想让用户在下拉列表中选择productName，将所选值发送到一个sproc并让查询吐出价格，这样我就可以在我计划的其他一些函数中操作它。
i试图修改一些代码来为我做这个基本上完全相同的事情和悲伤的面孔......我什么都没回来，无处可去。
基本思路：
用户选择productCode并将代码发送到sql表，以根据用户选择的productCode检索productRI。我将其设置为sql输出，以便我可以将小数用于其他内容。
感谢您的帮助。我确定这通常是愚蠢和令人尴尬的事情。
sql table：productTable
productName | productCode | RI
varchar | varchar |小数（6,4我认为......）
sproc：
ALTER PROCEDURE dbo.getProductRI
（
@ productCode varchar （ 50 ），
@productRI decimal （ 6 ， 4 ）输出
）
AS
SET NOCOUNT ON
SELECT @ productRI = RI
FROM productTable
WHERE productCode = @ productCode
materialDataBLL
public class materialDataBLL
{
public string productCode;
public decimal pRIOut;
public decimal pISOOut;
public decimal pEFOut;
public decimal pDRIOut;
public materialDataBLL（）
{
}
public void materialData（）
{
string connSTR = ConfigurationManager.ConnectionStrings [ connStr]。ToString（）;
// 创建新的connSTR
SqlConnection conn = new SqlConnection（connSTR）;
// 创建新的sqlcomm并将类型声明为sproc
SqlCommand c1 = new SqlCommand（ dbo .getProductRI，conn）; // RI
SqlParameter sp = new SqlParameter（ @ productCode，SqlDbType.VarChar）;
sp.Size = 50 ;
sp.Value = productCode;
// 将productRefractiveIndex声明为输出
SqlParameter pRI = < span class =code-keyword> new SqlParameter（ @ productRefractiveIndex，SqlDbType .Decimal）;
pRI.Direction = ParameterDirection.Output;
pRI.Precision = 6 ;
pRI.Scale = 4 ;
c1.Parameters.Add（sp）;
c1.Parameters.Add（pRI）;
conn.Open（）;
c1.ExecuteNonQuery（）;
pRIOut = Convert.ToDecimal（pRI.SqlValue.ToString（））;
conn.close（）;
codebehind：
// 下面的行采用ddl选择值并设置lbo.productCode属性
lBO.productCode = productDDL.SelectedValue.ToString（）;
// 根据所选材料检索材料/产品参数......
materialDataBLL mdBLL = new materialDataBLL（）;
mdBLL.productCode = lBO.productCode;
mdBLL.materialData（）;
要注意的事项......我看看变量，我看到了productCode就在那里。所以这看起来很好。我的输出肯定是空的，我不确定我做错了什么。非常感谢任何帮助
更改SqlParameter pRI = new SqlParameter（@ productRefractiveIndex，SqlDbType.Decimal）
到SqlParameter pRI =新的SqlParameter（@ productRI ，SqlDbType.Decimal）
-
我为误读你的一些代码而道歉。我看到它是小数，所以请看我的例子来解决你的问题。
StoredProc：
创建 过程 [dbo]。[getNameId]
@name varchar （ 256 ），
@ rate 十进制（ 6 ， 4 ）输出
as
选择 @ rate = names.rate 来自名称其中名称= @ name
UI：
< div >
名称
< asp：TextBox runat = server ID = nameTextBox > < ; / asp：TextBox >
< br / >
< asp ：按钮 runat = server ID = getButton 文本 = 获取数据
onclick =getButton_Click / >
< asp：Label runat = server ID = returnValueLabel > < / asp：标签 >
< ; / div >
代码：
protected void getButton_Click（object sender，EventArgs e）
{
string name = nameTextBox.Text;
十进制速率= 0;
using（SqlConnection connection = new SqlConnection（ConfigurationManager.ConnectionStrings [localTest]。ConnectionString））
{
//设置命令
SqlCommand command = new SqlCommand （dbo.getNameId，连接）;
command.CommandType = CommandType.StoredProcedure;
//设置参数
SqlParameter nameParam = new SqlParameter（@ name，SqlDbType.VarChar）;
nameParam.Size = 256;
nameParam.Value = name;
SqlParameter rateParam = new SqlParameter（@ rate，SqlDbType.Decimal）;
rateParam.Direction = ParameterDirection.Output;
//而不是rateParam.Size = 10，设置以下内容，因此它匹配SQL结构和StoPro参数。
rateParam.Precision = 6;
rateParam.Scale = 4;
command.Parameters.Add（nameParam）;
command.Parameters.Add（rateParam）;
//获取
connection.Open（）;
command.ExecuteNonQuery（）;
rate =（decimal）rateParam.Value;
//设置ui。
returnValueLabel.Text = rate.ToString（）;
}
}
注意：在代码行上rate =（decimal）rateParam.Value;你也可以这样做：
rate = Convert.ToDecimal（rateParam.SqlValue.ToString（））;你也可以访问param.SqlValue。
i have a table that has a productCode and productRI. i would like to have the user select the productName in a dropdownlist which sends the selected value to a sproc and have the query spit out the price so that i can manipulate it in some other functions i plan on doing.
i tried to modify a bit of code to do this for me that does essentially the EXACT same thing and sad face...i'm getting back nothing and going nowhere fast.
basic idea:
user selects productCode and the code is sent to the sql table to retrieve the productRI based on the user selected productCode. i set this up as a sql output so that i can use the decimal for other stuff.
thanks for the help. i'm sure it's something silly and embarrassing as things like this usually are.
sql table: productTable
productName | productCode | RI
varchar | varchar | decimal (6,4 i think...)
sproc:
ALTER PROCEDURE dbo.getProductRI
(
@productCode varchar(50),
@productRI decimal(6,4) OUTPUT
)
AS
SET NOCOUNT ON
SELECT @productRI = RI
FROM productTable
WHERE productCode = @productCode
materialDataBLL
public class materialDataBLL
{
public string productCode;
public decimal pRIOut;
public decimal pISOOut;
public decimal pEFOut;
public decimal pDRIOut;
public materialDataBLL()
{
}
public void materialData()
{
string connSTR = ConfigurationManager.ConnectionStrings["connStr"].ToString();
//create new connSTR
SqlConnection conn = new SqlConnection(connSTR);
//create new sqlcomm and declare type as sproc
SqlCommand c1 = new SqlCommand("dbo.getProductRI", conn); //RI
SqlParameter sp = new SqlParameter("@productCode", SqlDbType.VarChar);
sp.Size = 50;
sp.Value = productCode;
//declare productRefractiveIndex as output
SqlParameter pRI = new SqlParameter("@productRefractiveIndex", SqlDbType.Decimal);
pRI.Direction = ParameterDirection.Output;
pRI.Precision = 6;
pRI.Scale = 4;
c1.Parameters.Add(sp);
c1.Parameters.Add(pRI);
conn.Open();
c1.ExecuteNonQuery();
pRIOut = Convert.ToDecimal(pRI.SqlValue.ToString());
conn.close();
codebehind:
//the line below takes the ddl selected value and sets the lbo.productCode property
lBO.productCode = productDDL.SelectedValue.ToString();
//retrieves material/product parameters based on selected material...
materialDataBLL mdBLL = new materialDataBLL();
mdBLL.productCode = lBO.productCode;
mdBLL.materialData();
things to note...i look in the variables and i see that the productCode is set in there. so that seems fine. my output for sure is null and i'm unsure of what i'm doing wrong. any help is greatly appreciated
Change SqlParameter pRI = new SqlParameter("@productRefractiveIndex", SqlDbType.Decimal)
to SqlParameter pRI = new SqlParameter("@productRI", SqlDbType.Decimal)
--
I do apologise for misreading some of your code. I see it is a decimal so please see my example to fix your issue.
StoredProc:
create procedure [dbo].[getNameId] @name varchar(256), @rate decimal(6,4) output as select @rate = names.rate from Names where Name = @name
UI:
<div> Name <asp:TextBox runat="server" ID="nameTextBox"></asp:TextBox> <br /> <asp:Button runat="server" ID="getButton" Text="Get data" onclick="getButton_Click" /> <asp:Label runat="server" ID="returnValueLabel"></asp:Label> </div>
Code:
protected void getButton_Click(object sender, EventArgs e) { string name = nameTextBox.Text; decimal rate = 0; using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["localTest"].ConnectionString)) { // Set command SqlCommand command = new SqlCommand("dbo.getNameId", connection); command.CommandType = CommandType.StoredProcedure; // Set parameters SqlParameter nameParam = new SqlParameter("@name",SqlDbType.VarChar); nameParam.Size=256; nameParam.Value = name; SqlParameter rateParam = new SqlParameter("@rate",SqlDbType.Decimal); rateParam.Direction = ParameterDirection.Output; // Instead of rateParam.Size = 10, set the following, so it matches the SQL structure and StoPro param. rateParam.Precision = 6; rateParam.Scale = 4; command.Parameters.Add(nameParam); command.Parameters.Add(rateParam); // Get connection.Open(); command.ExecuteNonQuery(); rate = (decimal)rateParam.Value; // Set ui. returnValueLabel.Text = rate.ToString(); } }
Note: on the code line "rate = (decimal)rateParam.Value;" you could also do:
rate = Convert.ToDecimal(rateParam.SqlValue.ToString());You can access the param.SqlValue also.
这篇关于十进制数据类型输出用于从sql表问题进行操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
