By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Error in Update Query Syntax

P: 1
hi, am florence. i keep having a runtime error stating error in update query syntax. i have looked through my code over and over gain and seem to find no flaw. i need you guys to help me out!

Expand|Select|Wrap|Line Numbers
  1. dim conn as adodb.connection
  2. dim strsql as string
  3.  
  4. strsql="update regdetails set title= ' " & me.title.value & " ' , surname= " " & me.surname.value & " ',  firstname= ' " & me.firstname.value & " ' where regnumber= ' "  & me.regnumberdisplay.value & " ' "
  5.  
  6. set conn=currentproject.connection
  7.  
  8. conn.execute strsql,,adexecutenorecords
  9.  
  10. if err.number=1 then
  11. msgbox err.description
  12. else
  13. end if
  14.  
  15. conn.close
  16. set conn=nothing
Jan 4 '08 #1
Share this Question
Share on Google+
21 Replies


debasisdas
Expert 5K+
P: 8,127
try to use this

Expand|Select|Wrap|Line Numbers
  1.  
  2. strsql="update regdetails set title= '" & me.title.value & "' , surname= '" & me.surname.value & "',  firstname= '" & me.firstname.value & "' where regnumber= '"  & me.regnumberdisplay.value & "' "
  3.  
  4. conn.begintrans
  5. conn.execute strsql
  6. conn.committrans
Jan 4 '08 #2

lotus18
100+
P: 866
hi, am florence. i keep having a runtime error stating error in update query syntax. i have looked through my code over and over gain and seem to find no flaw. i need you guys to help me out!

Expand|Select|Wrap|Line Numbers
  1. dim conn as adodb.connection
  2. dim strsql as string
  3.  
  4. strsql="update regdetails set title= '" & me.title.value & "' , surname= '" & me.surname.value & "',  firstname= '" & me.firstname.value & "' where regnumber= '"  & me.regnumberdisplay.value & "' "
  5.  
  6. set conn=currentproject.connection
  7.  
  8. conn.execute strsql,,adexecutenorecords
  9.  
  10. if err.number=1 then
  11. msgbox err.description
  12. else
  13. end if
  14.  
  15. conn.close
  16. set conn=nothing
As looked into your codes, the following grayish lines are comments. Take a look ahead. I deleted the extra ".

Rey Sean
Jan 4 '08 #3

Plater
Expert 5K+
P: 7,872
if regnumber is the name of an integer (or numerical) column, you would want to remove the single quotes ' from around the value you are assigning to it.
Jan 4 '08 #4

Expert 5K+
P: 8,434
As looked into your codes, the following grayish lines are comments. Take a look ahead. I deleted the extra ".
If you're talking about line 4 in post #1, the quotes are all messed up. Have a look at the colour-coding.
Jan 7 '08 #5

Plater
Expert 5K+
P: 7,872
If you're talking about line 4 in post #1, the quotes are all messed up. Have a look at the colour-coding.
No, it's the same way in everyone else's too.
Numerical columns should not have single quotes ' around their values, that casts the value as a string, even though a numerical values is needed.
Jan 7 '08 #6

Expert 5K+
P: 8,434
No, it's the same way in everyone else's too.
"If everyone else jumped off a bridge, would you?" :)

Seriously, this line is messed up...
Expand|Select|Wrap|Line Numbers
  1. strsql="update regdetails set title= ' " & me.title.value & " ' , surname= " " & me.surname.value & " ',  firstname= ' " & me.firstname.value & " ' where regnumber= ' "  & me.regnumberdisplay.value & " ' "
The section & me.surname.value & is part of the string (well... part of a string), because of the extra double-quote character after surname=. Pretty sure this line won't even compile, since it includes two strings in a row with no operator to indicate what to do with them.

Try this version...
Expand|Select|Wrap|Line Numbers
  1. strsql="update regdetails set title= '" & me.title.value & "' , surname= '" & me.surname.value & "',  firstname= '" & me.firstname.value & "' where regnumber= '"  & me.regnumberdisplay.value & "' "
Note that I've removed the extra spaces inside the single quotes, which I'm fairly certain would have been included in the database. Note also, with the WHERE clause commented out before, it seems as though you would have updated every record in regdetails.

