Incorrect Syntax near WHERE

Question

2.00/5 (1 vote)

See more:

, +

incorrect syntax near "WHERE". Hi, I am stuck with it:confused: . Could you help me please.

string[] propertyArray = {"Property!",addressLbl.Text,proprietorlbl.Text, arealbl.Text,
        cornerlbl.Text, datebuiltlbl.Text, salerentlbl.Text,
        pricelbl.Text, commentlbl.Text,lblstatus.Text,storieslbl.Text, property_No.Text, 
        sectionNo.Text, sectionName.Text, northEast.Text, northWest.Text,southEast.Text
        , southWest.Text};


        //check for any field if changed, any 
        if (addressLbl.Text == "*" || proprietorlbl.Text == "*" || arealbl.Text == "*" ||
            cornerlbl.Text == "*" || datebuiltlbl.Text == "*" || salerentlbl.Text == "*" ||
            pricelbl.Text == "*" || commentlbl.Text == "*" || lblstatus.Text == "*" || storieslbl.Text == "*" ||
            LabelpropertyNo.Text == "*" || LabelSectionNo.Text == "*" || LabelSectionName.Text == "*" ||
            LabelNE.Text == "*" || LabelNW.Text == "*" || LabelSE.Text == "*" || LabelSW.Text == "*")            
        {
        
            string updateProperty = "UPDATE Property SET ";

            for (int c = 1; c < 18; c++)
            {
                if (propertyArray[c] == "*")
                {
                    switch (c)   //switch nested in an if and for statments
                    {
                        case 1:
                            updateProperty += "Address = @Address, "; break;
                        case 2:
                            updateProperty += "Proprietor_ID = @Proprietor_ID, "; break;
                        case 3:
                            updateProperty += "Area = @Area, "; break;
                        case 4:
                            updateProperty += "Corner_Status = @Corner_Status, "; break;
                        case 5:
                            updateProperty += "Date_Built = @Date_Built, "; break;
                        case 6:
                            updateProperty += "Sale_Rent = @Sale_Rent, "; break;
                        case 7:
                            updateProperty += "Price = @Price, "; break;
                        case 8:
                            updateProperty += "Comment = @Comment, "; break;
                        case 9:
                            updateProperty += "Sold_Rented = @Sold_Rented, "; break;
                        case 10:
                            updateProperty += "Stories = @Stories, "; break;
                        case 11:               
                            updateProperty += "Property_No = @Property_No, "; break;
                        case 12:
                            updateProperty += "Section_Number = @Section_Number, "; break;
                        case 13:
                            updateProperty += "Section_Name = @Section_Name, "; break;
                        case 14:
                            updateProperty += "Boundary_NorthEast = @Boundary_NorthEast, "; break;
                        case 15:
                            updateProperty += "Boundary_NorthWest = @Boundary_NorthWest, "; break;
                        case 16:
                            updateProperty += "Boundary_SouthEast = @Boundary_SouthEast, "; break;
                        case 17:
                            updateProperty += "Boundary_SouthWest = @Boundary_SouthWest, "; break;
                    
                    }//end of switch
                }//end of if
            }//end of for

            if (updateProperty.EndsWith(", "))
            {
                char charToTrim = ' ';
                char charToTrim2 = ',';
                
                updateProperty = updateProperty.TrimEnd(charToTrim);
                updateProperty = updateProperty.TrimEnd(charToTrim2);
            }

            updateProperty += " WHERE House_ID=" + HsUpdateIDList.SelectedItem.Value;

            SqlCommand comProperty = new SqlCommand(updateProperty, connect1);


            for (int b = 1; b < 18; b++)
            {
                if (propertyArray[b] == "*")
                {
                    switch (b)   //switch nested in an if and for statments
                    {
                        case 1:
                            comProperty.Parameters.AddWithValue("@Address", prptHsTxt.Text);
                            break;
                        case 2:
                            comProperty.Parameters.AddWithValue("@Proprietor_ID", prptHsTxt1.Text);
                            break;
                        case 3:
                            comProperty.Parameters.AddWithValue("@Area", prptHsTxt2.Text);
                            break;
                        case 4:
                            comProperty.Parameters.AddWithValue("@Corner_Status", prptHsTxt3.Text);
                            break;
                        case 5:
                            comProperty.Parameters.AddWithValue("@Date_Built", prptHsTxt4.Text);
                            break;
                        case 6:
                            comProperty.Parameters.AddWithValue("@Sale_Rent", prptHsTxt6.Text);
                            break;
                        case 7:
                            comProperty.Parameters.AddWithValue("@Price", prptHsTxt7.Text);
                            break;
                        case 8:
                            comProperty.Parameters.AddWithValue("@Comment", prptHsTxt5.Text);
                            break;
                        case 9:
                            comProperty.Parameters.AddWithValue("@Sold_Rented", status_list.SelectedValue.ToString());
                            break;
                        case 10:
                            comProperty.Parameters.AddWithValue("@Stories", prptHsTxt2_.Text);
                            break;
                        case 11:
                            comProperty.Parameters.AddWithValue("@Property_No", property_No.Text);
                            break;
                        case 12:
                            comProperty.Parameters.AddWithValue("@Section_Number", sectionNo.Text);
                            break;
                        case 13:
                            comProperty.Parameters.AddWithValue("@Section_Name", sectionName.Text);
                            break;
                        case 14:
                            comProperty.Parameters.AddWithValue("@Boundary_NorthEast", northEast.Text);
                            break;
                        case 15:
                            comProperty.Parameters.AddWithValue("@Boundary_NorthWest", northWest.Text);
                            break;
                        case 16:
                            comProperty.Parameters.AddWithValue("@Boundary_SouthEast", southEast.Text);
                            break;
                        case 17:
                            comProperty.Parameters.AddWithValue("@Boundary_SouthWest", southWest.Text);
                            break;
                    
                    }//end of switch

                }//end of if
            }//end of for


            try
            {
                connect1.Open();
                int updated2 = comProperty.ExecuteNonQuery();
                lbl1.Text = updated2.ToString() + " records updated";
            }

            catch (Exception error)
            {
                lbl1.Text = "Error Updating";
                lbl1.Text += "  " + error;
            }
            finally
            {
                connect1.Close();
                //reset textboxes
                prptHsTxt.Text = ""; prptHsTxt1.Text = ""; prptHsTxt2.Text = "";
                prptHsTxt3.Text = ""; prptHsTxt4.Text = ""; prptHsTxt5.Text = "";
                prptHsTxt6.Text = ""; prptHsTxt6.Text = ""; prptHsTxt7.Text = "";
                status_list.SelectedItem.Value = ""; prptHsTxt2_.Text = "";
                property_No.Text = ""; sectionNo.Text = ""; sectionName.Text = "";
                northEast.Text = ""; northWest.Text = ""; southEast.Text = "";
                southWest.Text = "";
                //reset lables
                addressLbl.Text = ""; proprietorlbl.Text = ""; arealbl.Text = "";
                cornerlbl.Text = ""; datebuiltlbl.Text = ""; salerentlbl.Text = "";
                pricelbl.Text = ""; commentlbl.Text = ""; lblstatus.Text = "";
                storieslbl.Text = ""; LabelpropertyNo.Text = ""; LabelSectionNo.Text = "";
                LabelSectionName.Text = ""; LabelNE.Text = ""; LabelNW.Text = "";
                LabelSE.Text = ""; LabelSW.Text = "";
            }
        }

