.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

Return value when empty table

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :ASP.Net

I have the following code that i call from the page behind code.

when no records are in the table i get an error message


conversion from type DBNULL to type Decimal is not valid

I would prefare if the procedure did not throw this error message and returned a value to notify me that no records found when zero records returned

Public Shared Function invoicetotal(ByVal CustEmail As String) As Decimal
        ' Dim selectcommand As New SqlCommand
        Dim CONNECTION As SqlConnection = MaintainDB.GetConnectionString
        Dim sel As String _
        = "SELECT sum(total) from invoices WHERE CustEmail = @CustEmail  or  @CustEmail =   " & -1
        Dim CMD As SqlCommand = New SqlCommand(sel, CONNECTION)
        If CustEmail = "" Then
            CMD.Parameters.AddWithValue("@CustEmail", -1)


View Complete Post

More Related Resource Links

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Writing Webservice return values to table within SQL CLR

Visual Studio 2010/Sql Server 2005/ .net 3.5/ C# I have compiled and created assemblies and all works fine when all I do is .pipe.send.   I have changed it now to capture the values in local parameters so I can write to a sql table on the same server.  I have the connection to the database and the table created.  The code compiles fine but when I run it from sql I get the following error.  The code follows the error.   Msg 6522, Level 16, State 1, Procedure CventSP, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "CventSP": System.Data.SqlClient.SqlException: Incorrect syntax near '@eventtitle'. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at StoredP

Empty/blank group at top of matrix table

I'm having trouble creating a report with matrix tables.  I've got all the data showing up properly, but the first line in the preview is always blank.  If I subtotal the groupings, I get a blank Total line as well.  How can I stop this from happening? I'm using SQL 2005, report designer.  Data is coming from a stored procedure.  I've executed the sp in management studio as well as in the data tab of the report and it comes back with the correct data (no completely blank/null lines).  I'm new to SSRS and I've tried googling for a solution, but haven't found anything similar.  Below is the sp and the output data. SP: SELECT s.Status_Name + ' (' + CAST(CAST(s.Status_Weight*100 AS int) AS varchar(50)) + '%)' AS Status, s.Status_Order, CASE WHEN co.Company_Name IS NULL THEN '' ELSE co.Company_Name END AS Company_Name, co.Est_Income FROM tblStatus AS s LEFT OUTER JOIN tblCompany AS co ON (co.Status_ID = s.Status_ID AND co.New_Upsell = @NewUpsell) GROUP BY co.Company_Name, s.Status_Order, s.Status_Name, s.Status_Weight, co.Est_Income ORDER BY s.Status_Order, co.Company_Name Output (mgmt studio): Still Connecting (0%),1,Company1,400000.00 Still Connecting (0%),1,Company2,3000000.00 Clear Future (0%),2,Company3,250000.00 Clear Future (0%),2,Company4,250000.00 Appointment (10%),3,,NULL Proposal (40%),5,,NUL

how to return records in squence of inner join table?

Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

Caml returns table with column, whose value is empty string always, but it's not

I wrote caml query   SPSiteDataQuery query = new SPSiteDataQuery(); query.ViewFields = @"<FieldRef Name='Title' /><FieldRef Name='cmAssignedTo' Nullable='TRUE' />" + "<FieldRef Name='cmDueDate' Nullable='TRUE' /><FieldRef Name='Author' /><FieldRef Name='cmStatus' />" + "<FieldRef Name='cmControler' Nullable='TRUE' /><FieldRef Name='cmControlers' Nullable='TRUE' />" + "<FieldRef Name='FileDirRef' /><FieldRef Name='File_x0020_Type' /><FieldRef Name='cmIcon' /><FieldRef Name='cmPackageGuid' />" ; query.Webs = string .Format("<Webs Scope='{0}' />" , searchScope); query.Lists = "<Lists ServerTemplate='10553'/>" ; Fields cmAssignedTo, cmControler and cmControles return always "", but they include users! Definition of these fields is similar:   <Field xmlns="http://schemas.microsoft.com/sharepoint/" DisplayName="cmAssignedTo" StaticName="cmAssignedTo" Name="cmAssignedTo" ID="{B4B52176-1961-4b06-B7B4-C7DBB751A53F}" Type="User" List="UserInfo" ShowField="ImnName" Mult="TRUE" />   List, where i'm looking for in, includes 2 contenttypes. First one contains these

