Coffeehouse Thread

63 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Stupid Question

Back to Forum: Coffeehouse
  • User profile image
    Steve411

    I'm running a local SQL 05' database, the appropriate rights are granted for the user, however im having a problem with one of the tables.

    The table contains data for certain notes; i have a simple SELECT query executing directly from code and a stored precedure. No matter WHICH one of these methods i use it ALWAYS returns one row, even though there are multiple.

    note that the WHERE clause is not used in any of these queries

    I tried this good ol' method:
    SELECT     Notes.* FROM       [Notes]

    However that works in the query designer, but no where else!

    What could be causing this?
    - Steve

  • User profile image
    Steve411

    jaylittle wrote:
    Stupid Suggestion:  Try reffering to the tablename consistently.  Instead of flip flopping between Notes and [Notes] try using the same nomenclature everytime.  You may find that different DB drivers (depending upon your connection string) interpret things like this differently so it's important to be consistent.


    That query was generated by the SQL Query designer; tell that to the guys who created it. Either way, I don't believe this should be causing it, as the same script works for a different table.

    - Steve

  • User profile image
    Steve411

    jaylittle wrote:
    Why ask for suggestions again?


    I'm pointing out a mistake that i know occurs in SQL; you personally didn't have to answer my question.

    - Steve

  • User profile image
    Minh

    Check the default database for the account doing the query.

  • User profile image
    Steve411

    Minh wrote:
    Check the default database for the account doing the query.


    This database runs on SQL Express, therefor since its external it has to be attached. Attachment and everything else works fine except for the SELECT string for that certain table.

    Also, i am running it under the UserInstance = true property.

    - Steve

  • User profile image
    julianbenja​min

    What's the code you use to run the Stored Procedure from your application?  And how do you know it's only returning 1 row?

    Could it be that you're not iterating through the returned resultset?

  • User profile image
    Steve411

    julianbenjamin wrote:
    What's the code you use to run the Stored Procedure from your application?  And how do you know it's only returning 1 row?

    Could it be that you're not iterating through the returned resultset?


    SqlCommand tempCom = new SqlCommand("Notes_GetCollection", mCon);
    tempCom.CommandType = CommandType.StoredProcedure;

    tempRdr = tempCom.ExecuteReader();

    while(tempRdr.Read())
    {
       // the processing here
    }

    I execute the stored proc directly through the designer and it sais it only returns one row, when two or more exist.

    Running [dbo].[Notes_GetCollection].

    Fields are too long to type
    No rows affected.
    (1 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[Notes_GetCollection].

  • User profile image
    Cornelius Ellsonpeter

    julianbenjamin wrote:
    What's the code you use to run the Stored Procedure from your application?  And how do you know it's only returning 1 row?

    Could it be that you're not iterating through the returned resultset?
    Hey Beer, how ya doing.

    That's a good thing to look at though. You have to check through every step of the process from table creation, to population (speaking of that, there is more than one row of data in the table, right?), to the code that processes the results.

  • User profile image
    Steve411

    Cornelius Ellsonpeter wrote:
    
    julianbenjamin wrote: What's the code you use to run the Stored Procedure from your application?  And how do you know it's only returning 1 row?

    Could it be that you're not iterating through the returned resultset?
    Hey Beer, how ya doing.

    That's a good thing to look at though. You have to check through every step of the process from table creation, to population (speaking of that, there is more than one row of data in the table, right?), to the code that processes the results.


    Yes there are multiple rows.

    This fails on the code level, stored proc level and even not using a stored procedure. It works like crazy for the "Categories" table though.

    - Steve

  • User profile image
    julianbenja​min

    Steve411 wrote:
    

    julianbenjamin wrote: What's the code you use to run the Stored Procedure from your application?  And how do you know it's only returning 1 row?

    Could it be that you're not iterating through the returned resultset?


    SqlCommand tempCom = new SqlCommand("Notes_GetCollection", mCon);
    tempCom.CommandType = CommandType.StoredProcedure;

    tempRdr = tempCom.ExecuteReader();

    while(tempRdr.Read())
    {
       // the processing here
    }

    I execute the stored proc directly through the designer and it sais it only returns one row, when two or more exist.

    Running [dbo].[Notes_GetCollection].

    Fields are too long to type
    No rows affected.
    (1 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[Notes_GetCollection].



    And are you sure there are multiple records in the Notes table?  You're not selecting from Notes via an outer join to Categories, and thus returning the same note for all categories?

  • User profile image
    julianbenja​min

    Cornelius Ellsonpeter wrote:
    
    julianbenjamin wrote: What's the code you use to run the Stored Procedure from your application?  And how do you know it's only returning 1 row?

    Could it be that you're not iterating through the returned resultset?
    Hey Beer, how ya doing.

    That's a good thing to look at though. You have to check through every step of the process from table creation, to population (speaking of that, there is more than one row of data in the table, right?), to the code that processes the results.


    Huh?  Beer?  I don't like beer.  I'm a Ketel One man myself.

  • User profile image
    julianbenja​min

    blatzcoder wrote:
    
    julianbenjamin wrote: 
    Cornelius Ellsonpeter wrote: 
    julianbenjamin wrote: What's the code you use to run the Stored Procedure from your application?  And how do you know it's only returning 1 row?

    Could it be that you're not iterating through the returned resultset?
    Hey Beer, how ya doing.

    That's a good thing to look at though. You have to check through every step of the process from table creation, to population (speaking of that, there is more than one row of data in the table, right?), to the code that processes the results.


    Huh?  Beer?  I don't like beer.  I'm a Ketel One man myself.
    Never mind him. He's confused. He thinks everyone is a Beer clone. Paolo suffers from the same problem, and Harlequin too. Now, if Karim would make a cameo appearance it would be a full house of trolls.

    And JonathanH would nuke away people's posts.


    Blatz,

    While I appreciate the defense, you're not really adding anything useful to this thread other than waiting for someone to post so you can criticize.  And I'm sure your comments don't really help Steve with his problem.

  • User profile image
    julianbenja​min

    blatzcoder wrote:
    
    julianbenjamin wrote: 
    blatzcoder wrote: 
     
    Never mind him. He's confused. He thinks everyone is a Beer clone. Paolo suffers from the same problem, and Harlequin too. Now, if Karim would make a cameo appearance it would be a full house of trolls.

    And JonathanH would nuke away people's posts.


    Blatz,

    While I appreciate the defense, you're not really adding anything useful to this thread other than waiting for someone to post so you can criticize.  And I'm sure your comments don't really help Steve with his problem.
    Well thanks for taking the higher ground, Beer. I'm sure the mods appreciate it too.  


    Sure, no problemo.

  • User profile image
    Steve411

    julianbenjamin wrote:
    
    Steve411 wrote: 

    julianbenjamin wrote: What's the code you use to run the Stored Procedure from your application?  And how do you know it's only returning 1 row?

    Could it be that you're not iterating through the returned resultset?


    SqlCommand tempCom = new SqlCommand("Notes_GetCollection", mCon);
    tempCom.CommandType = CommandType.StoredProcedure;

    tempRdr = tempCom.ExecuteReader();

    while(tempRdr.Read())
    {
       // the processing here
    }

    I execute the stored proc directly through the designer and it sais it only returns one row, when two or more exist.

    Running [dbo].[Notes_GetCollection].

    Fields are too long to type
    No rows affected.
    (1 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[Notes_GetCollection].



    And are you sure there are multiple records in the Notes table?  You're not selecting from Notes via an outer join to Categories, and thus returning the same note for all categories?


    Yes, there are multiple rows. And no i am not doing an outer join.

    - Steve

  • User profile image
    julianbenja​min

    Steve411 wrote:
    
    julianbenjamin wrote: 

    And are you sure there are multiple records in the Notes table?  You're not selecting from Notes via an outer join to Categories, and thus returning the same note for all categories?


    Yes, there are multiple rows. And no i am not doing an outer join.

    - Steve


    Well, I'm surely stumped.  I'm sure you don't have row-level security on (not sure if SQL express supports it).  Have you tried logging into the SQL server with the admin account?

  • User profile image
    Steve411

    julianbenjamin wrote:
    
    Well, I'm surely stumped.  I'm sure you don't have row-level security on (not sure if SQL express supports it).  Have you tried logging into the SQL server with the admin account?


    I dont have row-level security now. I've also tried the admin login but there seems to be no way to fix it.

    This is the only table this bug occurs on; no other.

    - Steve

  • User profile image
    julianbenja​min

    Steve411 wrote:
    
    julianbenjamin wrote: 
    Well, I'm surely stumped.  I'm sure you don't have row-level security on (not sure if SQL express supports it).  Have you tried logging into the SQL server with the admin account?


    I dont have row-level security now. I've also tried the admin login but there seems to be no way to fix it.

    This is the only table this bug occurs on; no other.

    - Steve


    Have you tried renaming the table?

  • User profile image
    Rotem Kirshenbaum

    Check the CommandBehavior of the reader, maybe it has somehow (?) The value of SingleRow or SingleResult (though I doubt it...)

    Rotem

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.