asked on

SQL Stored Procedure Parameter Between two table values

Can not get the between statement (>= <=) to work in my code. I need to get the markup where the TextBoxWeight in my form is between the StartWeight
and EndWeight in Markups table.

I have the following stored procedure which executes with no errors.

ALTER PROCEDURE [dbo].[spGetMarkup6]

@CustomerID int,
@GlobalOriginID int,
@GlobalDestinationID int,
@ServiceID int,
@PackageTypeID int,
@TextBoxWeight int,
@Markup Decimal(18,2) OUTPUT
AS

SELECT @Markup =(Markup), @GlobalOriginID = (GlobalOriginID)
FROM Markups
WHERE (CustomerID = @CustomerID) AND
            (GlobalOriginID = @GlobalOriginID) AND
            (GlobalDestinationID = @GlobalDestinationID) AND
            (ServiceID = @ServiceID) AND
            (PackageTypeID = @PackageTypeID)AND
            (WeightStart >= @TextBoxWeight) AND
            (WeightEnd <= @TextBoxWeight)

And the following code to call the procedure which works with no errors but the lblmessage.text is blank.

SqlCommand cmd = new SqlCommand("spGetMarkup6", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@CustomerID", DropDownList1.SelectedValue);
cmd.Parameters.AddWithValue("@GlobalOriginID", DropDownList2.SelectedValue);
cmd.Parameters.AddWithValue("@GlobalDestinationID", DropDownList3.SelectedValue);
cmd.Parameters.AddWithValue("@ServiceID", DropDownList4.SelectedValue);
cmd.Parameters.AddWithValue("@PackageTypeID", DropDownList5.SelectedValue);
cmd.Parameters.AddWithValue("@TextBoxWeight", TextBoxWeight.Text);

SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@Markup";
outputParameter.SqlDbType = System.Data.SqlDbType.Decimal;
outputParameter.Precision = 8;
outputParameter.Scale = 2;
outputParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outputParameter);

con.Open();
cmd.ExecuteNonQuery();

string Markup = outputParameter.Value.ToString();

lblMessage.Text = "Markup Amount = " + Markup;

But if I remove the @TextBoxWeight parameter from the sp and the code above then the lblMessage.Text works and shows the correct makup.

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExpressMan1

ASKER

It is a numeric data type ( Int ) in the database. I don't see Int type in the Intellisense drop down as a choice.

Jim Horn

Couple of thoughts..

@TextBoxWeight is being passed as an int, so may have issues if you're dealing with decimal values

I'm counting five other WHERE clause criteria all using AND, and any of them could be filtering values, so make sure it's not one of them that are causing rows to not be returned.

The SELECT into variable statement might be returning multiple rows, which is a different result than the envisioned one row. So .. how would you handle it if the SELECT returns two rows? No rows?

Are you handling any NULL values in the passed parameters?

ExpressMan1

ASKER

When I reduce the WHERE clauses to just 5, without the between weight clauses a result is returned.

SELECT not returning multiple rows. Fields will have validation / can't be left empty so NULL values not possible?

Changed datatype of WeightStart and WeightEnd to Numeric (18,0) but no luck.

Should I be using something other than "TextBoxWeight.Text" ?

Fernando Soto

Hi ExpressMan1;

Seeming the value in the database is an int then convert the string to an int.
You should do this before the other code so that if an error in converting happened you can jump out

// Will hold the Integer value of the TextBox or zero if can not convert to an integer.
int testWeight = int.MinValue;
// Convert the String data type to an integer
int.TryParse(TextBoxWeight.Text, out testWeight);
// testWeight is not a zero then you have a good conversion
if (testWeight != 0)
{
     cmd.Parameters.AddWithValue("@TextBoxWeight", testWeight);
}

// If you do not have a good convertion to integer then then jump out and report error

Open in new window

ExpressMan1

ASKER

Before the other code....like this? Get error "No overload for method "AddWithValue"takes on arguments.

SqlCommand cmd = new SqlCommand("spGetMarkup6", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

// Will hold the Integer value of the TextBox or zero if can not convert to an integer.
int testWeight = int.MinValue;
// Convert the String data type to an integer
int.TryParse(TextBoxWeight.Text, out testWeight);
// testWeight is not a zero then you have a good conversion
if (testWeight != 0)

cmd.Parameters.AddWithValue("@CustomerID", DropDownList1.SelectedValue);
cmd.Parameters.AddWithValue("@GlobalOriginID", DropDownList2.SelectedValue);
cmd.Parameters.AddWithValue("@GlobalDestinationID", DropDownList3.SelectedValue);
cmd.Parameters.AddWithValue("@ServiceID", DropDownList4.SelectedValue);
cmd.Parameters.AddWithValue("@PackageTypeID", DropDownList5.SelectedValue);
cmd.Parameters.AddWithValue("@TextBoxWeight,testWeight);

Fernando Soto

On which line of code are you getting the error on?

Please also post complete error message.

You are also missing the { and } for the if statement.

ExpressMan1

ASKER

Error is on this line
cmd.Parameters.AddWithValue("@TextBoxWeight,testWeight);

When I run the code I get this error:
Procedure or function 'spGetMarkup6' expects parameter '@TextBoxWeight', which was not supplied.

When I hover over the line: cmd.Parameters.AddWithValue("@TextBoxWeight,testWeight);
it shows Error: "No overload for method "AddWithValue"takes on arguments.

Not sure where to place {and } for if statement.

ExpressMan1

ASKER

Got it! Not relevant but changed text box name to txtWeight

int SetValueForText = 0;
Int32.TryParse(txtWeight.Text, out SetValueForText);

cmd.Parameters.AddWithValue("@Weight", txtWeight.Text);

Fernando Soto

So is it now working correctly?

ExpressMan1

ASKER

Thank You for help Fernando, and Jim Horn as well.