Solved

Syntax Error in WHERE clause?

Posted on 2012-03-19
7
418 Views
Last Modified: 2012-03-19
Hi:

We're creating a web application with VS 2010 using Visual Basic.

We'd like to update an access database table using OldeDb...

For some reason, when we run the code, the program hanged and a message: "syntax error in WHERE clause".

We've checked again and again the statement string but could not find any error. It's likely that we missed something.

Would you help to look into the code and tell us what's wrong.

Thanks a lot,

Somits

//-----------------------------


 ' Set up the connection to connect to the ACCESS database
       adbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
       adbDataSource = "Data Source =
E:\DATA_2\DESDEV\VISUAL_BASIC\PROJECTS\VB_ACCESS\Database3.accdb"

       Dim adbConnectionStr As String
       adbConnectionStr = adbProvider & adbDataSource
       adbConnection.ConnectionString = adbConnectionStr

       Dim adbOrderID As String
       Dim adbAmount As Double
       Dim adbTax As Double

       Dim adbCmdStr As String

       adbOrderID = TextBox1.Text.ToString()
       adbAmount = Convert.ToDouble(TextBox2.Text.ToString())
       adbTax = Convert.ToDouble(TextBox3.Text.ToString())

       

       adbCmdStr = "UPDATE vb_access_db3_tb2 SET Amount = " &
adbAmount & ", Tax = " & adbTax & " WHERE Order ID = '" & adbOrderID &
"'"

     

       adbCommand = New OleDbCommand(adbCmdStr, adbConnection)

       adbConnection.Open()

       Try
           adbCommand.ExecuteNonQuery()
       Catch ex As Exception
           MsgBox(ex.Message)
       End Try

       adbConnection.Close()
0
Comment
Question by:somits
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37737236
Hi,

This string

" WHERE Order ID = '" & adbOrderID &
"'"

imples that the OrderID is a text value.  Try

" WHERE Order ID = " & adbOrderID & ";"

Regards,

Bill
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 37737241
Use Brackets around Order ID if there is a space in the fieldname:

adbCmdStr = "UPDATE vb_access_db3_tb2 SET Amount = " _
   &  adbAmount & ", Tax = " & adbTax _
   & " WHERE [Order ID] = '" & adbOrderID & "'"


Also, if Order ID is numeric, use this syntax instead:

adbCmdStr = "UPDATE vb_access_db3_tb2 SET Amount = " _
   &  adbAmount & ", Tax = " & adbTax _
   & " WHERE [Order ID] = " & adbOrderID


(Note that the brackets are not needed if you avoid spaces in your field naming conventions, but they are needed if you have spaces/special characters)
0
 
LVL 9

Assisted Solution

by:sachinpatil10d
sachinpatil10d earned 150 total points
ID: 37737242
Try this

adbCmdStr = "UPDATE vb_access_db3_tb2 SET Amount = " & adbAmount & ", Tax = " & adbTax & " WHERE [Order ID] = '" & adbOrderID

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37737256
Try following this method

adbCmdStr = String.Format("UPDATE vb_access_db3_tb2 SET Amount ={0} , Tax ={1}, WHERE [Order ID] = '{2}'", adbAmount, adbTax, adbOrderID)

Open in new window

0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 37737343
I suspect amount to be a decimal, thus you may need:

SET Amount = " & Str(adbAmount) & ", Tax

/gustav
0
 
LVL 16

Assisted Solution

by:Rose Babu
Rose Babu earned 100 total points
ID: 37737344
Hi,

enclose all the values with single quote as SET Field = 'value'
and enclose where clause field Order ID with square brackets as [Order ID]. because Order ID field has a space.
Dim adbCmdStr = "UPDATE vb_access_db3_tb2 SET " & _
                "Amount = '" & adbAmount & "', " & _
	        "Tax = '" & adbTax & "' " & _
	        "WHERE [Order ID] = '" & adbOrderID & "' "

Open in new window

Test this line of code. Hope It will work.
0
 

Author Closing Comment

by:somits
ID: 37737505
Thanks so much for all your help.
We need [ ] around "Order ID". It works.

Really appreciated.

Somits
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question