• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Getting the data from a comma delimited string using SQL Query

I need to loop thru and query the database to optain the users email address...
this function obtains the delimited string (csv)
I need help on the code structure...
-----------------------------------------------------------
     Shared sb As New StringBuilder
     Protected Sub grdAttendees_ItemDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
         
         If e.Item.ItemType = ListItemType.Item Then
             If Not sb.ToString().Contains(e.Item.Cells(0).Text) Then
                 sb.Append(e.Item.Cells(0).Text)
                 sb.Append(",")
             End If
         End If

         Dim strKeywordID As String = sb.ToString()
         Session("sbEmailAddressID") = strKeywordID
 End Sub
------------------------------------------------------------
the 'strKeywordID' string is like: 154,154,155,156,
I pass this to another file using -> Session("sbEmailAddressID")
-----------------------------------------------------------------------------------
In this file I need to know how to parse session var sb
 Dim sb As String = Session("sbEmailAddress")

Parse or split sb(",") ... and create a for loop based on the count of 'ID' .... the name of items in the string ... and then loop thru and query the sql/server database
For loop?
SELECT strBadgeName FROM  i2Integration_EventRegv45_RegistrationUser where intRegistrationUserID = @ID
next
---------------------------------------------------------------------------------
0
westdh
Asked:
westdh
  • 6
  • 5
  • 4
1 Solution
 
Reza RadConsultant, TrainerCommented:
what you want to do exactly?
do you want to split a string by comma, if yes use string.Split(",") this will return array of string
if you want anything else explain more, I have no idea what you want to do already
0
 
amar31282Commented:
hi westdh,

Hmm not very much sure what you want...

perhaps you need this
int length = Convert.ToString(Session["sbEmailAddress"]).split(',').Length;
string[] ids = new string[length];

for(int i=0;i<length;i++)
{
// Use the query operations here to get the values u want
based on the             ids[i]  you can loop through the db.
}

I hope this helps..

Cheers....
Regards,
Amarjit Singh Dhunna





0
 
amar31282Commented:


Also @reza_rad Split(",") should be Split(',')   :-)


Cheers....
Regards,
Amarjit Singh Dhunna


0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
westdhAuthor Commented:
How about this... also I have a problem with converting the C# to vb on the for a length statement. But does this help explain what I am trying to do
-------------------------------------------------------------------------------------------------------------------
  Dim strEmailAddress As String
            Dim dtVolumeOrder As New DataTable()
            Dim length As Integer = Convert.ToString(Session("sbEmailAddress")).Split(","c).Length
            Dim ids As String() = New String(length - 1) {}
            For i As Integer = 1 To 10; i<length; i++)'loop through the db gettin email address

                Dim strSelectCommand As String = "  SELECT strBadgeName[i] FROM  i2Integration_EventRegv45_RegistrationUser where intRegistrationUserID = ids[i]"
                Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
                    Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                        adapPatientBills.Fill(dtVolumeOrder)
                    End Using
                End Using

                strEmailAddress = strEmailAddress + strSelectCommand + ","
                ' build string of email addresses seperated by ','
            Next i
0
 
westdhAuthor Commented:
here i have correct some convert C# to vb error: check this out

 Dim strEmailAddress As String = ""
            Dim dtVolumeOrder As New DataTable()
            Dim length As Integer = Convert.ToString(Session("sbEmailAddress")).Split(","c).Length
            Dim ids As String() = New String(length - 1) {}
            For i As Integer = 0 To length - 1

                Dim strSelectCommand As String = "  SELECT strBadgeName[i] FROM  i2Integration_EventRegv45_RegistrationUser where intRegistrationUserID = ids[i]"
                Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
                    Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                        adapPatientBills.Fill(dtVolumeOrder)
                    End Using
                End Using

                strEmailAddress = strEmailAddress + strSelectCommand + "," ' build string of email addresses seperated by ','
            Next
0
 
westdhAuthor Commented:


 got this error  A critical error has occurred. Incorrect syntax near 'i'.

