We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

how to split the record from db

Medium Priority
181 Views
Last Modified: 2013-11-07
i need to split the records from db and show it on the page in the calendar_selectionchanged event ,
if the subjects columns contains records like this way ,

For EG
2#10#103,3#11#110,4#12#120
this should be displayed like this , i mean seperating # and (kama),  (after kama it should be entered in next line  )
                                             
For 2 you get 10 of 103---------->2#10#103
for 3 you get 11 of 110----------->3#11#110
for 4 you get 12 of 120------------>4#12#120

my Table contains like this ,
name          subjects
James        2#10#103,3#11#110,4#12#120
Nelson        4#12#123,5#11#120,6#2#133
romeo          5#6#123,8#1#100

this loop manages to get the name from the db, but i find difficcult to split  the values and display as such ,which  i mentioned above ..
 While (myDataReader.Read())
               If (myDataReader.Item("real") = 1 And myDataReader.Item("illusion") = 1) Then
                temp &= "<br>name :" & myDataReader.Item("name") & ""
            ElseIf (myDataReader.Item("real") = 0 And myDataReader.Item("illusion") = 1) Then
                temp &= "<br>name :" & myDataReader.Item("name") & ""
            End If


Public Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim myDataReader As SqlDataReader
        myConnection = New SqlConnection(ConfigurationSettings.AppSettings("str"))
        myCommand = New SqlCommand("design", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
        Dim smalldt As New SqlParameter("@smalldt", SqlDbType.DateTime, 8)
        smalldt.Value = Calendar1.SelectedDate
        myCommand.Parameters.Add(smalldt)
        myConnection.Open()
        myDataReader = myCommand.ExecuteReader()
        Dim temp As String = ""
         While (myDataReader.Read())
               If (myDataReader.Item("real") = 1 And myDataReader.Item("illusion") = 1) Then
                temp &= "<br>name :" & myDataReader.Item("name") & ""
            ElseIf (myDataReader.Item("real") = 0 And myDataReader.Item("illusion") = 1) Then
                temp &= "<br>name :" & myDataReader.Item("name") & ""
            End If
        End While
        myDataReader.Close()
        myConnection.Close()
        Label1.Text = temp
    End Sub

Open in new window

Comment
Watch Question

Commented:
I'm not completly sure what you want.
Maybe you could use the Replace function.
temp.Replace(",", "<br/">) 'This will replace all , with a <br/> to ge a linebreak in HTML.
alt.
myDataReader.Item("name").ToString().Replace(",", "<br/">)

or use the split function
Dim tempArr() as String = temp.Split(",")
alt.
Dim tempArr() as String = myDataReader.Item("name").ToString().Split(",")
Rajar AhmedConsultant
CERTIFIED EXPERT

Author

Commented:
well, one of my field(subjects) in db stored the records in this format ,

12#23#200,5#4#200

this represents there are two characters between the integers one is # and other is ,(kama)

 i need to seperate these  two characters and display on the page .

rightnow the code  displays only this,
 name : michael

but i want to bring the subjects fields also in the below format

if michel has subject fields like this  2#10#103,3#11#110,4#12#120

then in the calendar _selectionchanged
OUTPUT
name:michael    
for 2 you get 10 of 103
 for 3 you get 11 of 110
for 4 you get 12 of 120

You can see this above output ,
 that # are replaced by the string (you get) and (of) are
,(kama) are replaced by the <br>
 
Rajar AhmedConsultant
CERTIFIED EXPERT

Author

Commented:
You can see this above output ,
 that # are replaced by the string (you get) and (of) and (not are as it was in previous comment)
,(kama) are replaced by the <br>
Commented:
Try this....
         While (myDataReader.Read())
               If (myDataReader.Item("real") = 1 And myDataReader.Item("illusion") = 1) Then
                temp &= "<br>name :" & myDataReader.Item("name") & "<br/>" & FormatSubject(myDataReader.Item("subject"))
            ElseIf (myDataReader.Item("real") = 0 And myDataReader.Item("illusion") = 1) Then
                temp &= "<br>name :" & myDataReader.Item("name") & "<br/>" & FormatSubject(myDataReader.Item("subject"))
            End If
        End While
 
    Private Function FormatSubject(ByVal subject As String) As String
        Dim text As String = ""
        Dim subjects() As String = subject.Split(",")
        For Each subj As String In subjects
            Dim vals() As String = subj.Split("#")
            If vals.Length > 2 Then
                text += "for " + vals(0).ToString() + " you get " + vals(1).ToString() + " of " + vals(2).ToString() + "<br/>"
            End If
        Next
        Return text
    End Function

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Rajar AhmedConsultant
CERTIFIED EXPERT

Author

Commented:
it worked , can u please explain the function  ,

Wats this do . i mean subjects()  --??
Dim subjects() As String = subject.Split(",") <---

Rajar AhmedConsultant
CERTIFIED EXPERT

Author

Commented:
Thanks mate :) .Dats a great function ..

Commented:
Dim subjects() As String  'Declares the variable subjects as an array of strings.
subject.Split(",") 'Splits the subject string into multiple strings at the places where "," occurs.

    Private Function FormatSubject(ByVal subject As String) As String
        Dim text As String = ""
        Dim subjects() As String = subject.Split(",")  'Splits the string up into an string array. The separator i ","
        For Each subj As String In subjects 'Loops through all parts of the string array
            Dim vals() As String = subj.Split("#") 'Splits the current part into a new string array
            If vals.Length > 2 Then 'Just a check so we won't get an error if the string we splitted didn't contain more than a value
                text += "for " + vals(0).ToString() + " you get " + vals(1).ToString() + " of " + vals(2).ToString() + "<br/>"
            End If
        Next
        Return text
    End Function


I hope this explains the function.
Rajar AhmedConsultant
CERTIFIED EXPERT

Author

Commented:
thanks again ..:)...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.