Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
7
Erster Beitrag:
vor 2 Jahren, 10 Monaten
Letzter Beitrag:
vor 2 Jahren, 10 Monaten
Beteiligte Autoren:
Danny Lauwers, Paulo Oliveira, Peter Holemans

[WB19] setting NULL via Record to MSSQL nullable Column [SOLVED]

Startbeitrag von Danny Lauwers am 17.04.2015 07:23

Hi,

I have a table in MSSQL connected via OLEDB that has a column (uniqueidentifier) that has the option "Allow Nulls" marked.

The analysis of the project from this table also displays this option set correctly.
[attachment 1450 Analysis.png]

So this also looks ok.

I create a record of the destination file


TmpRecord is Record = {DestFile,indFile}


and I fill this record from other sources like this


{"pRecord."+sFieldName} = sFieldValue


Now when I detect that there is a NULL value or 00000000-0000-0000-0000-000000000000 then I would like to also put a NULL in my record variable.

I do this via:


{"pRecord."+sFieldName}..Null = true

also tried

{"pRecord."+sFieldName} = null


But when I HADD or HMODIFY the record, I get an error:

'HModify' function, syntax 1

What happened?
OLE DB access error.
Error Number = 170137

Failure saving value of < xxxxxID > item present in file.
Check whether items are supported by your provider.
Value =

Error code: 73001
Level: fatal error (EL_FATAL)
WD55 error code: 3001

System error message:
Description = Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Source = Microsoft OLE DB Provider for SQL Server
Error Number = -2147217887 (0x80040e21)

Dump of the error of 'WD190HF.DLL' module (19.0.102.2).
Identifier of detailed information (.err): 72801
Debugging information:
IEWDOLDB=113.9
Module=
Version=
Provider: SQLOLEDB
User: xxxxxxx
Data source: xxxxxxxx
Database: xxxxxxxx
Unicode supported: 1
Page code of WL: 1252
Page code of the connection: UTF-16

MDAC Version = .

[Data format]
Query Parameter Checked = =
Query Parameter Needing conversion = =
DecimalSeparator =
DateFormat =

[Cursor settings Match]
LockType = , Location = , Type = , Capacities =
LockType = , Location = , Type = , Capacities =
LockType = , Location = , Type = , Capacities =

[Recordset 1]
Settings =
CapacitiesSupported =
CapacitiesNotsupported =
HFFunctionsSupported =
HFFunctionsNotsupported =


Fonction (7,16)
Additional Information:
EIT_NATIVECODE :
EIT_ADOCODE :
EIT_BASECODE :
EIT_ODBCDESCRIPTION :
EIT_LOGICALTABLENAME :
EIT_PILEWL :


When I set the record variable to GUID null string like this


{"pRecord."+sFieldName} = "00000000-0000-0000-0000-000000000000"


Then this works, but in the DB the value is not NULL but a blanco GUID !


How can I set a real NULL via a record variable via HADD or HMODIFY to a certain column ? This should be possible i guess :confused: What I'm doing wrong here ?

Thanks
Danny

Antworten:

Re: [WB19] setting NULL via Record to MSSQL nullable Column

Hi Danny,

Not sure if this would help but did you try assigning the character directly based on the encoding scheme:
E.g. :
x = charact(0) //ASCII or ANSI scheme - Position 0 = NULL
y = ansitounicode(charact(0))

Cheers,

Peter H.

von Peter Holemans - am 17.04.2015 08:31

Re: [WB19] setting NULL via Record to MSSQL nullable Column

In your image i can't see what you mean with uniqueidentifier, is uniqueidentifier the data type in the database?

von Paulo Oliveira - am 17.04.2015 08:41

Re: [WB19] setting NULL via Record to MSSQL nullable Column

Peter,

Assigning a ansitounicode(charact(0)) does not work either ? Same error. :-(

Paulo,

Yes the MSSQL datatype is an uniqueidentifier, but because the analysis does not know this type it is defined automatically as a Text of 38 when you update or load the analysis from an existing MSSQL database.

Thanks
Danny

von Danny Lauwers - am 17.04.2015 11:47

Re: [WB19] setting NULL via Record to MSSQL nullable Column

i never used it with nulls, can you check in the SQL Server Management Studio or using some tool like wireshark what is the SQL sentence WB is generating

From my experience sometimes this the only way we can check what WB is doing when converting HADD,... to the SQL format, sometimes the convertion is very strange.

von Paulo Oliveira - am 17.04.2015 13:46

Re: [WB19] setting NULL via Record to MSSQL nullable Column

Hi Danny,

I suggest you check with Tech Support...
Alternatively, to move on until you have (or will never have) an answer from TS you might create a 'Before Insert' trigger on the SQLServer side where you modify "" to NULL for the key field. In WX you just assign "" to the field...

Just my 2 cents,

Peter H.

von Peter Holemans - am 17.04.2015 20:33

Re: [WB19] setting NULL via Record to MSSQL nullable Column

Hi,

I changed 2 things in my project to get it working:

1) Changed the use of HCOPYRECORD(A,B) to A = B to preserve the NULL's
2) Changed the data access provider to MyConnection..Provider = "SQL Server Native Client 11.0"

After this I can set NULL to the records and in the Analysis the DATE type variable is then supported, before that this was Text[10].

Hope this can help others !
Have a nice day
Danny

von Danny Lauwers - am 22.04.2015 22:23
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.