[Solved] RADUpload Multiple Files and Database Inserts

[PJ Rodriguez]

I have RADUpload component on a page that allows for uploads of files to the server and inserting of records onto a database after the upload is complete.

Here’s the scenario, a file is uploaded via the RADUpload component. After a successful upload, a table in the database called FILES is populated with data about the uploaded file (ie file name, size, mime type, etc). After data is inserted into the FILES table via stored procedure, the primary key of that recently inserted record is returned to my application via a Scope Identity request. I will call this primary key IDFILES.

After the IDFILES primary is returned to my application, I then grab that data, and perform another insert—this time to a different table called PLANSETS.

All the above is done automatically for every uploaded file.

All the above functions with no problems—unless you are uploading multiple files using that one RADUPLOAD component. However, if you upload more than one file, only the last uploaded file returns the IDFILES data.

Is there a workaround in RADupload that allows me to be able to see that IDFILES even if multiple files are being uploaded?

My code is below:

 

        If ruShell.UploadedFiles.Count > 0 Then

            For Each validFile As UploadedFile In ruShell.UploadedFiles

                Dim targetFolder As String = Server.MapPath("/Plansets/" & rcbStreets.SelectedValue & "/" & rcbAddresses.SelectedValue & "/1")

                'Dim targetFolder As String = "~/Plansets/" & rcbStreets.SelectedValue & "/" & rcbAddresses.SelectedValue & "/1"

                validFile.SaveAs(Path.Combine(targetFolder, validFile.GetName()), True)

 

                sqlPlansetFiles.InsertParameters("description").DefaultValue = validFile.GetNameWithoutExtension

                sqlPlansetFiles.InsertParameters("contenttype").DefaultValue = validFile.ContentType

                sqlPlansetFiles.InsertParameters("size").DefaultValue = validFile.ContentLength

                sqlPlansetFiles.InsertParameters("extension").DefaultValue = validFile.GetExtension

                sqlPlansetFiles.InsertParameters("filename").DefaultValue = validFile.GetName

                sqlPlansetFiles.InsertParameters("path").DefaultValue = "~\Plansets\" & rcbStreets.SelectedValue & "\" & rcbAddresses.SelectedValue & "\1\" & validFile.GetName

 

                sqlPlansetFiles.Insert()

                'Label1.Text = sqlPlansetFiles.InsertParameters("newIDPlansetFiles").DefaultValue.ToString

 

                sqlPlansets.InsertParameters("idaddresses").DefaultValue = rcbAddresses.SelectedValue

                sqlPlansets.InsertParameters("idPlansetTypes").DefaultValue = "1"

                sqlPlansets.InsertParameters("idplansetcategory").DefaultValue = "1"

                sqlPlansets.InsertParameters("isdeleted").DefaultValue = "0"

                sqlPlansets.InsertParameters("idplansetfiles").DefaultValue = idPlansetFiles

                sqlPlansets.InsertParameters("modifieddate").DefaultValue = Now

                sqlPlansets.Insert()

 

            Next

            'labelNoResults.Visible = False

            'repeaterValidResults.Visible = True

            'repeaterValidResults.DataSource = RadUpload1.UploadedFiles

            'repeaterValidResults.DataBind()

        Else

            'labelNoResults.Visible = True

            'repeaterValidResults.Visible = False

        End If

 

    Private Sub sqlPlansetFiles_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sqlPlansetFiles.Inserted

        'Label1.Text = sqlPlansetFiles.InsertParameters("newIDPlansetFiles").DefaultValue.ToString

 

 

        idPlansetFiles = Convert.ToInt32(e.Command.Parameters("@newIDPlansetFiles").Value)

 

       

        'Label1.Text = newProductID

    End Sub

[Genady Sergeev]

Hello PJ Rodriguez,

To be honest I am not sure why it is going wrong with your scenario. There should be no problem to get the ID of the last inserted record for every uploaded file. However, I suppose that SqlDataSource is not quite suited for such, more advanced, scenarios. Here is one of the possible workarounds: Add this to your stored procedure:

