Question Find Logic for reading SQL data

sifar786

Member
Joined
Feb 14, 2010
Messages
5
Programming Experience
3-5
Hi,

I am trying to find a logic for reading sql query data and displaying it properly.

i have 2 sql tables Modul & Relation (see attachments) which i am using to create a GDL file format (see Old and New GDL file attachments).

I am trying to extract data from these tables based on the Logic that,

1] for each "von" column element in Relation table, search for row containing "von" element in Modul table and see to which "group" it belongs and place the corresponding nodes and edges under that group.

The nodes and their corresponding edges will be understood from the GDL files which i have attached.

If any group element is empty, then place the nodes and edges in the default group above as shown in the New GDL file example.

Right now the code which i have written in VB.Net does not produce the desired format.

Its coming as :
(for empty group){
nodes
edges
nodes
edges

(for G1 group)
group{
nodes
edges
}

group{
nodes
edges
}

group{
nodes
edges
}

(for G2 group)
group{
nodes
edges
}
group{
nodes
edges
}
group{
nodes
edges
}

}

.........

Whereas it should be like this: (can also see attached New GDL file)

(for empty group)
Default Group {
nodes
edges
nodes
edges

(for G1 group)
group{
nodes
nodes
nodes
edges
edges
edges
}
(for G2 group)
group{
nodes
nodes
nodes
edges
edges
edges
}

}
.........

VB.NET:
Imports System.Data.SqlClient
Public Class Form1
    Dim myConnection As New SqlConnection
    Dim myCommand As SqlCommand
    Dim dr As SqlDataReader
    Dim connStr As String

    'declaring the objects
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim GDL As String
        Dim Risiko As Integer
        Dim Arr As New ArrayList()
        Dim Counter As Integer



        GDL = "graph: {title: ""Student""" & vbCrLf & " node.color : lightcyan " & vbCrLf & _
                " node.textcolor : darkblue " & vbCrLf & " edge.color : darkblue " & vbCrLf & " outport_sharing: yes  " & vbCrLf & _
                " splines: yes " & vbCrLf & " manhattan_edges: yes " & vbCrLf & " display_edge_labels: yes " & _
                vbCrLf & " node.fontname   : ""helvB10"" " & vbCrLf & " edge.fontname   : ""helvB10"" " & _
                vbCrLf & " layoutalgorithm: dfs " & vbCrLf & " crossing_optimization: yes " & _
                vbCrLf & " orientation:lefttoright "

        connStr = "Data Source=SIFAR;Initial Catalog=test;Integrated Security=True"
        myConnection = New SqlConnection(connStr)

        Try

            myConnection.Open()

            'get groups
            myCommand = New SqlCommand("SELECT DISTINCT [group] FROM modul ORDER BY [group]", myConnection)

            dr = myCommand.ExecuteReader()

            While dr.Read()

                Arr.Add(Trim(dr.Item("group").ToString))

            End While

            dr.Close()



            Dim i As Integer

            'loop thru each group, blank or non-blank
            For i = 0 To Arr.Count - 1

                Counter = 0

                myCommand = New SqlCommand("SELECT COUNT(*) AS Cntr FROM modul AS m INNER JOIN relation AS r ON m.mod_name = r.von WHERE (m.[group] = '" & Arr.Item(i).ToString & "')", myConnection)
                dr = myCommand.ExecuteReader()
                Do
                    While dr.Read()

                        Counter = Counter + 1

                    End While
                Loop While dr.NextResult

                MsgBox(Counter)

                dr.Close()


                myCommand = New SqlCommand("SELECT DISTINCT m.mod_name, m.eigenrisiko, m.TMP_Eingangsrisiko, r.relation_name, r.von, r.nach, r.tmp_risiko AS risiko FROM modul AS m INNER JOIN relation AS r ON m.mod_name = r.von WHERE (m.[group] = '" & Arr.Item(i).ToString & "') ORDER BY m.mod_name", myConnection)

                dr = myCommand.ExecuteReader()

                Do
                    While dr.Read()

                        If Arr.Item(i).ToString = "" Then

                            Risiko = CInt(Trim(dr.Item("eigenrisiko").ToString))

                            'for Nodes
                            If Risiko <= 100 Then

                                GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: green} "

                            ElseIf (Risiko > 101) And (Risiko <= 300) Then

                                GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: yellow} "

                            ElseIf (Risiko > 300) Then

                                GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: red} "

                            End If


                            'for Edges
                            If Risiko <= 100 Then

                                GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "

                            ElseIf (Risiko > 101) And (Risiko <= 300) Then

                                GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "

                            ElseIf (Risiko > 300) Then

                                GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "

                            End If


                        ElseIf Arr.Item(i).ToString <> "" Then

                            GDL &= vbCrLf & "graph: {status: clustered" & vbCrLf & "title: """ & Arr.Item(i).ToString & """" & vbCrLf & "textcolor: black" & vbCrLf & "status: folded" & vbCrLf & "width:70" & vbCrLf & "bordercolor: darkgreen" & vbCrLf & "fontname: ""timB"""

                            Risiko = CInt(Trim(dr.Item("eigenrisiko").ToString))

                            'for Nodes
                            If Risiko <= 100 Then

                                GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: green} "

                            ElseIf (Risiko > 101) And (Risiko <= 300) Then

                                GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: yellow} "

                            ElseIf (Risiko > 300) Then

                                GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: red} "

                            End If


                            'for Edges
                            If Risiko <= 100 Then

                                GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "

                            ElseIf (Risiko > 101) And (Risiko <= 300) Then

                                GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "

                            ElseIf (Risiko > 300) Then

                                GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "

                            End If

                        End If

                    End While

                Loop While dr.NextResult

                GDL &= vbCrLf & "}"

                dr.Close()

            Next i


            myConnection.Close()

            MsgBox(GDL.ToString())

        Catch ex As Exception

        End Try

    End Sub

