Retrive OLE Object from Access Database and display in GridView using VB.Net in ASP.Net

Last Reply one year ago By xboxown

Posted one year ago

Hey guys,

In Access table I mad a new column with the type OLE Object and then in the row of the table I added 3 pictures into it and one from a file which is stored as package as the final picture.

Then I did the approriate table relationship so that the package appears in the column/row in ACCESS table.

In my project this is what I added in the datagrid 

<asp:TemplateField HeaderText="Log">
        <asp:Image ID="idLog" runat="server"  ImageUrl='<%#"~/" + DataBinder.Eval(Container.DataItem, "Icon") %>'> </asp:Image>                    

In the ASP.NET side this is what I added

 Private Sub Bind_Data()
        Using con As New OleDbConnection(ConfigurationManager.ConnectionStrings("TestLocal").ConnectionString)
            Dim cmd As New OleDbCommand("SELECT tblWorkOrderItem.WorkOrderItemID, tblWorkOrderItem.bffGrade AS Item, tblWorkOrderItem.Quantity AS Qty, tblWorkOrderItem.Description, tblWorkOrderItem.Note, tblIcons.Icon FROM (tblWorkOrderItem INNER JOIN tblWorkOrder ON tblWorkOrderItem.[WorkOrderID] = tblWorkOrder.[WorkOrderID]) INNER JOIN tblIcons ON tblWorkOrderItem.IconType = tblIcons.ID WHERE [tblWorkOrderItem].[WorkOrderID]=@ID", con) With {.CommandType = CommandType.Text}
            Dim myParamCollection As OleDbParameterCollection = cmd.Parameters
            Dim myParam As OleDbParameter = myParamCollection.Add(New OleDbParameter("ID", OleDbType.Integer))
            myParam.Value = Convert.ToInt16(Session("ID"))
            ' Dim myICounter As OleDbParameter = myParamCollection.Add(New OleDbParameter("iCounter", OleDbType.Integer))
            ' myICounter.Value = 0
            Dim selectResults As New DataSet()
            Dim ole_DBAdapter As New OleDbDataAdapter(cmd)
            If cmd.ExecuteReader.HasRows Then
                GridView1.DataSource = selectResults
            End If
        End Using
    End Sub

However it shows only this in the datagrid no matter what I do, any help please?

Posted one year ago

Very simple I added another column in the datagrid (not edititem) but normal one for displaying text and in the side of things I just used directcast to find controller of that item by searcing for it's ID name. It takes whatever value entered from row(i) in the loop and stores it into access table.


Posted one year ago


HTML side of the code:

    <asp:HiddenField ID="HiddenField1" runat="server" />
    <asp:GridView ID="GridView1" runat="server" EmptyDataText="No data found."  OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowCancelingEdit="GridView1_RowCancelingEdit"  AutoGenerateColumns="False" AllowCustomPaging="True" AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333" GridLines="None">
        <AlternatingRowStyle BackColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#F5F7FB" />
        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
        <SortedDescendingCellStyle BackColor="#E9EBEF" />
        <SortedDescendingHeaderStyle BackColor="#4870BE" />
            <asp:CommandField ShowEditButton="false" />
            <asp:TemplateField HeaderText="WorkOrderItemID">
                       <asp:Label ID="lblWorkOrderItemID" runat="server" Text='<%# Eval("WorkOrderItemID") %>'></asp:Label>
            <asp:TemplateField HeaderText="Item">
                    <asp:Label runat="server" ID="Items" Text='<%# Eval("Item") %>'></asp:Label>
            <asp:TemplateField HeaderText="Qty">
                    <asp:Label runat="server" ID="idQty" Text='<%# Eval("Qty") %>'></asp:Label>
            <asp:TemplateField HeaderText="Description">
                    <asp:Label runat="server" ID="idDescription" Text='<%# Eval("Description") %>'></asp:Label>
             <asp:TemplateField HeaderText="Units Received"> 
                    <asp:Label runat="server" ID="lblNoteDisplay" Text='<%# Eval("Note") %>'></asp:Label>
                    <asp:textbox ID="lblNote" runat="server" AutoPostBack="true" Text='<%# Bind("Note") %>'></asp:textbox>
            <asp:TemplateField HeaderText="Complete?">
                    <asp:CheckBox runat="server" ID="chkComplete" Checked='<%# Eval("IsComplete") %>' />
            <asp:TemplateField HeaderText="Units Received All Fill">
                    <asp:TextBox ID="idTxt" runat="server" TextMode="Number" AutoPostBack="true"  Text='<%# Bind("Note") %>'></asp:TextBox>
                    <asp:RegularExpressionValidator ID="rglExp" ControlToValidate="idTxt" runat="server" ForeColor="Red" ErrorMessage="* Only Integers!" ValidationExpression="\d+"></asp:RegularExpressionValidator>
            <asp:TemplateField HeaderText="Log">
                    <img runat="server" id="idLog" width="32" height="32" src='<%# Eval("Icon") %>' /> 

