This is a known issue that's been acknowledged by DMXzone, but I wanted to get it on the support forum as a method of bug tracking and progress on the fix for this...

ENVIRONMENT

• DW CC 2014 on Windows 8.1
• Site is classic ASP with MS SQL Server 2005/2008+
• Using very latest versions of all DMXzone extensions

ISSUE
When setting up OR refreshing a database connection, the DMXzone Database Connector extension assigns type 129 (adChar) to database fields that are actually type 200 (varchar). This causes big problems when saving form data to these fields in the database, saving blank spaces beyond the actual input into the form fields.

HOW TO FIX
Currently, you must go into the dmxConnections folder, open up the connection file, and manually change all of type 129 to type 200. Only then do you get the desired behavior when saving data to the database (just the data you entered and not extra blank spaces.

The worst part is that you must perform the above fix every time you refresh your database after making changes to it (modifying fields, adding fields, adding tables, etc.).

When is the fix for this going to be implemented?

Hello Steve,
This fix was already included in the latest update.

Well, my extensions are all updated and I did a database refresh and it still uses type 129 for varchar fields....

Please setup a new database connection. This will build a new connection file with the proper values in it.

This reply was removed on 8/6/2014 8:42:37 PM.
See the changelog

Ok, I did this - set up a new database connection.
I then looked at the file created in the dmxConnections directory and it just shows all the names for tables and views.

Next, I set up a new data source.
Again, I opened the file in the dmxConnections folder, and could see the new information was added for the table I used in the data source I set up. The varchar fields are all assigned to type 129.

If this fixed, why is it still assigning type 129 to data source fields that are varchar?

I followed your instructions. Please advise...

Just a follow up on this... Would really like a response on this!

On a test site, I created a brand new database connection, and then set up one data source for one table of a few fields - all of which are varchar type. Here's what the code for the connection file looks like.... all type 129, and from what you've told me in the past, these should be type 200. Correct?

<script runat="server" language="javascript">
// Database Type : "SQL_Server"
// Database Adapter : "mssql"
var dmxConnectionString = "Provider=SQLNCLI10;Data Source=OMITTED_FOR_THIS_EXAMPLE;
var dmxConnectionLimit = 1000000;
var dmxConnectionDebug = false;

var dmxConnectionMeta = //<<<JSON
{"allTables": ["articleTest", "contacts", "orders"], "allViews": [], "tables": {"contacts": {"columns": {"contactID": {"type": 3, "primary": 1}, "fname": {"type": 129, "size": 255, "nullable": 1}, "lname": {"type": 129, "size": 255, "nullable": 1}, "address": {"type": 129, "size": 255, "nullable": 1}, "city": {"type": 129, "size": 255, "nullable": 1}, "state": {"type": 129, "size": 30, "nullable": 1}, "zip": {"type": 129, "size": 20, "nullable": 1}, "phone": {"type": 129, "size": 30, "nullable": 1}, "email": {"type": 129, "size": 255, "nullable": 1}}, "keys": {"PK__contacts__7121FD157F60ED59": ["contactID"]}}}}
//JSON
;
</script>

Just for the record... This issue is definitely NOT solved. I still have this same problem when setting up a connection, even when creating a brand new site. MS SQL varchar fields get setup as type 129 in the connection file created by HTML5 DB.

Hi Steve,
This is a really strange issue indeed. We already mapped the type 129 to 200 in the extension code.
The issue is most probably caused by the fact that adodb and asp aren't updated anymore and probably won't detect all the modern databases correctly, as the code hasn't been updated for about 10 years... Still looking if this can fixed anyhow.

I suppose I can live with it if all I have to do is open the connection file and do a find/replace on type 129, although I assume you realize that this will come up more often as more people start using this extension.

When you rely on the extensions to do what they do, it can be easy to forget that you have to go in and manually change the data in the connection file. You could end up with support issues on this simply because people will make mistakes when doing this, and may not know that they caused a problem, but instead just assume that "something" is wrong with your extension.

Tough issue to deal with...

Hi Steve,
After further investigation, i can confirm the ADO is returning wrong file types. You can see this table:
webcoder.info/reference/mssqldatatypes.html
The values in the brackets ( ) are the ones returned by ADO and the ones without brackets are the real values, the ones that should be returned:


The only way of fixing this buggy behavior of ADO / ASP is to always rewrite 129 to 200... whether the db field varchar or not... Most probably this will be added into the next update.
Once again - classic ASP is quite dead and is no longer updated.
Even in the ASP.NET ADO is still reporting this wrong... social.msdn.microsoft.com/Forums/en-US/d1980198-d4eb-4787-b3b1-7a73a8678f32/oledb-getschemacolumns-reports-the-incorrect-datatype?forum=adodotnetdataproviders