Link to home
Start Free TrialLog in
Avatar of braunis
braunis

asked on

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>
Avatar of Mrunal
Mrunal
Flag of India image

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

ASKER CERTIFIED SOLUTION
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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"))
Avatar of braunis
braunis

ASKER

I only had to tweak it a little but the solution led me down the correct path.