SQL Query Error in C#

Question

I am new to SQL Queries, I want to load "PSX LAGA" Value from SettlementTypeSetup (Table) where Settlement Type is equals to Regular/BO and Sale / Purchase is equals to "Purchase";

below is my code and this is my table

private void Load_Settlement_Type()
        {
        SqlCeConnection conn = null;
        SqlCeCommand cmd = null;
        SqlCeDataReader rdr = null;

        try
        {
            conn =
                new SqlCeConnection(
                   @"Data Source=|DataDirectory|\Database.sdf;Persist Security Info=False");
            conn.Open();
            cmd = new SqlCeCommand("SELECT PSXLaga FROM SettlementTypeSetup where SettlementType=BO/REGULAR;" , conn);
            rdr = cmd.ExecuteReader();

            if (rdr == null)
            {
                MessageBox.Show("Reader is null");
            }
            else
            {
                while (rdr.Read())
                {
                    PSXLAGATEXT  = rdr["PSXLaga"].ToString();

                }

                 rdr.Close();
                cmd.Dispose();

            }
        }
        finally
        {
            conn.Close();
            PSXLagaTextBox.Text = PSXLAGATEXT;

        }

    }

****It gives me error: Column Name: BO/REGULAR not found, whereas BO/REGULAR is not a column name, BO/REGULAR is a value of a SettlementType (Column), The condition should be as follows.**

Give me PSX Laga Value where SettlementType(Column) value is BO/REGULAR and Sale/Purchase(Column) is Purchase.

**

where SettlementType='BO/REGULAR' Note the quotes around the value — Simon Wilson
– Simon Wilson, Commented Dec 6, 2016 at 12:55

mybirthname · Accepted Answer · 2016-12-06 13:00:50Z

6

You need write your value in '' because it is a string. Other way is to do it using parameters.

cmd = new SqlCeCommand("SELECT PSXLaga FROM SettlementTypeSetup where SettlementType=@Type" , conn);

cmd.Parameters.AddWithValue("@Type", "BO/REGULAR");

answered Dec 6, 2016 at 13:00

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Patrick Over a year ago

I see and what about this query?

cmd = new SqlCeCommand("SELECT PSXLaga FROM SettlementTypeSetup where SettlementType='BO/REGULAR' AND Sale/Purchase = 'Purchase'", conn);

this one is not working saying column name not found.

mybirthname Over a year ago

@Patrick you can try to wrap the [Sale/Purchase] like this, but why you define your columns this way ?

Patrick Over a year ago

i get it ,, that slash is giving me error. one more thing, should I recreate my table because I have Slash in btw sale and purchase, would it give me error in the future?

mybirthname Over a year ago

@Patrick SaleAndPurchase will be okay, if you have empty spaces between them you again need to wrap it [Sale And Purchase]

Patrick Over a year ago

I mean now I have created this table with [Sale/Purchase] it has a slash "/" in between, is that a correct way to create tables? would this give me any sort of difficulties in the future? or I should re-create my column with Sale_Purchase?

score 3 · Accepted Answer · 2016-12-06 13:14:33Z

3

This query means you want to retrieve PSXLaga from your SettlementTypeSetup table, where SettlementType equals to a given value. In your case this is BO/REGULAR. If your SettlementType is a string, you'll have to put quotes around your value like this: 'BO/REGULAR'

So your correct query would look like this:

"SELECT PSXLaga FROM SettlementTypeSetup WHERE SettlementType = 'BO/REGULAR';"

Edit: I see you also wanted to check if sale/purchase is equals to "Purchase". You can do this by adding this to your query: (I'm unsure if it likes the / in your table name though..)

"AND Sale/Purchase = 'Purchase'"

I suggest using mybirthname's answer though. It's objectively better than the query above.

Second edit: You forgot the quotes again. Incorrect:

"SELECT PSXLaga FROM SettlementTypeSetup where SettlementType="+settlementTypeComboBox.Text + " AND [Sale/Purchase]='Purchase'"

Correct:

"SELECT PSXLaga FROM SettlementTypeSetup WHERE SettlementType = '" + settlementTypeComboBox.Text + "' AND [Sale/Purchase] = 'Purchase';"

But again, try to write your code like mybirthname has shown. I don't have a lot of experience with queries in C# code.

edited Dec 6, 2016 at 13:14

answered Dec 6, 2016 at 12:58

user4189129

1 Comment

Patrick Over a year ago

what if i want to select "BO Regular using comboBox?"

cmd = new SqlCeCommand("SELECT PSXLaga FROM SettlementTypeSetup where SettlementType="+settlementTypeComboBox.Text + " AND [Sale/Purchase]='Purchase'", conn);

Collectives™ on Stack Overflow

SQL Query Error in C#

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related