473,320 Members | 1,799 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Error in Update Query Syntax

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
21 2734
debasisdas
8,127 Expert 4TB
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
866 512MB
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
7,872 Expert 4TB
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
Killer42
8,435 Expert 8TB
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
7,872 Expert 4TB
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
Killer42
8,435 Expert 8TB
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
7,872 Expert 4TB
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
CyberSoftHari
487 Expert 256MB
I agree with Killer42 and there may be possible regNumber in text.
Jan 8 '08 #9
Plater
7,872 Expert 4TB
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
Killer42
8,435 Expert 8TB
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
1,445 Expert 1GB
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
1,445 Expert 1GB
Hi,

Just Try this :

currentproject.connection.execute strsql


Regards
Veena
Jan 9 '08 #13
Plater
7,872 Expert 4TB
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
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
1,445 Expert 1GB
Hi Killer,

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

Regards
Veena
Jan 10 '08 #17
lotus18
866 512MB
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
1,445 Expert 1GB
Hi Lotus,

I guess OP means "Originally Posted"...

Regards
Veena
Jan 10 '08 #19
lotus18
866 512MB
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
CyberSoftHari
487 Expert 256MB
Veena guess is correct.
Note: This shows both of you never read Posting guidelines.
Jan 10 '08 #21
Plater
7,872 Expert 4TB
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

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

Similar topics

3
by: Robert Mark Bram | last post by:
Hi All! I have the following two methods in an asp/jscript page - my problem is that without the update statement there is no error, but with the update statement I get the following error: ...
0
by: Morten Gulbrandsen | last post by:
C:\mysql\bin>mysql -u elmasri -pnavathe company Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 to server version: 4.1.0-alpha-max-debug Type...
0
by: Morten Gulbrandsen | last post by:
mysql> USE company; Database changed mysql> mysql> DROP TABLE IF EXISTS EMPLOYEE; -------------- DROP TABLE IF EXISTS EMPLOYEE -------------- Query OK, 0 rows affected (0.00 sec)
7
by: Jack | last post by:
Hi, I am trying to test a sql statement in Access which gives me the error as stated in the heading. The sql statement is built as a part of asp login verification, where the userid and password...
7
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" ...
19
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
2
by: technocraze | last post by:
Hi guys, I have encountered this error when updating the values to the MS Acess table. Error : Update on linked table failed. ODBC sql server error Timeout expired. MS Acess is my front end and...
2
by: Flic | last post by:
Hi, I have a basic db that I access with MySQL query browser. Everything seems fine to me but I am using this db as part of a php shopping basket and when I try to add an item I get: Notice:...
5
by: Chris Kennedy | last post by:
Hi all I'm running SQL Server 2005 Express Edition. One database One table called Sites Fields as follows: id (bigint, Identity, Primary Key) SiteName (varchar(50), allows nulls) Generation...
2
by: benicio | last post by:
The subject update failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 6' at line 5. This is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.