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?
 
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
 
MrunalCommented:
you can go with nested controls (e.g. nested gridview)

0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.