Insert NULL into DB with Database Connectivity Toolkit

[Porras]

Hi all.

I'm trying to insert several 'NULL' values into a DB table using DB TOOLS INSERT DATA from Database Connectivity Toolkit. I've designed the DB so that this three fields are foreign-keys, but they're also allowed to be 'NULL' values. I don't have much experience with DBs, but I think this isn't a mistake since it's been working for a long time.

My problem comes when I try to insert 'NULL' values into these fields (see attached picture). I use DB TOOLS NULL for this.

I get the following error:
"The INSERT statement conflicted with the FOREIGN KEY constraint ..."

Could you please help me?

Thanks in advance,

Francisco

[Matthew_Kelton]

Your combined keys must be unique.  So, if you set all three to NULL, you can't do it again.  If you set one to a value, such 123, NULL, NULL, you can't repeat that combination again.  Your problem is not with trying to insert null values, but the deisgn of your database.

[Yamaeda]

Most probably Value1 is the key. Simply wiring it to a loop counter wont help as you'll start over from 0 the next time the program is run.

There is 2 easy solutions:

1 - change the database to allow duplicates (often not an option)

2 - make Value an autonumbered field (or add such a field)

3 - Read out the highest Value in the table and work from there.

/Y

[Porras]

Thanks for your replies, but, I don't understand you clearly.

Attached you can see a view of the table. Those values were inserted with other DB software and there was no problem. Column2 can be NULL as well (despite it's not shown). The primary key of the table is another column I didn't include in this snapshot to make it clearer. It has identity specification. And, as far as I know, there's no need to introduce any value to the primary key field, as it'll be completed by the DB itself.

[Yamaeda]

Then it's a contraint limitation. One of the fields dosn't allow Null. 🙂

/Y

[Porras]

I don't think that's the problem. Attached you can see the design view of the table.

[Himanshu_Goyal]

Hi,

To insert the null character into the Database, You must use the Insert query. In insert query you just pass the null character. The database property itself save the blank value as a "NULL" character.

[Porras]

Hi, Himanshu Goyal.

What do you mean with "Insert query"?, making a SQL query with the following VI?

Thanks.

[Yamaeda]

---

@Porras wrote:

I don't think that's the problem. Attached you can see the design view of the table.

---

You're not allowed to enter Nulls into Column1. Nor Column5.

If the string in Column5 is empty, doesn't it get translated to Null?

/Y

[Porras]

But I'm not inserting an empty value in column5. You can see in the first picture I attached that the inserted value is a string called "Value5" (and it isn't empty, I've just tried it). I only try to insert NULL values into columns 2, 3 and 4, the ones where NULL values are allowed, as you can see in the design view.