Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • 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 RadCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 RadCommented:
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 RadCommented:
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 RadCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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