ASP.NET side of the code:

 Protected Sub SaveAllGrid()
        Dim strNote As String
        Dim qty As Integer
        Dim WorkOrderItemID As Integer = 0

        For i = 0 To GridView1.Rows.Count - 1
            strNote = DirectCast(GridView1.Rows(i).FindControl("idTxt"), TextBox).Text
            Session("WorkOrderItemID") = Convert.ToInt32(DirectCast(GridView1.Rows(i).FindControl("lblWorkOrderItemID"), Label).Text)
            qty = Convert.ToInt32(DirectCast(GridView1.Rows(i).FindControl("idQty"), Label).Text)
            WorkOrderItemID = Convert.ToInt32(DirectCast(GridView1.Rows(i).FindControl("lblWorkOrderItemID"), Label).Text)
            Dim chkIsComplete As CheckBox = DirectCast(GridView1.Rows(i).FindControl("chkComplete"), CheckBox)
            Dim intLog As Byte = 0
            If strNote = "" Then strNote = 0
            Session("strNote") = Convert.ToInt32(strNote)
            Session("Qty") = Convert.ToInt32(qty) - Convert.ToInt32(strNote)

            If Convert.ToInt32(strNote) = 0 And Convert.ToInt32(strNote) <= Convert.ToInt32(qty) Then
                intLog = IconType.OPEN
            ElseIf Convert.ToInt32(strNote) > Convert.ToInt32(qty) Then

                intLog = 9
                ' If vbYes = mdMessageBox.mdMessageBox("You are receiving more than what is available. Are you sure you want to continue for WorkOrderItemID=" & WorkOrderItemID & "?", "Confirm", MessageBoxIcons.Question, MessageBoxButtons.YesOrNo, MessageBoxStyle.StyleA) Then
                '       intLog = IconType.OVERSHIPPED
                '   Else
                '      intLog = IconType.OPEN
                '  End If
            ElseIf Convert.ToInt32(strNote) = Convert.ToInt32(qty) Then
                intLog = IconType.COMPLETE
                chkIsComplete.Checked = True
            ElseIf chkIsComplete.Checked = True Then
                intLog = IconType.COMPLETE : chkIsComplete.Checked = True
            ElseIf Convert.ToInt32(strNote) > 0 And Convert.ToInt32(strNote) <= Convert.ToInt32(qty) Then
                intLog = IconType.OPEN
                intLog = IconType.BLANK
            End If

            ExecuteNonQuery("Update tblWorkOrderItem Set tblWorkOrderItem.Note='" & Convert.ToString(strNote) & "',tblWorkOrderItem.IconType=" & intLog & ",tblWorkOrderItem.IsComplete=" & chkIsComplete.Checked & " Where WorkOrderItemID=" & Convert.ToInt32(Session("WorkOrderItemID")) & "")
            ExecuteNonQuery("Update tblWorkOrder Set [tblWorkOrder].SimplyIgnore=true Where WorkOrderID=" & Convert.ToInt32(Session("ID")) & "")
            ClientScript.RegisterStartupScript(Me.GetType(), "PopupScript", "alert('Update tblWorkOrderItem Set tblWorkOrderItem.Note='" & Convert.ToString(strNote) & "',tblWorkOrderItem.IconType=" & intLog & ",tblWorkOrderItem.IsComplete=" & chkIsComplete.Checked & " Where WorkOrderItemID=" & Convert.ToInt32(Session("WorkOrderItemID")) & "')", True)

        Next i

        GridView1.EditIndex = -1