• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

Update access via SQL?

I am trying to upadate and access db with SQL and I keep running into Update syntax error?  

Here is the code I am using

           


           sqlqry = "UPDATE eurusd SET HIGH =" + Str(vHigh) + ", LOW =" + Str(vLow) + ", OPEN =" + Str(vOpen) + ", CLOSE =" + Str(vClose) + ", TIME =" + Str(vTime) + " WHERE ID =" + Str(ry)
           Set RS = Conn.Execute(sqlqry)


when I print the string out eveything seems to be fine? Not sure what I am doing wrong here?
0
bliesveld
Asked:
bliesveld
  • 7
  • 5
  • 3
  • +1
2 Solutions
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Should you values for HIGH, LOW, OPEN, CLOSE, etc be in quotation marks?

e.g.

UPDATE eurusd SET HIGH = "something", LOW = "something else" WHERE ID=7

or even
0
 
[ fanpages ]IT Services ConsultantCommented:
UPDATE eurusd SET HIGH = "something", LOW = "something else" WHERE ID="7"

?

(Sorry, I accidentally submitted before I'd finished typing)
0
 
ShauliCommented:
All non-numeric fields should be surrounded by single quotes. All dates should be surrounded by # sign for access db, or single quote for sql server. All numeric fields should not besurrounded at all:

sqlqry = "UPDATE eurusd SET HIGH ='" & Str(vHigh) & "', LOW ='" & Str(vLow) & "', OPEN ='" & Str(vOpen) & "', CLOSE ='" & Str(vClose) & "', TIME ='" & Str(vTime) & "' WHERE ID ='"  & Str(ry) & "'"

S


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!

 
bliesveldAuthor Commented:

- Shauli

I updated my sql string to fit the access syntax and still get the same error?  

New string

sqlqry = "UPDATE eurusd SET HIGH = " & vHigh & ", LOW = " & vLow & ", OPEN = " & vOpen & ", CLOSE = " & vClose & ", TIME = #" & vTime & "# WHERE ID =" & ry
0
 
PePiCommented:
i assume that all the variables are numeric except for vTIME? and are all the other fields numeric too? except for vClose of course which is date/time

if so, then:

sqlqry = "UPDATE eurusd SET HIGH = " & vHigh & ", LOW = " & vLow & ", OPEN = " & vOpen & ", CLOSE = " & vClose & ", TIME = " & CDate(vTime) & " WHERE ID =" & ry


i like using the CDate function, just my preference.
0
 
ShauliCommented:
I take it that HIGH, LOW, OPEN, CLOSE and ID are all numeric fields?

S
0
 
ShauliCommented:
PePi, now you drew first :)

S
0
 
bliesveldAuthor Commented:
yes they are.
0
 
PePiCommented:
lol!
0
 
[ fanpages ]IT Services ConsultantCommented:
sqlqry = "UPDATE eurusd SET HIGH =" + Str(vHigh) + ", LOW =" + Str(vLow) + ", OPEN =" + Str(vOpen) + ", CLOSE =" + Str(vClose) + ", TIME =#" + Format$(vTime,"d-mmm-yyyy") + "# WHERE ID =" + Str(ry)
           Set RS = Conn.Execute(sqlqry)
0
 
ShauliCommented:
Hold the horses, guys, TIME is a built in function. You know 'myTime=Time()'...
So, first question would be, is TIME a DATE type field? and the second one would be to change the field name to something like aTime, or bTime, just not TIME.

Let us know,

S
0
 
PePiCommented:
CLOSE is numeric not of date/time datatype?
0
 
ShauliCommented:
Now when you ask PePi, Open and Close are also built in functions...


S
0
 
PePiCommented:
AHA! I think Shauli has something. He's correct, either change the name of the field TIME to something else or enclose it in brackets "[]" like ...., [TIME] = " ....
0
 
PePiCommented:
lol. yes they are. and one more thing. if CLOSE is suppose to be a date/time, shouldn't OPEN be one too?
0
 
ShauliCommented:
Is there a host somewhere near here? We have a lot of questions... :)

S
0
 
bliesveldAuthor Commented:
Yup that fixed it....
0
 
ShauliCommented:
Way to go, I like it... :)

S
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now