SQL Server And C# WinForms error

Question

I call this method on form load ivent GetProducts(" "); This Query works fine in sql. It was working till i added WHERE When i use debugger on this line >> SqlDataReader myReader = cmd.ExecuteReader(); Can anyone advice me something?

 public void GetProducts(string find)
    {
        try
        {
            using (SqlCommand cmd = new SqlCommand("SELECT ID, BarCode, ArtNumber, ProductName, Price, SelfPrice, PriceWithOutAWD, TotalSelfPrice, UnitsInStock, " +
                                                " Comment, InputDateTime, InputQuantity, Margin, CategoryName, TypeName, ExpDate FROM GetProducts"+
                                                "WHERE BarCode LIKE '%@F%' OR ArtNumber LIKE '%@F%' OR ProductName LIKE '%@F%' OR Price LIKE '%@F%' OR Comment LIKE '%@F%' ", 
                                                new SqlConnection(Program.ConnectionString)))
            {
                cmd.Parameters.AddWithValue("@F", find);
                cmd.Connection.Open();

                SqlDataReader myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {

                    ProductTable.Rows.Add
                        (
                        (int)myReader["ID"],
                        myReader["BarCode"].ToString(),
                        myReader["ArtNumber"].ToString(),
                        myReader["ProductName"].ToString(),
                        (decimal)myReader["Price"],
                        (decimal)myReader["SelfPrice"],
                        (decimal)myReader["PriceWithOutAWD"],
                        myReader["TotalSelfPrice"].ToString(),
                        myReader["UnitsInStock"].ToString(),
                        myReader["Comment"].ToString(),
                        myReader["InputDateTime"].ToString(),
                        myReader["InputQuantity"].ToString(),
                        myReader["Margin"].ToString(),
                        myReader["CategoryName"].ToString(),
                        myReader["TypeName"].ToString(),
                        myReader["ExpDate"].ToString()
                        );
                }
                cmd.Connection.Close();
            }
        }
        catch (Exception)
        {
            MessageBox.Show(Program.MsgError1, "Acid", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }

    }

Can you specify the error that you are getting

Developer
– Developer

2011-03-15 12:20:25 +00:00
Commented Mar 15, 2011 at 12:20 — Developer
– Developer, Commented Mar 15, 2011 at 12:20

Martin Smith · Accepted Answer · 2011-03-15 13:08:10Z

5

... FROM GetProducts"+
"WHERE BarCode LIKE ...

Should be (note additional space between GetProducts and WHERE)

... FROM GetProducts "+
"WHERE BarCode LIKE ...

Also don't use

WHERE BarCode LIKE '%@F%'

Either Use

WHERE BarCode LIKE '%' + @F + '%'

Or alternatively have the parameter value contain the wild cards and use

cmd.Parameters.AddWithValue("@F", "%" + find + "%"); 
 ...
WHERE BarCode LIKE @F

If you will always be searching with a leading wildcard however you should probably use

WHERE CHARINDEX(@F,BarCode) > 0

Your query won't be able to use an index anyway and this method avoids issues if the users search for substrings that contain characters that have a special meaning in the pattern syntax.

However from the amount of columns you are searching and the leading wildcards you should probably use full text indexing for this anyway.

edited Mar 15, 2011 at 13:08

answered Mar 15, 2011 at 12:19

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

1 Comment

Martin Smith Over a year ago

@Acid needs more details than that! You also need to add a space before the WHERE

RQDQ · Accepted Answer · 2011-03-15 12:54:04Z

2

It looks like you're trying to reference a parameter inside of a string literal:

WHERE BarCode LIKE '%@F%'

In this case, I think you're stuck concatenating some sql:

" WHERE BarCode LIKE '%" + find +  "%' "

Just make sure you escape any apostrophes from find (and protect yourself from SQL Injection in general):

find = find.Replace("'","''");

answered Mar 15, 2011 at 12:54

RQDQ

15.6k2 gold badges36 silver badges61 bronze badges

2 Comments

Martin Smith Over a year ago

Deleted earlier comment. As I see you are actually suggesting concatenating the executable string. The OP is not stuck doing this. The approaches in my answer definitely work (and without causing potential SQL injection issues)

RQDQ Over a year ago

@Martin - I wasn't aware that would work. Your approach is definitely more robust.

Collectives™ on Stack Overflow

SQL Server And C# WinForms error

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related