asked on

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

Reza Rad

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

amar31282

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

amar31282

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

Cheers....
Regards,
Amarjit Singh Dhunna

westdh

ASKER

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

westdh

ASKER

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

westdh

ASKER

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

Reza Rad

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?

amar31282

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

ASKER CERTIFIED SOLUTION

Reza Rad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

westdh

ASKER

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

westdh

ASKER

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

Reza Rad

I think it's better to explain what you want exactly, your code has lot's of error.
so describe what you want exactly?

amar31282

have you tried my code?

westdh

ASKER

Thanks I am going back to my old way, even though it not working right yet

amar31282

strange i the solution was initiated and provided correctly by me.. ):