--- In flexcoders@yahoogroups.com, "bredwards358" <[EMAIL PROTECTED]>
wrote:
>
> Okay, I discovered the solution to my problem. All I had to do was
just
> check the length of the dataProvider before doing an insert, if the
> length of the arraycollection was 0(no entries) then it would amend
the
> insert statement to insert a zero into the primary key column. If
the
> length was anything but zero, it would insert the statement
normally,
> allowing the primary key column to increment normally. Here's the
code I
> used to do this:
>
> providerLength = model.prodMaintData.length;
> if(providerLength == 0)
> {
> sqlText = "INSERT INTO Up18Products(UniqueID,
ProductID,
> ProductName, Barcode, ReorderUnit, ReorderQty, Vendor, "
> + "Cost, ListCost, Class, Billable, Status,
> ToPrint, DateImported, DateUpdated," +
> "RptQtyUsed, RptPercentUsed)" +
> "VALUES(0, :productID, :productName, :Barcode,
> :reorderUnit, :reorderQty, :Vendor, :Cost, :listCost, :Class," +
> ":Billable, :Status, :toPrint, :dateImported,
> :dateUpdated," +
> ":rptQtyUsed, :rptPercentUsed)";
> }
> else
> {
> sqlText = "INSERT INTO Up18Products(ProductID,
ProductName,
> Barcode, ReorderUnit, ReorderQty, Vendor, " +
> "Cost, ListCost, Class, Billable, Status,
ToPrint,
> DateImported, DateUpdated," +
> "RptQtyUsed, RptPercentUsed)" +
> "VALUES(:productID, :productName, :Barcode,
> :reorderUnit, :reorderQty, :Vendor, :Cost, :listCost, :Class," +
> ":Billable, :Status, :toPrint, :dateImported,
> :dateUpdated," +
> ":rptQtyUsed, :rptPercentUsed)";
> }
>
> The rest you can probably guess. So far this seems to work
perfectly, if
> for any reason you don't think it will in the future, don't
hesitate to
> tell me.
It sounds like you're expecting that your primary key will always be
the same as your index within the dataProvider. It won't! Assume
for instance that you insert 10 records into the table, then you
delete record 10. If your database is relational (let's hope it is),
likely there will be other tables with a foreign key that points to
the pk's in this table. So you can't reassign them (and I doubt the
db would let you anyway). Now, your table will have these UniqueID's:
0, 1, 2, 4, 5, 6, 7, 8, 9.
Instead, you should be looking at the relative position of the
returned record within the returned Array (data) that represents the
recordset. You can probably either add this as a property to each
array item in a loop or you may be able to use a subquery to add a
field that reflects the order of the item within the recordset. I'm
not sure if SQLLite will allow such a subquery, but it might be worth
a try.
HTH;
Amy