ALTER PROCEDURE dbo.[name of the table]
    // Parameters, for example:
    @Name NVARCHAR(300)
AS
    BEGIN
        // your insertion code, for example;
        INSERT INTO UploadedFiles (FileName) VALUES (@Name);
        // Select the id of the last inserted record
        SELECT @@Identity FROM UploadedFiles;
    END
   
RETURN

Then get rid of the datasource ( why do you need it ? ) and call your stored procedure using the following syntax:

Dim id As Object
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("The name of your connection string").ConnectionString)
    connection.Open()
    For Each file As UploadedFile In RadUpload1.UploadedFiles
        Dim fileName As String = file.FileName
        Session("fileName") = fileName
        SqlDataSource1.Insert()

        Using cmd As New SqlCommand("Name of the your stored procedure", connection)
            cmd.CommandType = System.Data.CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Name", fileName)
            id = cmd.ExecuteScalar()
        End Using
    Next
End Using

When you call id = cmd.ExecuteScalar, the id is returned by the stored procedure. Then you can proceed with yor other insert. You can find sample project as an attachment. However, please have in mind that this functionality is in no way connected with the RadUpload and we cannot afford you full support with it.

Regards,
Genady Sergeev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[PJ Rodriguez]

Thanks, Grenady. Based on your suggestion, I was able to formulate a solution. I figured it would be a SQL issue instead of a RADUpload issue--just wanted to make sure. In any case, I ended up changing my upload command to the following:

 

Dim id As Object

 

 

If ruShell.UploadedFiles.Count > 0 Then

 

 

 

Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("BG_AddressTableConnectionString").ConnectionString)

 

connection.Open()

 

For Each validFile As UploadedFile In ruShell.UploadedFiles

 

 

Dim targetFolder As String = Server.MapPath("/Plansets/" & rcbStreets.SelectedValue & "/" & rcbAddresses.SelectedValue & "/1")

 

 

'Dim targetFolder As String = "~/Plansets/" & rcbStreets.SelectedValue & "/" & rcbAddresses.SelectedValue & "/1"

 

validFile.SaveAs(Path.Combine(targetFolder, validFile.GetName()),

True)

 

 

 

Using cmd As New SqlCommand("sp_insert_PlansetFiles", connection)

 

cmd.CommandType = System.Data.CommandType.StoredProcedure

cmd.Parameters.AddWithValue(

"@description", validFile.GetNameWithoutExtension)

 

cmd.Parameters.AddWithValue(

"@contenttype", validFile.ContentType)

 

cmd.Parameters.AddWithValue(

"@size", validFile.ContentLength)

 

cmd.Parameters.AddWithValue(

"@extension", validFile.GetExtension)

 

cmd.Parameters.AddWithValue(

"@filename", validFile.GetName)

 

cmd.Parameters.AddWithValue(

"@path", "~\Plansets\" & rcbStreets.SelectedValue & "\" & rcbAddresses.SelectedValue & "\1\" & validFile.GetName)

 

id = cmd.ExecuteScalar()

 

Using cmdPlansets As New SqlCommand("sp_insert_Plansets", connection)

 

cmdPlansets.CommandType = System.Data.CommandType.StoredProcedure

cmdPlansets.Parameters.AddWithValue(

"@idaddresses", rcbAddresses.SelectedValue)

 

cmdPlansets.Parameters.AddWithValue(

"@idPlansetTypes", "1")

 

cmdPlansets.Parameters.AddWithValue(

"@idplansetcategory", "1")

 

cmdPlansets.Parameters.AddWithValue(

"@isdeleted", "0")

 

cmdPlansets.Parameters.AddWithValue(

"@idplansetfiles", id)

 

cmdPlansets.Parameters.AddWithValue(

"@modifieddate", Now)

 

cmdPlansets.ExecuteNonQuery()

 

End Using

 

 

End Using

 

 

 

Next

 

connection.Close()

 

End Using

 

 

End If