Manipulating a SqlDatSource UpdateCommand in Code-behind

We've got an employee database that I'm modifying to include two photos of each employee, a small thumbnail image and a full-size image. The HR department maintenance page contains a listbox of employee names, which, when clicked, populates a detailsview control.
To get the images to display and be updatable, I've had to structure the following SqlDatasource and DetailsView:

1 <asp:DetailsView ID="dvEmp" runat="server"
2 AutoGenerateRows="false"
3 DataSourceID="dsEmpView"
4 DataKeyNames="empID">
5 <Fields>
6 <asp:CommandField ShowEditButton="true" ShowCancelButton="true" ShowInsertButton="true" />
7 <asp:BoundField HeaderText="Name (Last, First)" DataField="empname" />
8 <asp:TemplateField HeaderText="Thumbnail photo">
9 <ItemTemplate>
10 <asp:Image ID="imgThumbnail" runat="server" ImageUrl='<%# formatThumbURL(DataBinder.Eval(Container.DataItem,"empID"))%>' />
11 </ItemTemplate>
12 <EditItemTemplate>
13 <asp:Image ID="imgThumbHidden" runat="server" ImageUrl='<%# Bind("thumbURL")%>' Visible="false" />
14 <asp:FileUpload ID="upldThumbnail" runat="server" />
15 </EditItemTemplate>
16 </asp:TemplateField>
17 <asp:TemplateField HeaderText="Full Photo">
18 <ItemTemplate>
19 <asp:Image ID="imgPhoto" runat="server" ImageUrl='<%# formatImageURL(DataBinder.Eval(Container.DataItem,"empID"))%>' />
20 </ItemTemplate>
21 <EditItemTemplate>
22 <asp:Image ID="imgPhotoHidden" runat="server" ImageUrl='<%# Bind("photoURL")%>' Visible="false" />
23 <asp:FileUpload ID="upldPhoto" runat="server" />
24 </EditItemTemplate>
25 </asp:TemplateField>
26 </Fields>
27 </asp:DetailsView>
28
29 <asp:SqlDataSource ID="dsEmpView"
30 runat="server"
31 ConnectionString="<%$ ConnectionStrings:eSignInConnectionString%>"
32 OnInserting="dsEmpView_Inserting"
33 OnUpdating="dsEmpView_Updating"
34 SelectCommand="SELECT empID, empname, photoURL, thumbURL FROM employees where (empID = @.empID)"
35 InsertCommand="INSERT INTO employees (empname, photoURL, thumbURL) values(@.empname, @.photoURL, @.thumbURL)"
36 UpdateCommand="UPDATE employees SET empname=@.empname, photoURL=@.photoURL, thumbURL=@.thumbURL WHERE (empID = @.empID)">
37 <SelectParameters>
38 <asp:ControlParameter ControlID="lbxEmps" Name="empID" PropertyName="SelectedValue" Type="Int16" />
39 </SelectParameters>
40 </asp:SqlDataSource>
41
42 --
43
44 Protected Sub dsEmpView_Updating(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
45 Dim bAbort As Boolean = False
46 Dim bThumb(), bPhoto() As Byte
47 If e.Command.Parameters("@.ename").Value.trim = "" Then bAbort = True
48 Dim imgT As FileUpload = CType(dvEmp.FindControl("upldThumbnail"), FileUpload)
49 If imgT.HasFile Then
50 Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream)
51 bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength)
52 e.Command.Parameters("@.thumbURL").Value = bThumb
53 End Using
54 End If
55 Dim imgP As FileUpload = CType(dvEmp.FindControl("upldPhoto"), FileUpload)
56 If imgP.HasFile Then
57 Using reader As BinaryReader = New BinaryReader(imgP.PostedFile.InputStream)
58 bPhoto = reader.ReadBytes(imgP.PostedFile.ContentLength)
59 e.Command.Parameters("@.photoURL").Value = bPhoto
60 End Using
61 End If
62 e.Cancel = bAbort
63 End Sub

If the user updates both images at the same time by populating their respective FileUpload boxes, everything works as advertized. But if the user only updates one image (or neither image), things break. If they upload, say, just the full-size photo during an update, then it gives the error "System.Data.SqlClient.SqlException: Operand type clash: nvarchar is incompatible with image".

I think this error occurs because the update command is trying to set the parameter "thumbURL" without having any actual data to set.  But since I really don't want this image updated with nothing, thereby erasing
the photo already in the database, I'd rather remove this parameter from the update string.

So, let's remove the parameter that updates that image by adding the following code just after the "End Using" lines:
 
Else Dim p As SqlClient.SqlParameter = New SqlClient.SqlParameter("@.thumbURL", SqlDbType.Image) e.Command.Parameters.Remove(p)
(Similar code goes into the code block that handles the photo upload)

Running the same update without an image in the thumb fileupload box, I now get this error: "System.ArgumentException: Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection."

Huh?  It's not there?  Okay, so lets work it from the other end: let's remove all references to the thumbURL and photoURL from the dsEmpView datasource.  We'll make its UpdateCommand = "UPDATE employees SETempname=@.empname WHERE (empID = @.empID)", and put code in the
dsEmpView_Updating sub that adds the correct parameter to the update command, but only if the fileupload box has something in it.  Therefore: 

