Testing for a null and replacing with a default value

Hi All,

Just a quick question about database nulls. I have a text box in a form view that I use to update a database record. The database does not allow nulls for that column.

Rather than throw an error or use a validation control that forces data entry, I would simply like to check if nothing has been entered, and if that's the case replace it with default text like 'No data present'.

I'm using VB and MS SQL

Thanks in advance for your help.

-1
magicjoef
8/6/2008 6:26:23 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

7 Replies
470 Views

Similar Articles

[PageSpeed] 23

Without allowing nulls, you can use the CASE statement in SQL select queries.

 

Select CASE WHEN myField = ''
         THEN 'No data present'
          ELSE myField END AS myField 
   From myTable
 
---------------------------------------
MCP - Web Based Client Development .NET 2.0
-1
ps2goat
8/6/2008 6:50:26 PM

Thanks for your reply. I haven't used CASE before, it looks ideal.

I'm struggling to fit it into the SqlDataSource that I have created in my aspx page. Here is how it looks (I've changed it to include ambiguous columns). It is column two I need to apply the CASE to.

SelectCommand="SELECT [ID], [columnOne], [columnTwo], [columnThree] FROM [tableName] WHERE ([requiredField] = @requiredField)"
 

Thanks

-1
magicjoef
8/7/2008 12:25:42 PM

Actually, looking at it, it might be better checking at the stage of update whether there is a null, and creating a new default value then to store in the database.

Any help on doing that?

Thanks

1
magicjoef
8/7/2008 1:55:47 PM

magicjoef:
replace it with default text like 'No data present'.

 

u mean in table, you wants to insert "No data present" when you are passing null or emptystring.

then write the insert statement like this 

  insert into tablename (colname) values (isnull(nullif(@name,''),'No Data Present'))

 

 


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
1
ramireddyindia
8/7/2008 2:16:43 PM

Thanks for the reply, that looks great. Again, not experienced with SQL language. Can I add it to an update statement I'm using for formview? It looks like below and was created using SQL datasource wizard:

UpdateCommand="UPDATE [tableName] SET [columnOne] = @columnOne, [columnTwo] = @columnTwo WHERE [ID] = @original_ID AND [columnOne] = @original_columnOne AND [columnTwo] = @original_Two"> 

When I run an update, if there is a null value in the database, it doesn't update.

Thanks

-1
magicjoef
8/7/2008 2:29:12 PM

once you created those stmts with wizard, after modify those statement manually.

replace the below statement in that statement 

UpdateCommand="UPDATE [tableName] SET [columnOne] = isnull(nullif(@columnOne,''),'No data Present'),  [columnTwo] = isnull(nullif(@columnTwo,''),'No data Present')  WHERE [ID] = @original_ID AND [columnOne] = @original_columnOne AND [columnTwo] = @original_Two"> 

magicjoef:
When I run an update, if there is a null value in the database, it doesn't update.

actual it will not happen like that. But its better, to give default values of those columns as like "No Data Present". so that there would not be any nulls in those columns.

 


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
1
ramireddyindia
8/7/2008 2:36:09 PM

Thanks for your help. Sadly that SQL hasn't worked.

AMEND:

I double checked my code. I had made an error. It did work! Thank you very much, you've really helped me out with this.

Cheers!!

-1
magicjoef
8/7/2008 3:02:27 PM
Reply:

Similar Artilces:

Null Default Value on Control Parameter.
Greetings! I am working with a gridView that plugs into a sqlDataSource which executes a stored procedure that has 3 parameters. I would like to set two of the parameters to a value and have the third default to a null value. I have tried the following code: <asp:SqlDataSource ID="sqldsJobs" runat="server" ConnectionString="<%$ ConnectionStrings:ExampleConnString %>" SelectCommand="sp_Example" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="813" Name="CompanyID" Type="Int32" /> <asp:Paramet...

context.SubmitChanges() fails for null values (which are set to NOT NULL but have default values)
the topic pretty much says it all.I have a table with a column of type varchar(5). It's defined as NOT NULL and a default value of let's say "test".Now whenever I try to insert a new row into that column and I do not define the column mentioned above - the value for that column is "null". This of course leads to an exception.I do not want to insert null or Dbnull.Value or whatever.. I don't want to insert anything at all in that column so that the database can insert the default value.How am I supposed to do that? I do need that column later in my program so d...

