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.
ExpressMan1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
It looks like the value you are sending to the database is a text string, cmd.Parameters.AddWithValue("@TextBoxWeight", TextBoxWeight.Text);, but in the database is WeightStart and WeightEnd a character data type or is it a numeric data type.?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpressMan1Author Commented:
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 HornMicrosoft SQL Server Data DudeCommented:
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?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ExpressMan1Author Commented:
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 SotoRetiredCommented:
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

ExpressMan1Author Commented:
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 SotoRetiredCommented:
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.
ExpressMan1Author Commented:
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.
ExpressMan1Author Commented:
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 SotoRetiredCommented:
So is it now working correctly?
ExpressMan1Author Commented:
Thank You for help Fernando, and Jim Horn as well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.