If imgT.HasFile Then Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream) bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength) e.Command.Parameters.Add(New SqlClient.SqlParameter("@.thumbURL", SqlDbType.Image, imgT.PostedFile.ContentLength)) e.Command.Parameters("@.thumbURL").Value = bThumb End UsingEnd If
(Similar code goes into the code block that handles the photo upload)

But reversing the angle of attack only reverses the error. Uploading only the photo and not the thumb image results in: "System.Data.SqlClient.SqlException: The variable name'@.photoURL' has already been declared. Variable names must be unique within a query batch or stored procedure."

So now it's telling me the parameter IS there, even though I just removed it.

ARRRGH!

What am I doing wrong, and more importantly, how can I fix it?

Thanks in advance.
 

This: 
 Dim p As SqlClient.SqlParameter = New SqlClient.SqlParameter("@.thumbURL", SqlDbType.Image) e.Command.Parameters.Remove(p)
will never work. You've just created parameter p, so it obviously can't be contained in the parameters collection. Regardless of what you've given it for a name, it's not the same as anything that might have been in the collection.
 
 
29  <asp:SqlDataSource ID="dsEmpView"
30    runat="server"
31    ConnectionString="<%$ ConnectionStrings:eSignInConnectionString%>"
32    OnInserting="dsEmpView_Inserting"
33    OnUpdating="dsEmpView_Updating"
34    SelectCommand="SELECT empID, empname, photoURL, thumbURL FROM employees where (empID = @.empID)"
35    InsertCommand="INSERT INTO employees (empname, photoURL, thumbURL) values(@.empname, @.photoURL, @.thumbURL)"
    UpdateCommand="UPDATE employees SETempname=@.empname WHERE (empID = @.empID);IF (NOT @.photoURL IS NULL) UPDATE employees SETphotoURL=@.photoURL WHEREempID=@.empID;IF (NOT @.thumbURL IS NULL) UPDATE employees SET thumbURL=@.thumbURL WHEREempID=@.empID;">
37    <SelectParameters>
38      <asp:ControlParameter ControlID="lbxEmps" Name="empID" PropertyName="SelectedValue" Type="Int16" />
39    </SelectParameters>
<UpdateParameters>
<asp:Parameter Name="empname" Type="String" />
<asp:ControlParameter ControlID="lbxEmps" Name="empID" PropertyName="SelectedValue" Type="Int16" />
<asp:Parameter Name="photoURL" Type="Image" />
<asp:Parameter Name="thumbURL" Type="Image" />
</UpdateParameters>
40  </asp:SqlDataSource>
41  
42  --
43  
44  Protected Sub dsEmpView_Updating(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
45    Dim bAbort As Boolean = False
46    Dim bThumb(), bPhoto() As Byte
47    If e.Command.Parameters("@.empname").Value.trim = "" Then bAbort = True
48    Dim imgT As FileUpload = CType(dvEmp.FindControl("upldThumbnail"), FileUpload)
49    If imgT.HasFile Then
50      Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream)
51        bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength)
52         e.Command.Parameters("@.thumbURL").Value = bThumb
53      End Using
54    End If
55    Dim imgP As FileUpload = CType(dvEmp.FindControl("upldPhoto"), FileUpload)
56    If imgP.HasFile Then
57       Using reader As BinaryReader = New BinaryReader(imgP.PostedFile.InputStream)
58        bPhoto = reader.ReadBytes(imgP.PostedFile.ContentLength)
59        e.Command.Parameters("@.photoURL").Value = bPhoto
60      End Using
61    End If
62    e.Cancel = bAbort
63  End Sub
 
I fixed stuff ingreen

|||

Oh, geez, you're right. (sigh) Sometimes the obvious just isn't.

But I still need to point at that parameter and delete it. If I change that line to:

Dim p As SqlClient.SqlParameter = e.Command.Parameters("@.thumbURL")

The error becomes "System.Data.SqlClient.SqlException: Must declare the variable '@.thumbURL'".

I guess the question is this: how do I remove (or add) a parameter in the update command of a SqlDataSource in the code-behind?

|||

If you remove the parameter, you must also remove the reference to it in your Update commandtext.

So you'd need to change

UPDATE ... SETthumbURL=@.thumbURL,... WHERE ...

to:

UPDATE ... SET ... WHERE ...

In code (I believe) you would put this in the same place you remove your paramter:

e.command.commandtext=e.command.commandtext.replace(",thumbURL=@.thumbURL","")

|||

I somehow missed your corrections in green. Things aren't wrapping properly on my browser for some reason.

I tried your changes, and I think they might have worked, except that the compiler (and the VS2005 IDE) doesn't like Type="Image" in the update parameters. Type=Binary doesn't work either, 'cause the photo is larger than 8,000 bytes. Any way around this?

Thanks for your help so far.

|||

I did remove it from the commandupdate as I wrote in my original question, but I had not tried removing it from e.Command.CommandText. It works! Thank you so much!