SQL QUERY replace NULL value in a row with a value from the previous known value
Hi!! I have a table with no unique ID, I created an Id with ROW_NUMBER () OVER (ORDER BY column asc) rank and created a stored procedure with the rank and table. In this stored procedure I want to create an extra column that checks if the value from another columns contains value = 100 rank,Ifnummer,followingnr,productnr testing 2313,'6497269','001' ,412 , NULL 2314,'6497269','002' ,413 , NULL 2315,'6497269','003' ,100 , yes 2316,'6497269','004' ,430 ,NULL ...

Gridview control replaces a NULL value in SQL with &nbsp;
Hello, I have a Gridview control that I am using to display information.  In the RowDataBound() I am retreiving data from SQL to use; however, if my varchar() field in SQL is NULL, then the GridView is telling me that the value is "&nbsp;". Has anyone seen this, and if so, how can I get the value to be blank.  I have already tried re-defining my SQL query to replace the NULL value with an empty string; however, even with the empty string, the GridView is replacing it with "&nbsp;".  If anyone has any insight it'd greatly be appreciated because I am out of ideas. T...

Testing for NULL return values in test scripts
Hi, I was wondering if there is a way to use the ok() function in Test.pm to check for a null return value. It looks like the 3 arg form of ok() I'm using only tests the first 2 args to see if they're equal. I'm considering this approach: $val =3D some_func(); # returns NULL on failure if($val !=3D 0 && $val ne "0") { $isnull =3D 0; } else { $isnull =3D 1; } ok($isnull,0,"NULL returned"); Thanks, Walter On Tue, Apr 12, 2005 at 12:49:30PM -0500, Walter Goulet wrote: > Hi, > > I was wondering if there is a w...

getdate() default value / Cannot insert the value NULL into column
I'm not sure if this is a .asp issue or a SQL issue..... i've created a form on a .asp page in dreamweaver.. all fields point to the correct dataset, etc... instead of adding a field for the "date entered" , I've put a getdate() data value in the SQL Express 2005 database field.. so, theoretically, when I submit the form to the database, SQL will fill in this date entered field automatically with the current date/time stamp... but, when I submit the form, I get a message on the web page similar to this: System.Data.OleDb.OleDbException: The statement has been terminate...

Control.Value and Control.SelectedItem.Value
Can someone explain to me the difference between say, Dropdownlist.SelectedItem.Value and Dropdownlist.SelectedValue?? Just curious, Thanks!Tim These are going to return the same thing. dropdownlist.selectedvalue is a shortcut to get the currently selected value in the dropdown. If that is all you want then you can just use that. If you want to access the currently selected item you can use dropdownlist.selecteditem and that return the item and you can then access any of the item's properties, including in this case the value property. If this post answered your question please r...

Unable to update the values for the cloumn which have null values in detailsview control
i m updating a record in a details view by clicking update button. I m able to change the values of the various columns which have non null values. However update is not working when the value of the cloumn is a null value in the database. Any idea how to resolve this?RAJAT Do you get an error or does the update simply not work?Are you capturing the error using the "ItemUpdated" event by checking e.Exception?  http://authors.aspalliance.com/aspxtreme/sys/web/ui/webcontrols/DetailsViewClassItemUpdated.aspxhttp://www.rocksthoughts.comhttp://www.jumpstarttv.com - Free Techn...

