Many-to-many Relationship Issue/Question

Hello All,

Here's what I got so far:

- Employee fact table with 10 dimensions connected to it

- A Factless Bonus table and the Employee fact table are connected through a common dimension of Time.

- The Factless Bonus table connects to a Bonus Dimension

- added a many-to-many relationship with the Bonus Dimension to the Employee fact table

How can I slice and dice by both the other 10 dimensions and the Bonus Dimension?

Thank you.

-Gumbatman

"How can I slice and dice by both the other 10 dimensions and the Bonus Dimension?" - but aren't you already able to slice and dice the measures from the Employee fact table this way? If not, could you explain what the problem is in more detail? It seems strange that Bonus should only be a function of time, and not of any other dimension; but maybe that's in the nature of your problem.|||

Deepak,

I apologize for not expressing this as well as I could. I just used the Time dimension as an example. Sorry about that...

Here's the situation:

I have a fact table that has one row per employee, per month. I now need to add bonuses that were received by the employees. The issue is that employees can received more than one bonus per month and sometimes those bonuses can be the same type.

So the question is: How can I have multiple bonuses for an employee in a single month when they only have one row in that month?

When I tried connecting the Fact tables, I didn't know what made the most logical sense to have as that intermediate dimension. When I tried using Time, I could get that they got a bonus and when they got it, but I could not do any additional slicing across other dimensions (the 10 I mentioned) that the Employee Fact table is connected to.

My results would be that Employee John Doe got five bonuses this month, but it would not show that he was in the Southeast. It would place those bonuses only in the Midatlantic area.

Thank you so much for the help. This has been driving me crazy.

-Gumbatman

p.s. Another thing I need to do is show which employees when to which school. Again, employees may have attended more than one school.

