>I am executing a procedure and passing empty strings from a VB application. Is there an easy way to set my fields to treat these empty strings as nulls and not allow them. I already have the Allow Nulls field attribute unchecked for these fields? In other words, I want the database to not allow empty strings in specific fields.
The most efficient way to do it would be to validate the data in your VB code, and convert any empty strings to the value null, and then let SQL handle the rest (by generating an error). Like so:
If myStr = "" or len(myStr) = 0 Then
myStr = "null"
Else
myStr = "'" & myStr & "'"
End If
SQL = "exec sp_myProcedure " & myStr
For SQL to recognize your parameter as a null value, you must send it without quotes, otherwise it will take it as the literal 'null'.
If this approach is not feasible, then you could:
1) since you're using a stored procedure, add code to it to change the empty string to null, like above (but using T-SQL syntax). This approach is actually good because you can handle the error before executing the update/insert statement.
2) use triggers on the table, but those are more expensive (performance wise).
There may be other ways of doing it, but this is what i could come up with. Good luck!
Andres M. Chiriboga, MCSE, MCP+I