Dim strEmailAddress As String = ""
            Dim dtVolumeOrder As New DataTable()
            Dim length As Integer = Convert.ToString(Session("sbEmailAddress")).Split(","c).Length
            Dim ids As String() = New String(length - 1) {}
            For i As Integer = 0 To length - 1

                Dim strSelectCommand As String = "  SELECT strBadgeName[i] FROM  i2Integration_EventRegv45_RegistrationUser where intRegistrationUserID = " + ids[i]
                Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
                    Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                        adapPatientBills.Fill(dtVolumeOrder)
                    End Using
                End Using

                strEmailAddress = strEmailAddress + strSelectCommand + "," ' build string of email addresses seperated by ','
            Next
0
 
Reza RadConsultant, TrainerCommented:
change this line:
Dim length As Integer = Convert.ToString(Session("sbEmailAddress")).Split(","c).Length
to
Dim length As Integer = Convert.ToString(Session("sbEmailAddress")).Split(",").Length


and say what error do you receive?
0
 
amar31282Commented:
well i m not expert of VB but still tried bit

hope it helps

 Dim strEmailAddress As String = ""
        Dim dtVolumeOrder As New DataTable()
        Dim length As Integer = Convert.ToString("amarjit,singh,dhunna").Split(",").Length
        Dim ids As String()
        Dim strSelectCommand As String
        ids = Convert.ToString("amarjit,singh.dhunna").Split(",")
        For i As Integer = 0 To length - 1
            strSelectCommand = "SELECT strBadgeName FROM  i2Integration_EventRegv45_RegistrationUser where intRegistrationUserID = " & ids(i)
            'Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            'Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
            'adapPatientBills.Fill(dtVolumeOrder)
            ' End Using
            ' End Using

            'strEmailAddress = strEmailAddress + strSelectCommand + "," ' build string of email addresses seperated by ','
        Next

Open in new window

0
 
Reza RadConsultant, TrainerCommented:
try this

Dim strEmailAddress As String = ""
            Dim dtVolumeOrder As New DataTable()
            Dim length As Integer = Convert.ToString(Session("sbEmailAddress")).Split(",").Length
            Dim ids As String() = New String(length - 1) {}
            For i As Integer = 0 To length - 1

                Dim strSelectCommand As String = "  SELECT strBadgeName(i) FROM  i2Integration_EventRegv45_RegistrationUser where intRegistrationUserID = " + ids[i]
                Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
                    Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                        adapPatientBills.Fill(dtVolumeOrder)
                    End Using
                End Using

                strEmailAddress = strEmailAddress + strSelectCommand + "," ' build string of email addresses seperated by ','
            Next

Open in new window

0
 
westdhAuthor Commented:
     A critical error has occurred. Incorrect syntax near 'i'. is thier a problem with the way I have ids[i] inserted inthe select string.

SELECT strBadgeName[i] FROM  i2Integration_EventRegv45_RegistrationUser where intRegistrationUserID = ids[i]"

also I find I am still picking up only one I ID in my previous rountine
when I submit the email address xxxxxx@charter.net, yyyyyyyyy@charter.net
but some times it works??? especially if I go away from my Pc for sometime...

   If e.Item.ItemType = ListItemType.Item Then
             If Not sb.ToString().Contains(e.Item.Cells(0).Text) Then
                 sb.Append(e.Item.Cells(0).Text)
                 sb.Append(",")
             End If
         End If
0
 
westdhAuthor Commented:
current script

 Dim strEmailAddress As String = ""
            Dim dtVolumeOrder As New DataTable()
            Dim length As Integer = Convert.ToString(Session("sbEmailAddress")).Split(",").Length
            Dim ids As String() = New String(length - 1) {}
            For i As Integer = 0 To length - 1

                Dim strSelectCommand As String = "SELECT strBadgeName[i] FROM  i2Integration_EventRegv45_RegistrationUser where intRegistrationUserID = ids[i]"
                Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
                    Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                        adapPatientBills.Fill(dtVolumeOrder)
                    End Using
                End Using

                strEmailAddress = strEmailAddress + strSelectCommand + "," ' build string of email addresses seperated by ','
            Next
0
 
Reza RadConsultant, TrainerCommented:
I think it's better to explain what you want exactly, your code has lot's of error.
so describe what you want exactly?
0
 
amar31282Commented:
have you tried my code?
0
 
westdhAuthor Commented:
Thanks I am going back to my old way, even though it not working right yet
0
 
amar31282Commented:
strange i the solution was initiated and provided correctly by me..  ):
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now