if NULL in database table - display empty string?


I have a table in an Sql Server Database from which I want to fetch data to a form. 

[Headline] [varchar](max) NULL,
[Text] [varchar](max) NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

[Headline] [varchar](max) NULL,

[Text] [varchar](max) NULL,

I want to check both Headline and Text to see if they are null, and if so display an empty string each for them. I've looked at some tutorials but don't yet understand really how to use it. DBNull? DataRow.IsNull? Something else? Can I check both Headline and Text at the same time or do I need do check them separately?

return table from function (function has WITH common_table_expression )


I am trying to create following function which should return table but when I try to create it I get error message as "Select statements included within a function cannot return data to a client.". I am looking for workaround for this issue.



function dbo.GetCategoryIdTillBase(@Category_Id as char

Conditional insert: If select return rows, insert rows to table otherwise insert specific row indica

This is what I have

Insert into ReportDetail( Partcipantid, Reportid)  

select distinct ParticipantID , 9 from OpenCredit

      except select ParticipantID, 9 from StoreCredit where Closed = 0

 Issue is that when above select statement returns no row, it seems like no record is

Select Sql does not return the data eventhough the data does in particular table.


Hi Dude,

I am new to Sql server 2005. I have a table in which contains many data. I need to take particular data. For example, I want to take value of 110652.795813712 from FTEBASEPAY column .  So i have wrote the sql statment like in below.

SELECT * From tblEmployees where FTEBASEPAY='53842.7782655718'

But i am not able to get the particular value. Manually i have seen the tblEmployees table, in which contains the particular data ('53842.7782655718').

When i execute the above select statement, there is no result for it. Please let me know anyone face the same problem? Please give a solution for it. What i have to for overcome this issue?I have to give one more information, the FTEBASEPAY dataType is float in tblEmployees table.

Thanks in Advance


if a value is not in a mssql table, return something also



I have kind of simple  question, but can't find anything usefull on the internet.

In my web application i have a sqldatasource control:

 <asp:SqlDataSource ID="sourceTrainingDate" runat="server"
    ConnectionString="<%$ ConnectionStrings:login_ConnectionString %>"
    ProviderName="<%$ ConnectionStrings:login_ConnectionString.ProviderName %>"
    SelectCommand="SELECT convert(varchar,convert(varchar,year(createtime))+'-'+convert(varchar,month(createtime))+'-'+convert(varchar,day(createtime))) as TrainingTime FROM [EmployeeTraining] where ([employee_id] = @employee_id)"
            <asp:Parameter Name="employee_id" Type="Int32" />


 protected void sourceTrainingDate_Selecting(object sender, SqlDataSourceSelectingEventArgs e)

Dataview filter on querystring parameters - how to return all when empty?




I'm creating a page with a dataview wepart. The dataview is filtered by three querystring parameters. This works fine as long as all parameters are present in the querystring. The parameters currently all have defaultvalue set to "". If one parameter is missing from the querystring, the dataview returns no results (no column values matches "" they all have values). I would like it to return all results if the parameter is not present, does anyone know how to do this?


The dataview is displaying items in a Sharepoint list and currently the filter uses "equals" because I'm filtering on a MultipleLookup field type. 


I would really like to see a wildcard character in CAML like I can use % when I do this for a dataview with a sql server datasource.





Receiving a fatal error 3624 @pageref.cpp:913 inserting into an empty table


Hi guys,

I'm using Integration Services to insert rows into a SQL Server 2008 table, and I'm receiving an error - only when the table is empty before the insert.  I use SSIS quite frequently, and this is the first time I've seen this kind of issue.  Since I'm getting some internal errors that seem related to the engine in the reports, I figured I'd post here for ideas.

Here's the dump from the Agent job that runs the package:

Executed as user: CORP\_DWAdmin_Service. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  8:38:29 PM  Error: 2010-10-11 20:39:14.14     Code: 0xC0202009     Source: Insert New Records DIM_CUSTOMER [6]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Warning: Fatal error 3624 occurred at Oct 11 2010  8:39PM. Note the error and time, and contact your system administrator.".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x800

Event Handlers need to return the actual table, column and value of column and name of task which tr



Within SQL 2005 within the Event Handler tab I created a an Execute SQL Task with the following SQL statement; -

INSERT INTO dbo.CustomSSISLog (SourceName, SourceDescription, ErrorDescription,PackageName,ErrorCode,TaskName) VALUES (?,?,?,?,?,?)

In conjunction with using the Parameter Mapping tab to map parameters to system variables the above work's quite well. However the TaskName contains the name of the task which inserts the error record, I want it to contain the name of the Task (SSIS component) which triggered the error.

Also I need the error record to contain the actual table, column and value in the column which triggered the error, any ideas? There does not seem to be readily available system variables to do this.

Kind Regards,


If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds

Where not exists or Empty table


How do i deal with this, I have a query that works great just as long as the table i'm referencing has some records in it, unfortunately for me there is the possiblity that some of the tables that I will reference against might be empty.

Like I stated this query returns the appropriate amount just so long as there is data in the reference tables:

declare @Amount float
select top 1 @Amount = CAST(dbo.Designated_Amount.Amount AS Float) 
    FROM  dbo.Designated_Amount INNER JOIN
       dbo.SIMS ON dbo.Designated_Amount.Unit_Code = dbo.SIMS.Unit_Code
    WHERE not exists (SELECT   dbo.SIMS.Unit_Code, dbo.Designated_Amount.Amount
FROM     dbo.Designated_Amount INNER JOIN
           dbo.SIMS ON dbo.Designated_Amount.Unit_Code = dbo.SIMS.Unit_Code
WHERE   (dbo.SIMS.Avail_Cap = 0) AND dbo.SIMS.Event_Type in ('IR','ME','MO','PE','PO','RU','SF','U1','U2','U3') AND 
           (dbo.SIMS.EventEndTS IS NULL)and (dbo.Designated_Amount.Unit_Code = '114'))
    ORDER BY dbo.SIMS.EventStartTS DESC

SELECT TOP (1) CASE WHEN V301030A <= '.1' THEN cast(coalesce(@Amount,0) as varchar(100))
 ELSE '0' END AS Availability

So how do I check for the Table is empty part?

Any assistance would be greatly welcomed. 

WCF Callback Exception When Table is empty



I have a WCF service which basically functions as a dataservice for clients.

It recieves requests from clients and places database calls, returning the result to the clients via a defined callback contract.

Everything works fine.

But, when a database call returns a datatable without rows or columns defined, the service throws an exception in the callback call.

An table without row or column definition is usually the result of a database query returning zero rows or db error

Faith as small as a mustard seed, can move mountains - Jesus Christ

Dynamically added table rows disappear on return



I am adding table rows dynamically, after I return from a post back, these rows disappear.

Is there a way to preserve these rows and add them back on page load?

using a stored procedure for a listview, I am trying to only return items in a column of a table tha


I am trying to create a stored procedure that when fed a session parameter, it will only find items that  start with a particular character.  For example, if I have a table named table1, and a column name col1, then if I have 10 items in the column and 5 of them start with the letter c, I want to use a stored procedure to onlly return the items that start with a c.

I am sure it is somethin like:


FROM table1 

WHERE col1 = "c??????????"

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