The SQL string starts from:

string updateProperty = "UPDATE Property SET ";

and goes on through the conditions!

Posted 8-Jan-10 2:30am

techy_00

Updated 8-Jan-10 2:40am

v2

Add a Solution

9 solutions

Solution 1

What's the sql string that is being passed?

Put a break point at

SqlCommand comProperty = new SqlCommand(updateProperty, connect1);

and check

updateProperty

Post that string here please.

[Edit] I asked the entire string not where it starts, when we can see the entire sql string we might be able to determine what is wrong and from there where it goes wrong. So put that break point where I said and then check the FULL sql string and post the FULL sql string [/Edit]

Posted 8-Jan-10 2:36am

Tom Deketelaere

Updated 8-Jan-10 3:46am

v2

Solution 3

wrote:
updateProperty += " WHERE House_ID=" + HsUpdateIDList.SelectedItem.Value;

if House_id is a string-ish type then you'll need quotes around the value after the equals:

updateProperty += " WHERE House_ID='" + HsUpdateIDList.SelectedItem.Value + "'";

[EDIT]
and I believe Tom was asking for the actual SQL statement that is executed, not the stack trace from exception that is thrown.

Posted 8-Jan-10 6:34am

Jimmanuel

Updated 8-Jan-10 6:36am

v2

