locked
Image upload to database using sqlDataSource1.InsertParameters RRS feed

  • Question

  • User126014556 posted

    Is it possible to save the image binary into SQL table using sqlDataSource.InsertParameters?

    The issue is with the following parameter, I cannot get it to save the file as binary into the SQL table.

    TokenRecDS.InsertParameters.Add("TokenRecovery", DbType.Binary)

    Here is asp page:

        <asp:FormView ID="FormView1" runat="server" DataKeyNames="AutoNo" DataSourceID="TokenRecDS" DefaultMode="Insert">
            <InsertItemTemplate>
                <table style="width:100%; border: 1px solid #F0F0F0">
                    <tr>
                        <td>&nbsp;</td>
                        <td nowrap="nowrap">Full Name</td>
                        <td nowrap="nowrap">PC Name</td>
                        <td nowrap="nowrap">PC Model</td>
                        <td nowrap="nowrap">Serial Number</td>
                        <td>Department</td>
                        <td>Date</td>
                        <td nowrap="nowrap">Token Recovery</td>
                    </tr>
                    <tr>
                        <td nowrap="nowrap">
                            <asp:LinkButton ID="LnkInsert" runat="server" OnClick="LnkInsert_Click">Insert</asp:LinkButton>
                            &nbsp;
                            <asp:LinkButton ID="LnkCancel" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
                        </td>
                        <td>
                            <asp:TextBox ID="TxtFullName" runat="server" Text='<%# Bind("[Full Name]") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="TxtPCName" runat="server" Text='<%# Bind("[PC Name]") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="TxtPCModel" runat="server" Text='<%# Bind("[PC Model]") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="TxtSerialNo" runat="server" Text='<%# Bind("[Serial Number]") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="TxtDepartment" runat="server" Text='<%# Bind("Department") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="TxtDate" runat="server" Text='<%# Bind("Date") %>'></asp:TextBox>
                            <ajaxToolkit:CalendarExtender ID="TxtDate_CalendarExtender" runat="server" BehaviorID="TxtDate_CalendarExtender" Format="dd/MM/yyyy" TargetControlID="TxtDate" />
                        </td>
                        <td nowrap="nowrap">
                            <asp:FileUpload ID="ImageUpload" runat="server" />
    
                        </td>
                    </tr>
                    <tr>
                        <td>&nbsp;</td>
                        <td>&nbsp;</td>
                        <td>&nbsp;</td>
                        <td>&nbsp;</td>
                        <td>&nbsp;</td>
                        <td>&nbsp;</td>
                        <td>&nbsp;</td>
                        <td nowrap="nowrap">
                            <asp:Label ID="LblStatus" runat="server" Text="Upload status: "></asp:Label>
                        </td>
                    </tr>
                </table>
            </InsertItemTemplate>
        </asp:FormView>
        <asp:SqlDataSource ID="TokenRecDS" runat="server" ConnectionString="<%$ ConnectionStrings:TockenRecCS %>" InsertCommand="INSERT INTO TokenRec([Full Name], [PC Name], [PC Model], [Serial Number], Department, Date, [Token Recovery]) VALUES (@FullName, @PCName, @PCModel, @SerialNumber, @Department, @Date, @TokenRecovery)" SelectCommand="SELECT TokenRec.* FROM TokenRec">
            <InsertParameters>
                <asp:Parameter Name="FullName" />
                <asp:Parameter Name="PCName" />
                <asp:Parameter Name="PCModel" />
                <asp:Parameter Name="SerialNumber" />
                <asp:Parameter Name="Department" />
                <asp:Parameter Name="Date" />
                <asp:Parameter Name="TokenRecovery" />
            </InsertParameters>
        </asp:SqlDataSource>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>

    And the VB code page:

        Protected Sub LnkInsert_Click(sender As Object, e As EventArgs)
            Dim FileUploadControl As FileUpload
            Dim Status As Label
            Dim FullName As TextBox
            Dim PCName As TextBox
            Dim PCModel As TextBox
            Dim SerialNumber As TextBox
            Dim Department As TextBox
            Dim DateTime As TextBox
            Dim FData As Byte()
    
            FullName = FormView1.FindControl("TxtFullName")
            PCName = FormView1.FindControl("TxtPCName")
            PCModel = FormView1.FindControl("TxtPCModel")
            SerialNumber = FormView1.FindControl("TxtSerialNo")
            FileUploadControl = FormView1.FindControl("ImageUpload")
            Status = FormView1.FindControl("LblStatus")
            Department = FormView1.FindControl("TxtDepartment")
            DateTime = FormView1.FindControl("TxtDate")
            FData = FileUploadControl.FileBytes
    
            If FileUploadControl.HasFile Then
    
                Try
                    TokenRecDS.InsertParameters("FullName").DefaultValue = FullName.Text
                    TokenRecDS.InsertParameters("PCName").DefaultValue = PCName.Text
                    TokenRecDS.InsertParameters("PCModel").DefaultValue = PCModel.Text
                    TokenRecDS.InsertParameters("SerialNumber").DefaultValue = SerialNumber.Text
                    TokenRecDS.InsertParameters("Department").DefaultValue = Department.Text
                    TokenRecDS.InsertParameters("Date").DefaultValue = DateTime.Text
                    TokenRecDS.InsertParameters.Add("TokenRecovery", DbType.Binary)
                    TokenRecDS.Insert()
                    Status.Text = "Upload status: File uploaded!"
                Catch ex As Exception
                    Status.Text = "Upload status: The file could not be uploaded. The following error occured: " & ex.Message
                End Try
            End If
    
    
    
        End Sub

    I appreciate any help

    Saturday, July 27, 2019 6:05 PM

Answers

  • User288213138 posted

    Hi Matt99,

    According to your description, here is a demo for your reference.

    In the SqlDataSource OnInserting event, i got the value and inserted it into database.

     
    The code:

    <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" DefaultMode="Insert">
                    <InsertItemTemplate>
                <asp:FileUpload runat="server" ID="FileUpload1" />
                        <br />
                        <asp:Button ID="btnInsert" runat="server" CausesValidation="True" CommandName="Insert" Text="Upload" />
                    </InsertItemTemplate>
                </asp:FormView>
    
                <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CaseTestConnectionString %>" OnInserting="SqlDataSource1_Inserting"
                    InsertCommand="INSERT INTO [Files] ([Name], [Type], [Data]) VALUES (@Filename, @Contenttype, @Data)">
                    <InsertParameters>
                        <asp:Parameter Name="Filename" Type="String" />
                        <asp:Parameter Name="Contenttype" Type="String" />
                        <asp:Parameter Name="Data" Type="Object" />
                    </InsertParameters>
                </asp:SqlDataSource>
    
    
    Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
        Dim fileUpload1 As FileUpload = (TryCast(FormView1.FindControl("FileUpload1"), FileUpload))
    
        Using fs As Stream = fileUpload1.PostedFile.InputStream
            Dim br As BinaryReader = New BinaryReader(fs)
            Dim bytes As Byte() = br.ReadBytes(CInt(fs.Length))
            Dim fileName As String = Path.GetFileName(fileUpload1.PostedFile.FileName)
            e.Command.Parameters("@Filename").Value = fileName
            e.Command.Parameters("@Contenttype").Value = fileUpload1.PostedFile.ContentType
            e.Command.Parameters("@Data").DbType = System.Data.DbType.Binary
            e.Command.Parameters("@Data").Value = bytes
            Label1.ForeColor = System.Drawing.Color.Green
            Label1.Text = "File Uploaded Successfully"
        End Using
    End Sub

    The result:

    Best regards,

    sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 29, 2019 7:41 AM