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>

Mrunal

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

ASKER CERTIFIED SOLUTION

Mlanda T

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Jesse Houwing

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"))

braunis

ASKER

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