Solution 5

Why do you place all set variables as Parameters and where param directly?

You should definitely replace " WHERE House_ID=" + HsUpdateIDList.SelectedItem.Value;

with something like :
" WHERE House_ID=@pIdList";

And pass the value of SelectedItem to the parmeter @pIdList just like what you are doing to others. :rose:

Posted 8-Jan-10 10:09am

Abhishek Sur

Solution 7

At the core, it's simple. You need to check the generated SQL to see what's wrong with it. You need to post the SQL if you want us to debug it.

Posted 8-Jan-10 22:01pm

Christian Graus

Solution 8

My answer would be:
Check out what John Simmons / outlaw programmer said.

Posted 8-Jan-10 23:29pm

Ankur^\m/

Solution 2

Hi,

You mean this, right?

System.Data.SqlClient.SqlException was caught
Message="Incorrect syntax near the keyword 'WHERE'."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=15
LineNumber=1
Number=156
Procedure=""
Server="localhost\\SQLEXPRESS"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at UpdateHouse.updateHouseImg_Click(Object sender, EventArgs e) in c:\Documents and Settings\Administrator\Desktop\Real Estate\UpdateHouse.aspx.cs:line 498
InnerException:

Posted 8-Jan-10 6:11am

techy_00

Solution 6

Hi, sorry I can't follow what you're saying except for the following:

Something like this:

UPDATE Property SET Boundary_NorthEast = @Boundary_NorthEast, Boundary_NorthWest = @Boundary_NorthWest WHERE House_ID=21

and__

comProperty.Parameters.AddWithValue("@Boundary_NorthEast", northEast.Text); comProperty.Parameters.AddWithValue("@Boundary_NorthWest", northWest.Text);

Posted 8-Jan-10 21:29pm

techy_00

Solution 9

Aha!!!

I think I just got it.

This is strange!
The updateProperty will have this value:
"UPDATE Property SET WHERE House_ID=22"

----

Final discovery!

I got it. It was my mistake I should have assigned the values of the labels
but what I had done was assigning the values of the text boxes.

string[] propertyArray = {"Property!",addressLbl.Text,proprietorlbl.Text, arealbl.Text,
        cornerlbl.Text, datebuiltlbl.Text, 
salerentlbl.Text,pricelbl.Text, commentlbl.Text,
lblstatus.Text,storieslbl.Text, 
property_No.Text, ectionNo.Text, sectionName.Text, northEast.Text, northWest.Text,southEast.Text, southWest.Text};

WHILE IT SHOULD'VE BEEN LIKE THIS:

string[] propertyArray = {"Property!",addressLbl.Text,proprietorlbl.Text, arealbl.Text,
cornerlbl.Text, datebuiltlbl.Text, 
salerentlbl.Text,
pricelbl.Text, commentlbl.Text,lblstatus.Text,storieslbl.Text, LabelpropertyNo.Text,
LabelSectionNo.Text,LabelSectionName.Text,
LabelNE.Text,LabelNW.Text,LabelSE.Text, LabelSW.Text};

Thanks for all. :)

Posted 9-Jan-10 1:36am

techy_00

Updated 10-Jan-10 3:21am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

#realJSOP · Accepted Answer · 2010-01-08T08:53:00

Solution 4

The first thing I would do is evaluate the string in the debugger BEFORE it's sent to your stored procedure. At that point, you can paste it into SQL Manager Express and try the query there to see WHERE your problem is.

Posted 8-Jan-10 8:53am

#realJSOP