Insert value of fileUpload control into SQL column

Not certain, but I think you may be having trouble using an NVARCHAR datatype for image data.   What is the datatype of the UserImage column?  What is the max size of the image file?

Never mind, I see now you are only storing the path to the image.

What is the datatype of the UserImage column? Step through the code to confirm that the UserImage variable is being populated as expected.

That error message has to do with an invalid connection string.  Assuming that the following is not your real code:
Dim connectionString As String = "ConnectionString"

Try posting your connection string.

You also have an invalid INSERT statement (you cannot have a WHERE clause there):
Dim cmdText = "INSERT INTO vw_aspnet_MembershipUsers (UserName, UserImage) VALUES (@UserName, @UserImage) WHERE (UserName = @UserName));"

Try it this way:
Dim cmdText = "INSERT INTO vw_aspnet_MembershipUsers (UserName, UserImage) VALUES (@UserName, @UserImage)"

@acperkins
Well noticed. I did not notice the where clause in insert statement!!!

Hi guys I'll try your suggestions this evening.

I have removed the WHERE statement on the insert ( real newbie error! :) ), still get the error though. So I suppose that boils down to a poor ConnectionString setup.

@acperkins, where I used: Dim connectionString As String = "ConnectionString"

I intended to point to the connectionString in the web.config, is this how it is achieved, If "ConnectionString" is the variable assigned in web.config?

Thanks for your assistance people!

Here is how you get connectionstring from web.config

ConfigurationManager.ConnectionStrings.Item("MyConnectionString").ConnectionString

>>So I suppose that boils down to a poor ConnectionString setup.<<
That would appear to be the case.

Unfortunately it seems to be opening a massive can of worms.

Basically, the connection string was sorted as per CodeCruiser's suggestion. However a new error highlights that it cannot update a view due to it needing to insert into more than one table. So I set up the insert statement to populate the two separate tables with the relevant data.

This resulted in a new error which claimed it couldn't insert a Null value into column ApplicationId of aspnet_Membership nor could it insert a Null value into column UserName of aspnet_Users. I know it isn't passing a null value, as these values are being successfully utilised and working for actually saving the image to the server. Which leads me to believe that it is trying to insert a new record (and therefore new user) instead of amending the existing UserImage column for the selected user.

This is getting incredibly messy!...
Maybe utilising the details view would be easier to achieving my goal?

>So I set up the insert statement to populate the two separate tables with the relevant data.
>Which leads me to believe that it is trying to insert a new record (and therefore new user) instead of amending the existing UserImage column for the selected user.


You have setup the insert statement so it would insert the new record and not update it.

Can you post me the corrections to the code below that would actually update aspnet_Membership.UserImage column with the file name WHERE aspnet_Users.UserName is conditional value?


please note:
UserName is a value held in table aspnet_Users
UserImage is a value held in table aspnet_Membership

