.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

SqlDataReader returns no rows

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

I have a simple C# 2.0 app that performs a simple query.

  SqlConnection oSqlCon=new SqlConnection(strConnectStr);
  SqlCommand oSqlCmd=new SqlCommand("select Name from Person",oSqlCon);
  SqlDataReader oSqlRdr=oSqlCmd.ExecuteReader();

I has been working fine. A client recently installed SQL2005 (their old version is SQL2000). When executing the above code on a machine other than the server no rows are returned. The connection uses sqlserver authentication, not integrated authentication.

To summarize: the above code works against the old sql instance from both the server machine and other local network machines; works against the new sql instance only from the new server; does NOT work against the new sql instance from local network machines

There are no errors reported; the query simply acts as though the table has no rows. I believe this to a security/permission issue but the two slq instances appear to be configured identically.

Any ideas or suggestions would be appreciated.

View Complete Post

More Related Resource Links

Searching a NVarChar Column with multiple wildcards returns all rows

I'm building a stored procedure to return a set of records, yes nothing big. The column is an NVarchar column and I'm using a select statement of Select * from Table1 where Column1 Like @Column1 Table is currently one record containing the words:  ***Some Test Word***  Is my test word I've set the value of @Column1 to: Some - no records returned Some% - no records returned %Some% - 1 record returned  % - 1 record returned *% - no records returned %*% - 1 record returned %Not Here% - 1 record returned   Can someone tell me why if I have a leading and trailing wildcard I will get all records returned?  Does it have something to do with the '*' characters in the field because some of my users are using these characters. I've also tried changing the select to: where RTRIM(Column1) Like @Column1 with the same result and where RTRIM(Column1) Like N'%' + @Column1 with the same result   What way should I give a user the way to search for a substring inside of an NVarchar field? Oh, I tried using the Substring function and got the same result. Thanks Mike    

Need advice on preformance (web service returns list items on 20000 rows list)