Within a SELECT, how do I replace an empty or null value with a value from another table?
Hello,I'm a beginner in SQL and I have been searching through the SQL Cookbook and Google but I can't seem to find an example of what I want to do. I want to create a report that will return names and emails using two of my tables. I want to use the email in my primary table in the select but if it is null or empty I want to replace it with an email from my secondary table. Below is what I would like to do but I got a syntax error with it in SQL Server 2000. SELECT MemberID As ID, MemberFirstName As FirstName, MemberLastName As LastName, (IF MemberEmail = &#...

Testing for Null values
I am tearing my (remaining) hair out here, trying to test for a NULL value from the database. I am using SQL Server 2000, with Visual Web Developer 2005 Express (language VB).  My database design uses Guids (uniqueidentifiers to SQL) to key and relate records.  My application architecture has a Data Access Layer with Tableadaptors for most tables. I want to test if a field (IndiPicID uniqueidentifier) has a null value.   I have a TableRow called trINDI that has been correctly set to the record that I'm interested in.  This contains a property, trIndi.INDIPicID, tha...

Testing for a null value
(Using User App 3.51) In a workflow, I have a Mapping activity that gets a value from NDS and sets it to a variable, with the source expression being IDVault.get(recipient, 'user', 'val1') and the target expression of flowdata.var1. The value val1 in NDS may or may not be valued (usually not), so I want to do certain things in the workflow only if the field has a value. Next there is a Condition statement that evaluates the variable to see if it is null (flowdata.get('var1')!= null). The problem is, whether or not the field existed, the condition statemen...

Testing for Null value
I seem to have a problem testing for null values in a SortedList. I want to assign them to text boxes on a web form, but if I assign a null value to a text box it throws an exception. If I try to test for a null value in the sorted list I get an exception. Help! Here's some sample code that doesn't work, and I don't understand why. All I wanna do is test for a null value without generating an exception! ' -------------------------------------------------------------------------- ' Set the value of a textbox control. Set it to nothing if value is null. ' -------...

control and default value?
when i am developing a control then i have set default value but when i drag and drop the control onto the form then nothing is showing as default value.i am giving fragment of my code please tell me what goes wrong <Browsable(True), Category("PageLink"), DefaultValue("<<"), Description("Get/Set the First Navigation Link Text")> _ Public Property FirstLinkText() As String Get Dim obj As Object = ViewState("FirstLinkText") If obj = Nothing Then Return "&quo...

How to test for Null value?
In IDM2 in Policy Builder how could I test if an attribute is being "cleared" from one Edir driver to the next? I clear an attribute in a tree which flows through the Subscriber. I want the Publisher in the other tree to realize this is a clear all values so I can write a rule that will trigger when this attribute is being cleared. I have written many rules testing attribute values, just not one to test if an attribute is getting deleted. The eDirectory event system only reports that each individual value was removed. It isn't possible to tell if the action(s) that r...

Web resources about - Testing for a null and replacing with a default value - asp.net.presentation-controls

Facebook Tests Replacing Keyword Ad Targeting With Broad Category Targeting
Facebook is testing a major functionality change for its self-serve performance ad tool that would replace the specific Like keyword targeting ...

Replacing a Pebble Smartwatch screen - YouTube
I got a broken Pebble from a friend, and fixed it with a dremel, some epoxy, and a replacement screen. Total cost of $30.

School apologises for taking cane off blind boy and replacing it with pool noodle
A primary school has apologised for taking away the cane of an eight-year-old blind boy and replacing it swimming pool noddle as a form of punishment. ...

Music teachers outraged over CDs replacing accompanists
Whether it is violin, flute or percussion, each year tens of thousands of nervous children across Australia are guided through their music exams ...

Replacing China's broken economic gauge
As Chinese growth shifts away from its reliance on heavy industry, we need a new index to the track the ups and downs of the economy.

Matt Toomua focused on Brumbies, not replacing Quade Cooper in Wallabies
ACT Brumbies flyhalf Matt Toomua is refusing to get caught up in Wallabies distractions this week.

Apache Software Foundation unveils Hadoop 2, replacing MapReduce with YARN
The Apache Software Foundation unveiled its latest release of its open source data processing program, Hadoop 2. It runs multiple applications ...

No talk of replacing Bill Shorten as Labor leader: Anthony Albanese
Labor frontbencher Anthony Albanese says he's not interested in taking over the leadership, and the party remains united behind Bill Shorten. ...

Reject Shop names Ross Sudano as new chief replacing Chris Bryce
Discount variety retailer The Reject Shop has appointed a former beer and grocery industry executive, Ross Sudano, as chief executive.

A case of one bad government replacing another
THERE is a widening gap between the sort of government a Tony Abbott-led Coalition will need to become to steer the nation successfully in the ...

Resources last updated: 12/18/2015 12:54:48 AM