Below is my code so far. It is a page for sending a mailing list. I have a datagrid showing all the users and there email address. The table EMail has two columns one called EMailID and the other EMail. What I want to do is create a loop which gets out all the email addresses from the table. So how do I do this?
I don't even know how to get a single piece of data out from my database. What would the code be.
Thanks guys.
<%@ Page Language="VB" %>
<script runat="server">
Function MailingList() As System.Data.DataSet
Dim connectionString As String = "server='localhost'; user id='sa'; password='***'; Database='MailingList'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [EMail].* FROM [EMail]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack then
dgMailingList.DataSource = MailingList()
dgMailingList.DataBind()
End If
End Sub
Sub dgMailingList_Page(sender As Object, e as DataGridPageChangedEventArgs)
'Assign the CurrentPageIndex property to the new page index value
dgMailingList.CurrentPageIndex = e.NewPageIndex
'Rebind the data to the dataGrid
dgMailingList.DataSource = MailingList()
dgMailingList.DataBind()
End Sub
Function DeleteEMail(ByVal eMail As String) As Integer
Dim connectionString As String = "server='localhost'; user id='sa'; password='****'; Database='MailingList'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "DELETE FROM [EMail] WHERE ([EMail].[EMail] = @EMail)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@EMail", System.Data.SqlDbType.NVarChar).Value = eMail
Dim rowsAffected As Integer = 0
sqlConnection.Open
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Finally
sqlConnection.Close
End Try
Return rowsAffected
End Function
Sub btnDelete_Click(sender As Object, e As EventArgs)
'Determine the value of the Product ID Column
Dim EMail as String = delete.text
DeleteEmail(EMail)
dgMailingList.EditItemIndex = -1
dgMailingList.DataSource = MailingList()
dgMailingList.DataBind()
delete.text=""
End Sub
Sub btnSend_Click(sender As Object, e As EventArgs)
' Build a MailMessage
Dim mailMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage
mailMessage.From = "ell@frantic.com"
mailMessage.To = "ellalderton@hotmail.com"
mailMessage.Subject = title.text
mailMessage.BodyFormat = System.Web.Mail.MailFormat.Text
mailMessage.Body = message.text
System.Web.Mail.SmtpMail.SmtpServer = "frantic.com"
System.Web.Mail.SmtpMail.Send(mailMessage)
Dim connectionString As String = "server='localhost'; user id='sa'; password='sufcea16'; Database='MailingList'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim Ng as String = (SELECT [EMail].Rows(0).* FROM [EMail])
Response.Write(Ng)
End Sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<h1 align="center">Mailing List
</h1>
<p align="center">
</p>
<p align="left">
<asp:DataGrid id="dgMailingList" runat="server" BorderStyle="None" CellSpacing="2" BorderWidth="1px" BorderColor="#DEBA84" BackColor="#DEBA84" CellPadding="3" AllowPaging="True" AutoGenerateColumns="False" PageSize="5" OnPageIndexChanged="dgMailingList_Page">
<FooterStyle forecolor="#8C4510" backcolor="#F7DFB5"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#A55129"></HeaderStyle>
<PagerStyle nextpagetext="Next &gt;" prevpagetext="&lt; Previous" horizontalalign="Center" forecolor="#8C4510"></PagerStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#738A9C"></SelectedItemStyle>
<ItemStyle forecolor="#8C4510" backcolor="#FFF7E7"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="EMailID" HeaderText="E-Mail ID"></asp:BoundColumn>
<asp:BoundColumn DataField="EMail" HeaderText="E-Mail Address"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</p>
<p align="left">
</p>
<p align="left">
Delete E-Mail Address:<asp:TextBox id="delete" runat="server" Width="240px"></asp:TextBox>
<asp:Button id="btnDelete" onclick="btnDelete_Click" runat="server" Text="Delete"></asp:Button>
</p>
<p align="left">
</p>
<h2 align="left">Mailing List Message
</h2>
<p align="left">
Title:<asp:TextBox id="title" runat="server" Width="307px"></asp:TextBox>
</p>
<p align="left">
Message:<asp:TextBox id="message" runat="server" Width="554px" TextMode="MultiLine" Height="203px"></asp:TextBox>
</p>
<p align="left">
<asp:Button id="btnSend" onclick="btnSend_Click" runat="server" Text="Send to Mailing List"></asp:Button>
</p>
<!-- Insert content here -->
</form>
</body>
</html>