decimal datatype output for manipulation from sql table question

Question

0.00/5 (No votes)

See more:

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:

SQL

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

C#

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:

C#

//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

Posted 13-Jan-14 8:08am

memberxxxxxxxxxxxxxxxxx

Updated 27-Jan-14 9:05am

v2

Add a Solution

Comments

bowlturner 13-Jan-14 16:15pm

Happen to have the dbo.getProductRI code available?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

njammy · Accepted Answer · 2014-01-13T14:07:00

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:

SQL

create procedure [dbo].[getNameId]
@name varchar(256),
@rate decimal(6,4) output
as
select @rate = names.rate from Names where Name = @name

UI:

XML

<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.