Click here to Skip to main content
14,929,864 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Debugger says incorrect syntax near 'where', but in the text visualizing for my query text its working code.

C#
if (string.IsNullOrEmpty(make) && (string.IsNullOrEmpty(model)) && (string.IsNullOrEmpty(color)) && (string.IsNullOrEmpty(min)) && (string.IsNullOrEmpty(max)) && (string.IsNullOrEmpty(miles)))
                {
                    SqlCommand updateDataGridViewCmd = new SqlCommand("select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic from car join makemodel as m ON m.mmid = car.mmid join color ON  car.colorid = color.colorid join carlot ON  carlot.carid = car.carid; ", sqlCon);

                    dt.Load(updateDataGridViewCmd.ExecuteReader());

                }
                else
                {
                    StringBuilder sqlCommandText = new StringBuilder();
                    sqlCommandText.Append("select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic from car join makemodel as m ON m.mmid = car.mmid join color ON  car.colorid = color.colorid join carlot ON  carlot.carid = car.carid where");
                    string CommandText = sqlCommandText.ToString();

                    SqlCommand updateDataGridViewCmd = new SqlCommand(CommandText, sqlCon);
                    updateDataGridViewCmd.Parameters.AddWithValue("@make", make);
                    updateDataGridViewCmd.Parameters.AddWithValue("@model", model);
                    updateDataGridViewCmd.Parameters.AddWithValue("@min", min);
                    updateDataGridViewCmd.Parameters.AddWithValue("@max", max);
                    updateDataGridViewCmd.Parameters.AddWithValue("@mileage", miles);
                    updateDataGridViewCmd.Parameters.AddWithValue("@color", color);



                    if (!string.IsNullOrEmpty(make))
                    {
                        sqlCommandText.Append(" m.make = @make");
                        CommandText = sqlCommandText.ToString();
                    }

                    if (!string.IsNullOrEmpty(model))
                    {
                        sqlCommandText.Append(" OR m.model = @model");
                        CommandText = sqlCommandText.ToString();
                    }

                    if (!string.IsNullOrEmpty(min))
                    {
                        sqlCommandText.Append(" car.price between @min");
                        CommandText = sqlCommandText.ToString();
                        if (!string.IsNullOrEmpty(max))
                        {
                            sqlCommandText.Append(" AND @max");
                            CommandText = sqlCommandText.ToString();

                        }
                        else 
                        {
                            sqlCommandText.Append(",");
                            CommandText = sqlCommandText.ToString();
                        }
                    }

                    if (!string.IsNullOrEmpty(color))
                    {
                        sqlCommandText.Append(" color.color = @color,");
                        CommandText = sqlCommandText.ToString();
                    }

                    if (!string.IsNullOrEmpty(miles))
                    {
                        sqlCommandText.Append(" car.price <= @mileage");
                        CommandText = sqlCommandText.ToString();
                    }
                    sqlCommandText.Append(";");
                    CommandText = sqlCommandText.ToString();
                    dt.Load(updateDataGridViewCmd.ExecuteReader());
                    dataGridView1.DataSource = dt;

also I know code is not working for all conditions

What I have tried:

I've tried to do min and max only as well as make and model only.
I've taken the text from commandtext and through it into my database query directly and it worked.
Posted
Updated 28-Apr-21 22:53pm
Comments
Richard MacCutchan 28-Apr-21 7:28am
   
There is an easy way to find out what is wrong, and that is to display the complete string when you have added all the parameters. Also, there is no need to keep saving the string from the stringbuilder. Do it just once at the end when all changes have been added.

Changes to your 'CommandText' variable after you newed up a SqlCommand have no effect on the sqlcommand itself.
You should create the sqlcommand after you finished composing your sql-string.

Cheers
   
Quote:
C#
if (!string.IsNullOrEmpty(make))
{
    sqlCommandText.Append(" m.make = @make");
    CommandText = sqlCommandText.ToString();
}

if (!string.IsNullOrEmpty(model))
{
    sqlCommandText.Append(" OR m.model = @model");
    CommandText = sqlCommandText.ToString();
}

if (!string.IsNullOrEmpty(min))
{
    sqlCommandText.Append(" car.price between @min");
    CommandText = sqlCommandText.ToString();
    if (!string.IsNullOrEmpty(max))
    {
        sqlCommandText.Append(" AND @max");
        CommandText = sqlCommandText.ToString();

    }
    else 
    {
        sqlCommandText.Append(",");
        CommandText = sqlCommandText.ToString();
    }
}

if (!string.IsNullOrEmpty(color))
{
    sqlCommandText.Append(" color.color = @color,");
    CommandText = sqlCommandText.ToString();
}

if (!string.IsNullOrEmpty(miles))
{
    sqlCommandText.Append(" car.price <= @mileage");
    CommandText = sqlCommandText.ToString();
}
Just thinking through some of the possible variations your code will generate, does this look like a valid WHERE clause to you?
SQL
WHERE OR m.model = @model car.price between @min, color.color = @color, car.price <= @mileage
Also, you never update the CommandText property of the SqlCommand; you just repeatedly update the CommandText variable. Changes to that variable will not affect the CommandText property of the SqlCommand.

Try something more like this:
C#
SqlCommand updateDataGridViewCmd = new SqlCommand(string.Empty, sqlCon);

StringBuilder sqlCommandText = new StringBuilder();
sqlCommandText.Append("select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic from car join makemodel as m ON m.mmid = car.mmid join color ON  car.colorid = color.colorid join carlot ON  carlot.carid = car.carid");

if (!string.IsNullOrEmpty(make))
{
    sqlCommandText.Append(updateDataGridViewCmd.Parameters.Count == 0 ? " where " : " or ");
    updateDataGridViewCmd.Parameters.AddWithValue("@make", make);
    sqlCommandText.Append("m.make = @make");
}

if (!string.IsNullOrEmpty(model))
{
    sqlCommandText.Append(updateDataGridViewCmd.Parameters.Count == 0 ? " where " : " or ");
    updateDataGridViewCmd.Parameters.AddWithValue("@model", model);
    sqlCommandText.Append("m.model = @model");
}

if (!string.IsNullOrEmpty(min))
{
    sqlCommandText.Append(updateDataGridViewCmd.Parameters.Count == 0 ? " where " : " or ");
    updateDataGridViewCmd.Parameters.AddWithValue("@min", min);
    
    if (!string.IsNullOrEmpty(max))
    {
        sqlCommandText.Append(updateDataGridViewCmd.Parameters.Count == 0 ? " where " : " or ");
        updateDataGridViewCmd.Parameters.AddWithValue("@max", max);
        sqlCommandText.Append("car.price Between @min And @max");
    }
    else
    {
        sqlCommandText.Append("car.price >= @min");
    }
}
else if (!string.IsNullOrEmpty(max))
{
    sqlCommandText.Append(updateDataGridViewCmd.Parameters.Count == 0 ? " where " : " or ");
    updateDataGridViewCmd.Parameters.AddWithValue("@max", max);
    sqlCommandText.Append("car.price <= @max");
}

if (!string.IsNullOrEmpty(miles))
{
    sqlCommandText.Append(updateDataGridViewCmd.Parameters.Count == 0 ? " where " : " or ");
    updateDataGridViewCmd.Parameters.AddWithValue("@mileage", miles);
    sqlCommandText.Append("car.mileage <= @mileage");
}

if (!string.IsNullOrEmpty(color))
{
    sqlCommandText.Append(updateDataGridViewCmd.Parameters.Count == 0 ? " where " : " or ");
    updateDataGridViewCmd.Parameters.AddWithValue("@color", color);
    sqlCommandText.Append("color.color = @color");
}

sqlCommandText.Append(";");
updateDataGridViewCmd.CommandText = sqlCommandText.ToString();
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900