ASP - populate a gridview from datasource (blob field)

I am trying to populate a gridview in ASP (vb.net).  Normally this is really easy but in this case one of the fields that I need is a BLOB Field.  This BLOB field is actually a large memo field and not a picture.   I had thought to create a template in the gridview that would display the fields since they need to be listed Vertically.  Below is what I have that does not work.

Protected Sub TabPanel5_Load()
      'Customer Notes Tab
            Dim oradb As String = "Data Source=VMFG;User Id=SYSADM;Password=Ocala;"      ' Visual Basic
            Dim orclConn As New OracleConnection(oradb)      ' VB.NET

            Dim cmdText = "SELECT TYPE, OWNER_ID, CREATE_DATE, NOTE FROM NOTATION WHERE (TYPE = 'C' OR TYPE = 'CC') AND OWNER_ID = '" & lblCustNum.Text & "' ORDER BY CREATE_DATE DESC"
            Dim orclCmd As OracleCommand = New OracleCommand("SELECT TYPE, OWNER_ID, CREATE_DATE, NOTE FROM NOTATION WHERE (TYPE = 'C' OR TYPE = 'CC') AND OWNER_ID = '" & lblCustNum.Text & "' ORDER BY CREATE_DATE DESC")
            Dim da As New OracleDataAdapter(cmdText, orclConn)

            'orclCmd.Parameters.Add(New OracleParameter("RECORD", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

            'Setting the dataAdapter to the sqlCommand.
            Dim ds As New DataSet()
            Dim dt As New DataTable()

            // get the schema
            da.FillSchema(dt, SchemaType.Source)

            Try

                    da.Fill(ds, "NOTATION")
                    gvCustNotes.DataSource = ds.Tables("NOTATION")
                    gvCustNotes.DataBind()

            Catch ex As Exception

            Finally

                    orclConn.Close()

            End Try
                  'Create an instance of DataTable
                  'Dim dt As New DataTable()

                  ''Create an ID column for adding to the Datatable
                  'Dim dcol As New DataColumn(CreateDate, GetType(System.String))
                  'dcol.AutoIncrement = True
                  'dt.Columns.Add(dcol)

                  ''Create an ID column for adding to the Datatable
                  'dcol = New DataColumn(CustNotes, GetType(System.String))
                  'dt.Columns.Add(dcol)


      'Clear fields prior to load
            'gvCustNotes.Columns.Clear()

            'Dim getCustNotes As String

            'Try

            '      Dim byteBLOBData As [Byte]() = New [Byte](-1) {}
            '      byteBLOBData = DirectCast(dbpRow("NOTE"), [Byte]())
            '      getCustNotes = System.Text.Encoding.UTF8.GetString(byteBLOBData)

HTML layout

                  <cc1:TabPanel ID="TabPanel5" runat="server" BackColor="#CCCCCC" Font-Size="Small" ForeColor="Black" HeaderText="SEARCH Results">
                  <HeaderTemplate>Notes</HeaderTemplate>
                  <ContentTemplate>
                  <table>
                        <tr><td><asp:Panel ID="Panel15" runat="server" BackColor="#CCCCCC" Height="450px"
                                    Width="700px">
                                    <table>
                                    <tr><td width="700">
                                          <asp:Label ID="Label44" runat="server" Text="Under Development"></asp:Label>
                                          </td>
                                    </tr>
                                    <tr><td>
                                          <asp:GridView ID="gvCustNotes" runat="server" AutoGenerateColumns="False"
                                                Width="675px">
                                                <Columns>

                                                </Columns>
                                          </asp:GridView>
                                          </td></tr>
                                    </table>
                              </asp:Panel>
                              </td></tr>
                              <tr><td>
                                    <asp:SqlDataSource ID="dsCustomerNotes" runat="server"
                                          ConnectionString="<%$ ConnectionStrings:dsCustomerNote %>"
                                          ProviderName="<%$ ConnectionStrings:dsCustomerNote.ProviderName %>" SelectCommand="SELECT OWNER_ID, CREATE_DATE, NOTE
FROM NOTATION
WHERE (TYPE = 'C' OR TYPE = 'CC') AND
OWNER_ID = :svOWNER_ID
ORDER BY CREATE_DATE DESC">
                                          <SelectParameters>
                                                <asp:ControlParameter ControlID="lblCustNum" Name="svOWNER_ID"
                                                      PropertyName="Text" />
                                          </SelectParameters>
                                    </asp:SqlDataSource>
                              </td>
                              </tr>
                  </table>
                  </ContentTemplate>
                  </cc1:TabPanel>
braunisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MrunalCommented:
you can go with nested controls (e.g. nested gridview)

0
MlandaTCommented:
Create a template column in your grid and put a suitable control in it... I assume either a textbox or a label.

<asp:TemplateField HeaderText="Notes">
    <ItemTemplate>
         <asp:Label ID="lblNotes" runat="server"> </asp:Label>
    </ItemTemplate>
</asp:TemplateField>

Open in new window


Then in the RowDataBound event for the grid, use this code:
  Sub gvCustNotes_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles gvCustNotes.RowDataBound

    If e.Row.RowType = DataControlRowType.DataRow Then

          Dim lblNotes  As Control = e.Row.FindControl("lblNotes")

          if  lblNotes IsNot Nothing

               Dim byteBLOBData As [Byte]() = New [Byte](-1) {}
               byteBLOBData = DirectCast(dbpRow("NOTE"), [Byte]())
               getCustNotes = System.Text.Encoding.UTF8.GetString(byteBLOBData)

               lblNotes.Text = getCustNotes;

         end if

     end if

end 

Open in new window



something along those lines (I have not tried to compile this code and there may be one or two errors... but I hope you get the idea)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jesse HouwingScrum Trainer | Microsoft MVP | ALM Ranger | ConsultantCommented:
Also, before continuing, please check your code for SQL Injection attacks, as it contains them for sure.

You can use parameters to pass variables to a SQL statement in order to prevent them:

            Dim cmdText = "SELECT TYPE, OWNER_ID, CREATE_DATE, NOTE FROM NOTATION WHERE (TYPE = 'C' OR TYPE = 'CC') AND OWNER_ID = :CustNum ORDER BY CREATE_DATE DESC"
            Dim orclCmd As OracleCommand = New OracleCommand(cmdText)

            orclCmd.Parameters.AddWithValue(":CustNum",lblCustNum.Text)

            Dim da As New OracleDataAdapter(cmdText, orclConn)

Also, in your finally clause, dispose your Command objects properly. OracleCommand objects can hold native resources, database connections and RefFursors open. You need to dispose them properly as you would with your Connection object.

Before calling Close on your Connection object, check if it exists and it's state (it must be open in order to call close).

Instead of using Directcast, you could use the Field(Of T) extension method to access the data. It will do the proper checks and will cast the value to the proper value.

getCustNotes = System.Text.Encoding.UTF8.GetString(dbpRow.Field(Of [Byte]())("NOTE"))
0
braunisAuthor Commented:
I only had to tweak it a little but the solution led me down the correct path.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.