Solved

Insert  then select and then insert again Stored Procedure

Posted on 2012-03-13
31
396 Views
Last Modified: 2012-03-16
Hi

I am developing a video uploading website. One of the upload criteria is to associate a video with as many courses or as many modules as the user chooses.

I relise that I need multiple tables to achieve this and have the tables as follows:

Video: VideoNo, VideoName, UserNo. UploadDate, VideoUrl etc
Module: ModuleNo, ModuleName
Course: CourseNo, CourseName
VideoAssociation: VideoAssociationNo, CourseNo, ModuleNo, VideoNo

I have two drop downs that users can select from and add them to list (Both Course and Module). The user then adds all other details about the video and uploads it.
I would like every course and module that is added to those lists to be added as an entry to the database table (VideoAssociation).
All of the other details would be wriiten to the video table.
The trouble is to write to the VideoAssociation table I would need a VideoNo as the identifier and that is not assigned until the video is added to the video table.

I need help how to write a query or multiple queries in a stored procedure to add all entries to the VideoAssociation Table after inserting the video details.
I think aloop would have to be used to get entries from the courses and modules lists, but I don't know where to start?
I have included an image of the interface and the stored procedure I have written so far.
Thanks

ALTER PROCEDURE dbo.sprocUploadVideo
         @UserNo                         INT                      ,
       @VideoName                      VARCHAR(50)      = NULL  ,
       @ModuleNo                       INT                  ,
       @CourseNo                       INT              = NULL  ,
       @DateUploaded                   DATETIME         = NULL  ,
       @VideoUrl                       VARCHAR(50)      = NULL  ,
       @ThumbUrl                       VARCHAR(50)              ,
         @Description                    VARCHAR(500)             ,
         @Keywords                                 VARCHAR(50)                        ,
         @Visible                                 VARCHAR(10)
AS


     INSERT INTO dbo.Video
          (
            UserNo                  ,
            VideoName                     ,
            CourseNo                   ,
            ModuleNo                    ,
            DateUploaded                   ,
            VideoUrl                          ,
            ThumbUrl                          ,
                  Description                      ,
                  Keywords                          ,
                  Visible          
          )
     VALUES
          (
            @UserNo                  ,
            @VideoName                     ,
            @CourseNo                   ,
            @ModuleNo                     ,
            @DateUploaded                   ,
            @VideoUrl                         ,
            @ThumbUrl                        ,
                  @Description               ,
            @Keywords                              ,
                  @Visible
          )
upload.jpg
0
Comment
Question by:ntd1
  • 15
  • 14
  • 2
31 Comments
 
LVL 2

Expert Comment

by:JAruchamy
ID: 37717377
Hi,

1. First Insert into the video table.

2. Get the VideoNo using SCOPE_IDENTITY ......   http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx

3. Then get the items from your CourseNo "something" separated string as follows

SELECT RowNum = ROW_NUMBER() OVER(ORDER BY splitvalue) FROM dbo.Split('test1,test2,test3',',')

now loop as in the following example....

http://sqlserverplanet.com/tsql/sql-server-for-each-row-next

Note: Do everything in one DB call or in one SP
0
 
LVL 2

Expert Comment

by:winston33
ID: 37720420
I would recommend passing the CourseNo and ModuleNo lists as XML parameters to avoid looping. Build the XML in code so it looks something like:

<Courses>
   <CourseNo>1</CourseNo>
   <CourseNo>2</CourseNo>
</Courses>

DECLARE @course TABLE (CourseNo INT)

DECLARE @module TABLE(ModuleNo INT)

INSERT INTO @course
 SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
 FROM      @courseXML.nodes('/Course/CourseNo') AS ParamLabel (ID)      

INSERT INTO @module
 SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
 FROM      @courseXML.nodes('/Module/ModuleNo') AS ParamLabel (ID)      

Then your insert can simply select the identity from the new video row and the CourseNo/ModuleNo field from the table parameters.

SELECT VideoID,
             CourseNo
FROM @course
0
 

Author Comment

by:ntd1
ID: 37721696
Would I loop through the list box then insert?

This is really hard for me to get my head around.

 Dim sqlInsert As String = String.Empty
                                                Dim sb As New StringBuilder(String.Empty)
                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstCourses.Items(i).Selected = True Then
                                                        sb.AppendFormat("'{0}'", lstCourses.Items(i).Text)
                                                        sqlInsert += String.Format("INSERT INTO TABLE1 (Field1)VALUES ({0});", sb.ToString())
                                                    End If
                                                Next
0
 
LVL 2

Expert Comment

by:winston33
ID: 37721739
Which method are you attempting?
0
 
LVL 2

Expert Comment

by:JAruchamy
ID: 37722014
Get the SelectedValues from the listbox and then either form a comma separated string or a xml string as told by winston33 and pass it as a parameter to the SP. Then you can do the looping in the SP.

I guess U should do a join instead of looping.

