how to handle null values in SQL query

[purplehaze1]

how to handle null values while doing insert/update? here's my problem, the following SQL fails:

INSERT INTO t_customer (cust_cde, cust_name, m_ind, start_dte, lst_upd_id)
VALUES (,'Tata Young','','2/4/2005','sResh12')

db design:

cust_cde is smallint type in db that allows null.
cust_ind is char(1)

program creating SQL:

For Each aColumn In aTable.Columns
If (aColumn.Unique) Then
strSQLWhere = String.Format(" WHERE {0} = {1}", _
aColumn.ColumnName, aDataRow(aColumn))
ElseIf (aColumn.DataType Is System.Type.GetType("System.String")) Then
strSQL &= String.Format(" {0} = '{1}'", _
aColumn.ColumnName, aDataRow(aColumn))
Else
strSQL &= String.Format(" {0} = {1}", _
aColumn.ColumnName, aDataRow(aColumn))
End If

If (aCounter < aColumnCount) Then
strSQL &= ", "
End If

aCounter += 1
Next


Thanks,

 

[shatch]

Try something like...

For Each aColumn In aTable.Columns
if aDataRow(aColumn) = "" then
strSQLWhere = String.Format(" WHERE {0} = NULL", _
aColumn.ColumnName)
If (aColumn.Unique) Then
strSQLWhere = String.Format(" WHERE {0} = {1}", _
aColumn.ColumnName, aDataRow(aColumn))
ElseIf (aColumn.DataType Is System.Type.GetType("System.String")) Then
strSQL &= String.Format(" {0} = '{1}'", _
aColumn.ColumnName, aDataRow(aColumn))
Else
strSQL &= String.Format(" {0} = {1}", _
aColumn.ColumnName, aDataRow(aColumn))
End If

If (aCounter < aColumnCount) Then
strSQL &= ", "
End If

aCounter += 1
Next

 

[chrissie1]

this

WHERE {0} = NULL

should be

WHERE {0} IS NULL

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting