Error in Update Query Syntax

[missflorenceokosun]

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!

[code=vb]
dim conn as adodb.connectio n
dim strsql as string

strsql="update regdetails set title= ' " & me.title.value & " ' , surname= " " & me.surname.valu e & " ', firstname= ' " & me.firstname.va lue & " ' where regnumber= ' " & me.regnumberdis play.value & " ' "

set conn=currentpro ject.connection

conn.execute strsql,,adexecu tenorecords

if err.number=1 then
msgbox err.description
else
end if

conn.close
set conn=nothing[/code]

[debasisdas]

try to use this

[code=vb]

strsql="update regdetails set title= '" & me.title.value & "' , surname= '" & me.surname.valu e & "', firstname= '" & me.firstname.va lue & "' where regnumber= '" & me.regnumberdis play.value & "' "

conn.begintrans
conn.execute strsql
conn.committran s[/code]

[lotus18]

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!

[code=vb]
dim conn as adodb.connectio n
dim strsql as string

strsql="update regdetails set title= '" & me.title.value & "' , surname= '" & me.surname.valu e & "', firstname= '" & me.firstname.va lue & "' where regnumber= '" & me.regnumberdis play.value & "' "

set conn=currentpro ject.connection

conn.execute strsql,,adexecu tenorecords

if err.number=1 then
msgbox err.description
else
end if

conn.close
set conn=nothing[/code]

As looked into your codes, the following grayish lines are comments. Take a look ahead. I deleted the extra ".

Rey Sean

[Plater]

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.

[Killer42]

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.

[Plater]

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.

[Killer42]

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...
[CODE=vb]strsql="update regdetails set title= ' " & me.title.value & " ' , surname= " " & me.surname.valu e & " ', firstname= ' " & me.firstname.va lue & " ' where regnumber= ' " & me.regnumberdis play.value & " ' "[/CODE]
The section & me.surname.valu e & 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...
[CODE=vb]strsql="update regdetails set title= '" & me.title.value & "' , surname= '" & me.surname.valu e & "', firstname= '" & me.firstname.va lue & "' where regnumber= '" & me.regnumberdis play.value & "' "[/CODE]
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".)

[Plater]

By everyone's, I ment that you all have this line:
[code=vbnet]
"regnumber= '" & me.regnumberdis play.value & "' "
[/code]
which is incorrect because it has the single quotes ' around the value.

It should be without the single qutoes '
[code=vbnet]
"regnumber= " & me.regnumberdis play.value & " "
[/code]

(NOTE: I added an " at the begining to make sure it the colors came out right)

[CyberSoftHari]

I agree with Killer42 and there may be possible regNumber in text.

[Plater]

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.

[Killer42]

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)...

[CODE=vb]
Dim conn As Adodb.Connectio n
Dim strsql As String

' *** This version assumes regnumber is Text ***
strsql = "update regdetails set title= '" & Me.title.Value & "' , surname= '" & Me.surname.Valu e & "', firstname= '" & Me.firstname.Va lue & "' where regnumber = '" & Me.regnumberdis play.Value & "'"

' *** This version assumes regnumber is numeric ***
' strsql = "update regdetails set title= '" & Me.title.Value & "' , surname= '" & Me.surname.Valu e & "', firstname= '" & Me.firstname.Va lue & "' where regnumber = " & Me.regnumberdis play.Value

Set conn = currentproject. Connection

conn.Execute strsql, , adexecutenoreco rds

If Err.Number = 1 Then
MsgBox Err.Description
End If

conn.Close
Set conn = Nothing[/code]

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.

[QVeen72]

Hi,

What is your VB Version....?

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

[code=vb]
strsql="update regdetails set title= ' " & me.title.Text & " ' , surname= " " & me.surname.Text & " ', firstname= ' " & me.firstname.Te xt & " ' where regnumber= ' " & me.regnumberdis play.Text & " ' "
[/code]

Regards
Veena

[QVeen72]

Hi,

Just Try this :

currentproject. connection.exec ute strsql


Regards
Veena

[Plater]

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 , , adExecuteNoReco rds

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.

[Killer42]

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.