Comments by Franklin Smith

Franklin Smith 17-Oct-12 3:07am View

After a student logs in, they choose a course to study. The way things come down the line from there is.

Table 1 - CourseID
Table 2 - CourseID,ChapterID
Table 3 - ChapterID,AssessmentID(There can be multiple assessments per chapter not all will be vocab.)
Table 4 - GroupID(AssessmentID), WordID, Word, Description(Maybe better name would have been Definition)
Table 5 - WordID, SimilarWordID

That third table(Table_3) is a generated table that is being returned to a webpage. It doesn't actually exist. This selection of the exclusions is also not the normal. I just need it for the administration section to have a listbox that shows the words not currently linked to WordId.

I went back to check and I have setup Table 4 so that WordId is the Primary Key/Index and the GroupID is indexed. I'm thinking I should remove the indexing on WordId as it seems to me that what I want indexed together would be the GroupID records. I normally would worry about efficiency because in the past most of the stuff I put together was simple one or two concurrent user stuff. But on the student side I will end up with 3-4 hundred concurrent users.

But with regards to the Query would it be better to(I removed description as it is not needed for this Query)

SELECT Id, Word
FROM (SELECT Id, Word FROM Table_1 WHERE GroupID = @GroupID) AS Group
WHERE Group.Id <> @ExcludedItem
AND Group.Id NOT IN (SELECT SimilarWordID FROM Table_2 WHERE WordId = @ExcludedItem) AND Group.Id NOT IN (SELECT WordId FROM Table_2 WHERE SimilarWordID = @ExcludedItem)

Would that be more efficient?

Franklin Smith 16-Oct-12 9:49am View

The GroupID is actually what I have as a AssessmentID in another table. The "Words" here are vocabulary words for a particular subject and chapter. Because of this in the stored proc I am Selecting A Group of words first into a temp table. But after seeing how you used the view I think I'd rather use them. I used the temp tables because I saw someone elses code use them, but I really hate dealing with the drop statements and IF exist Drop statements cause it is just more overhead. So I'm going to remove those in favor of the views.

So I'm thinking to solve the GroupID problem I need to do the following.
SELECT Id, Word, Description
FROM Table_1
--Changed line here
WHERE GroupID = @GroupID
AND Id <> @ExcludedItem
AND Id NOT IN (SELECT SimilarWordID FROM Table_2 WHERE WordId = @ExcludedItem)
AND Id NOT IN (SELECT WordId FROM Table_2 WHERE SimilarWordID = @ExcludedItem)

Franklin Smith 16-Oct-12 3:18am View

Very elegant way of solving the problem. I'm going to have to see if I can implement that into my stored procedure. It would save me the temp tables. However, I'm not sure because one thing I found out I'm going to have to do. I have to add a column in the out table that specifies whether the WordID was found in the WordID Column or the SimilarWordID, but that is a matter for the included side not the exclusion.

One thing I'd like to ask though is, the data that this applies to is actually a subset. The table actually has something like GroupID, Id, Word, Description for columns. I'm just assuming here but to add the GroupID to this I'd need to add a "@GroupID int" to the parameters and in the SELECT's WHERE clause add "GroupID = @GroupID AND"

Franklin Smith 16-Oct-12 3:01am View

I found the excluded ones by finding the included ones. It just happens that's how the code worked. I worked on this all day finding the bits and pieces I was missing. It was by no means simple. I just posted what I finally worked out. I like what you posted better as it's more elegant than what I put up.

Franklin Smith 15-Oct-12 14:20pm View

This would almost work accept the Orange can not be hard coded and because these are going to put into Stored Procedures I needed similar coding that works both ways. So I came up what you will see below. But I still am having a problem with the one that gives me the results for those that aren't being used.

Franklin Smith 19-Mar-11 17:46pm View

This will not work, this is being done in webbrowser control not on a aspx page. It has to be manually inserted after the page loads. I also have javascript that has to be inserted to operate the autocomplete on a text box that will speed up data entry. If I was going to do it as a file I would just create a link instead and set the src and appropriate type.

Franklin Smith 7-Feb-11 16:30pm View

I just had a thought that allows me to get away with all this. The images on the control are all the same height, width. They are more or less Icons, the issue I had with the paint was the label. I just had a thought make a label, but don't show it. Do a .DrawToBitmap() on the label then place the bitmap on control in the paint. Solves all my problems and I don't have to do the calculations for the caption if it is to long and needs to extend to the second row.

Thanks for the help guys.

Franklin Smith 7-Feb-11 16:20pm View

I want only events on the UserControl to actually fire because I want it to behave as if the child controls don't actually exist except to paint stuff for me so I don't have to do it manually. What can I say I'm a little lazy. :)

Franklin Smith 7-Feb-11 16:17pm View

The click event is just an example. Basically what I want is the control to be treated like it is an individual control and not a panel with multiple child controls.

I may just have to go into the Paint event and paint what I want on the user control. While this would solve the problem it's just a pain as I have a Label on the control that allowed for two rows of text if needed. Doing the Paint way I have to do all the coding to make it drop a line if the text extends beyond the boundaries of the control. While it's more work than I wanted to deal with. Sometimes you just do what you have to.

Comments by Franklin Smith (Top 9 by date)