Updating external database

[Leslie Bell]

I have an Actinic Business shop that is still running in test mode - it will use netbanx for credit card payments. It is linked to an external SQL Server database but when I make a test purchase using either 'netbanx' or the 'credit card capture for later processing' payment methods, the stockinhand in the database does not get altered.

What would make actinic update the stockinhand?

[Jan]

Stock levels are updated automatically in two places, firstly when the order is downloaded and again if you cancel order lines.

If it is not being updated it would because either your linking is not correct or you do not have stock monitoring enabled for the product you are purchasing.

[cdicken]

Also make sure you have 'Disable Automatic Stock Monitoring' un-ticked in 'Business Settings | Orders'.

[Leslie Bell]

Thanks to those who have replied. I have to leave this till after the Christmas break, now.

[Leslie Bell]

When I retrieve orders from the website, I get the message 'updating stock levels' followed by an alert pop-up saying 'Actinic Ecommerce was unable to adjust some or all stock levels. The error was Data type mismatch in criteria expression'.

All data types for fields in the SQL Server external database are varchar, including the field 'stockinhand' which is mapped to 'Stock in hand' in actinic. There is also a varchar field called 'enabled' which maps to 'Stock checks enabled' in Actinic, and that has the value 1.

What could be required to make this work? Note that if I manually change the stock level in Actinic, it is reflected in the external database correctly, it is just that 'retrieve orders' brings this error.

Any clues to a solution eagerly sought.


:)

[Jan]

It's odd that it works in one place but not the other - but I think that maybe the root of your problem lies in the fact that you should be linking to a numeric field rather than a varchar.

Regards,

[Leslie Bell]

Thanks for your reply, Jan. I too would have thought a numeric data type best, but Actinic version 5 help says:

If you are using MS SQL server then you must use VARCHAR data type for the columns of the linked table. With other database systems use the equivalent syntax. This is so that a value in the external table, which is shorter than the size of its corresponding table column, will not be right-padded to the size of the column.

I wonder if there is a clue there about the size of the column - it is rather larger than needed in our external table.

Thanks for your interest.

[Jan]

Originally posted by Leslie Bell
If you are using MS SQL server then you must use VARCHAR data type for the columns of the linked table. With other database systems use the equivalent syntax. This is so that a value in the external table, which is shorter than the size of its corresponding table column, will not be right-padded to the size of the column.
I might be wrong but I'm fairly sure that sentence is meant to refer to text fields rather than numeric fields. What happens if you try to link it to a numeric field?

Regards,

[Leslie Bell]

Well I don't know why if it works manually it shouldn't work when retrieving, but I am now awaiting advice from actinic cover, so I won't try anything till I hear from them.

[Leslie Bell]

The puzzle was solved when Actinic admitted that their help files, which said use text fields in excel and varchar fields in SQL databases, was misleading. The numeric fields should be integer in SQL databases, even including the 'stock checks enabled' field which can be either 0 or 1 and you would expect to be a bit or boolean.

Thanks for your interest.