FYI...
SELECT splitvalue FROM dbo.Split('test1,test2,test3',',') --- this way you can read from a comma seperated string as reading from a table.
0
 

Author Comment

by:ntd1
ID: 37722027
I'm not very experienced with sql, but I have wriiten the insert procedure in the original post. In that statement it adds the course and module single value to the video table.
I have since removed the courseNo and ModuleNo columns from the video table.

The original sql was only taking one value from the drop down of each (cboCourse and cboModule) now I want the user to select as many as they want from the drop down and populate two lists (lstCourses and lstModules) from these lists I would like to insert every value into the video association table.
I know I would need to loop through each list and assign the value to a parameter in stored procedure:
cmd.Parameters.AddWithValue("@CourseNo", lstCourses.SelectedValue())

I want to add this to to statement simular to the original one.
It must obviously insert the video details first then use a scope_identity to pass the video number to the insert of the video association table.
I know what I want to do, but I just don't know how to write the srored procedure and the code that goes with it.
Thanks
0
 
LVL 2

Expert Comment

by:winston33
ID: 37722076
OK. Here is what I would do (although the comma-separated list will work as well):

In the code-behind where the listbox resides, created two XML strings:

            Dim xmlCourse as String
            Dim xmlModule as String
            Dim pNode As XmlNode = oXml.CreateElement("Courses")
            oXml.AppendChild(pNode)

            oNode = oXml.SelectSingleNode("Courses")

            For i As Integer = 0 To lstCourses.Items.Count - 1
                  If lstCourses.Items(i).Selected = True Then
                          oElement = oXml.CreateElement("CourseNo")
                          oElement.InnerText = lstCourse.Items(i).Text
                          oNode.AppendChild(oElement)
                  End If
            Next

            xmlCourse = oXml.InnerXml

            ****NOTE: Repeat this code to build xmlModule

Pass these two strings as XML into stored procedure and process them as I mentioned earlier. From my nderstanding, there will be no need to join anything. You will simply need to select the VideoID (created by the insert) and the CourseNo or ModuleNo (depending on which table you are inserting into) from the table created from the XML parameters.

For reference, here is the SQL from earlier:

DECLARE @course TABLE (CourseNo INT)

DECLARE @module TABLE(ModuleNo INT)

INSERT INTO @course
 SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
 FROM      @courseXML.nodes('/Course/CourseNo') AS ParamLabel (ID)      

INSERT INTO @module
 SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
 FROM      @courseXML.nodes('/Module/ModuleNo') AS ParamLabel (ID)      

Then your insert can simply select the identity from the new video row and the CourseNo/ModuleNo field from the table parameters.

SELECT VideoID,
             CourseNo
FROM @course
0
 

Author Comment

by:ntd1
ID: 37722166
Hi

that is giving syntax errors.

I have included:

Imports System.Xml
0
 
LVL 2

Expert Comment

by:winston33
ID: 37722223
Could you post all the relevant code here so I can take a look in its entirety?
0
 

Author Comment

by:ntd1
ID: 37722289
Hi

