numeric db update

[Mettá]

I'm having trouble updating an Access DB

In the first db results I have a result based on ID and FieldName1
If a db record is found the following happens without any problems.

<p>id:<%=FP_FieldVal(fp_rs,"id")%><br>
name:<%=FP_FieldVal(fp_rs,"FieldName1")%><br>
ipa:<%=FP_FieldVal(fp_rs,"ipa")%></p>

<%
Dim ipa1
ipa1=ipa1+FP_FieldVal(fp_rs,"ipa")+1
%>


On the same asp page lower down I have a db update that is not working.
I get the following error
(...Syntax error in UPDATE statement.)

UPDATE TableName
Set ipa=::ipa1::
WHERE ID=::ID:: AND FieldName1='::FieldName1::'



ipa = numeric filed (Long Integer)
id = Autonumber (record id)
field1 = Text field


Any idea why this would not work?
Thanks
M

 

[Thomas A. Rowe]

Try writing the update statement like so:

UPDATE TableName
Set ipa=::ipa1::
WHERE FieldName1='::FieldName1::' and ID=::ID

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

 

[Stefan B Rusynko]

Check your fieldname case / spelling and syntax
- ID is not the same as id,

UPDATE TableName Set ipa=::ipa1:: WHERE id=::id:: AND FieldName1='::FieldName1::'

or as a string to pass in an execute separate you variables from the UPDATE string text

strSQL =
"UPDATE TableName Set ipa=" & ipa1 & " WHERE id=" & id & " AND FieldName1='" & name &"'"'

presuming in your top results you also save the all the needed variables

<%
Dim ipa1, id, name
ipa1=ipa1+FP_FieldVal(fp_rs,"ipa")+1
' not sure why you have the 1st ipa1+ in the above
id = FP_FieldVal(fp_rs,"id")
name = FP_FieldVal(fp_rs,"FieldName1")
%>




| I'm having trouble updating an Access DB
|
| In the first db results I have a result based on ID and FieldName1
| If a db record is found the following happens without any problems.
|
| <p>id:<%=FP_FieldVal(fp_rs,"id")%><br>
| name:<%=FP_FieldVal(fp_rs,"FieldName1")%><br>
| ipa:<%=FP_FieldVal(fp_rs,"ipa")%></p>
|
| <%
| Dim ipa1
| ipa1=ipa1+FP_FieldVal(fp_rs,"ipa")+1
| %>
|
|
| On the same asp page lower down I have a db update that is not working.
| I get the following error
| (...Syntax error in UPDATE statement.)
|
| UPDATE TableName
| Set ipa=::ipa1::
| WHERE ID=::ID:: AND FieldName1='::FieldName1::'
|
|
|
| ipa = numeric filed (Long Integer)
| id = Autonumber (record id)
| field1 = Text field
|
|
| Any idea why this would not work?
| Thanks
| M
|
|
| --
| ---
|
|
|

 

[Mettá]

Still no luck, I even tried...


UPDATE TableName
Set ipa=(ipa=(ipa+1))
WHERE id=::id::

and

UPDATE TableName
Set ipa=(ipa=ipa+1)
WHERE id=::id::

and

<%
Dim ipa1
ipa1=FP_FieldVal(fp_rs,"ipa")+1
%>

UPDATE TableName
Set ipa=::ipa1::
WHERE id=::id::

Argggghhh

 

[Thomas A. Rowe]

Try:

update TableName set ipa = " & ipa1 & " where FieldName1 = '" & FieldName1 & "' and ID = " & ID
(note: not ending ")


FYI: Since you are using the autonumber field, you really don't need to test for FieldName1 match,
as the autonumber field will always unique identify the record.

update TableName set ipa = " & ipa1 & " where ID = " & ID (note: not ending ")

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================