(One more thing. I just checked, and this line does not appear like this in any other post here, if that's what you meant by "everyone else's".)
Jan 8 '08 #7

Plater
Expert 5K+
P: 7,872
By everyone's, I ment that you all have this line:
Expand|Select|Wrap|Line Numbers
  1. "regnumber= '"  & me.regnumberdisplay.value & "' "
  2.  
which is incorrect because it has the single quotes ' around the value.

It should be without the single qutoes '
Expand|Select|Wrap|Line Numbers
  1. "regnumber= "  & me.regnumberdisplay.value & " "
  2.  
(NOTE: I added an " at the begining to make sure it the colors came out right)
Jan 8 '08 #8

Expert 100+
P: 487
I agree with Killer42 and there may be possible regNumber in text.
Jan 8 '08 #9

Plater
Expert 5K+
P: 7,872
I agree with Killer42 and there may be possible regNumber in text.
My original post asked if it was a numerical column, that part was glossed over and we went off on a tangent.
The OP has not yet been back to answer anything.
Jan 8 '08 #10

Expert 5K+
P: 8,434
Yes, the question as to whether it's a numeric field has yet to be answered, and is a bit of a show-stopper.

However, the line I pointed out in the original post is wrong, regardless of the field type. It simply won't compile. It doesn't even pass the initial syntax check - "Compile error: Expected: end of statement". I don't know whether this is as originally posted, or due to a later edit, but it needs to be sorted out.

Here's my reconstruction of the code in post #1, with a couple of little annoyances corrected (such as extra spaces between the single quotes)...

Expand|Select|Wrap|Line Numbers
  1. Dim conn As Adodb.Connection
  2. Dim strsql As String
  3.  
  4. ' *** This version assumes regnumber is Text ***
  5. strsql = "update regdetails set title= '" & Me.title.Value & "' , surname= '" & Me.surname.Value & "',  firstname= '" & Me.firstname.Value & "' where regnumber = '"  & Me.regnumberdisplay.Value & "'"
  6.  
  7. ' *** This version assumes regnumber is numeric ***
  8. ' strsql = "update regdetails set title= '" & Me.title.Value & "' , surname= '" & Me.surname.Value & "',  firstname= '" & Me.firstname.Value & "' where regnumber = "  & Me.regnumberdisplay.Value 
  9.  
  10. Set conn = currentproject.Connection
  11.  
  12. conn.Execute strsql, , adexecutenorecords
  13.  
  14. If Err.Number = 1 Then
  15.   MsgBox Err.Description
  16. End If
  17.  
  18. conn.Close
  19. Set conn = Nothing
This also highlights another issue, which is that code posted here should always be copied from VB so that we know it is actual code. It's obvious that the code in the first post of this thread was simply typed in here. Which means we aren't seeing the actual code, but an interpretation of it, which may have bugs added (or removed) in the process.
Jan 9 '08 #11

QVeen72
Expert 100+
P: 1,445
Hi,

What is your VB Version....?

For VB6, textbox control does not supprt "Value" property..
may be your is error is pointing to that..

Expand|Select|Wrap|Line Numbers
  1. strsql="update regdetails set title= ' " & me.title.Text & " ' , surname= " " & me.surname.Text & " ',  firstname= ' " & me.firstname.Text & " ' where regnumber= ' "  & me.regnumberdisplay.Text & " ' "
  2.  
Regards
Veena
Jan 9 '08 #12

QVeen72
Expert 100+
P: 1,445
Hi,

Just Try this :

currentproject.connection.execute strsql


Regards
Veena
Jan 9 '08 #13

Plater
Expert 5K+
P: 7,872
Hi,

What is your VB Version....?
I had completely forgotten I had been tapped for a VB forum (I was still in .NET mode)

I just got this in a PM:
hi platter,
tanx, removing the single quotes from regnumber really helped make the number of bugs i had to deal with smaller but i am having another puzzle which is: "ONE OR MORE ARGUEMENTS ARE MISSING". and it falls on this line of code:

# conn.execute StrSql , , adExecuteNoRecords

what is the possible cause(s) and what should i do to rectify it? thanks once again platter.
Since I am unfamiliar with VB6(?) syntax, I'm defering it off to the rest of you.
Good luck.
Jan 9 '08 #14

Expert 5K+
P: 8,434
For VB6, textbox control does not support "Value" property
Yeah, I didn't know what version we're dealing with, so left that alone. We're not getting a lot of feedback from the OP on this one.
Jan 10 '08 #15

Expert 5K+
P: 8,434
Since I am unfamiliar with VB6(?) syntax, I'm defering it off to the rest of you.
Two question, for the OP...
  1. Are we dealing with VB6? We don't know what version it is yet.
  2. Is the "ONE OR MORE ARGUEMENTS ARE MISSING" a runtime or compile error?
    If runtime, then it's probably the databsae engine complaining, so the VB version would be irrelevant.
    Note, if the single quotes made a difference, I'd say we're dealing with a database issue rather than VB.
Jan 10 '08 #16

QVeen72
Expert 100+
P: 1,445
Hi Killer,

I had got a PM from OP and it is not VB6, it is VBA

Regards
Veena
Jan 10 '08 #17

lotus18
100+
P: 866
Hi Killer,

I had got a PM from OP and it is not VB6, it is VBA

Regards
Veena

Hello

Could anyone explain to me what does the OP means?
Jan 10 '08 #18

QVeen72
Expert 100+
P: 1,445
Hi Lotus,

I guess OP means "Originally Posted"...

Regards
Veena
Jan 10 '08 #19

lotus18
100+
P: 866
Hi Lotus,

I guess OP means "Originally Posted"...

Regards
Veena
This is only a guessed and you are not sure. I think you are right. (LOL) But anyway thanks for replying : ) Have a nice day Veena.

Rey Sean
Jan 10 '08 #20

Expert 100+
P: 487
Veena guess is correct.
Note: This shows both of you never read Posting guidelines.
Jan 10 '08 #21

Plater
Expert 5K+
P: 7,872
Hello

Could anyone explain to me what does the OP means?
Original Poster
It gets used to help avoid gender mix-ups when using "he/she, him/her his/hers"
Jan 10 '08 #22

Post your reply

Sign in to post your reply or Sign up for a free account.