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
Solved

Syntax Error in WHERE clause?

Posted on 2012-03-19
7
415 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
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 49

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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