Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wtech
Starting Member
11 Posts |
 Posted - 2004-09-04 : 08:37:09
|
| hi,i am using int datatype for a certain column.it allows nulls no problem while insertion but when i try to edit the column(using asp querystring) on the front end it is taking zero by default.in the stored procedure i am using an if condition like:if len(@value)=0 beginset @value=nullend is there any other way i can solve this problem?regards,Wtech |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-04 : 08:48:48
|
| The reason is that an int datatype in the client defaults to 0 if it is not initialized. ( value types cannot be null, as objects can )First option: In ASP.NET you can set the value to DBNull.Value, which will insert/update NULL in Sql Server.Second option: Otherwise you have to provide a placeholder value that signifies NULL,like so : IF @value = "placeholder" THEN SET @value = NULL. ( placeholder could be 0, or -99 that you set in the client app )Third option is for the client to not pass this value at all to the stored procedure or sql that performs the update.rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-04 : 08:56:45
|
| len gives the length of character data - so your test will convert to character then give the length of the result. For an int this can never be 0.datalength will give the length of the variable which will be 4 of null.The only way to get a null into the column is to place it there from the front end or to place a value which can be detected in the sp.You could pass the parameter to the sp as a varchar and translate an empty string to null but better to have an int and pass a null.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
using int datatype