title: Problems retrieving Data using stored procedure in asp.net web service
body: <p>i have this stored procedure in my DataBase:</p>
  
  <pre><code>Select something From somewhere Where Id = @Id  And Category in (Select carId From CarCategory where..)
  </code></pre>
  
  <p>when i test this stored procedure in the <strong>Visual Studio</strong> <em>Execute</em>  it works just fine</p>
  
  <p><strong>But</strong> When i call my web service function that uses this procedure it doesn't give me any results (the <em>DataSet</em> retrieved is empty)</p>
  
  <p>so i had no choice but to rewrite the stored procedure in like this:</p>
  
  <pre><code> Select somthing From somewhere Where Id = @Id  And Category = @Category
  </code></pre>
  
  <p>But that isn't what i really want i want the previous method to function.</p>
  
  <p><strong>more info on this problem:</strong></p>
  
  <p>I think that the <em>remote server</em>  can't access the <em>CarCategory</em> table to get the data,,, is that possible??</p>
  
  <p>because when i use another stored procedure to call a function that call a another procedure to get info from <em>CarCategory</em> it doesn't work and send me an error.</p>
  
  <p>Why cant the server find any row,, there is plenty of rows should be retrieved it is really annoying, i cant figure out why can any one help??????????????</p>
  
  <p><em><strong>this error is:</em></strong></p>
  
  <pre><code>System.Web.Services.Protocols.SoapException: Server was unable to process request. ---&gt; System.IndexOutOfRangeException: There is no row at position 0.
     at System.Data.RBTree`1.GetNodeByIndex(Int32 userIndex)
     at System.Data.DataRowCollection.get_Item(Int32 index)
     at Selling_Agency.categoryNameToID(String Name) in e:\from moaaz\WEB SERVICES\carBroker_SellingAgency1\App_Code\Selling_Agency.cs:line 68
     --- End of inner exception stack trace ---
  </code></pre>