0 Replies - 527 Views - Last Post: 28 January 2013 - 11:44 PM Rate Topic: -----

#1 gaja23.sg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 11-January 13

retreiving data from database inside a loop

Posted 28 January 2013 - 11:44 PM

the below function is used to retrieve priority value according to the corresponding input strings..
but the priority value that is displayed is the the same as the value obtained for the first input string..
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim str As String
        Dim inputstr() As String
        Dim strarr() As Char
        Dim c As Integer
        str = TextBox1.Text
        inputstr = str.Split(",")

        Dim con As New OleDbConnection
        Dim cmd As New OleDbCommand
        Dim da As New OleDbDataAdapter
        Dim da1 As New OleDbDataAdapter
        Dim da2 As New OleDbDataAdapter
        Dim da3 As New OleDbDataAdapter
        Dim ds As DataSet
        Dim ds1 As DataSet
        Dim ds2 As DataSet
        Dim ds3 As DataSet
        da = New OleDbDataAdapter
        da1 = New OleDbDataAdapter
        da2 = New OleDbDataAdapter
        da3 = New OleDbDataAdapter
        cmd = New OleDbCommand()
        con = New OleDbConnection

        ds = New DataSet()
        ds1 = New DataSet()
        ds2 = New DataSet()
        ds3 = New DataSet()
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Govin\Documents\database3.mdb"
        con.Open()

        Dim sql1 As String = "SELECT ScenarioNo FROM ScenarioNumber WHERE ID=1"
        da = New OleDbDataAdapter(sql1, con)
        ds.DataSetName = "NewDataSet"
        da.Fill(ds, "database3")
        Dim sNo As Integer = ds.Tables("database3").Rows(0).Item(0)
        Dim sNo1 As Integer = sNo + 1

        Dim objCmd As New OleDbCommand
        Dim SQL2 As String = "UPDATE ScenarioNumber SET ScenarioNo=" & sNo1 & " WHERE ID=1;"
        cmd = New OleDbCommand(SQL2, con)
        objCmd = New OleDbCommand(SQL2, con)
        objCmd.ExecuteNonQuery()

        Dim xmlName As String = "xmlFile" & sNo & ".xml"
        Dim writer As New XmlTextWriter(xmlName, System.Text.Encoding.UTF8)
        writer.WriteStartDocument(True)
        writer.Formatting = Formatting.Indented
        writer.Indentation = 2
        writer.WriteStartElement("Scenario")

        Dim sLen = inputstr.Length
        Dim pr As Integer() = New Integer(inputstr.GetUpperBound(0)) {}
        Dim ap As Integer() = New Integer(inputstr.GetUpperBound(0)) {}
        Dim lp As Integer() = New Integer(inputstr.GetUpperBound(0)) {}
        Dim dp As Integer() = New Integer(inputstr.GetUpperBound(0)) {}

        For c = 0 To sLen - 1
            strarr = inputstr(c).ToCharArray
            'MsgBox("s" & c & " : " & strarr)
            Dim len As Integer = strarr.Length
            Dim i As Integer
            Dim j As Integer
            Dim y As Integer = 0
            Dim n As Integer
            Dim count As Integer = 0
            Dim count1 As Integer = 0
            Dim count2 As Integer = 0
            Dim count3 As Integer = 0
            Dim count4 As Integer = 0
            Dim usern As String
            Dim action As String
            Dim Location As String
            Dim time As String

            For i = 0 To len - 1
                If strarr(i) = "\" Then
                    For j = i + 1 To len - 1
                        If strarr(j) <> "/" Then
                            count += 1
                        Else
                            Exit For
                        End If
                    Next
                    Exit For
                    n = count + 2
                End If
            Next
            usern = Mid(strarr, i + 2, count)

            For i = n + 1 To len - 1
                If strarr(i) = " " Then
                    For j = i + 1 To len - 1
                        If strarr(j) <> " " Then
                            count1 += 1
                        Else
                            Exit For
                        End If
                    Next
                    Exit For
                End If
            Next
            action = Mid(strarr, i + 2, count1)

            For i = 0 To len - 1
                If strarr(i) = "(" Then
                    For j = i + 1 To len - 1
                        If strarr(j) <> ")" Then
                            count2 += 1
                        Else
                            Exit For
                        End If
                    Next
                    Exit For
                End If
            Next
            Location = Mid(strarr, i + 2, count2)

            For i = 0 To len - 1
                If strarr(i) = "<" Then
                    For j = i + 1 To len - 1
                        If strarr(j) <> ">" Then
                            count3 += 1
                        Else
                            Exit For
                        End If
                    Next
                    Exit For
                End If
            Next
            time = Mid(strarr, i + 2, count3)
            Dim p As Integer = i + count3 + 3

            For i = p To len - 1
                If strarr(i) = " " Then
                    For j = i + 1 To len - 1
                        If strarr(j) <> " " Then
                            count4 += 1
                        Else
                            Exit For
                        End If
                    Next
                    Exit For
                End If
            Next
            Dim status As String = Mid(strarr, i + 2, count4)

            Dim q As Integer = i + count4 + 2
            Dim r As Integer = len - 1
            Dim device As String = Mid(strarr, q, r)
            MsgBox(action)
            'MsgBox(Location)
            'MsgBox(device)

            [b]Dim sql3 As String = "SELECT DefaultPriority FROM HomeAction WHERE Action='" & action & "';"
            'MsgBox("A" & c)
            da1 = New OleDbDataAdapter(sql3, con)
            ds1.DataSetName = "NewDataSet"
            da1.Fill(ds1, "database3")
            Dim dr As OleDbDataReader = da1.SelectCommand.ExecuteReader()
            If (dr.Read()) Then
                ap(c) = ds1.Tables("database3").Rows(0).Item(0)
                MsgBox(ap(c))
            End If
            dr.Close()

            Dim sql4 As String = "SELECT DefaultPriority FROM HomeLocation WHERE Location='" & Location & "';"
            'MsgBox("L" & c)
            da2 = New OleDbDataAdapter(sql4, con)
            ds2.DataSetName = "NewDataSet"
            da2.Fill(ds2, "database3")
            dr = da2.SelectCommand.ExecuteReader()
            If (dr.Read()) Then
                lp(c) = ds2.Tables("database3").Rows(0).Item(0)
                MsgBox(lp(c))
            End If
            dr.Close()

            Dim sql5 As String = "SELECT DefaultPriority FROM HomeDevice WHERE Device='" & device & "';"
            'MsgBox("D" & c)
            da3 = New OleDbDataAdapter(sql5, con)
            ds3.DataSetName = "NewDataSet"
            da3.Fill(ds3, "database3")
            dr = da3.SelectCommand.ExecuteReader()
            If (dr.Read()) Then
                dp(c) = ds3.Tables("database3").Rows(0).Item(0)
                MsgBox(dp(c))
            End If
            dr.Close()[/b]

            pr(c) = ap(c) + lp(c) + dp(c)

    
            writer.WriteStartElement("Scenario" & c + 1)
            writer.WriteStartElement("User")
            writer.WriteString(usern)
            writer.WriteEndElement()
            writer.WriteStartElement("Action")
            writer.WriteString(action)
            writer.WriteEndElement()
            writer.WriteStartElement("Location")
            writer.WriteString(Location)
            writer.WriteEndElement()
            writer.WriteStartElement("Time")
            writer.WriteString(time)
            writer.WriteEndElement()
            writer.WriteStartElement("Device")
            writer.WriteString(device)
            writer.WriteEndElement()
            writer.WriteStartElement("Status")
            writer.WriteString(status)
            writer.WriteEndElement()
            writer.WriteEndElement()
        Next

        For n As Integer = 0 To sLen - 1
            For m As Integer = 0 To sLen - 1
                If (pr(n) > pr(m)) Then
                    Dim t As Integer = pr(m)
                    pr(m) = pr(n)
                    pr(n) = t
                End If
            Next
        Next
        For i As Integer = 0 To sLen - 1
            MsgBox(pr(i))
        Next
                
        writer.WriteEndElement()
        writer.WriteEndDocument()
        writer.Close()

        con.Close()
        MessageBox.Show("xml File has been successfully created")
        TextBox2.Text = "xmlFile"
        Dim frm As New viewXml()
        frm.Show()
        Me.Hide()
    End Sub

This post has been edited by modi123_1: 29 January 2013 - 07:56 AM
Reason for edit:: highlight the text and just click the 'code' button in the text editor


Is This A Good Question/Topic? 0
  • +

Page 1 of 1