locked
Putting data into GridView Object RRS feed

  • Question

  • User-548542357 posted

    I would like to put data into a GridView object. Here are what my objects look like:


        Public Class QueryResultSingleRow
            Inherits SerializableStringDictionary
        End Class
        '
    
        ' This class represents all query results (list of all result rows).
        Public Class QueryResultAllRows
            Inherits List(Of QueryResultSingleRow)
        End Class
        '
    
        ' This class includes all query results and advises # rows and columns involved.
        Public Class CompiledQueryResults
            Public numResultRows As Integer
            Public numColsPerResult As Integer
            Public resultRows As QueryResultAllRows
        End Class
        '
    
        ' This class contains returning info for query attempts.
        Public Class QueryResultPackage
            Public successful As Boolean
            Public name As String
            Public queryResults As CompiledQueryResults
            Public errorText As String
        End Class


    I return an object of type QueryResultPackage.


    thanks in advance


    Thursday, July 16, 2009 2:44 PM

All replies

  • User16212438 posted
    Once you have the data, assuming it is in a variable called qrp, and assuming your GridView has ID="GridView1"
    GridView1.DataSource = qrp.queryResults.resultRows
    GridView1.DataBind()
    
    Thursday, July 16, 2009 3:50 PM
  • User-548542357 posted

    I implemented that and i got a column that said count and had about 30 rows of the number 8. I then did this:

    Count
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8
    8

    For i As Integer = 0 To response.queryResults.numColsPerResult - 1

                resultsGrid.DataSource = response.queryResults.resultRows(i)
                resultsGrid.DataBind()

            Next

     

    and it returned only the FIRST result and not in a row. All in a column.

     

    The keys should be at the top and the rest of th values populating like normal i guess.

    Key Value
    ID

    NGB00000154

    HEADLINE If NGB documentation not installed, says it's a software version issue.
    NAME Postponed
    OE_CONTACT Cindy
    SCHEDULED_VERSION Next available version
    FOUND_VERSION Before 1.0.0
    BUILT_VERSION  
    TESTED_BY  
    Thursday, July 16, 2009 5:24 PM
  • User-548542357 posted

















    Thursday, July 16, 2009 5:26 PM
  • User16212438 posted
    Hmmm, not the result I expected. But I do think I can explain what happened. The question is how to solve it.

    GridView is made to display rows. Each row is filled from an object. The DataSource you give to GridView must be an IEnumerable of the objects to display. That is why I suggested using resultRows as DataSource.

    However, then GridView has to render each object it gets as a row. To do that it has 2 options.
    If the object is a DataRowView (or something similar) the columns can be accessed through an indexer and each column in the DataRow will become a field to bind to in the row.
    If it is something it doesn't know, GridView looks at the object for public properties that it can display. In this case, the type apparently only has a public property Count.

    The reason it displays only one when DataBinding in a loop is something like this.
    The GridView gets an object to bind to that implements IEnumerable. So it will make a row for each entry. Each entry is a KeyValuePair that has public properties Key and Value, which are turned into columns. Each entry will become a row.
    GridView.DataBind() always replaces the entire content by the new. So doing it in a loop like that will not result in data getting added. Instead I would expect the last row to be displayed.

    I don't know what to do to a class so it will behave like a DataRowView so you can pass the properties and values to GridView to make the columns right. That will take some research.
    Friday, July 17, 2009 5:31 AM
  • User-548542357 posted

    Could I parse everything out to a text file then bring it back in an bind it somehow?

    Friday, July 17, 2009 11:13 AM
  • User-548542357 posted

    Maybe this will help. Here is how the data is put into the object. I didnt write this code i just gotta get the data out of it.


      Public Function RetrieveQueryResults(ByRef cqSession As ClearQuestOleServer.Session, _
                                                ByVal sqlStmt As String) As CompiledQueryResults
    
            Dim numCols As Integer, status As Integer, columnIdx As Integer
            Dim numRows As Integer
            Dim rowContents As String
            Dim queryResultRow As QueryResultSingleRow
            Dim queryResults As New CompiledQueryResults
            Dim cqResultSet As ClearQuestOleServer.OAdResultset
    
            cqResultSet = cqSession.BuildSQLQuery(sqlStmt)
            cqResultSet.Execute()
    
            ' Get the number of columns returned by the query.
            numRows = 0
            numCols = cqResultSet.GetNumberOfColumns
            queryResults.resultRows = New QueryResultAllRows
            status = cqResultSet.MoveNext
    
            ' Collect query results.
            Do While status = AD_SUCCESS
    
                numRows = numRows + 1
                queryResultRow = New QueryResultSingleRow
    
                ' Construct the row contents into a single line of tab-separated fields.
                rowContents = ""
                For columnIdx = 1 To numCols
                    queryResultRow.Add(cqResultSet.GetColumnLabel(columnIdx), cqResultSet.GetColumnValue(columnIdx))
                Next columnIdx
    
                ' Add the query row result to the compiled list of all rows.
                queryResults.resultRows.Add(queryResultRow)
                status = cqResultSet.MoveNext
    
            Loop
    
            queryResults.numColsPerResult = numCols
            queryResults.numResultRows = numRows
    
            ' Free up CQ resources.
            GC.KeepAlive(cqResultSet)
            cqResultSet = Nothing
    
            Return queryResults


    Friday, July 17, 2009 1:04 PM
  • User16212438 posted
    I see you have an Sql statement. That opens the possibility of bypassing these objects alltogether. Do you need these objects for anything else?
    Friday, July 17, 2009 1:50 PM
  • User-548542357 posted

    Yes I use an SQL statement but i think the data comes back all weird. Its coming from a Clear Quest Oracle Server. This is what the API documentation says:


    BuildSQLQuery

    <!-- -->

    Description

    <!-- -->Creates and returns a ResultSet object using a raw SQL string.

    <!-- -->You use the <samp>BuildQuery</samp> method to define a query and filter(s), as opposed to writing a SQL query string and using it with the BuildSQLQuery method.

    <!-- -->Like <samp>BuildResultSet</samp>, this method creates a ResultSet object that you can use to run a query. Unlike BuildResultSet, this method uses a raw SQL string instead of a QueryDef object to build the data structures of the ResultSet object. Do not call this method until you have completely constructed the SQL query string.

    <!-- -->Like <samp>BuildResultSet</samp>, this method generates the data structures needed to store the query data but does not fetch the data. To run the query and fetch the resulting data, you must call the ResultSet object's <samp>Execute</samp> method.

    <!-- -->Unlike BuildResultSet, BuildSQLQuery makes no use of a QueryDef object, so the query defined by the SQL string cannot be manipulated before constructing the ResultSet.


    Then


    ResultSet Object

    <!-- -->You can use a ResultSet object to execute a query and browse the query results.

    <!-- -->When you create queries using the QueryDef Object, you must create a corresponding ResultSet object to run the query and obtain the results. Each ResultSet object is customized for the query it is running. The ResultSet object contains data structures that organize data from the query into rows and columns, where each row represents a single data record and each column represents one field from that data record. After running the query, you can navigate (move) from row to row, and from column to column, to obtain the data you want.

    <!-- -->Note that:

    • <!-- -->Columns are numbered from (1 through N), not (0 through N-1).
    • <!-- -->After the result set is generated, you may need to fill in parameter values, if it is a parameterized query.
    • <!-- -->After the result set is generated and parameters (if any) are set, you may execute it to see the output of the query. It is permitted to execute the result set multiple times, if you wish to rerun the query. (Perhaps you have cleared and reset the parameter values.) It is also legal to get the SQL for the query.
    • <!-- -->Conceptually a query may generate so much output that it would be impossible or greatly inefficient to just copy it from the database over into the memory of the program. So, you have to use a "cursor" to navigate through the output, using the <samp>MoveNext</samp> method.
    • <!-- -->Immediately after executing the result set, the cursor is positioned "just before" the first item, so you have to call MoveNext before you can extract the first value.
    • <!-- -->To get the value after you are positioned, use the <samp>GetColumnValue</samp> method.
    Friday, July 17, 2009 2:11 PM
  • User16212438 posted
    No expert, but I think this is making it complicated. I take it the code in your first post is generated. Could you adapt that code (add something to it) for your application or will that cause other problems? Not that I have any idea what to change...
    Friday, July 17, 2009 2:22 PM
  • User-548542357 posted

    Well Clear Quest, when the sql is ran, returns the information in that ResultSet object and its in a very strange pattern. It is then broken down to those other objects inorder to make it easy to put up to display. The problem is now I guess that I have no clue what the key values are. If i find that out i can loop through the data... put it in a text file...then parse it out and plop it into a grid of some sort. Good idea?

    Friday, July 17, 2009 4:03 PM
  • User-548542357 posted

    ok i got it out to a file...how do i parse this thing?


    ¥<QueryResultSingleRow>
        <ID>NGB00001079</ID>
        <HEADLINE>HeadType.ini does not match document ngb-0069</HEADLINE>
        <NAME>Released</NAME>
        <OE_CONTACT>Brian</OE_CONTACT>
        <SCHEDULED_VERSION>Release 2.0.0</SCHEDULED_VERSION>
        <FOUND_VERSION>Before 1.0.0</FOUND_VERSION>
        <BUILT_VERSION>1.9.16 Date: 08_02_02</BUILT_VERSION>
        <TESTED_BY>Mark</TESTED_BY>


    Friday, July 17, 2009 5:02 PM
  • User16212438 posted
    Looks like some nicely built XML. I don't have much experience with it, but XmlDataSource comes to mind.

    I find it odd that ClearQuest does have an API for .NET, but then it doesn't properly interface with the DataBinding Controls. Are there any such Controls in the ClearQuest API?
    And have you tried fora about ClearQuest with this question?
    Friday, July 17, 2009 5:48 PM
  • User-548542357 posted

    Ill try Datasource.


    Ya its weird but the way the do their data storage is odd. Im just learning it now but I am hoping it gets easier than what I am dealing with now.


    Clear Quest forums are useless. I posted on there and i got 80 views with no replies. What a waste.

    Friday, July 17, 2009 6:02 PM
  • User-548542357 posted

    Ok so i have no clue what ive done. I had the data getting written to a file. I guess somewhere i didnt save it or something because it has reverted to a previos version of code. How can i just push the information out to a file? Now when I try i get an error that I am out of range!?!?!

    Monday, July 20, 2009 11:55 AM
  • User16212438 posted
    Hmmm, that sounds weird. It should save when you test. ASP.NET can only run saved files. No idea what the error comes from.

    I have been mulling over this a bit. And I am left with 2 thoughts I haven't fully explored yet.

    1) The QueryResultSingleRow could be extended to do what DataRowView does. Basicaly, it overrides GetProperties and GetProperty so the DataBinding can get the column-names and -values. This is only possible if you can adapt the generated code. But if you can it is probably the easiest solution.
    2) After getting the QueryResults, you could take the first row and use the Keys collection to build a DataTable. Then loop through the rows to fill that DataTable with DataRows. You can then turn the DataTable into a DataView and set it as DataSource. Sounds pretty complicated, but once you know how to do it it's really quite straightforward. This is the only option if you can't adapt the generated code.

    See if you can work with any of this. And I'll be happy to answer more questions if needed. Maybe even try a few things to see what happens.
    Monday, July 20, 2009 5:41 PM
  • User-548542357 posted

    Ok so amazingly I actually implemented what you said already. Weird. But I made an object class that took all the data out of each row because when i pushed out just the first element i figured that i got the key which is the tag from teh XML form from my previous posting. So i took all that...put it into an object then put all the objects into a collection then did exactly what you said to do to populate the table.


    Now I got a table with data but its just that...only a table of data and I have no clue how to make it clickable like a datagridview in a windows form. I need a row to be clickable and trigger an event. Even if i can make just teh ID portion clickable (and notciably so) would work. i would like to have that event bring up a new page then go get more data and pop it into a new window.

    Problem is grid view does not have this option...and i dont know how to start tackling this cause nothing is clickable in the table.

    Monday, July 20, 2009 5:53 PM
  • User16212438 posted
    GridView has an attribute AutoGenerateSelectButton. Set it to true and you get a Select-linkbutton in each row. When you click it the row it is in is selected. Catch this with SelectedIndexChanged.
    One catch: Once a line is selected, clicking Select again won't fire SelectedIndexChanged. So you may want to set SelectedIndex to -1 in the handler.
    However, that will only get you a postback. You will have to use Redirect to get to another page, and decide on a way to pass data to that new page.

    If you really want to make it look classy, there is a way to emulate clicking Select on the entire row, and without the ugly linkbutton. But I would have to look up how it is done. It requires setting some things that GridView normaly sets, but in different places.
    Monday, July 20, 2009 6:21 PM
  • User-548542357 posted

    Awesome. I would really like to make this as classy as possible.


    Thanks so much for your help! I really appreciate it all.

    Monday, July 20, 2009 6:42 PM
  • User16212438 posted

    Ok. Looked it up. It's the kind of thing I will probably never memorize.

    Provided you got the selection working with AutoGenerateSelectButton="true", RowCreated of my GridView1 has the following for  e.Row.RowType == DataControlRowType.DataRow:

    // Show hand like the mouseover of buttons
    e.Row.Attributes["onmouseover"] = "this.style.cursor='pointer';";
    
    // Do "Click" on clicking anywhere in the row.
    e.Row.Attributes["onclick"] = Page.ClientScript.GetPostBackClientHyperlink(GridView1, "Select$" + e.Row.RowIndex.ToString());

     

    And to prevent Validation problems, the following code is in the overridden Render:

    // Register the rows for validation.
    // This will keep ASP from giving nasty errors from getting events from controls that shouldn't be sending any.
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        Page.ClientScript.RegisterForEventValidation(GridView1.UniqueID, "Select$" + i);
    }
    
    // Do all that would normaly be done.
    base.Render(writer);
    

     

    A few words of explanation may be in order.

     

    GetPostBackClientHyperlink returns the javascript that causes a PostBack for the Control. The PostBack will have the String as arguments. The arguments are for interpretation by the Control. So they can differ per Control type.

    For GridView, the arguments are CommandName and CommandArgument, separated by a $. Select is a CommandName that GridView supports, taking RowIndex as CommandArgument. A generated SelectButton will have exactly this PostBackClientHyperlink, CommandName and CommandArgument.

     

    The stuff in PreRender is the other part of a generated SelectButton. In ASP.NET PostBacks are validated. A Control that isn't registered to cause a certain PostBack will not be allowed to process that PostBack when it occurs. It will lead to an Exception about Event Validation failing.

    If you have a generated SelectButton, that will register the event. However, with a Row-select you will typically not want the generated SelectButton. So the event will need registering. That's what is done in the PreRender.

     

    Tuesday, July 21, 2009 5:09 AM