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

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

Desired rows not returned because of null values

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

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        tblProperty.PropertyID, tblProperty.SubmitPersonKey, tblProperty.SubmitDate, tblProperty.Active, tblProperty.PropName, tblProperty.StreetAddress1, tblProperty.Storage,
FROM            tblProperty INNER JOIN
                         tblPropertyImages ON tblProperty.PropertyID = tblPropertyImages.PropertyKey
WHERE        (tblProperty.PropNa

View Complete Post

More Related Resource Links

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?

SSIS excel data source numeric values returned as null

Hello everyone, long time no see!, I have a problem and was hoping someone can help me:
I'm using SSIS 2005 Enterprise edition,  I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server).
When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.

The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it.

I tried the answer posted here: http://devselekta.blogspot.com/2007/09/ssis-excel-data-source-values-returned.html but it only works when the first row of data has a number beginning with zero on this column.
Someone knows how to get the data imported correctly? Thanks in advance.

How do display null rows returned from my table valued function?

DECLARE @techNumLoop VARCHAR(25)
 Tech Varchar(25) 
,Item_Count Integer NULL 
,Expenses MONEY NULL

SELECT DISTINCT t.Case_Number Tech
FROM tblTechnology t
OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
INSERT @myTemp
FROM ufn_Find_Some_Stuff(@techNumLoop)


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

Refer Rows/values of OLEDB Source inside DFT

I have a Data Flow task that implement Insert/Update using logic:  http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/131dbe38-95a9-4b64-8434-60ba3cd6de00/. Now I want to also include "Delete" just after the OLEDB Source where I will delete from Target Table if the Rows are present in Target and No in the OLEDB Source. So what control I should use and how do I refer to the OLEDB Source records. May be something like: Delete from dest_table where ID NOT IN (SELECT ID from OLEDBSourceControlRecords)... Thanks, Prabhat

Access Denied and Null Returned while Accessing Web Services from remote server.

Please go through following scenario.  I am writing a web Service and one of its web method, I want it to download a png file in form of buffer from remote server. And My consume application will write that buffer retured by Web service in to file on User's Computer. However, I am able to do this using Local Web Service but when I published that service on my web server, I always get null retuned And even in one of my test code while copying file from that server , I always get Access Denied.  Following things I have done for above issue. 1] I even tried making the Anonymous user for the web service the system administrator (with the correct password), to no avail. 2] I also tried sharing out the folder I'm pulling files from to allow access from Everyone.   [Previously same service was working fine on another remote server with IIS 7.0, we checked server IIS Settings but in vain.]   if you know where we are mistaken please guide me to resolve this issue as its very urgent. Please go through following scenario.                  

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

Count how many rows are returned from a stored procedure

Hi,   I have written a stored procedure for my database which takes two varchar parameters and returns lots of rows of data. This data will be passed back to my .NET application with a reader for parsing.   Imagine this scenario of calling a stored procedure:   MyDatabase.dbo.sp_MyStoredProcedure 'String 1', 'String 2'   Let's say that this command returns 12,434 rows of data when executed in SQL Server Management Studio Express containing the data of 5 joined tables which contains 2 or 3 unions (depending on the input of 'String 2') and the data within each union is correctly sorted.   How do I get a row count from the execution of the above stored procedure command? I need to pass this back to my application for the progress bar to function correctly.   I don't want to replicate the function and modify it for counting rows because the stored procedure consists of approximately 150 lines of SQL code.   Thanks in advance.   Sean

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

How can I replace values in GridView returned from a Stored Procedure

I am working on creating a stored procedure that will output a pivot table.  In the pivot table will be either the string NULL or a number.  How can I reformat this in ASP.NET so the NULL value becomes a blank cell in the gridview and the number (whatever it is) becomes an 'X' ?

Infopath 2007 Repeating Table - Multiple Value Column Text - Hiding Rows based on Column text values

Infopath 2007 browser based form Full Trust Example: I have a repeating table (FruitChoice) that has multiple columns. Both drop down list point to sharepoint list data sources. Choose your tree ft. drop down list – 6Ft Choose your Department drop down list - 103 This repeating table is conditional on the drop down values. This works great. Trees     Fruit       Cost   Date Ordered    Date Delivery Department 6Ft        Peaches                                                        103 3Ft        Apples                                                          102 3Ft        Peaches         &

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  
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