Very Computer

My ActiveX Script is the following:

Function Main()

set conn = CreateObject ("ADODB.Connection")
conn.open ="Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=InventoryDW;Data Source=SQLDB"

 Set rsItemLedger = CreateObject ("ADODB.Recordset")
 rsItemLedger.Open "Select * From FACT_ITEM_CONVERSION Order By
item_number", conn

  rsItemLedger.movefirst
 do while not rsItemLedger.eof

     if rsItemLedger("trans_qty") = 0 and rsItemLedger("extended_cost") = 0
then
        rsItemLedger("pricing_uom") = rsItemLedger("trans_um")
        rsItemLedger("pricing_qty") = 0
        rsItemLedger("trans_unit_Cost") = 0
        rsItemLedger.update
  end if

 rsItemLedger.movenext
 loop

rsItemLedger.close

Set rsItemLedger= nothing
Set conn= nothing

End Function

The errors message display after DTS Execution is the following:
Error Source : ADODB.Recordset

Error Description : Error Code : 0
Error Source = ADODB.Recordset
Error Description : Current Recordset does not support udating. This may be
a limitation
                             of the provider, or of the selected locktype.

Questions:-
- Any limitation of ADO ActiveX?
- How do I solve this problem?

Many thanks.

Hi,

I'd rather use SQL (see below)

Quote:>-----Original Message-----
>My ActiveX Script is the following:

>Function Main()

>set conn = CreateObject ("ADODB.Connection")
>conn.open ="Provider=SQLOLEDB;Integrated

Security=SSPI;Persist Security

Quote:>Info=False;Initial Catalog=InventoryDW;Data Source=SQLDB"

conn.Execute "Update FACT_ITEM_CONVERSION set
pricing_uom=trans_um, pricing_qty = 0, trans_unit_Cost = 0
where trans_qty = 0 and extended_cost=0"

Set conn= nothing

End Function
======================================================
That's it. The rest is not needed.

Quote:> Set rsItemLedger = CreateObject ("ADODB.Recordset")
> rsItemLedger.Open "Select * From FACT_ITEM_CONVERSION
Order By
>item_number", conn

>  rsItemLedger.movefirst
> do while not rsItemLedger.eof

>     if rsItemLedger("trans_qty") = 0 and rsItemLedger

("extended_cost") = 0

Quote:>then
>        rsItemLedger("pricing_uom") = rsItemLedger
("trans_um")
>        rsItemLedger("pricing_qty") = 0
>        rsItemLedger("trans_unit_Cost") = 0
>        rsItemLedger.update
>  end if

> rsItemLedger.movenext
> loop

>rsItemLedger.close

>Set rsItemLedger= nothing
>Set conn= nothing

>End Function

>The errors message display after DTS Execution is the
following:
>Error Source : ADODB.Recordset

>Error Description : Error Code : 0
>Error Source = ADODB.Recordset
>Error Description : Current Recordset does not support

udating. This may be

Quote:>a limitation
>                             of the provider, or of the
selected locktype.

>Questions:-
>- Any limitation of ADO ActiveX?
>- How do I solve this problem?

>Many thanks.

>.

Forget the recordset and use TSQL

UPDATE FACT_ITEM_CONVERSION
SET pricing_uom = trans_um,
 pricing_qty = 0,
 trans_unit_Cost = 0
WHERE
trans_qty = 0 and extended_cost = 0

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE, MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community for SQL Server
professionals - http://www.sqlpass.org

Quote:> My ActiveX Script is the following:

> Function Main()

> set conn = CreateObject ("ADODB.Connection")
> conn.open ="Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=InventoryDW;Data Source=SQLDB"

>  Set rsItemLedger = CreateObject ("ADODB.Recordset")
>  rsItemLedger.Open "Select * From FACT_ITEM_CONVERSION Order By
> item_number", conn

>   rsItemLedger.movefirst
>  do while not rsItemLedger.eof

>      if rsItemLedger("trans_qty") = 0 and rsItemLedger("extended_cost") =
0
> then
>         rsItemLedger("pricing_uom") = rsItemLedger("trans_um")
>         rsItemLedger("pricing_qty") = 0
>         rsItemLedger("trans_unit_Cost") = 0
>         rsItemLedger.update
>   end if

>  rsItemLedger.movenext
>  loop

> rsItemLedger.close

> Set rsItemLedger= nothing
> Set conn= nothing

> End Function

> The errors message display after DTS Execution is the following:
> Error Source : ADODB.Recordset

> Error Description : Error Code : 0
> Error Source = ADODB.Recordset
> Error Description : Current Recordset does not support udating. This may
be
> a limitation
>                              of the provider, or of the selected locktype.

> Questions:-
> - Any limitation of ADO ActiveX?
> - How do I solve this problem?

> Many thanks.

rsItemLedger.Open Select * From FACT_ITEM_CONVERSION Order By item_number,
sconn, adOpenKeyset, adLockOptimistic, adCmdTable

Will open an updateable recordset in VB, you will need to define the
constants for this to work in VBS

-Euan

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Quote:> My ActiveX Script is the following:

> Function Main()

> set conn = CreateObject ("ADODB.Connection")
> conn.open ="Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=InventoryDW;Data Source=SQLDB"

>  Set rsItemLedger = CreateObject ("ADODB.Recordset")
>  rsItemLedger.Open "Select * From FACT_ITEM_CONVERSION Order By
> item_number", conn

>   rsItemLedger.movefirst
>  do while not rsItemLedger.eof

>      if rsItemLedger("trans_qty") = 0 and rsItemLedger("extended_cost") =
0
> then
>         rsItemLedger("pricing_uom") = rsItemLedger("trans_um")
>         rsItemLedger("pricing_qty") = 0
>         rsItemLedger("trans_unit_Cost") = 0
>         rsItemLedger.update
>   end if

>  rsItemLedger.movenext
>  loop

> rsItemLedger.close

> Set rsItemLedger= nothing
> Set conn= nothing

> End Function

> The errors message display after DTS Execution is the following:
> Error Source : ADODB.Recordset

> Error Description : Error Code : 0
> Error Source = ADODB.Recordset
> Error Description : Current Recordset does not support udating. This may
be
> a limitation
>                              of the provider, or of the selected locktype.

> Questions:-
> - Any limitation of ADO ActiveX?
> - How do I solve this problem?

> Many thanks.