Greetings I need to write a web service that will return sharepoint list items (default web services of sharepoint are no use to me since they just suck when the site is not in english).  Since we have lists with 20k+ items, the return xml might get large. Query web service did manage to do it, yet it took some time to output the xml with those 20k rows.  I am affraid that my custom made web service will preform poorly since it wont be "native" to sharepoint. I am asking for your judgement, did i fell into common pitfalls or something or is there any way to improve preformance of my code? My own web service code looks like this:   [WebMethod] public XmlDocument GetListItems(string siteurl, string listguid,string querytext,string rowlimit) { InitSchemaExporter(siteurl); SPQuery query = new SPQuery(); query.RowLimit = rowlimit.Length==0?10:Convert.ToUInt32(rowlimit); query.Query=querytext; return SchemaExporter.GetListItems(listguid, query); } public void InitSchemaExporter(string siteurl) { SchemaExporter.SetSite(siteurl); SchemaExporter.IgnoreField = ConfigurationSettings.AppSettings["IgnoreField"].Split(new string[1] { "\r\n" }, System.StringSplitOptions.None).ToList<string>(); ; SchemaExporter.IgnoreList = ConfigurationSettings.AppSettings["IgnoreList"].Split(new string[

Query returns correct rows but I am not able to concatenate the rows into a variable :(


Hi all,

Why in the code below I am able to list all rows and display the field 'stepID' but I am not able to concatenate them all in the variable @stepIds?

declare @date as datetime
set @date = '2010-09-20 23:01:20:000'
declare @instance as uniqueidentifier
set @instance = '04658F34-CBF1-4CCD-A683-F23BDB669D35'

  Declare @CrLf char(2)
  Set @CrLf = Char(13) + Char(10)
	Declare @stepIds varchar(8000)
	Set @stepIds = ''
		--@stepIds = @stepIds + ' - Step: ' + CONVERT(varchar(4), stepID)

Select second query if first query returns no rows



I need to return result of the second select statement if the first select statement returns nothing:

-- First statement
SELECT Salesmen.SalesmanID, Salesmen.FullName, 
  Salesmen.AssignedAppointments, Salesmen.Picture, 
FROM Appointments INNER JOIN
  Salesmen ON 
  Appointments.SalesmanID = Salesmen.SalesmanID
WHERE (Appointments.Closed = 1)
ORDER BY Salesmen.AssignedAppointments
-- Second statement
FROM Salesmen
ORDER BY AssignedAppointments

Select Primary Key Column of Table returns 0 rows


I have three tables: Design, Box, Wall.

Design has a column DesignId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false for compatibility with Entity Framework v4.

Box has  a column BoxId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Box also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall has  a column WallId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Wall also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall also has a column BoxId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Box.DesignId has been set as a ForeignKey relating to Design.DesignId.

Wall.DesignId has been set as a ForeignKey relating to Design.DesignId

Wall.BoxId has been set as a ForeignKey relating to Box.BoxId.


If I execute      SELECT DesignId FROM Design     I see the correct number of rows.

If I execute     SELECT BoxId From Box        I see zer

SQL Server user-defined aggregate returns an error if aggregating 0 rows

I have this SQL Server user-defined aggregate:

[SqlUserDefinedAggregate(Format.UserDefined, Name="median", IsInvariantToDuplicates=false, IsInvariantToNulls=true, IsInvariantToOrder=true, IsNullIfEmpty=true, MaxByteSize=8000)] 
public class MedianCalculator : IBinarySerialize { 
    private List<double> values; 
    public void Init() { 
        values = new List<double>(); 
    public void Accumulate(SqlDouble value) { 
        if (!value.IsNull) 
    public void Merge(MedianCalculator other) { 
    public SqlDouble Terminate() { 
        if (values == null || values.Count == 0) 
            return SqlDouble.Null; 
        return (values[(int)Math.Floor((values.Count - 1) / 2.0)] + values[(int)Math.Ceiling((values.Count - 1) / 2.0)]) / 2.0; 
    public void Read(BinaryReader r) { 
        int c = r.ReadInt32(); 
        values = new List<double>(c); 
        for (int i = 0; i < c; i++) 
    public void Write(BinaryWriter w) { 
        for (int i = 0; i < values.Count; i++) 
After deploying this aggregate, I try to run this query:

FullTextSqlQuery returns the right number of rows, but they have no data!


Hey all,

I'm running my first fulltextsqquery, using the function below - it seems to all execute fine and 24 rows get returned (query below) which sounds about right for all the people on my site. What's weird though is that all the columns in the results are empty string ("" or {}) - they definitely aren't empty in the user's profile - can anyone help me? Preferred email is a custom field and is mapped onto a People:PreferredEmail(Text) property in search admin - anything else i need to do?

 string query = string.Format("SELECT {0},{1} FROM SCOPE() WHERE \"scope\"='People'""PreferredEmail", "FirstName");
 public static ResultTableCollection RunFullTextSqlQuery(string sql, int rowLimit)

t-sql function returns only 3 rows


hi all,

the following function returns only 3 results, though it should return 7 result sets.

can someone please help me here?

thanks ahead.



ALTER PROCEDURE dbo.GetMissionsForBrowsing
    @Type int,
    @PageIndex INT,
    @NumRows INT,
    @LastItemPulled int

    Declare @startRowIndex INT;
    set @startRowIndex = (@PageIndex * @NumRows) + 1;

    With Entries as (
        SELECT ROW_NUMBER() OVER (ORDER BY Missions.Id ASC) as Row, 
               Missions.Id, Missions.SiteId,Missions.Uploader,Missions.TimeAdded,Missions.Subject,
			   Missions.Watched,Missions.Helped,Missions.Active,Users.Username, Sites.SiteName
        FROM Missions inner join Users
            ON Missions.Uploader = Users.Id 
			inner join Sites on Missions.SiteId = Sites.Id
        WHERE Missions.Type = @type
              AND (Missions.Visible = 1)

    SELECT Id, SiteId,Uploader,TimeAdded,Watched,Helped,Active,Username,Subject,SiteName
    FROM Entries
    WHERE Row between 
    @startRowIndex and @StartRowIndex+@NumRows-1


FullTextSqlQuery in SharePoint returns zero rows


Need to make a custom people picker in my custom web application that will be hosted on SharePoint.  To populate my search, I am making an AJAX call to a web service running the following code:





using (SPSite oSite = new SPSite(

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.

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