Solved

Syntax Error in WHERE clause?

Posted on 2012-03-19
7
417 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
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vb.net deleting excel sheet in workbook 11 30
ASP.NET Built-In Report Creator / Viewer 5 18
Calculating percentage 2 28
Access query error 3 24
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

735 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