E-mail SQL

I am trying to send a mass e-mail to only selected people using a checkbox list linked to a SQL database. Is there a way to add the Db filed into the mail.cc field?
pai_01Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RouchieConnect With a Mentor Commented:
There are TONS of other approaches for converting comma separated strings to table values in SQL, just do a Google search before somebody says there's a better method...!
0
 
RouchieCommented:
I do this in my app using the following logic:

1. Use the database user ID field as the Value string in the checkbox
2. On page submission, iterate through the checkboxes and read in each ID into a CSV string,
e.g. "3,6,8,45,75,757,453,7745,234,565,"
3. Pass that back to SQL and select based on those user IDs, and return their name and email.  Return to ASP.NET as a datatable
4. Create your email in ASP.NET, then iterate the datatable, adding each person as a CC
0
 
pai_01Author Commented:
do you think you can write me out a sample code, you have me a little confused right now.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
RouchieCommented:
Okay how far have you got?  How currently are you displaying the checkboxes, and what data have you currently got access to on the page?  Are you displaying using a gridview, repeater, checkboxlist etc?
0
 
pai_01Author Commented:
right now i am using a checkbox list to display the names, i have all the code wrote for the e-mail to send out. when a user clicks a checkbox it auto populates a label
0
 
RouchieCommented:
Do you have the email addresses as part of the CheckBoxList, or are they still in the database.  Is the UserID for each person stored in the CheckBoxList?
0
 
pai_01Author Commented:
only the name shows in the cblist. the e-mail is in the database in a separate field.
0
 
Manoj PatilSr. Software EngineerCommented:
Hello pai_01
I had given below my code for mail send to multiple users. Use the following code with little bit changes and your problem is solved....!!!


protected void btnSend_Click(object sender, EventArgs e)
 {
         try
        {
            String connstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlConnection con = new SqlConnection(connstring.ToString());
            con.Open();             
            SqlCommand cmd;
            String query = "SELECT Count(Cust_EmailID) FROM Customer_Master WHERE (Cust_IsActive = 1) AND (Cust_Subscribed = 1)";
            cmd = new SqlCommand(query, con);
            Object o = cmd.ExecuteScalar();
            int cnt = int.Parse(o.ToString());
            
            String[] ids = new String[cnt];
            int i=0;
            int emailsent=0;
            
            query = "SELECT Cust_EmailID FROM Customer_Master WHERE (Cust_IsActive = 1) AND (Cust_Subscribed = 1)";
            cmd = new SqlCommand(query,con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ids[i] = dr[0].ToString();
            }
            dr.Close();
            con.Close();
            con.Dispose();
            String msgB=this.Request.Form.Get("elm1").ToString();
             

            for (i = 0; i < cnt; i++)
            {
                EmailSend em = new EmailSend(ids[i].ToString(), msgB);

                emailsent++;
            }
            emailmsg.Text="Mail Send to "+emailsent+"Peoples";
        }
        catch (Exception ex)
        {
            lblDisplay.Visible = true;
            lblDisplay.Text = ex.Message.ToString();          
        }
    }

Open in new window


In above code,
With first query I am trying to get the count of users which are active ( Here you write your code to get the selected users with CheckBox ) In next query I am getting Email Addresses which will be stored in an Array of count which got in privious query.
     After that in for loop, I am writing mail send code which will send mail one by one to user till count limit.  

 
0
 
pai_01Author Commented:
Sorry to be a pain but can you re write that for vb.net instead and C#?
0
 
RouchieCommented:
Is this part of a previous question?
0
 
pai_01Author Commented:
yes
0
 
RouchieCommented:
As you've not had a response yet, here's how to collect the ID's.

Dim sb as New StringBuilder
For Each li as ListItem in MyCheckListID.Items
   If li.Selected Then
       sb.Append(li.Value & ",")
   End If
Next

Open in new window


That will then give a list of ID values for the selected fields.  So you'll get 1,2,4,5,6,784,467,43, etc

Then in SQL you can use a trick like this to get the string into a table variable:

DECLARE @separator CHAR(1)
SET @separator = ","
DECLARE @tbl_Ids TABLE (ID INT)
DECLARE @separator_position INT
DECLARE @array_value nvarchar(10)
WHILE PATINDEX('%' + @separator + '%' , @Ids) <> 0
	BEGIN -- feed array of integers into tempTable
		SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @Ids)
		SELECT @array_value = LEFT(@Ids, @separator_position - 1)
		INSERT INTO @tbl_Ids (IdVal) VALUES (@array_value)
		SELECT @Ids = STUFF(@Ids, 1, @separator_position, '')
	END
SELECT * FROM tbl_Ids

Open in new window

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.

All Courses

From novice to tech pro — start learning today.