Add Multiple Records to one TextBox from SQL
-
Hi Richard, All of the code I am using is placed on this thread. When I execute the code, I see an email form with an email address in the To: box. This is one of the email addresses in my database. It works just fine. However, as I have said, I have more than one email address in the Database but only one shows. I can't see how me providing a screenshot of an email form is going to solve the issue. All I want to know is how to place more than one value from an SQL row into one textbox, preferably so an email message can be sent to more than one email address at a time. So, if you read my first post in this thread, it explains the issue in detail and provides all of the code I am using. Thanks.
Dan O'Riordan wrote:
So, if you read my first post in this thread, it explains the issue in detail.
Sorry, but it doesn't. Nowhere have you shown how you get the email address(es) from the dataset into the textbox, or how you expect to get more than one email address into the text box. It is still not clear whether the database records contain a single email address for each member record or multiple addresses. If you have a single email address for each member and you want to send the same message to every member, then you should be using a
DataReaderto extract the detail from your database. You can then read each record in order and add the email address direct to the SMTP message, without the need for aTextBox. -
Hi Richard, I did mate: Calls the Data from the DQL Database:
Dim con As SqlConnection = New SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=FigClubs;Integrated Security=True;Pooling=False") Dim cmd As SqlCommand = New SqlCommand("SELECT Email FROM Members", con) con.Open() Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd) Dim myDataTable As DataTable = New DataTable myDA.Fill(myDataTable) con.Close() con = NothingThis tells the data to display in the TextBox:
Try Dim Smtp\_Server As New SmtpClient Dim e\_mail As New MailMessage() Smtp\_Server.UseDefaultCredentials = False Smtp\_Server.Credentials = New Net.NetworkCredential(My.Settings.ClubEmail, My.Settings.EmailPassword) Smtp\_Server.Port = 587 Smtp\_Server.EnableSsl = True Smtp\_Server.Host = "smtp.gmail.com" e\_mail = New MailMessage() e\_mail.From = New MailAddress(TextBox4.Text) e\_mail.To.Add(EmailTextBox.Text) e\_mail.Subject = TextBox1.Text e\_mail.IsBodyHtml = False e\_mail.Body = TextBox2.Text Smtp\_Server.Send(e\_mail) MsgBox("Your email has been sent.") Catch error\_t As Exception MsgBox(error\_t.ToString) End TryIt obviously works because one of the emails in the Database is displayed in the TextBox in question and the email is sent without error to this address. The TextBox for the To: field is bound to the Datatable called Members. In SQL this data has a field called "Email, where I am getting the email addresses from and displaying them into the TextBox.
Me.MembersTableAdapter.Fill(Me.MembersDataSet.Members)
Can you tell me please how to loop it and separate them with Commas? I have searched everywhere for a solution and not getting anywhere. Thanks.
So you're using data-binding to move the data from the
DataTableto theTextBox? That's not going to work; aTextBoxwill only bind to a single record. You can either remove the binding and loop through the values in code:string[] addresses myDataTable.Rows.Cast<DataRow>().Select(row => (string)row[0]).ToArray();
EmailTextBox.Text = string.Join(", ", addresses);Or you can change the query to return a single row with the concatenated addresses in it, using one of these methods[^]:
SELECT Stuff((SELECT ', ' + Email FROM Members FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') As Email;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
So you're using data-binding to move the data from the
DataTableto theTextBox? That's not going to work; aTextBoxwill only bind to a single record. You can either remove the binding and loop through the values in code:string[] addresses myDataTable.Rows.Cast<DataRow>().Select(row => (string)row[0]).ToArray();
EmailTextBox.Text = string.Join(", ", addresses);Or you can change the query to return a single row with the concatenated addresses in it, using one of these methods[^]:
SELECT Stuff((SELECT ', ' + Email FROM Members FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') As Email;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks Richard. I will see if I can implement one of these methods. Appreciate your help. Still new to this so it's all a learning curve.
-
Dan O'Riordan wrote:
So, if you read my first post in this thread, it explains the issue in detail.
Sorry, but it doesn't. Nowhere have you shown how you get the email address(es) from the dataset into the textbox, or how you expect to get more than one email address into the text box. It is still not clear whether the database records contain a single email address for each member record or multiple addresses. If you have a single email address for each member and you want to send the same message to every member, then you should be using a
DataReaderto extract the detail from your database. You can then read each record in order and add the email address direct to the SMTP message, without the need for aTextBox.Sadly, I think he may be using the designer binding crap. That's the only way I can think of where he doesn't have to write any code to get the data into the controls. It's all done for him in the Designer generated code.
A guide to posting questions on CodeProject
How to debug small programs
Dave Kreskowiak -
Sadly, I think he may be using the designer binding crap. That's the only way I can think of where he doesn't have to write any code to get the data into the controls. It's all done for him in the Designer generated code.
A guide to posting questions on CodeProject
How to debug small programs
Dave KreskowiakHi Dave, You are correct. It is easier for us who are not geniuses at the coding side of it and rely on forums like this for some help in learning.
-
Sadly, I think he may be using the designer binding crap. That's the only way I can think of where he doesn't have to write any code to get the data into the controls. It's all done for him in the Designer generated code.
A guide to posting questions on CodeProject
How to debug small programs
Dave KreskowiakHi again guys, I am now using the following For Each in my code. I think this is what you are on about regarding looping. Please set me right if I am off path here. However, if it is right, I am still only getting one address into the mail.to field.
' Determine the mailMessage.To property based on CheckBox checked status If CheckBox1.Checked = True Then For Each dr As DataRow In myDataTable.Rows mailMessage.To.Add(New MailAddress(dr.Item(0).ToString)) Next Else mailMessage.To.Add(New MailAddress(EmailTextBox.Text.Trim())) End IfThanks in advance.
-
Hi again guys, I am now using the following For Each in my code. I think this is what you are on about regarding looping. Please set me right if I am off path here. However, if it is right, I am still only getting one address into the mail.to field.
' Determine the mailMessage.To property based on CheckBox checked status If CheckBox1.Checked = True Then For Each dr As DataRow In myDataTable.Rows mailMessage.To.Add(New MailAddress(dr.Item(0).ToString)) Next Else mailMessage.To.Add(New MailAddress(EmailTextBox.Text.Trim())) End IfThanks in advance.
-
Hi Dave, You are correct. It is easier for us who are not geniuses at the coding side of it and rely on forums like this for some help in learning.
Two problem with the designer generated stuff is two fold: 1) It hides all the details of what's going on and how to do it from you, thereby keeping everything a "secret". It does nothing to teach you how to do this on your own. 2) In order to understand how to make it work and how to fix problems and adapt it to do the weird stuff, like you're doing, you have to understand how it works under the covers! See point 1.
A guide to posting questions on CodeProject
How to debug small programs
Dave Kreskowiak -
Two problem with the designer generated stuff is two fold: 1) It hides all the details of what's going on and how to do it from you, thereby keeping everything a "secret". It does nothing to teach you how to do this on your own. 2) In order to understand how to make it work and how to fix problems and adapt it to do the weird stuff, like you're doing, you have to understand how it works under the covers! See point 1.
A guide to posting questions on CodeProject
How to debug small programs
Dave Kreskowiak -
Hi guys, I have the following code that will send an email from Visual Basic project using data from an SQL row called "Email". It works fine. However, it appears it is only showing one email from SQL into the TextBox. Therefore, only sending it to one email address. I have two email addresses stored in the database for testing but only one of them get the email message. Here is the code:
'Connect to SQL Server database and query out only Address column to fill into DataTable Dim con As SqlConnection = New SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=FigClubs;Integrated Security=True;Pooling=False") Dim cmd As SqlCommand = New SqlCommand("SELECT Email FROM Members", con) con.Open() Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd) Dim myDataTable As DataTable = New DataTable myDA.Fill(myDataTable) con.Close() con = Nothing Try Dim Smtp\_Server As New SmtpClient Dim e\_mail As New MailMessage() Smtp\_Server.UseDefaultCredentials = False Smtp\_Server.Credentials = New Net.NetworkCredential(My.Settings.ClubEmail, My.Settings.EmailPassword) Smtp\_Server.Port = 587 Smtp\_Server.EnableSsl = True Smtp\_Server.Host = "smtp.gmail.com" e\_mail = New MailMessage() e\_mail.From = New MailAddress(TextBox4.Text) e\_mail.To.Add(EmailTextBox.Text) e\_mail.Subject = TextBox1.Text e\_mail.IsBodyHtml = False e\_mail.Body = TextBox2.Text Smtp\_Server.Send(e\_mail) MsgBox("Your email has been sent.") Catch error\_t As Exception MsgBox(error\_t.ToString) End Try Me.Close()Any help you can provide with this would be greatly appreciated. Thanks, Dan
Late but might still help. I am going to assume a few things and then also use code as an example to simplify the possible solution. Assumptions: In your DB you probably have a table for Members and another table for EmailMsg (to be distributed to Members) 'Lets assume all Members Your SQL query will be
"SELECT * FROM DB.Members"
'Lets assume Email based on Subject Your SQL query will be
"Select * FROM DB.MailMsg WHERE Subject = 'How do I'"
Run the first query against the database to get all the Members and populate your dataset (DBData) Now populate the "MailTo" textbox:
For i = 0 to DBData.rows - 1
MailTo.AppendText(DBData.rows(i).Item(0) & ";")Instead of the AppendText option, you can use the "ScrollToCaret" option, but I much prefer the AppendToText option. To populate the the "Subject" textbox and "EmailMessage" textbox is done exactly the same except you do not need to iterate through the dataset because there should only be one EmailMsg in there. You might want to use the Multiline option in your textbox containing the mail message you wish to distribute. Good luck and try to stay away from automated Wizards, they really don't teach you anything good.
Regards, Tino "Plan your work, Work your plan"
