.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Parameters and Null Values ..!

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :ASP.Net

Hi There,

I was develop an application (ASP.Net) Using (VS.Net C# 2010 & SQL Server 2008)

And I was applying (3-tyer Basics) and I'm not using wizard forever in my application.

The problem is :

When I try to passing the parameters to database, some fields I don't type it

But it's show in database (Empty like "Space") in text fields and (01-01-1900) in date fields

 Although it's must be (Null) .

How I can pass parameters with Null Values for the fields that's not typed  ?

Thanks in advanced 

View Complete Post

More Related Resource Links

How to deal with NULL values in a SQL table


 Hi all

In order to add/update/delete data from a table I build 2 classes: 1 class containing all the table fields and another class with methods to add/insert/update/delete records. I am learning how to avoid errors when your table must use SQL NULL values. One error is when I call my UPDATE method (see below):

  public void UpdateItem(OcItemConstraintDetails item)
  SqlConnection con = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("ItemConstraints_Update", con);
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.Add(new SqlParameter("@itemConstraintId", SqlDbType.Int, 4));
  cmd.Parameters["@itemConstraintId"].Value = item.ItemConstraintId;

  cmd.Parameters.Add(new SqlParameter("@path", SqlDbType.NVarChar, 150));
  cmd.Parameters["@path"].Value = item.Path;

if you watch the SQL profiler trace you will see that I am passing a value of 'default' ;so that causes me problems.

exec ItemConstraints_Update @itemConstraintId=1,@path=default

With that said, what are the best practices when your SQL table accepts NULL values and when:
- you are passing an empty value to your stored procedure;

Help: Too many lines of code to achieve a simple goal: keep my NULL SQL values...


Hello all

What is the best way (best practice) to preserve my NULL SQL values in the database
during an insert/update operation (without receiving cast invalid errors)?
Also, how to display a string 'n/a' when a sql value is NULL?

My project is using FormView with Edit/Insert templates and classes to represent my tables

Goal1....: display 'n/a' when there are NULL SQL values in the database
Solution1: I am using ISNULL(field,'') in my store procedure for SELECT statements

Goal2....: if the fields are 'n/a', then save them back in the database as NULL
Solution2: on my insert/edit methods I am having to check the values being passed, i.e:

cmd.Parameters.Add(new SqlParameter("@scope", SqlDbType.NVarChar, 50));

if (item.Scope == "n/a")
 cmd.Parameters["@scope"].Value = DBNull.Value;
 cmd.Parameters["@scope"].Value = item.Scope;

Not to mention that for SQL DateType fields when you use ISNULL(field,'') the return string is '1/1/1900 12:00:00 AM'
and I have to change the field value in every field that represents a date, i.e:

protected void FormView1_DataBound(object sender, Sy

Optional Parameters - Not taking values when supplied

All, I have a report that takes 2 optional parameters, one int and one datetime. My data tab is set up as follows: CommandType = Text, Parameters has Parameters.Value etc. for both. Report Parameters are allowed NULL value, Default Value is NULL, datatypes are Integer and Datetime as well. This only runs when the parameters are not supplied values, when I enter a date, it does not take it into account, same for the int value. I have run the proc manually to verify that the query is set up correctly, it seems like the values are not being passed? Any help debugging this would be helpful. Thanks.

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

Gridview RowCommand Event returning null values

HiI'm trying to retreive values entered by users in the textboxes in the footer of the gridview and insert these values into another sql table.My code is as follows :-protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)    {       if(e.CommandName.Equals("addnew"))       {                       TextBox t1 = (TextBox)GridView2.FooterRow.FindControl("tfrm");            string frm = t1.Text;            TextBox t2 = (TextBox)GridView2.FooterRow.FindControl("tsub");            string sub = t2.Text;            string cmd = "insert into suggestions values('" + frm + "','" + sub + "','c','c')";            con.Open();            SqlCommand com = new SqlCommand(cmd,con);            int a = com.ExecuteNonQuery();&n

Comparing Null Values(from variable) in SSIS

Hi All I need to create a script that checks to see if a variable value is NULL, if it is then it executes the next SQL Task and if not then the package has finished. I have this in a SQL 2000 DTS package script: Function Main() If isnull(DTSGlobalVariables("NextLoadDate")) Then Main = DTSStepScriptResult_DontExecuteTask Else Main = DTSStepScriptResult_ExecuteTask End if End Function As you can see, all I want to do is check to see if a variable is null then don't execute the next task. But i cannot get it to work in a SQL 2005 SSIS package, so far I have created a script and come up with the following: Public Sub Main() If Dts.Variables("NextLoadDate").Value.ToString <> "" Then Dts.TaskResult = Dts.Results.Success Else Dts.TaskResult = Dts.Results.Failure End If End Sub Any Ideas? Thanks BigGopher

Comparing columns with NULL values--Merge says unmatched when data is matched.

I found the article below describing this same issue with the Oracle merge statement.  It also described a work-around:    http://searchoracle.techtarget.com/tip/Merging-datasets-with-NULL-values I have been unable to find a solution to this issue for SQL Server 2008.  I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse.  I am using the Merge statement with the Unique key minus the modification date as my merge key list.  I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list.  I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success.  When I put zeros in the null columns, it works as expected.  I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement.  Has anyone else experienced this?  NULL values are valid in our Data Warehouse.  How can I make T-SQL handle them? Thank you for your help, Jesse

Problems querying XML data with null values

If the tags have "xsi:nil" in them, then my query failes with a "XML parsing: line [x], character [x], undeclared prefix" error.  So my question is how to get the query to handle or ignore those tags with "xsi:nil" embedded in them.  So specifically, the first SQL query below is successful, and the second one fails with the aforementioned error.  Any comments appreciated, thanks. -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N'<data> <customer> <id>1</id> <name>Allied Industries</name> </customer> <customer> <id>2</id> <name>Trades International</name> </customer> </data>'; -- Using the query() method SELECT T.customer.query('id').value('.', 'INT') AS customer_id, T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name FROM @data.nodes('data/customer') AS T(customer); Returns: 1 Allied Industries 2 Trades International ================== -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N'<data> <customer> <id>1</id> <name>Allied Industries</name> <fax xsi:nil="true" /> </customer> <customer> <id>2</id> <name>Trades International</name> <fax xsi:nil=&quo

Create unique constraint on a column which has null values

Hi All, I have a table suppose 'Temp' having one of the column as 'ColA' which has some null values as well as non null values. Now i have a requirement to create a unique constraint on it. We have tried but couldnt do it.Apart from having a trigger on insert and update statements is there any other alternate. Can any one please help me on this. Thanks & Regards, Srikanth  

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign

Using Visual Studio with MySQL.In my XSD dataset I created a query. It runs perfect. I can preview the data fine.In my BLL I wrote code (see below) to retrieve the query results and I'm getting...Using db As New dsDemoTableAdapters.DemoTableAdapter Dim dt As New DataTable dt = db.GetDemo(DemoId) ' ERROR HAPPENS HEREFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.Why would previewing the data work but in code it fails?Any ideas?

Null values in formula fields

Hi,I tried using this and am concatenating the fields together.  It does not appear to be working.  Here's what I have.  Any suggestions?   IIF(IsNull({nomination.recparty1}), '0.0', {nomination.recparty1} ) &  chr(13)IIF(IsNull({nomination.recparty2}), '0.0', {nomination.recparty2} ) & chr(13)IIF(IsNull({nomination.recparty3}), '0.0', {nomination.recparty3} )andif IsNull({nomination.recparty1}) then '0.0' else {nomination.recparty1} & chr(13)if IsNull({nomination.recparty2}) then '0.0' else {nomination.recparty2} & chr(13)if IsNull({nomination.recparty3}) then '0.0' else {nomination.recparty3}Both this gets failed beacuse my fields are returned as null.Please get me to solve this.Thanks in advance.Regards,Mugil

All parameters null in Update of SQLDataSource?


Hi All,

I have a FormView which is bound to an SQLDataSource. When a user clicks on a save button the form, I need to write any changes made to the FormView back to the table.

I have a Stored Procedure on my MSSQL 2005 database that should be executed when the user clicks on the save button.

It appears the Update is firing, but when I trap the DbCommand object in the SQLDataSource's 'Updating' event, all of the parameters that should be passed to the Stored Procedure are coming through as NULLs. Consequently, the Stored Procedure isn't updating anything.

In the click event of the save button I'm explicitly firing the SQLDataSource's Update method:


The parameters in the SQLDataSource <UpdateParameters></UpdateParameters> section appear to be correct.

Can anyone give me an idea on what to investigate to figure out why it appears the Update is only picking up NULLs?

Many thanks in adavnce,


Desired rows not returned because of null values


I have a property table and an image table.
I want this query to return all distinct properties and a thumbnail image. However some properties don't have thumbnail images and they
don't get returned by the query. If the imgid is null I still want to return the property. Not sure of how to do this. Thanks

ALTER PROCEDURE dbo.procGetPropertiesSelect2
SELECT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblProperty.PropertyID, tblProperty.SubmitPersonKey, tblProperty.SubmitDate, tblProperty.Active, tblProperty.PropName, tblProperty.StreetAddress1, tblProperty.Storage,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblPropertyImages.ImgID
FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblProperty INNER JOIN
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblPropertyImages ON tblProperty.PropertyID = tblPropertyImages.PropertyKey
WHERE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (tblProperty.PropNa

Can we pass dimension values as parameters in MDX


Hi All,

I am doing something similar to basket analysis and I wrote an MDX to see the trend of purchases over time for a customer. How can I dynamically pass values of customer or product purchased into mdx? In below example how can I change [product].[category] value? Also, can we change the dimension also dynamically?


with member measures.x as

Hidden Parameters do not refresh available values

I am trying to have preset values for date parameters that the user can override.  I set one parameter that has a list of presets, Last 24 hours, Last Year, Etc.  I then set my date parameters default values based on this selection.

The problem is that this happens once at report load based on the default period selection, but the dates are not recalculated if they change the date selection.

As a workaround, I created an intermediate parameter with an available value based on the period selection, and then based the default value of the date parameters based on this intermediate parameter.  This works perfectly, as long as the intermediate parameter remains visible.  If I set it to hidden (not internal) it seems that it's available values are not set, and thus the chain breaks.  Is there any way around this?

Which Format shows "0" for values "0" oder "(NULL)"?



  a hopefully simple question for a chance:

I´m using SSRS 2008 to create some reports. On textfields which contain numeral values, I use the Format-expression "#,###,##0".

That works fine if the value is not "0" or "(NULL)". In that case I want the textfield to show "0", but it shows nothing at all. No I could catch that by a value-expression, but isn´t there a Format expression that would lead to the same result? As I often have to calculate values it would be easier to use an expression on the Format-property instead of the Value.

Your help would be much appreciated!


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend