error with Sql Update statement

Question

0.00/5 (No votes)

See more:

Hello,
I have an error in my vba code but I don't know where.. help me please

VB

Set MyTable2 = MyDB.OpenRecordset("SELECT * FROM [tblRoutingMain] " & _
                                "WHERE ((([tblRoutingMain].[QUOTE NUMBER])='" & [QUOTE NUMBER] & "') AND (([tblRoutingMain].COMPPN)='" & COMPPN & "')) " & _
                                "ORDER BY tblRoutingMain.ROUTING ASC;")
    
  If Not MyTable2.EOF Then MyTable2.MoveFirst
  
 
      strUpdateQuery = "UPDATE tblRoutingMain SET  tblRoutingMain.[SEQ] = '" & MyTable2.Fields("SEQ") & "', " & _
                       "WHERE ((([tblRoutingMain].[QUOTE NUMBER])='" & [QUOTE NUMBER] & "CONT1" & "') AND (([tblRoutingMain].COMPPN)='" & COMPPN & "') AND (tblHardwareCost.[ROUTING]='" & MyTable2.Fields("ROUTING") & "'))" & _
                       "ORDER BY tblRoutingMain.ROUTING ASC;"
      CurrentDb.Execute strUpdateQuery, dbFailOnError

Posted 26-Mar-13 2:46am

juju5454

Updated 26-Mar-13 2:55am

Shanalal Kasim

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

StianSandberg · Answer 1 · 2013-03-26T02:51:00

Solution 1

You really should consider using SqlParameters. It's easier to write, easier to read/debug and more secure. Adding variables as a part of a sql-query will make it vulnerable for sql-injections.

But to debug this, the first thing I would do is to see the result of strUpdateQuery variable before it is executed. You will see what's wrong if you see what you are trying to execute.

And why are you doing ordering in an update query?

SQL

"ORDER BY tblRoutingMain.ROUTING ASC"

Posted 26-Mar-13 2:51am

StianSandberg

Updated 26-Mar-13 2:52am

v2

Comments

juju5454 26-Mar-13 9:04am

It is my first SQL code. About strUpdateQuery, it looks good with variable.
I put an order by to do like in MyTable2

How can I use a SqlParameters ?

StianSandberg 26-Mar-13 9:09am

You can't do an order by with update. Remove your order by clause. And please post the resulting sql-query. What does your sql looks like before it's executed?

juju5454 26-Mar-13 14:31pm

could you take a look ?

juju5454 · Answer 2 · 2013-03-26T03:28:00

I obtained this code :

<pre lang="sql">Set MyTable2 = MyDB.OpenRecordset("SELECT * FROM [tblRoutingMain] " & _
                               "WHERE ((([tblRoutingMain].[QUOTE NUMBER])='" & [QUOTE NUMBER] & "') AND (([tblRoutingMain].COMPPN)='" & COMPPN & "')) " & _
                               "ORDER BY tblRoutingMain.ROUTING ASC;")

 If Not MyTable2.EOF Then MyTable2.MoveFirst


     strUpdateQuery = "UPDATE tblRoutingMain SET  tblRoutingMain.[SEQ] = '" & MyTable2.Fields("SEQ") & "' " & _
                      "WHERE ((([tblRoutingMain].[QUOTE NUMBER])='" & [QUOTE NUMBER] & "CONT1" & "') AND (([tblRoutingMain].COMPPN)='" & COMPPN & "') AND (tblHardwareCost.[ROUTING]='" & MyTable2.Fields("ROUTING") & "'))"

     CurrentDb.Execute strUpdateQuery, dbFailOnError

I have an other error.. to few parameters. expected 1