Image upload to database using sqlDataSource1.InsertParameters [Answered]RSS

1 reply

Last post Jul 29, 2019 07:41 AM by samwu

‹ Previous Thread|Next Thread ›

Shortcuts

Matt99

Member

28 Points

147 Posts

Image upload to database using sqlDataSource1.InsertParameters

Jul 27, 2019 06:05 PM|Matt99|LINK

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

samwu

Contributor

2880 Points

1210 Posts

Re: Image upload to database using sqlDataSource1.InsertParameters

Jul 29, 2019 07:41 AM|samwu|LINK

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

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

‹ Previous Thread|Next Thread ›