This is the entire code behind the upload button:

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConfirm.Click

        If IsPostBack Then
            'declare the file path to store uploads
            Dim path As String = Server.MapPath("~/UploadedVideos/" & UserLogin.UserNo & "/")
            'Dim path As String = HttpRuntime.AppDomainAppPath ("~/UploadedVideos/")

            Dim fileOK As Boolean = False

            'If the file upload control has a file
            If FileUploadVideo.HasFile Then
                'check to see if the number of characters does not exceed 50 and is not empty
                If Len(txtVideoName.Text) <= 50 And txtVideoName.Text IsNot "" Then
                    'clear the error message
                    lblErrorTitle.Text = ""

                    'check to see if a course has been added to the list
                    If lstCourses.SelectedIndex <> -1 Then
                        lblErrorCourse.Text = ""
                        'check to see if a module has been added to the list
                        If lstModules.SelectedIndex <> -1 Then
                            lblErrorModule.Text = ""
                            If Len(txtDescription.Text) <= 500 And txtDescription.Text IsNot "" Then
                                lblDescriptionError.Text = ""

                                'check to see if the number of characters does not exceed 50 and is not empty
                                If Len(txtKeywords.Text) <= 50 And txtKeywords.Text IsNot "" And txtKeywords.Text = Replace(txtKeywords.Text, " ", "") Then

                                    'clear the error message
                                    lblErrorKeywords.Text = ""

                                    'check the file to make sure it is of the required type
                                    Dim fileExtension As String
                                    fileExtension = System.IO.Path. _
                                        GetExtension(FileUploadVideo.FileName).ToLower()
                                    'declare the allowed extentions
                                    Dim allowedExtensions As String() = _
                                        {".mov", ".wmv", ".avi", ".mp4", ".m4v"}
                                    'loop to check the extention type
                                    For i As Integer = 0 To allowedExtensions.Length - 1
                                        If fileExtension = allowedExtensions(i) Then


                                            '//////////////////////////// Convert File


                                            Call encode()


                                            fileOK = True

                                        End If
                                        '////////////////////////////////Upload File path


                                    Next
                                    If fileOK Then
                                        Using Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
                                            Try
                                                'open the database connection
                                                Conn.Open()
                                                Dim FilePath = fileNameWithoutExtention & ".flv"
                                                'Dim FilePath = path & FileUploadVideo.FileName
                                                'insert statement to add the video details to the database
                                                '  SqlCommand(cmd = New SqlCommand("GetAllData", connection))


                                                Const SQL As String = "sprocUploadVideo"
                                                Dim cmd As New SqlCommand(SQL, Conn)
                                                cmd.CommandType = CommandType.StoredProcedure


                                                Dim xmlCourse As String
                                                Dim xmlModule As String
                                                Dim pNode As XmlNode = oXml.CreateElement("Courses")
                                                oXml.AppendChild(pNode)

                                                oNode = oXml.SelectSingleNode("Courses")

                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstCourses.Items(i).Selected = True Then
                                                        oElement = oXml.CreateElement("CourseNo")
                                                        oElement.InnerText = lstCourses.Items(i).Text
                                                        oNode.AppendChild(oElement)
                                                    End If
                                                Next

                                                xmlCourse = oXml.InnerXml





                                                cmd.Parameters.AddWithValue("@UserNo", UserLogin.UserNo())
                                                cmd.Parameters.AddWithValue("@VideoName", txtVideoName.Text.Trim())
                                                cmd.Parameters.AddWithValue("@CourseNo", cboCourse.SelectedValue())
                                                cmd.Parameters.AddWithValue("@ModuleNo", cboModule.SelectedValue())
                                                cmd.Parameters.AddWithValue("@DateUploaded", Date.Now)
                                                cmd.Parameters.AddWithValue("@VideoUrl", UserLogin.UserNo & "/" & fileNameWithoutExtention & ".flv")
                                                cmd.Parameters.AddWithValue("@ThumbUrl", UserLogin.UserNo & "\" & fileNameWithoutExtention & ".jpg")
                                                cmd.Parameters.AddWithValue("@Description", txtDescription.Text.Trim())
                                                cmd.Parameters.AddWithValue("@Keywords", txtKeywords.Text.Trim())
                                                cmd.Parameters.AddWithValue("@Visible", "Yes")
                                                FileUploadVideo.PostedFile.SaveAs(path & _
                                                     FileUploadVideo.FileName)


                                                'MsgBox("File Uploaded Successfully - Thank You", MsgBoxStyle.Information, "Video Upload")
                                                'Successful upload message
                                                lblError.Text = "File Uploaded Successfully - Thank You"

                                                'clear the fields
                                                lblHoldFilePath.Text = ""
                                                lstModules.Items.Clear()
                                                lstCourses.Items.Clear()
                                                txtVideoName.Text = ""
                                                txtKeywords.Text = ""
                                                cboCourse.Text = ""
                                                cboModule.Text = ""
                                                txtDescription.Text = ""
                                                cmd.ExecuteNonQuery()

                                                'close the database connection
                                                Conn.Close()
                                            Catch ex As Exception

                                                'file can't be uploaded error message
                                                lblError.Text = "File could not be uploaded." & ex.Message
                                            End Try
                                        End Using
                                    Else
                                        'error message for incorrect file type
                                        lblError.Text = "Cannot accept files of this type."
                                    End If


                                Else
                                    'error message for too many characters in the video name field
                                    lblErrorKeywords.Text = "This is a required field with a maximum length of 50 characters and no spaces!"
                                End If


                            Else
                                lblDescriptionError.Text = "This is a required field with a maximum length of 500 characters"
                                'insert here

                            End If


                        Else
                            lblErrorModule.Text = "You must add at least 1 Module to the list"
                        End If
                    Else
                        lblErrorCourse.Text = "You must add at least 1 course to the list"
                    End If




                Else
                    'error message for too many characters in the keywords field
                    lblErrorTitle.Text = "This is a required field with a maximum length of 50 characters"
                End If
               


            Else
                'error message for no file selected
                lblHoldFilePath.Text = "Please Choose A File!!"
            End If
        End If


    End Sub
0
 
LVL 2

Expert Comment

by:winston33
ID: 37722959
Sorry - my fault. I forgot to include the declaration of a few variables:

        Dim oXml As New XmlDocument
        Dim oNode As XmlNode
        Dim oElement As XmlElement
0
 

Author Comment

by:ntd1
ID: 37723682
So where the  stored procedure sql above that uses the xml do I just add that in the original stored procedure and use it?

If so, do I pass the videoId using scope identity into an insert after that to the insert into VideoAssociation? Does in need to be built like the below?

ALTER PROCEDURE dbo.sprocUploadVideo
         @UserNo                         INT                      ,
       @VideoName                      VARCHAR(50)      = NULL  ,  
       @DateUploaded                   DATETIME         = NULL  ,
       @VideoUrl                       VARCHAR(50)      = NULL  ,
       @ThumbUrl                       VARCHAR(50)              ,
         @Description                    VARCHAR(500)             ,
         @Keywords                                 VARCHAR(50)                        ,
         @Visible                                 VARCHAR(10)                              ,
         @course TABLE (CourseNo INT)                                                ,
         @module TABLE(ModuleNo INT)



AS


     INSERT INTO dbo.Video
          (
            UserNo                  ,
            VideoName                     ,
            DateUploaded                   ,
            VideoUrl                          ,
            ThumbUrl                          ,
                  Description                      ,
                  Keywords                          ,
                  Visible          
          )
     VALUES
          (
            @UserNo                  ,
            @VideoName                     ,
            @DateUploaded                   ,
            @VideoUrl                         ,
            @ThumbUrl                        ,
                  @Description               ,
            @Keywords                              ,
                  @Visible
          )

INSERT INTO @course
 SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
 FROM      @courseXML.nodes('/Course/CourseNo') AS ParamLabel (ID)      

INSERT INTO @module
 SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
 FROM      @courseXML.nodes('/Module/ModuleNo') AS ParamLabel (ID)      

Then your insert can simply select the identity from the new video row and the CourseNo/ModuleNo field from the table parameters.

SELECT VideoID,
             CourseNo
FROM @course
0
 
LVL 2

Expert Comment

by:winston33
ID: 37724745
I have one question about the data here. Is there a relation between the course and module? It does not appear that there is from th code or data structure so this VideoAssociation table will not really work for both course and module (I could be misunderstanding the data here). From my reading of your code, you would need a VideoCourse table and a VideoModule table to store the information you need.

Additionally, in your VB code, you are storing the text of the list item, but you really want the selectedvalue (missed that earlier).

Given that you will store the records in two tables as I suggest above, the proc would look like this:

ALTER PROCEDURE dbo.sprocUploadVideo
      @UserNo                  INT,
      @VideoName            VARCHAR(50) = NULL,  
      @DateUploaded      DATETIME = NULL,
      @VideoUrl            VARCHAR(50) = NULL,
      @ThumbUrl            VARCHAR(50),
      @Description      VARCHAR(500),
      @Keywords            VARCHAR(50),
      @Visible            VARCHAR(10),
      @CourseXML            XML,
      @ModuleXML            XML
      
AS

      DECLARE @course TABLE (CourseNo INT)                                                
      DECLARE @module TABLE(ModuleNo INT)
      DECLARE @VideoID INT
      
      INSERT INTO @course
      SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
      FROM            @CourseXML.nodes('/Course/CourseNo') AS ParamLabel (ID)      

      INSERT INTO @module
      SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
      FROM            @CourseXML.nodes('/Module/ModuleNo') AS ParamLabel (ID)      
 
     INSERT INTO dbo.Video
          (
            UserNo                  ,
            VideoName                     ,
            DateUploaded                   ,
            VideoUrl                          ,
            ThumbUrl                          ,
            Description                      ,
                  Keywords                          ,
                  Visible          
          )
     VALUES
          (
            @UserNo                  ,
            @VideoName                     ,
            @DateUploaded                   ,
            @VideoUrl                         ,
            @ThumbUrl                        ,
                  @Description               ,
            @Keywords                              ,
                  @Visible
          )

      SET      @VideoID = SCOPE_IDENTITY()
      
      INSERT INTO VideoAssociation
      SELECT      @VideoID,
                  CourseNo
      FROM      @course

      
      INSERT INTO VideoModule
      SELECT      @VideoID,
                  ModuleNo
      FROM      @module
0
 

Author Comment

by:ntd1
ID: 37725156
Hi
I have taken your advice and made two tables:

VideoCourse
VideoModule

I have changed the stored procedure and updated my code, but it wont insert anything?

It says on the exit message not set to an instance of an object.
 
I really appreciate all of your help on this.

The stored procedure:

ALTER PROCEDURE dbo.sprocUploadVideo
      @UserNo                  INT,
      @VideoName            VARCHAR(50) = NULL,  
      @DateUploaded      DATETIME = NULL,
      @VideoUrl            VARCHAR(50) = NULL,
      @ThumbUrl            VARCHAR(50),
      @Description      VARCHAR(500),
      @Keywords            VARCHAR(50),
      @Visible            VARCHAR(10),
      @CourseXML            XML,
      @ModuleXML            XML
     
AS

      DECLARE @course TABLE (CourseNo INT)                                                
      DECLARE @module TABLE(ModuleNo INT)
      DECLARE @VideoNo INT
     
      INSERT INTO @course
      SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
      FROM            @CourseXML.nodes('/Course/CourseNo') AS ParamLabel (ID)      

      INSERT INTO @module
      SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
      FROM            @CourseXML.nodes('/Module/ModuleNo') AS ParamLabel (ID)      
 
     INSERT INTO dbo.Video
          (
            UserNo                  ,
            VideoName                     ,
            DateUploaded                   ,
            VideoUrl                          ,
            ThumbUrl                          ,
            Description                      ,
                  Keywords                          ,
                  Visible          
          )
     VALUES
          (
            @UserNo                  ,
            @VideoName                     ,
            @DateUploaded                   ,
            @VideoUrl                         ,
            @ThumbUrl                        ,
            @Description               ,
            @Keywords                              ,
                  @Visible
          )

      SET      @VideoNo = SCOPE_IDENTITY()
     
      INSERT INTO VideoCourse
      SELECT      @VideoNo,
                  CourseNo
      FROM      @course

     
      INSERT INTO VideoModule
      SELECT      @VideoNo,
                  ModuleNo
      FROM      @module



The Code Behind the button:


 Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConfirm.Click

        If IsPostBack Then
            'declare the file path to store uploads
            Dim path As String = Server.MapPath("~/UploadedVideos/" & UserLogin.UserNo & "/")
            'Dim path As String = HttpRuntime.AppDomainAppPath ("~/UploadedVideos/" & UserLogin.UserNo & "/")

            Dim fileOK As Boolean = False

            'If the file upload control has a file
            If FileUploadVideo.HasFile Then
                'check to see if the number of characters does not exceed 50 and is not empty
                If Len(txtVideoName.Text) <= 50 And txtVideoName.Text IsNot "" Then
                    'clear the error message
                    lblErrorTitle.Text = ""

                    'check to see if a course has been added to the list
                    If lstCourses.SelectedIndex <> -1 Then
                        lblErrorCourse.Text = ""
                        'check to see if a module has been added to the list
                        If lstModules.SelectedIndex <> -1 Then
                            lblErrorModule.Text = ""
                            If Len(txtDescription.Text) <= 500 And txtDescription.Text IsNot "" Then
                                lblDescriptionError.Text = ""

                                'check to see if the number of characters does not exceed 50 and is not empty
                                If Len(txtKeywords.Text) <= 50 And txtKeywords.Text IsNot "" And txtKeywords.Text = Replace(txtKeywords.Text, " ", "") Then

                                    'clear the error message
                                    lblErrorKeywords.Text = ""

                                    'check the file to make sure it is of the required type
                                    Dim fileExtension As String
                                    fileExtension = System.IO.Path. _
                                        GetExtension(FileUploadVideo.FileName).ToLower()
                                    'declare the allowed extentions
                                    Dim allowedExtensions As String() = _
                                        {".mov", ".wmv", ".avi", ".mp4", ".m4v"}
                                    'loop to check the extention type
                                    For i As Integer = 0 To allowedExtensions.Length - 1
                                        If fileExtension = allowedExtensions(i) Then


                                            '//////////////////////////// Convert File


                                            Call encode()


                                            fileOK = True

                                        End If
                                        '////////////////////////////////Upload File path


                                    Next
                                    If fileOK Then
                                        Using Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
                                            Try
                                                'open the database connection
                                                Conn.Open()
                                                Dim FilePath = fileNameWithoutExtention & ".flv"
                                                'Dim FilePath = path & FileUploadVideo.FileName
                                                'insert statement to add the video details to the database
                                                '  SqlCommand(cmd = New SqlCommand("GetAllData", connection))



                                               

                                                Dim oXml As New XmlDocument
                                                Dim oNode As XmlNode
                                                Dim oElement As XmlElement



                                                Dim xmlCourse As String
                                                Dim xmlModule As String
                                                Dim pNode As XmlNode = oXml.CreateElement("Courses")
                                                oXml.AppendChild(pNode)

                                                oNode = oXml.SelectSingleNode("Courses")

                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstCourses.Items(i).Selected = True Then
                                                        oElement = oXml.CreateElement("CourseNo")
                                                        oElement.InnerText = lstCourses.Items(i).Text
                                                        oNode.AppendChild(oElement)
                                                    End If
                                                Next

                                                xmlCourse = oXml.InnerXml


                                                Dim pNode2 As XmlNode = oXml.CreateElement("Modules")
                                                oXml.AppendChild(pNode)

                                                oNode = oXml.SelectSingleNode("Modules")

                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstCourses.Items(i).Selected = True Then
                                                        oElement = oXml.CreateElement("ModuleNo")
                                                        oElement.InnerText = lstCourses.Items(i).Text
                                                        oNode.AppendChild(oElement)
                                                    End If
                                                Next

                                                xmlModule = oXml.InnerXml



                                                Const SQL As String = "sprocUploadVideo"
                                                Dim cmd As New SqlCommand(SQL, Conn)
                                                cmd.CommandType = CommandType.StoredProcedure
                                                cmd.Parameters.AddWithValue("@UserNo", UserLogin.UserNo())
                                                cmd.Parameters.AddWithValue("@VideoName", txtVideoName.Text.Trim())
                                                'cmd.Parameters.AddWithValue("@CourseNo", cboCourse.SelectedValue())
                                                'cmd.Parameters.AddWithValue("@ModuleNo", cboModule.SelectedValue())
                                                cmd.Parameters.AddWithValue("@DateUploaded", Date.Now)
                                                cmd.Parameters.AddWithValue("@VideoUrl", UserLogin.UserNo & "/" & fileNameWithoutExtention & ".flv")
                                                cmd.Parameters.AddWithValue("@ThumbUrl", UserLogin.UserNo & "\" & fileNameWithoutExtention & ".jpg")
                                                cmd.Parameters.AddWithValue("@Description", txtDescription.Text.Trim())
                                                cmd.Parameters.AddWithValue("@Keywords", txtKeywords.Text.Trim())
                                                cmd.Parameters.AddWithValue("@Visible", "Yes")
                                                FileUploadVideo.PostedFile.SaveAs(path & _
                                                     FileUploadVideo.FileName)


                                                'MsgBox("File Uploaded Successfully - Thank You", MsgBoxStyle.Information, "Video Upload")
                                                'Successful upload message
                                                lblError.Text = "File Uploaded Successfully - Thank You"

                                                'clear the fields
                                                lblHoldFilePath.Text = ""
                                                lstModules.Items.Clear()
                                                lstCourses.Items.Clear()
                                                txtVideoName.Text = ""
                                                txtKeywords.Text = ""
                                                cboCourse.Text = ""
                                                cboModule.Text = ""
                                                txtDescription.Text = ""
                                                cmd.ExecuteNonQuery()

                                                'close the database connection
                                                Conn.Close()
                                            Catch ex As Exception

                                                'file can't be uploaded error message
                                                lblError.Text = "File could not be uploaded." & ex.Message
                                            End Try
                                        End Using
                                    Else
                                        'error message for incorrect file type
                                        lblError.Text = "Cannot accept files of this type."
                                    End If


                                Else
                                    'error message for too many characters in the video name field
                                    lblErrorKeywords.Text = "This is a required field with a maximum length of 50 characters and no spaces!"
                                End If


                            Else
                                lblDescriptionError.Text = "This is a required field with a maximum length of 500 characters"
                                'insert here

                            End If


                        Else
                            lblErrorModule.Text = "You must add at least 1 Module to the list"
                        End If
                    Else
                        lblErrorCourse.Text = "You must add at least 1 course to the list"
                    End If




                Else
                    'error message for too many characters in the keywords field
                    lblErrorTitle.Text = "This is a required field with a maximum length of 50 characters"
                End If
               


            Else
                'error message for no file selected
                lblHoldFilePath.Text = "Please Choose A File!!"
            End If
        End If


    End Sub
0
 
LVL 2

Expert Comment

by:winston33
ID: 37725233
I can see one thing:

You need to re-instantiate oXml before the second XML doc is created. Code should look like this:

oXml = New XmlDocument
Dim pNode2 As XmlNode = oXml.CreateElement("Modules")
                                                oXml.AppendChild(pNode)

                                                oNode = oXml.SelectSingleNode("Modules")

                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstCourses.Items(i).Selected = True Then
                                                        oElement = oXml.CreateElement("ModuleNo")
                                                        oElement.InnerText = lstCourses.Items(i).Text
                                                        oNode.AppendChild(oElement)
                                                    End If
                                                Next

                                                xmlModule = oXml.InnerXml

Is this the section where you see the error when you step through the code?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ntd1
ID: 37725347
Hi

I changed it and now it says:

The node to be inserted is from a different document context.

Also the sub convert is being called but not encoding the file (ffmpeg)

Thanks
0
 
LVL 2

Expert Comment

by:winston33
ID: 37726011
Another copy/paste error...

oXml = New XmlDocument
Dim pNode2 As XmlNode = oXml.CreateElement("Modules")
                                                oXml.AppendChild(pNode2)

                                                oNode = oXml.SelectSingleNode("Modules")

                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstCourses.Items(i).Selected = True Then
                                                        oElement = oXml.CreateElement("ModuleNo")
                                                        oElement.InnerText = lstCourses.Items(i).Text
                                                        oNode.AppendChild(oElement)
                                                    End If
                                                Next

                                                xmlModule = oXml.InnerXml

As for the file, I don't know what the method is doing, so I can't really help with that yet.
0
 

Author Comment

by:ntd1
ID: 37726124
Hi

It not failing at the converting stage now. its not inserting anything though?

I wondered if the parameters are correct?


Conn.Open()
                                               

                                                Dim oXml As New XmlDocument
                                                Dim oNode As XmlNode
                                                Dim oElement As XmlElement



                                                Dim xmlCourse As String
                                                Dim xmlModule As String
                                                Dim pNode As XmlNode = oXml.CreateElement("Courses")
                                                oXml.AppendChild(pNode)

                                                oNode = oXml.SelectSingleNode("Courses")

                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstCourses.Items(i).Selected = True Then
                                                        oElement = oXml.CreateElement("@CourseNo")
                                                        oElement.InnerText = lstCourses.Items(i).Text
                                                        oNode.AppendChild(oElement)
                                                    End If
                                                Next

                                                xmlCourse = oXml.InnerXml






                                                oXml = New XmlDocument
                                                Dim pNode2 As XmlNode = oXml.CreateElement("Modules")
                                                oXml.AppendChild(pNode2)

                                                oNode = oXml.SelectSingleNode("Modules")

                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstModules.Items(i).Selected = True Then
                                                        oElement = oXml.CreateElement("@ModuleNo")
                                                        oElement.InnerText = lstModules.Items(i).Text
                                                        oNode.AppendChild(oElement)
                                                    End If
                                                Next

                                                xmlModule = oXml.InnerXml










                                                Const SQL As String = "sprocUploadVideo"
                                                Dim cmd As New SqlCommand(SQL, Conn)
                                                cmd.CommandType = CommandType.StoredProcedure
                                                cmd.Parameters.AddWithValue("@UserNo", UserLogin.UserNo())
                                                cmd.Parameters.AddWithValue("@VideoName", txtVideoName.Text.Trim())
                                                cmd.Parameters.AddWithValue("@DateUploaded", Date.Now)
                                                cmd.Parameters.AddWithValue("@VideoUrl", UserLogin.UserNo & "/" & fileNameWithoutExtention & ".flv")
                                                cmd.Parameters.AddWithValue("@ThumbUrl", UserLogin.UserNo & "\" & fileNameWithoutExtention & ".jpg")
                                                cmd.Parameters.AddWithValue("@Description", txtDescription.Text.Trim())
                                                cmd.Parameters.AddWithValue("@Keywords", txtKeywords.Text.Trim())
                                                cmd.Parameters.AddWithValue("@Visible", "Yes")
                                                FileUploadVideo.PostedFile.SaveAs(path & _
                                                     FileUploadVideo.FileName)


                                                'MsgBox("File Uploaded Successfully - Thank You", MsgBoxStyle.Information, "Video Upload")
                                                'Successful upload message
                                                lblError.Text = "File Uploaded Successfully - Thank You"

                                                'clear the fields
                                                lblHoldFilePath.Text = ""
                                                lstModules.Items.Clear()
                                                lstCourses.Items.Clear()
                                                txtVideoName.Text = ""
                                                txtKeywords.Text = ""
                                                cboCourse.Text = ""
                                                cboModule.Text = ""
                                                txtDescription.Text = ""
                                                cmd.ExecuteNonQuery()

                                                'close the database connection
                                                Conn.Close()

And also as you said earlier. The drop down lists are databound as

cboModule.DataTextField = "ModuleCode"
cboModule.DataValueField = "ModuleNo"
cboCourse.DataTextField = "CourseName"
cboCourse.DataValueField = "CourseNo"

When they are added to the lists I would like them to display the courses and module names, but I am thinking using the code I have been (below) will only hold the text value and not the course and module numbers?

 'check if an item is selected
        If cboCourse.SelectedIndex <> 0 Then
            'check to see selected item is not already on the list
            If lstCourses.Items.Contains(cboCourse.SelectedItem) = True Then
                'tell user duplicates can't be added
                lblErrorCourse.Text = "This Course is Already on the List!"
            Else
                'clear the error message
                lblErrorCourse.Text = ""
                'if ok add to the list
                lstCourses.Items.Add(cboCourse.SelectedItem)

            End If
        Else
            'error message no course selected
            lblErrorCourse.Text = "Please First Select a Course"
        End If
0
 
LVL 2

Expert Comment

by:winston33
ID: 37726612
1) It doesn't look like you are adding the XML strings to the parameter list. Does the proc work when you run it alone in SQL Server (with those variables)?

2) You are safe with the way you are adding items. The SelectedItem is of type ListItem, so it will pass in both the text and the value. Of course, the listbox settings need to be the same (Value and Text fields). Are they?
0
 

Author Comment

by:ntd1
ID: 37727189
If it is copying the item then yes. As the drop box datavalue is the course/module number.

How do I add the xml to the parameter list?
0
 
LVL 2

Expert Comment

by:winston33
ID: 37727435
In VB:

cmd.Parameters.AddWithValue("@CourseXML", xmlCourse)
cmd.Parameters.AddWithValue("@ModuleXML", xmlModule)

In SQL, my version of the proc included the new parameters:

ALTER PROCEDURE dbo.sprocUploadVideo
      @UserNo                  INT,
      @VideoName            VARCHAR(50) = NULL,  
      @DateUploaded      DATETIME = NULL,
      @VideoUrl            VARCHAR(50) = NULL,
      @ThumbUrl            VARCHAR(50),
      @Description      VARCHAR(500),
      @Keywords            VARCHAR(50),
      @Visible            VARCHAR(10),
      @CourseXML            XML,
      @ModuleXML            XML
0
 

Author Comment

by:ntd1
ID: 37728242
Hi

I have added the parameters and it is now sucessfully inserting into the video table.

It is not inserting anything into

VideoCourse
VideoModule

The id of Videos is VideoNo

ALTER PROCEDURE dbo.sprocUploadVideo
      @UserNo                  INT,
      @VideoName            VARCHAR(50) = NULL,  
      @DateUploaded      DATETIME = NULL,
      @VideoUrl            VARCHAR(50) = NULL,
      @ThumbUrl            VARCHAR(50),
      @Description      VARCHAR(500),
      @Keywords            VARCHAR(50),
      @Visible            VARCHAR(10),
      @CourseXML            XML,
      @ModuleXML            XML
     
AS

      DECLARE @course TABLE (CourseNo INT)                                                
      DECLARE @module TABLE(ModuleNo INT)
      DECLARE @VideoNo INT
     
      INSERT INTO @course
      SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
      FROM            @CourseXML.nodes('/Course/CourseNo') AS ParamLabel (ID)      

      INSERT INTO @module
      SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
      FROM            @CourseXML.nodes('/Module/ModuleNo') AS ParamLabel (ID)      
 
     INSERT INTO dbo.Video
          (
            UserNo                  ,
            VideoName                     ,
            DateUploaded                   ,
            VideoUrl                          ,
            ThumbUrl                          ,
            Description                      ,
                  Keywords                          ,
                  Visible          
          )
     VALUES
          (
            @UserNo                  ,
            @VideoName                     ,
            @DateUploaded                   ,
            @VideoUrl                         ,
            @ThumbUrl                        ,
            @Description               ,
            @Keywords                              ,
                  @Visible
          )

      SET      @VideoNo = SCOPE_IDENTITY()
     
      INSERT INTO VideoCourse
      SELECT      @VideoNo,
                  CourseNo
      FROM      @course

     
      INSERT INTO VideoModule
      SELECT      @VideoNo,
                  ModuleNo
      FROM      @module
0
 

Author Comment

by:ntd1
ID: 37728888
The two list boxes the the loop is running on are showing the course and module names.

The selection mode of the list boxes are set to multiple else they wont accept more than one item.

Do you think that this could be the problem why it is not inserting correctly? Or is it the stored procedure? I have double checked all of the table names and colomn names.

Thanks again for your continued efforts.
0
 
LVL 2

Expert Comment

by:winston33
ID: 37729361
If you step through the code, are the Xml documents being created? Also, as I reviewed the code, I see the loop through the Modules list is checking courses list:

                                                For i As Integer = 0 To lstCourses.Items.Count - 1
                                                    If lstModules.Items(i).Selected = True Then
                                                        oElement = oXml.CreateElement("@ModuleNo")
                                                        oElement.InnerText = lstModules.Items(i).Text
                                                        oNode.AppendChild(oElement)
                                                    End If
                                                Next

                                                xmlModule = oXml.InnerXml

This should be changed to loop through lstModules instead.
0
 

Author Comment

by:ntd1
ID: 37729422
Hi

I have changed that error.

The lists are being created successfully as I skip through the code. The problem is though that the lists contain the module and course names and not their numbers?

Thanks
0
 
LVL 2

Expert Comment

by:winston33
ID: 37729468
OK. I thought I mentioned to use the Value of the listitem instead of the Text, but I keep pasting in code that includes the Text property. Change that and see if it helps.
0
 

Author Comment

by:ntd1
ID: 37729629
Hi

It now picks up the course and module codes.

It runs through both loops with all of the right numbers in them.

It has the right course/module details in the parameter

It writes all of the details into the video table.

It will not write anyting into the videocourse or videomodule tables.

Could it be the stored procedure?
0
 
LVL 2

Accepted Solution

by:
winston33 earned 500 total points
ID: 37730763
I notice one thing in the proc. The proc is expecting the XML to have elements that look like this:

<Course><CourseNo></CourseNo></Course>
<Module><ModuleNo></ModuleNo></Module>

However, the code is building:
<Courses><CourseNo></CourseNo></Courses>
<Modules><ModuleNo></ModuleNo></Modules>

So, if you change the proc, it should look like this:

      INSERT INTO @course
      SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
      FROM            @CourseXML.nodes('/Courses/CourseNo') AS ParamLabel (ID)      

      INSERT INTO @module
      SELECT      ParamLabel.ID.value('.', 'VARCHAR(20)')      
      FROM            @CourseXML.nodes('/Modules/ModuleNo') AS ParamLabel (ID)  

Hope this helps.
0
 

Author Comment

by:ntd1
ID: 37730992
That's IT!!!  It works!!

Unbelievable!

I can't thank you enough for all of your effort.

Now I just have to figure out how to do the edits on the edit video page where a user can add and removes courses as this form does, but the lists will be already populated with ones previously added!

Thank You again.
0
 

Author Closing Comment

by:ntd1
ID: 37730998
Winston33 went above and beyond to solve my problem. Very, Very Helpful! Excellent.
0
 
LVL 2

Expert Comment

by:winston33
ID: 37731641
Glad it worked for you.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now