CODEWITHSUNDEEP
sqlsql-servervb6sql-server-2000
Gopal
Gopal

Reputation: 11982

Zero should not save in the table

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

Upvotes: 0

Views: 166

Answers (2)

onedaywhen
onedaywhen

Reputation: 57053

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,

Upvotes: 1

gbn
gbn

Reputation: 432421

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

Upvotes: 3

Related Questions