Solved

Syntax Error in WHERE clause?

Posted on 2012-03-19
7
411 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
Comment Utility
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
Comment Utility
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
Comment Utility
Try this

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

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:sachinpatil10d
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks so much for all your help.
We need [ ] around "Order ID". It works.

Really appreciated.

Somits
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now