Connecting To MSSQL

Hi All,

I am connecting to an MSSQL databse via OLEODBC using the standard odbc driver. I seem to have a problem writing NULLS to fields in the SQL table, although NULLS are acceptable in the table. I get an odbc error when trying to insert a NULL value.

Has anyone else experienced this?

Thanks,

Milton

Hi Milton,

Personally I'll stay clear of ODBC. OLE DB is by far more stable to use with SQL Server.

Regards

Hi Milton

I never use ODBC. OLE DB works very reliably with SQL Server and performs well too. I've had no problems so far (using Windev/Webdev for a few months now.)

Regards

Justin

Thanks to those who replied - I have changed the connection to OLEDB and will use this from now on.

However, the problem still exists and I have had to set up a dummy entry in the SQL database to accomodate the problem. The code is shown below and what I would have liked to do is to set the tblElements.eleUomIDBuy to NULL if Form_ClonerQuery.eleUomIDBuy is NULL rather than having to set it as 19 which is the index number for the dummy entry.

IF Form_ClonerQuery.eleUomIDBuy>0 THEN
tblElements.eleUomIDBuy=Form_ClonerQuery.eleUomIDBuy
ELSE
tblElements.eleUomIDBuy=19
END

I will keep experimenting and thanks again for the input.

Milton

Hi again mate,

Could you post me the error message? Also, is the field in question indexed in any way?

Cheers

Hi Ben,

Yes, the field is indexed and is the database is a normalised database with tables indexed on a numeric (integer) ID field.

The error message relates to a foreign key error and is as shown here -

"Description = INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblElements_tblUnitsOfMeasures'. The conflict occurred in the database 'NewMKLSandbox' table 'tblUnitsOfMeasures' column 'uomID' "

The weird thing is that if I make a direct entry into the table via the software it will accept a blank uomID field in the table but if I try and do it via an INSERT it will not accept a blank or NULL field.

What I am actually doing is cloning an existing item in the database table and changing a few items before doing the insert. For example, I have a product called a B90 which has such additional attached data such as a unit of measure (UOM) and I want to make a copy of it and call it a B99.

Milton

Hi All,

For those that may be interested I finally solved the problem I had, connecting to an MSSQL database and not being able to set a field as NULL without getting an error message.

It had to do with my code where I was setting the field=Null

What ended up working was the following -

IF FGCStyleVariantsQuery2.fgcOrgIDDefaultSupplier>0 THEN
TblFinishedGoodsCosts.fgcOrgIDDefaultSupplier=FGCStyleVariantsQuery2.fgcOrgIDDefaultSupplier
ELSE
TblFinishedGoodsCosts.fgcOrgIDDefaultSupplier..Null=True
END
HWrite etc.

Thanks for those who suggested I use OLEDB which I am now doing and it is much better.

Milton

.../...
TblFinishedGoodsCosts.fgcOrgIDDefaultSupplier..Null = True
.../...

Thanks, that's new for me to.