CODEWITHSUNDEEP
c#sql-server-ce
Patrick
Patrick

Reputation: 217

sql query to show data from price between two columns C#

EDIT: I am not able to format my code below, if any one can fix it.

I am new to sql queries and still learning.

Table Name: CommissionSetupTable.

I want to display @Paisa if gross_amount is between the range of @FromRate and @ToRate

enter image description here

Below is my code: 

    string paisa;
        private void load_commission_setup()
        {
            SqlCeConnection conn = null;
            SqlCeCommand cmd = null;
            SqlCeDataReader rdr = null;

        try
        {
            conn =
                new SqlCeConnection(
                   @"Data Source=|DataDirectory|\Database.sdf;Persist Security Info=False");
            conn.Open();

            int rowindex = purchaseBillTableDataGridView.Rows.Count - 1;
            gross_amount = double.Parse(purchaseBillTableDataGridView[10, rowindex].Value.ToString());

            // Gross Amount is between the ranges of FromRate and ToRate.


            cmd = new SqlCeCommand("SELECT Paisa FROM CommissionSetupTable WHERE='" + gross_amount.ToString() + "' BETWEEN @FromRate AND @ToRate;", conn);
            rdr = cmd.ExecuteReader();

            if (rdr == null)
            {
            }
            else
            {
                while (rdr.Read())
                {
                    paisa = rdr["Paisa"].ToString();

                }
                rdr.Close();
                cmd.Dispose();
            }
        }
        finally
        {
            conn.Close();
            int rowindex = purchaseBillTableDataGridView.Rows.Count - 1;
            purchaseBillTableDataGridView[11, rowindex].Value = paisa;
        }
    }

Upvotes: 0

Views: 208

Answers (1)

Steve
Steve

Reputation: 216313

The correct syntax to use here is the following

  cmd = new SqlCeCommand(@"SELECT Paisa FROM CommissionSetupTable 
        WHERE @gross BETWEEN FromRate AND ToRate;", conn);

Notice that the two field names should not be prefixed with @, otherwise they will be considered parameters placeholders.

And now, before executing the command, add the parameter for the @gross placeholder

 cmd.Parameters.Add("@gross", SqlDbType.Decimal).Value = gross_amount;

I don't know what is the exact datatype of the columns FromRate and EndRate, but note that you should use the correct datatype for your parameter. Do not pass a string and expect the database engine do the conversion for you. (or worse concatenate your value to the rest of the sql using ToString()). This is always wrong also if sometime the database engine could understand your values.

EDIT
Also, following your comments below, it appears that this line is wrong

int rowindex = purchaseBillTableDataGridView.Rows.Count - 1;

If your DataGridView has the property AllowUserToAddRow set to True then you want to use 

int rowindex = purchaseBillTableDataGridView.Rows.Count - 2;

because the first line points to the empty row added to the DataGridView for inserting a new record.

Upvotes: 1

Related Questions