End Class

Any help would be most appreciated.

Regards
 

Attachments

  • Need_GDLfile_gdl.zip
    8.4 KB · Views: 16
I'm struggling to undertstand what a node and an edge is and your tables don't help, nor contain much data to allow me to guess. They certainly dont contain enough data (by the looks of things) to give the GDL file you specified

I did this query:
VB.NET:
select
  *
from
  relation r
  inner join
  modul m
  on r.von = m.mod_name
order by
  grp nulls first, relation_name
Then I would loop over the results:
VB.NET:
Dim gdlsb As New StringBuilder
for i as Integer = 0 to datatable.Rows.Count
  Dim ro as DataRow = datatable.Rows(i)

  If i = 0 Then
    gdlsb.AppendLine("Default Group {")
  Else 
    'has the group name changed between last row and this row?
    if ro("Group") <> datatable.Rows(i-1)("Group") Then gdlsb.AppendLine("}").AppendFormat("Group {0}{{", ro("Group"))
  End if

  gdlsb.AppendLine( 'whatever data is a NODE or an EDGE???)

Next i

And obviuiously you put the ORDER BY on your query so that it sorts the null groups first.. and also that it sorts NODES before EDGES
 
OleDbCommand.ExecuteReader.Read() Problem

Hi,

With reference to the earlier project, (i have attached the files), i was able to generate the GDL file as i wanted (Form1.txt contains the original code which i initially used it in Form_Load() event)....

But now when i implement this (All_Module) procedure which generates the full GDL file in a Form by CALLING it, an exception is raised at the 3rd dr.Read() in the For loop (For j=0 to Counter-1) ...

VB.NET:
'for NODES , for blank group
                For j = 0 To Counter - 1

                    myCommand = New OleDbCommand("SELECT DISTINCT m.mod_name, m.eigenrisiko, m.TMP_Eingangsrisiko FROM modul 

AS m left outer JOIN relation AS r ON m.mod_name = r.von WHERE (m.[group] = '" & Trim(Arr.Item(i).ToString) & "') OR 

(m.[group] IS NULL) ORDER BY m.mod_name", myConnection)

                    dr = myCommand.ExecuteReader()

                    [B]While dr.Read()[/B]

                        If Arr.Item(i).ToString = "" Then

                            [B]Risiko = CInt(Trim(dr.Item("eigenrisiko").ToString))[/B]
....


This is what i see in the WATCH window:
VB.NET:
dr.Read()                               Because this evaluation could cause side effects, it will not be executed until enabled by user.
dr.Item("eigenrisiko").ToString	   Because this evaluation could cause side effects, it will not be executed until enabled by user.

and this in the Locals Window:
VB.NET:
+		Me	{WindowsApplication1._1.Form1}	WindowsApplication1._1.Form1
+		Arr	Count = 8	System.Collections.ArrayList
		Counter	14	Integer
+		e	{X = 33 Y = 12 Button = Left {1048576}}	[B]System.EventArgs
-		ex	{"Conversion from string "" to type 'Integer' is not valid."}	[/B]System.Exception
+		System.InvalidCastException	{"Conversion from string "" to type 'Integer' is not valid."}	System.InvalidCastException
+		Data	{System.Collections.ListDictionaryInternal}	System.Collections.IDictionary
		HelpLink	Nothing	String
+		[B]InnerException	{"Input string was not in a correct format."}	[/B]
System.Exception
		Message	"Conversion from string "" to type 'Integer' is not valid."	String
		Source	"Microsoft.VisualBasic"	String
		StackTrace	"   at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)    at WindowsApplication1._1.Form1.All_Module(Object sender, EventArgs e) in C:\Documents and Settings\sifar786\Desktop\WindowsApplication1.1\WindowsApplication1.1\Form1.vb:line 185"	String
+		TargetSite	{System.Reflection.RuntimeMethodInfo}	System.Reflection.MethodBase
		GDL	"graph: {title: "Student"  node.color : lightcyan   node.textcolor : darkblue   edge.color : darkblue   outport_sharing: yes    splines: yes   manhattan_edges: yes   display_edge_labels: yes   node.fontname   : "helvB10"   edge.fontname   : "helvB10"   layoutalgorithm: dfs   crossing_optimization: yes   orientation:lefttoright "	String
		Risiko	0	Integer
		SaveGDLDialog	Nothing	System.Windows.Forms.SaveFileDialog
+		sender	{Text = "DONE"}	Object


I dont think that its a "Invalid Cast Exception" but something to do with dr.Read() not refreshing, because in Debug mode, when i click on the Refresh icon in the Watch Window, i see dr.Read() becomes True and i get the correct value in the "Cint(Trim(dr.Item("eigenrisiko").ToString))"

Now to do this i have to go into Debug mode, put Breakpoints on the above code and then Step Thru code and click on Refresh icon in Watch window...inorder to generate the GDL file completely...

This is happening only in this particular dr.Read() and once it comes True, the rest of the code executes perfectly generating GDL file...

So how do i refresh the dr.Read() automatically and what could be the problem here??? i am sure its nothing to do with the FormatException bcos the values are there and when i click on Refresh icon, dr.Read() becomes True and Cint(Trim(dr.Item("eigenrisiko").ToString)) gets values 400...

Why is dr.Read() malfunctioning???

P.S: The generated GDL file looks like as shown attached...

Please advice a solution as i cant write or think of code for "Section" on form1 if "All" is not working properly (this radiobutton on form1 is used to generate the whole GDL file showing all the groups, nodes and edges) ....

Section radiobutton will limit the nodes & their edges based on GROUP, Level and using VON and NACH...
German:
-------
"von"= FROM
"nach"=TO
 

Attachments

  • Form1.txt
    11.9 KB · Views: 16
  • 20100303_NRA.zip
    951 bytes · Views: 16
  • WindowsApplication1.1.zip
    80.7 KB · Views: 13
Last edited:
Back
Top