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! -
dim conn as adodb.connection
-
dim strsql as string
-
-
strsql="update regdetails set title= ' " & me.title.value & " ' , surname= " " & me.surname.value & " ', firstname= ' " & me.firstname.value & " ' where regnumber= ' " & me.regnumberdisplay.value & " ' "
-
-
set conn=currentproject.connection
-
-
conn.execute strsql,,adexecutenorecords
-
-
if err.number=1 then
-
msgbox err.description
-
else
-
end if
-
-
conn.close
-
set conn=nothing
21 2734
try to use this -
-
strsql="update regdetails set title= '" & me.title.value & "' , surname= '" & me.surname.value & "', firstname= '" & me.firstname.value & "' where regnumber= '" & me.regnumberdisplay.value & "' "
-
-
conn.begintrans
-
conn.execute strsql
-
conn.committrans
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! -
dim conn as adodb.connection
-
dim strsql as string
-
-
strsql="update regdetails set title= '" & me.title.value & "' , surname= '" & me.surname.value & "', firstname= '" & me.firstname.value & "' where regnumber= '" & me.regnumberdisplay.value & "' "
-
-
set conn=currentproject.connection
-
-
conn.execute strsql,,adexecutenorecords
-
-
if err.number=1 then
-
msgbox err.description
-
else
-
end if
-
-
conn.close
-
set conn=nothing
As looked into your codes, the following grayish lines are comments. Take a look ahead. I deleted the extra ".
Rey Sean
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.
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.
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.
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... - 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... - 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".)
By everyone's, I ment that you all have this line: -
"regnumber= '" & me.regnumberdisplay.value & "' "
-
which is incorrect because it has the single quotes ' around the value.
It should be without the single qutoes ' -
"regnumber= " & me.regnumberdisplay.value & " "
-
(NOTE: I added an " at the begining to make sure it the colors came out right)
I agree with Killer42 and there may be possible regNumber in text.
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.
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)... -
Dim conn As Adodb.Connection
-
Dim strsql As String
-
-
' *** This version assumes regnumber is Text ***
-
strsql = "update regdetails set title= '" & Me.title.Value & "' , surname= '" & Me.surname.Value & "', firstname= '" & Me.firstname.Value & "' where regnumber = '" & Me.regnumberdisplay.Value & "'"
-
-
' *** This version assumes regnumber is numeric ***
-
' strsql = "update regdetails set title= '" & Me.title.Value & "' , surname= '" & Me.surname.Value & "', firstname= '" & Me.firstname.Value & "' where regnumber = " & Me.regnumberdisplay.Value
-
-
Set conn = currentproject.Connection
-
-
conn.Execute strsql, , adexecutenorecords
-
-
If Err.Number = 1 Then
-
MsgBox Err.Description
-
End If
-
-
conn.Close
-
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.
Hi,
What is your VB Version....?
For VB6, textbox control does not supprt "Value" property..
may be your is error is pointing to that.. -
strsql="update regdetails set title= ' " & me.title.Text & " ' , surname= " " & me.surname.Text & " ', firstname= ' " & me.firstname.Text & " ' where regnumber= ' " & me.regnumberdisplay.Text & " ' "
-
Regards
Veena
Hi,
Just Try this :
currentproject.connection.execute strsql
Regards
Veena
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.
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.
Since I am unfamiliar with VB6(?) syntax, I'm defering it off to the rest of you.
Two question, for the OP... - Are we dealing with VB6? We don't know what version it is yet.
- 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.
Hi Killer,
I had got a PM from OP and it is not VB6, it is VBA
Regards
Veena
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?
Hi Lotus,
I guess OP means "Originally Posted"...
Regards
Veena
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
Veena guess is correct.
Note: This shows both of you never read Posting guidelines.
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"
Sign in to post your reply or Sign up for a free account.
Similar topics
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:
...
|
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...
|
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)
|
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...
|
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"
...
|
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...
|
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...
|
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:...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
| |