Solved

SP_HELP returning the values in .net

Posted on 2007-04-01
5
1,032 Views
Last Modified: 2008-01-09
OK, im trying to call  sp_help in a query using c# in .net 1.14 (2003). Unfortunetly I only get the first row back....how do I get all the other rows? Its like the reader only returns the first part of sp_help.....

'ConString' is the database connection string, 'table' is the table name

public static void SelectAnythingSPHelp(string ConString, string table ,ref ArrayList d1,ref ArrayList d2,ref ArrayList d3,ref ArrayList d4,ref ArrayList d5,ref ArrayList d6,ref ArrayList d7,ref ArrayList d8,ref ArrayList d9,ref ArrayList d10)
{
      SqlConnection myConnection = new SqlConnection(ConString);
      SqlCommand myCommand = new SqlCommand("sp_help "+table,myConnection);
      myConnection.Open();
      SqlDataReader myReader;
      myReader = myCommand.ExecuteReader();
      while (myReader.Read())
      {
            try{d1.Add(myReader[0].ToString());}catch{}
            try{d2.Add(myReader[1].ToString());}catch{}
            try{d3.Add(myReader[2].ToString());}catch{}
            try{d4.Add(myReader[3].ToString());}catch{}
            try{d5.Add(myReader[4].ToString());}catch{}
            try{d6.Add(myReader[5].ToString());}catch{}
            try{d7.Add(myReader[6].ToString());}catch{}
            try{d8.Add(myReader[7].ToString());}catch{}
            try{d9.Add(myReader[8].ToString());}catch{}
            try{d10.Add(myReader[9].ToString());}catch{}
      }
      myReader.Close();
      myConnection.Close();
      return;
}
0
Comment
Question by:Saber37886661
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 18834414
sp_help returns multiple tables - run it in Query anayser to see.
My GUESS is that you are returning the last one only
0
 
LVL 39

Accepted Solution

by:
appari earned 300 total points
ID: 18834417
use NextResult method to check if more results exists,try adding another while loop as follows
**may be my syntax is wrong in c sharp
do{
while (myReader.Read())
      {
            try{d1.Add(myReader[0].ToString());}catch{}
            try{d2.Add(myReader[1].ToString());}catch{}
            try{d3.Add(myReader[2].ToString());}catch{}
            try{d4.Add(myReader[3].ToString());}catch{}
            try{d5.Add(myReader[4].ToString());}catch{}
            try{d6.Add(myReader[5].ToString());}catch{}
            try{d7.Add(myReader[6].ToString());}catch{}
            try{d8.Add(myReader[7].ToString());}catch{}
            try{d9.Add(myReader[8].ToString());}catch{}
            try{d10.Add(myReader[9].ToString());}catch{}
      }
}
while(myReader.NextResult())
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 200 total points
ID: 18834419
sp_help returns 7 rowsets. This example shows how to step through recordsets.


http://support.microsoft.com/kb/311274
0
 
LVL 29

Expert Comment

by:QPR
ID: 18834423
You could disect sp_help into an sp of your own?
Or pipe the sp_help results into a temp table and then return * from that.
0
 
LVL 3

Author Comment

by:Saber37886661
ID: 18834506
appari and nmcdermaid both got it right, thanks guys :)
appari got more points because he was first (by 2 minutes). Thanks heaps for the prompt help guys :)
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question