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

Top 5 Contributors of the Month
Post New Web Links

How do I check if ExecuteScalar() returned me any rows when you are using casting this way...

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

Hi all

The code below works fine if I have records on my table but if the table is empty I get the following error:

latestTimeStamp1 = (DateTime)cmd.ExecuteScalar(); <=== {"Specified cast is not valid."}

So I am trying to add an IF statement to check if there was any return from ExecuteScalar() method.
How can I verify if something was returned or how can I prevent this cast error from happening?
I managed a workaround by using DataTable and checking if table was null but I need this code to have less overhead as possible since I will be pulling up this timeStamp date all the time.

 DateTime latestTimeStamp1 = DateTime.UtcNow;
 string select = "select max(timeStamp) from Table1";

 SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["Test"].ConnectionString);
		 SqlCommand cmd = new SqlCommand(select, conn);

	//if ExecuteScalar returns something 
	 	latestTimeStamp1 = (DateTime)cmd.ExecuteScalar();
		return latestTimeStamp1;
 catch (Exception ex)

Thank you

View Complete Post

More Related Resource Links

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

HOw to check whether all rows has been loaded successfully??

Hi All!   I have one question in mind. I have created package that extract data from sql table and populate in flat file. I used OLEDB source and flat file destination. Now, I have executed the package.  Source tabled has millions of record. After the completion of package, I want to make sure that all rows from table has been loaded into the file  and none of the rows are missed. source table has millions of record. How can i check that in SSIS? It doesn;t make sense to check every row in a file and compare with source. So, How can i do that??     Thanks,  

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

SSAS Excel Drill Thru : Number of Rows Returned don't match up


Hi, I have created an excel report which connects to my cube and have created drill thru action. When I double click on data cell in excel, what I would expect is to give details for that cell. For example, I have a cell in excel that represents 1000 Sales made. When I double click on it, it should only show me thousand rows, however for some reason it gives me more than 1000 rows. So basically the cell counts don't tie with drill thru row counts.....

Any idea on how I can rectify this?

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 do I check if my ExecuteScalar() is null?


Hi all,

I know for a fact that the SQL statement below returns NULL but my code statement "if (obj != null)" is not working.
When I debug it I see a value of {} ... not sure what that is. Here is my code:

protected string GetLatestTimeStamp()
	DateTime latestTimeStamp1 = DateTime.UtcNow;
	string latestTimeStamp2 = String.Empty;

	string select = "select max(timeStamp) from dbo.JobHistory";

	SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
	SqlCommand cmd = new SqlCommand(select, conn);

		object obj = cmd.ExecuteScalar();

		if (obj != null)
		latestTimeStamp1 = (DateTime)obj;
		latestTimeStamp2 = latestTimeStamp1.ToString("yyyy-MM-dd HH:mm:ss.fff");
	return latestTimeStamp2;

So, even if SQL returns NULL, it still validates obj as NOT NULL !!! :(

Any ideas? Thank you!

How to check to see if two rows in sql have the same data as one another?


I have a sql query that returns two rows of data, with 10 columns.  Is is possible to compare one row of data to another to determine if they are identical?   If all the fields for a row are identical to all the fields for another row, I'd like to return a "Yes".  If there is any discrepency (no matter if it's in one column, three columns,  or if all ten columns, I want to return a "No". 

I could do this in the code behind by setting all the values to labels and then comparing all the labels, but that seems like a lot of extra craziness.  Can sql compare one row to another in a stored procedure? 

Shared Data Set error when no rows returned



I've created a parameterised Shared Data Set and everything is fine when it returns data. However, when I set the parameters so that no data is returned I get the error message:

An error has occurred during report processing. (rsProcessingAborted)
The execution failed for the shared data set '<Data Set Name>'. (rsDataSetExecutionError)
Object reference not set to an instance of an object.

I've looked through the various knowledgebases etc and the only recommendation I've found is to add a NON EMPTY clause to the MDX query so data is always returned. This isn't ideal as I'm looking at activity over a given date range, and it's quite useful that weekends are missed out in the reports (as there is no activity during those days).

Does anyone have any other suggestions?


Resetting the default value of a control when a check box is returned to False (unchecked)


Let me start by saying I’m new at programming InfoPath and I value the advice I have found in this forum. Thank you...

 Here’s my situation: I have an InfoPath 2007 form that populates a SharePoint list once the form is submitted. On the form I have various textboxes that are conditionally formatted to remain hidden until an associated checkbox is checked (value of True).  All is good so far. 

The situation I have is if the user decides after populating a textbox they really didn’t need to fill in that textbox and the checkbox is unchecked (value of False), the textbox is hidden once again but the information remains in the textbox.  Thus, when the user submits the form the unneeded information is submitted as well.  

I would think returning a textbox control to its default value could be accomplished wi

Using a CompareValidator to check input is a valid date

The CompareValidator can do more than just compare two controls. You can also compare it against several of the main .net data types such as Date, Integer, Double and Currency.

To do this you would set Operator="DataTypeCheck" and instead of setting the ControlToCompare or ValueToCompare attributes as you normally would you use the Type="Date" (or any of the data types I have listed above).

Adding Dynamic Rows in ASP.NET GridView Control with TextBoxes and with Delete functionality

In my previous examples, I have demonstrated on how to add dynamic rows in GridView control with TextBoxes and how to save the values into the database. Now, seems that most of the developers are asking if how to add a delete functionality with it. So in this example, I'm going to show on how to delete a certain row in the dynamic GridView with TextBoxes.

jQuery: Highlight Gridview Rows with Checkbox

Im learning and experimenting with jQuery and ASP.NET. Below is a simple solution I created to highlight rows in a gridview by clicking on a checkbox next to the row.

Gridview setup:

Highlighting Rows with TextBox OnFocus

In another article I explained that how you can make the rows of the GridView control clickable and highlight them when they are clicked. In this article I will explain that how you can highlight the GridView rows when you focus on the TextBox which is contained inside the GridView control.

jQuery: Highlight Gridview Rows with Checkbox

Im learning and experimenting with jQuery and ASP.NET. Below is a simple solution I created to highlight rows in a gridview by clicking on a checkbox next to the row.

jQuery: Styling DropDownList Alternating Items (Rows)

In the following example, I will show how you can style any asp:dropdownlist very easily.

The example demonstrates how you can change alternating row items background color, using the fadeTo() to add a nice touch in the dropdownlist. You can change alt rows with a couple lines.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to format and update GridView and DataGrid rows using JQuery

The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.
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