Solved

E-mail SQL

Posted on 2011-09-07
12
233 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:pai_01
  • 6
  • 5
12 Comments
 
LVL 25

Expert Comment

by:Rouchie
Comment Utility
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
 

Author Comment

by:pai_01
Comment Utility
do you think you can write me out a sample code, you have me a little confused right now.
0
 
LVL 25

Expert Comment

by:Rouchie
Comment Utility
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
 

Author Comment

by:pai_01
Comment Utility
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
 
LVL 25

Expert Comment

by:Rouchie
Comment Utility
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
 

Author Comment

by:pai_01
Comment Utility
only the name shows in the cblist. the e-mail is in the database in a separate field.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Expert Comment

by:Manoj Patil
Comment Utility
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
 

Author Comment

by:pai_01
Comment Utility
Sorry to be a pain but can you re write that for vb.net instead and C#?
0
 
LVL 25

Expert Comment

by:Rouchie
Comment Utility
Is this part of a previous question?
0
 

Author Comment

by:pai_01
Comment Utility
yes
0
 
LVL 25

Expert Comment

by:Rouchie
Comment Utility
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
 
LVL 25

Accepted Solution

by:
Rouchie earned 500 total points
Comment Utility
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

Featured Post

Do email signature updates give you a headache?

Constantly trying to correctly format email signatures? Spending all of your time at every user’s desk to make updates? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today!

Join & Write a Comment

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
In this video we show how to create a Distribution Group in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >>…
In this video we show how to create a mailbox database in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Servers >> Data…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now