none
Accessing SQL Query Results RRS feed

  • Question

  • Hi All,

     

    I am having trouble working out how to analyse the results of a query passed from SQL. The problem is that the part of the results that I am interested in isn't part of any table in the database, so when I try to access it it gives me "No Column Exists" error. So I was wondering if the results of a query in SQLserver 2005 are stored in a temporary table that can be accessed via SQLDataAdapter?

     

    MonkeyMark

    Friday, January 18, 2008 2:27 AM

Answers

All replies

  • Could you provide some code? It sounds like you're trying to access the results from a datatable that isn't populated. The standard usage here is that you use the data adapter to fill a dataset and get the results from your query there. The results from a query aren't stored in a temp table unless you explicitly set it up that way which sounds beyond what you're trying to do here.

     

    Friday, January 18, 2008 2:45 AM
  • Query is:

    SELECT [PC],

    MAX([DateTime]) AS [DateTime],

    CASE WHEN DATEDIFF(mi,MAX([DateTime]), GETDATE()) < 30 then 'Yes'

    Else 'No' END AS [Running]

    FROM TABLE

    GROUP BY [PC]

     

    Results in:

    PC                      DateTime                   Running

    1                     5/1/08 15:30:58                No

    2                    18/1/08 09:17:10                Yes

    3                    18/1/08 09:53:43                Yes

    .                                 .                           .

    .                                 .                           .

    .                                 .                           .

     

     

    In C#:

    Code Block

    commandRUNNING.Connection = connectSQL;

    commandRUNNING.CommandText = /*Query above*/

    SqlDataAdapter dataAdapterRUNNING= new SqlDataAdapter();

     

    dataAdapterRUNNING.SelectCommand = commandALLANTS;

    dataAdapterRUNNING.SelectCommand.CommandTimeout = 900;

    dataAdapterRUNNING.TableMappings.Add("Table", "TABLE");

    dataAdapterRUNNING.Fill(datasetALLANTS);

     

    foreach (DataRow dataRow in datatableRUNNING.Rows)

    {

    string isRunning = dataRowRUNNING["Running"].ToString();

     

    if (isRunning == "No")

    //what I would like to do

    }

     

     

    The problem occurs because table TABLE (in the SQLserver database) doesn't contain a Column "Running", but my query does have this "Running" column

    Friday, January 18, 2008 4:48 AM
  • Hello,

     

    I think the problem is coming from the line

    dataAdapterRUNNING.TableMappings.Add("Table", "TABLE");

     

    I think you have to play with MissingMappingAction

    Try this link

    http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.missingmappingaction(VS.80).aspx

     

    Also try this link:

    http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.missingschemaaction(VS.80).aspx

     

     

    It's just an idea you may "dig"

     

    Have a nice day

    Monday, January 21, 2008 10:18 AM
  • I believe you do not need to use TableMapping in your case at all. Any specific reason why you set it during Fill? If you remove line of code that sets Tablemapping, it should work fine.

     

    Tuesday, January 22, 2008 12:30 AM
    Moderator
  • Thanks for your help Papy Normand and VMazur, both of your posts helped me out.

     

    MonkeyMark

    Friday, February 1, 2008 6:43 AM