|||So there are apparently 2 fact tables/measure groups here: EmployeeMonthly and BonusMonthly, with the latter having an associated "BonusType" dimension. But are the other 10 dimensions all directly related to EmployeeMonthly, or are some referenced via a separate "Employee" dimension (ie. each employee has a single associated member)? In either case, these dimensions would have referenced relationships to the BonusMonthly measure group, either via the Employee or EmployeeMonthly fact dimension. The only obvious many-to-many relation is between the BonusType dimension and the EmployeeMonthly measure group, via the BonusMonthly measure group (I'm assuming that EmployeeMonthly has a composite key like {Employee, Month}, which is a foreign key in BonusMonthly).|||

Deepak,

I couldn't get the quoting to work, but here are my answers:

So there are apparently 2 fact tables/measure groups here: EmployeeMonthly and BonusMonthly, with the latter having an associated "BonusType" dimension.

- Yes

But are the other 10 dimensions all directly related to EmployeeMonthly, or are some referenced via a separate "Employee" dimension (ie. each employee has a single associated member)?

- The other 10 dimensions all directly related to EmployeeMonthly,

In either case, these dimensions would have referenced relationships to the BonusMonthly measure group, either via the Employee or EmployeeMonthly fact dimension.

- I haven't tried that yet, but I will. I think that is one of the points I am missing.

The only obvious many-to-many relation is between the BonusType dimension and the EmployeeMonthly measure group, via the BonusMonthly measure group (I'm assuming that EmployeeMonthly has a composite key like {Employee, Month}, which is a foreign key in BonusMonthly).

- That makes perfect sense, but I am still confused on the exact relationship between EmployeeMonthly and BonusType. How do they connect to each other? Is that with a composite key or with BonusMonthly? Or is BonusMontly a table with the composite key?

When you are referring to a composite key, do you mean selecting the EmployeeKey, BonusKey, Date, etc. and make that a key? Or concatenating those keys into a single field for the row, which is now a Primary Key?

The way I picture this is:

EmployeeMonthly Table

Employee Monthly Key Employee Key Date Key Bonus Composite Key 1 1 20061001 0 2 2 20061001 2_20061001 3 1 20061101 0 4 2 20061101 0

BonusMonthly Table

Employee Key DateKey Bonus Key Bonus Composite Key 2 20061001 1 2_20061001 2 20061001 1 2_20061001 2 20061001 2 2_20061001

Here Employee #2 got three bonuses on 10/1/2006. Two of the bonuses were the same type and that is what confuses me about the composite keys, don't I need a Primary key in BonusMontly?

Also, I may be thinking in terms of a relational database, but when I connect EmployeeMonthly and BonusMonthly (in the setup above) I would expect three rows of duplicated data. Is that correct?

Thank you so much for your help with this.

-Gumbatman

|||Based on the sample data, EmployeeMonthlyKey could be added as a Foreign Key to BonusMonthly, allowing the EmployeeMonthly fact dimension to directly relate to BonusMonthly measure group. Then BonusType could have a many-to-many relation to EmployeeMonthly measure group, with BonusMonthly being the intermediate measure group. You might not need a primary key in BonusMonthly, unless you're also setting it up as a fact dimension (eg: for drillthrough).|||

Deepak,

I got this to work, almost...

The problem I am still having is with the Dimensions that are related only to the EmployeeMonthly fact table.

For example,

- EmployeeMonthy is related to BonusType dimension through BonusMonthly measure group.

- EmployeeMonthy also has dimensions directly related to it, such as a Geographic dimension, JobLevel dimension, Product dimension, etc.

When I want to see Bonus data by Geographic Area, I am getting strange results. The numbers are correct, but the bonuses are not properly breaking down by Geographic Area. It appears that it is only taking the first member of the Geographic dimension.

I tried setting them (BonusType and Geographic) as many-to-many, but that didn't work.

How is it supposed to work? Is there a way around this? I am thinking of just duplicating all the dimensions on the EmployeeMonthly onto the BonusMonthly fact table.

I really appreciate the time and energy you've put into helping me with this.

-Gumbatman

|||"The problem I am still having is with the Dimensions that are related only to the EmployeeMonthly fact table" - as I mentioned earlier, you could configure these dimensions with referenced (not many-to-many) relationships to the BonusMonthly measure group, via the EmployeeMonthly fact dimension (which you should have already configured as directly related to the BonusMonthly measure group). These referenced relationships could be materialized, for better performance. Or you could, as you suggest, directly relate these dimensions to the BonusMonthly measure group, but this might involve adding a join to the fact table named query.|||

Deepak,

Thank you so much, I finally got it to work!

I didn't really catch it when you spoke about referenced dimensions. Plus, my lack of understanding made me gloss over it.

I really appreciate all the time and effort you put into helping me with this. I would never of have gotten to the solution without your expertise.

-Gumbatman

Many To Many

hi im implementing a database in ms access to migrate it later to SQL, its a project tracking/ employee tracking and im having trouble with some of the tables... the relationships are as follow

Employee : M
Employee_ID
Name
Phone
Supevirsor_Name
Supervisor_Email

Project : M
Project_ID
Project_ Name
Description
Project_Added

EmployeeProject
Employee_ID
Project_ID
AssignedBy

i made this third table called EmployeeProjects for the relationship, but when i go to collect the data everything is ballistic, when i go to capture employees in the employee table everything is fine, i go to the projects and everything is fine there is a "+" in the projects its lists every single employee that i captured in employees in the same project i go to the next record and the same deal, there is a possibility that this could be that many employees can be in a project and also working in another project, what is wrong with it? can anybody help me?I'd say nothing...

Why don't you post the DDL for the tables (CREATE TABLE myTable99(Col1 int, ect)

Some sample Data (INSERT INTO myTable99(Collist) SELECT Data UNION ALL SELECT ect)

The DML You've attempted (SELECT Col1, Col2 FROM myT INNER JOIN myt2, ect)

And the results you'd expect...

I'd say you'd get an answer in 15 minutes of that post...|||The designn looks OK. Id' change your Employ3ee table a little though:

Employee_ID
LastName
FirstName
MiddleName
FullName (calculated...not sure if access has that)
PhoneArea
PhonePrefix
PhoneSuffix
Phone (calculated...same as FullName)
EMail
Supervisor_ID (NULL or same if it's a supervisor)

The reason you may have the situation you describe is only because of the contents of EmployeeProject. Do this:

select Project_ID, count(*) from EmployeeProject group by Project_ID
union all
select 0, count(*) from Employee

This will get you started on finding out how many employees are assigned to each project and what's the total number of employees assigned vs. the total number of employees in the organization.|||I don't have any SQL procedures that can parse that last sentence of yours, so I'm not exactly sure what the problem is.

But I have to ask why you are developing this in MS Access if you are planning to upsize it to SQL Server anyway. Have you considered creating it in SQL Server and using an Access Data Project (.adp file) front-end? You'd get all the benefits of Access forms, reports, and modules for the interface, and you wouldn't have to upsize it later. Plus SQL Server's security is much better and easier to implement than MS Access security.|||the reason for me doing it in access first is my boss maily.... hes a manufacturing engeneer and he knows nothing about servers and all that... he asked me to do it first kinda like a prototype for collecting the data... i said prior to him that i could build it in SQL save us a hole lot of time.. but he wouldnt budge... (putz)... any way i explained that particular situation ( me using access as a front end) but he was like no no Y complicate it so much... just doit in access and then we will see what to leave or what not... any ways thanx for your help...|||i actually did that... in fact i did get the results that i wanted... but also... i dropped the tables and created them again... exactly and got them as i wanted... your solution was indeed good just got to it now... if only i'd gotten to it sooner... thanx for your help u really did help me|||Exactly what i was doing... but also i don't know what in the heck was wrong with the tables... so i'd dropped'm and built them again got what i wanted... what u posted... was what i did and it worked... thanx|||Your boss is a putz. Oh wait, you already said that. Well tell him I said so too.

Asking him why he bothers hiring competent people if he isn't going to trust their expert judgement. Duh.

Manually Insert a Primary Key Value

I have a colleague who mysteriously lost his record in our Employee table.
The "employee ID" field serves as the primary key on the table.
How do I manually insert his record, including the old primary key value,
back into the table? That is, how do I bypass the primary-key constraint?
Thanks in advance,
Mark HolahanWhat is the definition of the table?
AMB
"Mark Holahan" wrote:

> I have a colleague who mysteriously lost his record in our Employee table.
> The "employee ID" field serves as the primary key on the table.
> How do I manually insert his record, including the old primary key value,
> back into the table? That is, how do I bypass the primary-key constraint?
> Thanks in advance,
> Mark Holahan
>
>|||Is this an identity field? if so use:
SET IDENTITY_INSERT ON
--execute insert statement here
SET IDENTITY_INSERT OFF|||You can't "bypass" a primary key constraint unless you drop it. I
assume you are actually referring to the IDENTITY property on this
column. The IDENTITY property is quite distinct from a PRIMARY KEY
constraint. If you want to insert an explicit IDENTITY value then use
the SET IDENTITY_INSERT table_name ON option.
Why does it matter to you if the row gets inserted with a different
IDENTITY value to the one it originally had? It shouldn't have been
possible for the accidental delete to cause "orphan" rows in a
referencing table - That's assuming you have correctly declared foreign
key constraints against the employee ID column. If you don't have
foreign keys then that's something you really ought to fix.
David Portas
SQL Server MVP
--|||AMB,
The table definition follows:
CREATE TABLE [dbo].[Employee] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchId] [int] NULL ,
[SalesRepId] [int] NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Deactivated] [datetime] NULL ,
[ResetPW] [bit] NOT NULL ,
[Tries] [tinyint] NULL ,
[LastLoginDtm] [datetime] NULL ,
[PendingInfoUpdate] [bit] NOT NULL ,
[IsSalesRep] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
[PendingInfoUpdate],
CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
> What is the definition of the table?
>
> AMB
> "Mark Holahan" wrote:
>|||Read David's post.
AMB
"Mark Holahan" wrote:

> AMB,
> The table definition follows:
> CREATE TABLE [dbo].[Employee] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BranchId] [int] NULL ,
> [SalesRepId] [int] NULL ,
> [Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Deactivated] [datetime] NULL ,
> [ResetPW] [bit] NOT NULL ,
> [Tries] [tinyint] NULL ,
> [LastLoginDtm] [datetime] NULL ,
> [PendingInfoUpdate] [bit] NOT NULL ,
> [IsSalesRep] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
> CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] ADD
> CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
> CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
> [PendingInfoUpdate],
> CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
>
>|||Distinction noted.
CIO of company claims RI puts unneeded burden on SQL Server. Therefore we
handle RI on the front end. I don't necessarily agree, especially when I
read in BOL that, "The query optimizer also uses constraint definitions to
build high-performance query execution plans." But I've never done the
homework to disprove his theory. So I abide.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107450710.189817.206210@.g14g2000cwa.googlegroups.com...
> You can't "bypass" a primary key constraint unless you drop it. I
> assume you are actually referring to the IDENTITY property on this
> column. The IDENTITY property is quite distinct from a PRIMARY KEY
> constraint. If you want to insert an explicit IDENTITY value then use
> the SET IDENTITY_INSERT table_name ON option.
> Why does it matter to you if the row gets inserted with a different
> IDENTITY value to the one it originally had? It shouldn't have been
> possible for the accidental delete to cause "orphan" rows in a
> referencing table - That's assuming you have correctly declared foreign
> key constraints against the employee ID column. If you don't have
> foreign keys then that's something you really ought to fix.
> --
> David Portas
> SQL Server MVP
> --
>|||The CIO is wrong. If he wants to design databases he should take a course
first ;-)
Obviously handling RI on the front end isn't working otherwise you wouldn't
have this problem. No surprises there.
David Portas
SQL Server MVP
--

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!