0

FlexGrid

While saving the flexgrid empty cell values, it is saving as 0 in table

For Example

Flexgrid

ID Value1 Value2

001 1 
002 
....

Saving...

Query

With flexgrid

 insert into table1 values (NULLIF('" & .TextMatrix(i, 0) & "', '0'), NULLIF('" & .TextMatrix(i, 1) & "', '0'), NULLIF('" & .TextMatrix(i, 2) & "', '0'))

End with

Table1

ID Value1 Value2

001 1 0
002 0 0

It should not allow save '0' values in the table, It should save as the null column.

Expected Output

ID Value1 Value2

001 1 
002

How to solve the probelm.

Need Help

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Gopal
  • 11,712
  • 52
  • 154
  • 229

2 Answers2

3

Assuming your columns are int, you are sending an empty string is implicitly converted to zero when stored. So your NULLIF needs to test for empty string. The zero occurs after the NULLIF has ben processed.

Also: Do not concatenate strings to build SQL statement. Parametrise to avoid SQL injection

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
1

This might be better handled on the server side e.g. 

CREATE PROCEDURE AddThing
@ID INTEGER, 
@Value1 INTEGER = NULL,
@Value2 INTEGER = NULL
AS
INSERT INTO table1 VALUES (@ID, NULLIF(@Value1, 0), NULLIF(@Value2, 0));

Then call the proc from VB using a Command object with strongly typed Parameter objects,

onedaywhen
  • 55,269
  • 12
  • 100
  • 138