Protected Sub fu_button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles fu_button.Click
        Dim fileOK As Boolean = False
 
 
        If fu_image.HasFile Then
 
            Dim selectedUser As String
            selectedUser = gv_members.SelectedDataKey.Value
 
            Dim fileExtension As String
            fileExtension = System.IO.Path.GetExtension(fu_image.FileName).ToLower()
 
            Dim userImage As String
            userImage = selectedUser & fileExtension
 
            Dim allowedExtensions As String() = {".jpg", ".jpeg", ".png", ".gif"}
            For i As Integer = 0 To allowedExtensions.Length - 1
                If fileExtension = allowedExtensions(i) Then
                    fileOK = True
                End If
            Next
 
            If fileOK Then
                Try
                    fu_image.SaveAs(Server.MapPath("~\Resources\images\staff\" & userImage))
 
 
                    Dim connectionString As String = "ConnectionString"
                    Dim objConn As New System.Data.SqlClient.SqlConnection(connectionString)
                    Dim cmdText = "UPDATE aspnet_Membership (UserImage) VALUES (@UserImage);"
                    Dim objCommand As New System.Data.SqlClient.SqlCommand(cmdText, objConn)
 
                    Dim objUserName As New System.Data.SqlClient.SqlParameter("@UserName", System.Data.SqlDbType.NVarChar)
                    objUserName.Value = selectedUser
                    objCommand.Parameters.Add(objUserName)
 
                    Dim objUserImage As New System.Data.SqlClient.SqlParameter("@UserImage", System.Data.SqlDbType.NVarChar)
                    objUserImage.Value = userImage
                    objCommand.Parameters.Add(objUserImage)
 
                    Try
                        objConn.Open()
                        objCommand.ExecuteNonQuery()
                        MsgBox("Record Inserted")
                    Catch exc As System.Exception
                        MsgBox(exc.Message())
                    Finally
                        objConn.Close()
                    End Try
 
 
                    lbl_image.Text = "File name: " & _
                       fu_image.PostedFile.FileName & "<br />" & _
                       "File Size: " & _
                       fu_image.PostedFile.ContentLength & _
                       " kb<br />" & _
                       "Content type: " & _
                       fu_image.PostedFile.ContentType & _
                       "<br />" & _
                       "File Extension is:" & _
                       fileExtension
 
 
 
                Catch ex As Exception
                    lbl_image.Text = "ERROR: " & ex.Message.ToString()
                End Try
            Else
                lbl_image.Text = "You have not specified a file."
            End If
        End If
 
        Page.MaintainScrollPositionOnPostBack = True
 
    End Sub

Select allOpen in new window

You gentlemen have been a massive help!!!!!!

Thanks for your support during this process, I have learned a fair bit here and can now move forward.

I utilised your code codecruiser and had to make some tweaks here and there, including :
ConnectionString and adjusting the INSERT to include an INNERJOIN

Please find the concluding code below.

Regards,


B

Public Sub fu_button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles fu_button.Click
        Dim fileOK As Boolean = False
 
 
        If fu_image.HasFile Then
 
            Dim selectedUser As String
            selectedUser = gv_members.SelectedDataKey.Value
 
            Dim fileExtension As String
            fileExtension = System.IO.Path.GetExtension(fu_image.FileName).ToLower()
 
            Dim userImage As String
            userImage = selectedUser & fileExtension
 
            Dim allowedExtensions As String() = {".jpg", ".jpeg", ".png", ".gif"}
            For i As Integer = 0 To allowedExtensions.Length - 1
                If fileExtension = allowedExtensions(i) Then
                    fileOK = True
                End If
            Next
 
            If fileOK Then
                Try
                    fu_image.SaveAs(Server.MapPath("~\Resources\images\staff\" & userImage))
 
 
                    Dim connectionString As String = ConfigurationManager.ConnectionStrings.Item("cs_tqbfjotld").ConnectionString
                    Dim objConn As New System.Data.SqlClient.SqlConnection(connectionString)
                    Dim cmdText = "UPDATE aspnet_Membership Set UserImage='" & userImage & "' FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE (aspnet_Users.UserName = '" & selectedUser & "')"
                    Dim objCommand As New System.Data.SqlClient.SqlCommand(cmdText, objConn)
                    Try
                        objConn.Open()
                        objCommand.ExecuteNonQuery()
                        MsgBox("Record Updated")
                    Catch exc As System.Exception
                        MsgBox(exc.Message())
                    Finally
                        objConn.Close()
                    End Try
 
                    lbl_image.Text = "File name: " & _
                       fu_image.PostedFile.FileName & "<br />" & _
                       "File Size: " & _
                       fu_image.PostedFile.ContentLength & _
                       " kb<br />" & _
                       "Content type: " & _
                       fu_image.PostedFile.ContentType & _
                       "<br />" & _
                       "File Extension is:" & _
                       fileExtension
                Catch ex As Exception
                    lbl_image.Text = "ERROR: " & ex.Message.ToString()
                End Try
            Else
                lbl_image.Text = "You have not specified a file."
            End If
        End If
 
        Page.MaintainScrollPositionOnPostBack = True
 
    End Sub

Select allOpen in new window

You are welcome.