Solved

Syntax Error in WHERE clause?

Posted on 2012-03-19
7
419 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

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!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

695 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