|
Hi, CP. I have a database hosted on an instance of SQL Server 2008 Express. I am using C# (.NET 4.0) and VS2010. I created a junction table in order to use a many-to-many relationship between two tables. I retrieve and manipulate data primarily using the main table but a part of the application requires me to find the most common value for a particular field, which is stored in a junction table.
For example, I call a SELECT statement which retrieves the fields from the table [Repair Data]. The junction table, [Failure Codes], contains the fields [Serial Number] and Failure. In order to find the most common [Failure Codes].[Failure] value based on a [Repair Data] record ([Repair Data] includes a field [Serial Number], which is how the junction table knows which [Repair Data] record it belongs to) would I do something like this:
reader = SELECT * FROM [Repair Data]
foreach (Record r in reader)
{
}
or is there a simpler method for achieving these results? I think I explained it correctly. I am currently not at work and I try to forget work when I'm at home . So I do not recall exactly what code I left off with. But I am quite positive I followed the "code" above.
Thanks CP.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
I don't know if I understand correctly your issue. I understand the issue is, that you have tables:
1. [Repaired item] ([Serial Number], [Some data about item]) - stores information about items, that are repaired (car, or whatever).
2. [Failure Codes] ([Failure Code], [Information about failure]) - stores information about failures, that can be repaired.
3. [Repair Data] ([Serial Number], [Failure Code]) - stores connection between [Repaired item] and [Failure Codes].
With the structure above you want (for the given [Serial Number]) find the failure that occurs most often.
If the above is correct, then I think you should do this in SQL using the following query:
SELECT
[Serial Number],
[Failure Code],
COUNT(*) AS [Counter]
FROM
[Failure Codes]
WHERE
[Serial Number] = @SerialNumber
GROUP BY
[Serial Number],
[Failure Code]
ORDER BY
[Counter] ASC
And you can retrieve it easily from C# without any loops.
Don't forget to rate answer, that helped you. It will allow other people find their answers faster.
|
|
|
|
|
My treeview is populated by an xml file. When I go to add a node, I'm actually altering the xml file (that's what I want). To refresh the treeview, I tried
treeSidebar.Refresh(); when that didn't work, I did
treeSidebar.Nodes.Remove(treeSidebar.Nodes[0]);
doc.Load("Subscription.xml");
treeSidebar.Nodes[0].Nodes.Add(name);
I got an error that says the file I want is being used by another process. How can I close a file so I can open it with another process?
|
|
|
|
|
XmlDoc doc = new XmlDoc();
using(FileStream fileStream = new FileStream("your file")){
doc.Load(fileStream);
}
|
|
|
|
|
Member 8069795 wrote: file I want is being used by another process
There's not much you can do about that.
Member 8069795 wrote: doc.Load("Subscription.xml");
Will close the file, so that shouldn't be the problem.
Just what is it you're trying to do? What creates the file? How many clients are trying to work with it?
|
|
|
|
|
It's an xml file that already exists, I load it once when the form loads, then when I refresh a treeview that it populates, that's the second time I want to use it.
|
|
|
|
|
If it's not shared then I don't see how another process would have it open. Maybe a threading issue? Dunno.
|
|
|
|
|
Load, parse and close.. this is some of my vb code, but it's the same principal for C#
//Load and parse tree nodes
defXmlDoc.Load(xmlPath)
TreeView1.Nodes.Clear()
//Ignore root node and add level 1
For Each node As XmlNode In defXmlDoc.DocumentElement.ChildNodes
AddNode(node, TreeView1.Nodes)
AddNodeTree(node, TreeView1.Nodes.Item(TreeView1.Nodes.Count - 1))
Next
defXmlDoc = Nothing
Private Sub AddNode(ByVal xmlNode As XmlNode, ByVal NodeCol As TreeNodeCollection)
Dim id, label, desc As String
label = xmlNode.Name
desc = ""
id = "Unknown"
If Not xmlNode.Attributes("label") Is Nothing Then
label = xmlNode.Attributes.GetNamedItem("label").Value
End If
If Not xmlNode.Attributes("description") Is Nothing Then
desc = xmlNode.Attributes.GetNamedItem("description").Value
End If
If Not xmlNode.Attributes("id") Is Nothing Then
id = xmlNode.Attributes.GetNamedItem("id").Value
If id > maxId Then maxId = id
End If
NodeCol.Add(id, label)
NodeCol(id).Tag = desc
End Sub
Private Sub AddNodeTree(ByVal xmlNode As XmlNode, ByVal treeNode As TreeNode)
Dim i As Integer
Dim xNode As XmlNode
Dim tNode As TreeNode
Dim nodeList As XmlNodeList
If (xmlNode.HasChildNodes) Then
nodeList = xmlNode.ChildNodes
For i = 0 To nodeList.Count - 1
xNode = xmlNode.ChildNodes(i)
AddNode(xNode, treeNode.Nodes)
tNode = treeNode.Nodes(i)
AddNodeTree(xNode, tNode)
Next
Else
End If
End Sub
|
|
|
|
|
I have to read an excel file and ensure the columns are in their correct order (the file is created manually and sometimes, columns are inserted in the incorrect order). I'm reading the file via microsoft jet oledb 4.0. I then insert the values into a DbDataReader.
I now have to check to see if each column is in it's propper position. So, as of now, I have the following to start with (The data reader field names are based on the column header names of the excel file):
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
String col1 = dr["OrderId"];
}
}
But my question is, how do I ensure dr["OrderId"] is the 1st field within the dbDataReader?
Thanks
|
|
|
|
|
You can try the other overload[^] of the DbDataReader's Item[] property:
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
String col1 = dr[1];
}
}
|
|
|
|
|
dr.GetOrdinal("fieldName")
|
|
|
|
|
There should never be any reason to insist that the columns are in a particular order. You're doing something wrong.
|
|
|
|
|
+5, since the order of the columns in a record could be coming from the server in a random order. And life is soo much easier if you just decide the order in the Presentation Layer - preferably having the user choose one.
OTOH, this is an Excel-sheet, and it might come without headers. In that case, only the position of the column would indicate what cell (dbfield) you're actually editing.
Things become harder if the user is allowed to move those things around. In an ideal world, each row would have a "caption" on top of the file.
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: it might come without headers
Then you're hosed.
|
|
|
|
|
if you need help i develop program with visual c and visual basic my e mail is albertodiprima@hotmail.com
|
|
|
|
|
... and you have been a member of CodeProject long enough to know that the forums are not the appropriate place for free advertising.
The best things in life are not things.
|
|
|
|
|
i write and study code i'm not playing
|
|
|
|
|
Why you want to get clients here in CodeProject?! In your place where you live there people don't use computers?
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
I am planning a memory game (as an exercise for myself) and thought of loading pictures, probably gifs or pngs into PictureBox controls like Microsoft does in this tutorial.
http://msdn.microsoft.com/en-us/library/dd492173.aspx
I would like to have the opinion of more experienced members in this forum, and mostly an answer to the following. During the game, two pieces will be revealed to the player for X seconds and then, in case he/she fails, they will have to be covered again. What does this mean in programming terms? I think that loading a picture and then loading a blank one would be a solution but probably a bad one, provoking unnecessary traffic. I have no better idea for the time being What do you think?
Thanks in advance.
PS: In those tutorials the last one is a Memory game, but with fonts.
http://msdn.microsoft.com/en-us/library/dd553233.aspx
What I am trying to do is to combine the techniques of those two tuts.
|
|
|
|
|
Sounds like a good enough solution to me. You could either set the visibility of the picture box control to show/hide it, or replace the image with a "blank" one you already have - it all depends on the aesthetic you're going for.
I would suggest pre-loading all the images (including the blank one) at startup and keeping them in memory (remembering to free them on close, GC may handle this for you, can't quite remember with images...). If you have a reasonable number of images, then this should be fine. Remember you'll only need one in-memory copy of the blank one although it may be used for all tiles.
Another solution would be simply to draw something over the top of the images in order to hide them, for example a black rectangle or another image. This may be another solution worth looking into.
Seems like you're on the right track though. Good luck!
Typical n-tiered architecture:
DB <-> Junk(0) <-> ... <-> Junk(n-1) <-> Pretty
modified on Wednesday, July 27, 2011 6:55 AM
|
|
|
|
|
Thanks for your reply.
I'm not quite sure, that I know how to do the following.
GlobX wrote: pre-loading all the images (including the blank one) at startup and keeping them in memory
GlobX wrote: you'll only need one in-memory copy
Could you give me any hint please, on what you refer to, please?
Thank you.
|
|
|
|
|
nstk wrote: I'm not quite sure, that I know how to do the following.
GlobX wrote: pre-loading all the images (including the blank one) at startup and keeping them in memory
I would suggest creating a class that contains a List of bitmaps.
St the bitmpas to your images on startup.
You can then point the pictureboxes on the form to the appropriate bitmap when needed.
This way the images are kept in memory within the List.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
In Oracle you can fire events via a trigger eg on insert and catch that event in your application.
I searched a little bit on this and found that the ODP.Net provider allows this. However, it looks a little complicated and before investing time into researching this route I was wondering if:
-> Someone has experience in this (catching events from the DB)? Any advice?
-> This can can be done with the OleDb provider as well, which is the provider we use today?
Many thanks.
V.
|
|
|
|
|
I've never done it, but I can tell you that OleDb will not work for this. It doesn't support database notifications.
Notifications from the database have to use the native provider for the database engine.
|
|
|
|
|
mmm Ok thanks. Good to know.
V.
|
|
|
|