Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add additional fields to code behind Insert Statement

Posted on 2014-07-28
6
Medium Priority
?
287 Views
Last Modified: 2014-07-29
The following code performs an insert of an MP4 file into a database table. I also have code that streams the binary file from SQL server into a media player. Works flawlessly. My issue is I have additional fields that I want to insert into the database table in addition to Name, ContentType and Data. My problem is I do not know how to write the code to make the additional fields part of the below insert statement. I tried to write some code but my additional code caused two records to be created. One record was created with my new fields and the other with the Name, ContentType and Data (mp4 binary file) fields. I would absolutely need the information in the same row of data.

The fields I want to add are username or userid of logged in user(I know this one may be more complicated,

savedfor varchar (150)

bioname varchar (150)

sharewith varchar (150)

description varchar (500)

Here is my current code:

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs)
' Read the file and convert it to Byte Array
Dim filePath As String = FileUpload1.PostedFile.FileName
Dim filename As String = Path.GetFileName(filePath)
Dim ext As String = Path.GetExtension(filename)
Dim contenttype As String = String.Empty

'Set the contenttype based on File Extension
Select Case ext

Case ".mp4"
contenttype = "video/mp4"
Exit Select

End Select
If contenttype <> String.Empty Then
Dim fs As Stream = FileUpload1.PostedFile.InputStream
Dim br As New BinaryReader(fs)
Dim bytes As Byte() = br.ReadBytes(fs.Length)

'insert the file into database
Dim strQuery As String = "insert into tblFiles" _
& "(Name, ContentType, Data)" _
& " values (@Name, @ContentType, @Data)"
Dim cmd As New SqlCommand(strQuery)
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value _
= contenttype
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
InsertUpdateData(cmd)
lblMessage.ForeColor = System.Drawing.Color.Green
lblMessage.Text = "File Uploaded Successfully"
Else
lblMessage.ForeColor = System.Drawing.Color.Red
lblMessage.Text = "" _
& " Upload MP4 format files only"
End If

End Sub

Here is the Connection:

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("SQL2008R2_504887_golivefitnesConnectionString").ConnectionString
Dim con As New SqlConnection(strConnString)
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
cmd.ExecuteNonQuery()
Return True
Catch ex As Exception
Response.Write(ex.Message)
Return False
Finally
con.Close()
con.Dispose()
End Try
End Function
0
Comment
Question by:derrekdeveloper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40225839
It would be useful if you could post your code that was creating 2 rows so we can see where you were going wrong and how you are getting the data for the additional fields. We will then be able to give you a more complete solution.

Please use the code button on the Icon Bar above the Comment box (When posting a comment) to surround your code as it is then easier to review.
0
 

Author Comment

by:derrekdeveloper
ID: 40225848
Additional fields are being created by form fields-asp:text box controls.
0
 

Author Comment

by:derrekdeveloper
ID: 40225850
If all else fails I will just do an after trigger with scope identity.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 29

Accepted Solution

by:
Paul Jackson earned 2000 total points
ID: 40225861
Should be something like below, obviously ensuring the column names in the query match your database and the textbox control names are correct where they are being assigned to the parameters.

...
'insert the file into database 
 Dim strQuery As String = "insert into tblFiles" _
 & "(Name, ContentType, Data, Username, SavedFor, BioName, Sharewith, Description)" _
 & " values (@Name, @ContentType, @Data, @UserName, @SavedFor, @BioName, @ShareWith, @Description)"
 Dim cmd As New SqlCommand(strQuery)
 cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
 cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value _
 = contenttype
 cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = username.Text
cmd.Parameters.Add("@SavedFor", SqlDbType.VarChar).Value = savedfor.Text
cmd.Parameters.Add("@BioName", SqlDbType.VarChar).Value = bioname.Text
cmd.Parameters.Add("@ShareWith", SqlDbType.VarChar).Value = sharewith.Text
cmd.Parameters.Add("@Description", SqlDbType.VarChar).Value = Description.Text
 InsertUpdateData(cmd)
...

Open in new window

0
 

Author Closing Comment

by:derrekdeveloper
ID: 40226926
Awesome Paul! Exactly what I needed. Thanks so much!
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40227058
No problem, glad to help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question