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
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
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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> </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> <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> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </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 SubI appreciate any help
Contributor
3370 Points
1409 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 SubThe result:
Best regards,
sam