.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

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

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :Sql Server


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)

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

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&

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


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

Return value when empty table


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)


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

DriveInfo.GetDrive() to return info in table format using SQL CLR


Hello SQL CLR experts,

We're using SQL Server 2008 R2 (French) and Visual Studio 2008.  I'm trying to get a "simple" SQL CLR to work, which calls DriveInfo.GetDrive() and returns drive name, space available and total size into a SQL Table from the UDF GetDriveInfo() defined below. I need help getting the code to work.

I realize that there is already an xp_fixeddrives extended stored procedure that returns only the available space.  I want to return the Total Size of each drive, the volume name, etc.

I based my code on the article http://www.sqlservercentral.com/articles/SQLCLR/65656/ but I want to use DriveInfo.GetDrive() instead of DirectoryInfo.GetFileSystemInfos().  I was successfully able to get the DirectoryInfo assembly from the article to work, but I am having trouble getting my DriveInfo.GetDrive() adaptation to work.

In Visual Studio, I created a new C# SQL Server project and am using the following code:

using System;
using System.IO;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

Return records from 1 table where no matching in 2nd


Hi.  I'm having trouble getting all the results I need with a query and hope its just my lack of experience.

Tables are Employee, Performance_log and Performance.

I need to see all Employees that have a performance_Log entry within a date range.  (done)

Then I was asked to include employees that do not have an entry in the Performance Log table but only if they don't have an entry within the date range previously mentioned.

I feel like I am almost there.  I can display all employees with performance logs and filter by date.   I can display all employees without performance logs within the same query.  My trouble is when an employee has a performance log outside of the date range.  The record should be included but its not.  I've tried using not exists() and not in() in the where but haven't figured it out yet.

Here is the query (the 4th line of the OR is a work in progress and is changing by the minute as I search for the solution)

The dates will eventually be replaced with variables and saved as a stored procedure to be called by Crystal.

  Employee.Employee_Id, Employee.Department, Employee.Active, Employee.NC_Type, Employee.NC_Full_Name,
  Performance_Log.NC_Performance_Note, Perfo

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Using jQuery to Filter Table Rows

The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

RadioButton Groups, Table Rows And NamingContainers in asp.net

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

RadioButton Groups, Table Rows And NamingContainers

(In which AJAX-enhanced CheckBoxes become more useful than RadioButtons but inheritance saves the day, and a simple RadioButton-derived control establishes the purpose of a control's naming container)

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.
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