ARITHABORT error when update from asp page

Hi all,
I get the following error when I try to update the table from asp page:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
UPDATE failed because the following SET options have incorrect settings:
'ARITHABORT'
I try the same update statement in query analyzer and it works fine. The
update command is very simple: update tableA set columnA='something' where
columnID=1
when I use SET ARITHABORT OFF in query analyzer, it fails. So I thought if I
use SET ARITHABORT ON, it would be OK again; but it doesn't work anymore.
The only thing I can get it work again in query analyzer is to close it and
re-open it.
Please help me so I can get the update statement work in asp page. Thanks
very much.VNN wrote:
> I try the same update statement in query analyzer and it works fine. The
> update command is very simple: update tableA set columnA='something' where
> columnID=1
What is the data type for ColumnA a and ColumnID?
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||> update command is very simple: update tableA set columnA='something' where
> columnID=1
Can you post the 'something'?
AMB
"VNN" wrote:

> Hi all,
> I get the following error when I try to update the table from asp page:
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E14)
> UPDATE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> I try the same update statement in query analyzer and it works fine. The
> update command is very simple: update tableA set columnA='something' where
> columnID=1
> when I use SET ARITHABORT OFF in query analyzer, it fails. So I thought if
I
> use SET ARITHABORT ON, it would be OK again; but it doesn't work anymore.
> The only thing I can get it work again in query analyzer is to close it an
d
> re-open it.
> Please help me so I can get the update statement work in asp page. Thanks
> very much.
>
>|||ColumnA: varchar
ColumnID: int
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:uXH9aFiDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> VNN wrote:
where
> What is the data type for ColumnA a and ColumnID?
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||this is the actual sql statement:
UpdateSQL = "UPDATE GusMenu.Depts SET DeptName='" & strValue & "' WHERE
GusMenu.Depts.DeptID = " & rsDept("DeptID") & ";"
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:0F6EDC7F-AB85-49D6-BE37-E0487737A662@.microsoft.com...
where
> Can you post the 'something'?
>
> AMB
>
> "VNN" wrote:
>
where
if I
anymore.
and
Thanks|||VNN wrote:
> UpdateSQL = "UPDATE GusMenu.Depts SET DeptName='" & strValue & "' WHERE
> GusMenu.Depts.DeptID = " & rsDept("DeptID") & ";"
You should really use parameters instead of trying to construct a
dynamic query like this. Check out this page for a good reference on how
to do it.
http://www.uberasp.net/ArticlePrint.aspx?id=46
This may also solve your problem as well.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||still doesn't work. Is there a way I can make it work in Query Analyzer
after I set ARITHABORT OFF? Please advice.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:O9S2HYiDFHA.3416@.TK2MSFTNGP09.phx.gbl...
> VNN wrote:
> You should really use parameters instead of trying to construct a
> dynamic query like this. Check out this page for a good reference on how
> to do it.
> http://www.uberasp.net/ArticlePrint.aspx?id=46
> This may also solve your problem as well.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||VNN wrote:
> still doesn't work. Is there a way I can make it work in Query Analyzer
> after I set ARITHABORT OFF? Please advice.
When you run Profiler, what is being sent down to the SQL Server?
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||You need to post the DDL of the table(s) involved, the actual update
statement (after inclusion of any variables). In addition, you should also
examine the code for any triggers fired by the statement and the DDL of any
views that use the table. Something somewhere is dependent on the
arithabort setting. This could be related to an index on a computed column
(or an indexed view). If this is the problem, then you need to enforce this
setting when connecting to the database (or at least performing operations
involving the problem objects).
"VNN" <VNN@.hotmail.com> wrote in message
news:u8eHscsDFHA.1392@.tk2msftngp13.phx.gbl...
> still doesn't work. Is there a way I can make it work in Query Analyzer
> after I set ARITHABORT OFF? Please advice.
>
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:O9S2HYiDFHA.3416@.TK2MSFTNGP09.phx.gbl...
WHERE
>|||I have the index view link to this table. I read MS website and looks like
the index view is causing this error. I tried to set conn.Execute "SET
ARITHABORT ON" right after I open the connection but didn't work.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:eeVagvsDFHA.3648@.TK2MSFTNGP10.phx.gbl...
> You need to post the DDL of the table(s) involved, the actual update
> statement (after inclusion of any variables). In addition, you should
also
> examine the code for any triggers fired by the statement and the DDL of
any
> views that use the table. Something somewhere is dependent on the
> arithabort setting. This could be related to an index on a computed
column
> (or an indexed view). If this is the problem, then you need to enforce
this
> setting when connecting to the database (or at least performing operations
> involving the problem objects).
> "VNN" <VNN@.hotmail.com> wrote in message
> news:u8eHscsDFHA.1392@.tk2msftngp13.phx.gbl...
> WHERE
how
>