VB.NET – Update record in code behind

September 23, 2011 - Posted in Applications - Permalink

Default.aspx

Project Status:


<asp:DropDownList ID="UpdateStatus" runat="server">
    <asp:ListItem Text="Open" Value="Open">
    <asp:ListItem Text="In process" Value="In process">
    <asp:ListItem Text="On hold" Value="On hold">
    <asp:ListItem Text="Closed" Value="Closed">
</asp:DropDownList>
<asp:Button ID="UpdateBtn" runat="server" Text="Update" />

Default.vb.aspx


Imports System.Web.UI.WebControls.DropDownList

Protected Sub UpdateBtn_Click(sender As Object, e As System.EventArgs) _
                                                          Handles UpdateBtn.Click
' - query identifier
  Dim pid As Integer = Request.QueryString("pid")
  Dim ProjStat As DropDownList = UpdateBtn.FindControl("UpdateStatus")

  If pid > 0 Then

   ' - def oledb conn
   Dim oConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; _
                                  Data Source=" & Server.MapPath("My_Projects.accdb"))
      Try
       oConn.Open()

        Dim sql As String
        sql = "UPDATE ProjectRequests SET ProjectStatus = @ProjStat WHERE ID = @ID"

        ' - def new oledb command
        Dim Cmd_UpdateStatus As New OleDbCommand(sql, oConn)
        Cmd_UpdateStatus.Parameters.AddWithValue("@ProjStat", ProjStat.Text.ToString)
        Cmd_UpdateStatus.Parameters.Add("@ID", OleDbType.Integer).Value = pid

        ' - execute query cmd / close conn
        Cmd_UpdateStatus.ExecuteNonQuery()
       oConn.Close()

         ' - update status
          Response.Redirect("Default.aspx?pid=" & pid)

      Catch ex As Exception
         lblMsg.Text = ex.Message.ToString()

       End Try
   End If
End Sub

VB.NET – Send Email

September 19, 2011 - Posted in Applications, Email - Permalink

Send Email

Page

Please complete input form:
<asp:Label ID="Confirm" runat="server" >
<asp:Label ID="litStatus" runat="server" style="color: Gray; padding-left: 20px;">

<asp:FormView ID="FormView1" runat="server" DataKeyNames="ID" 
        DataSourceID="AccessDataSource1" DefaultMode="Insert">

<InsertItemTemplate>
          Your Name:<asp:TextBox ID="NameTextBox" runat="server" 
                    Text='< %# Bind("Name") %>' Class="inputText" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" 
                    runat="server" ErrorMessage="<font color='red'>*</font>" 
                    ControlToValidate="NameTextBox"></asp:RequiredFieldValidator>    
            

            Code Number:<asp:TextBox ID="CodeNumberTextBox" runat="server" 
                      Text='< %# Bind("CodeNumber") %>' Class="inputText" />
                      <asp:RequiredFieldValidator ID="RequiredFieldValidator2" 
                      runat="server" ErrorMessage="<font color='red'>*</font>" 
                      ControlToValidate="TicketNumberTextBox">  
            

            <asp:Button ID="InsertButton" runat="server" CausesValidation="True" 
            CommandName="Insert" Text="Submit" />
</InsertItemTemplate>
</asp:FormView>

<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
        DataFile="~/MyDB.accdb" 
        DeleteCommand="DELETE FROM [Table1] WHERE [ID] = ?" 
        InsertCommand="INSERT INTO [Table1] ([Name], [CodeNumber] VALUES (?, ?)"  
        SelectCommand="SELECT * FROM [Table1]" 
        UpdateCommand="UPDATE [Table1] SET [Name] = ?, [CodeNumber] = ?"
        OnInserted="InsertMessage" >
        <DeleteParameters>
            <asp:Parameter Name="ID" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="Name" Type="String" />
            <asp:Parameter Name="CodeNumber" Type="Int32" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="Name" Type="String" />
            <asp:Parameter Name="CodeNumber" Type="Int32" />
            <asp:Parameter Name="ID" Type="Int32" />
        </UpdateParameters>
    </asp:AccessDataSource>   



Code behind

Sub InsertMessage(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)
    If e.AffectedRows > 0 Then
       Confirm.Text = "<b> Input received, thank you!" ' - input confirmation msg
    Try
      Dim SmtpServer As New SmtpClient
      Dim mail As New MailMessage
      SmtpServer.Credentials = New Net.NetworkCredential("my_username", "my_password")
      SmtpServer.Port = 587
      SmtpServer.Host = "smtp.google.com"  ' - IP address OK as (x.x.x.x)
            mail = New MailMessage()
            mail.From = New MailAddress("my_email@MyEmailProvider.com")
            mail.To.Add("your_email@YourEmailProvider.com")
            mail.Subject = "Website input form"
            mail.Body = "<p><h2>Website Input Form" +
                         "<p>New form input has been submitted. " +
                         "To see the form input, 
                          <a href=""http://mywebsite.com/form_input/"">click here." +
                         "<p style=""color: gray""><b>Important Message:

                          This is automatically generated email. 
                           Do not reply to this email."
           mail.IsBodyHtml = True ' - type "False" to send email as text; 
      SmtpServer.Send(mail)

       litStatus.Text = "Email has been sent." ' - email sent confirmation 

    Catch ex As Exception
       litStatus.Text = ex.ToString() ' - email error message
        End Try
        Else
            Confirm.Text = "<font color='red'>Not completed - please try again!"
        End If
    End Sub

VB.NET – Parameters

September 16, 2011 - Posted in Applications, ASP.NET, MS SQL - Permalink

Parameter default value in code behind

SQL Datasource

<asp:sqldatasource ID="ServiceExpired" runat="server" 
       ConnectionString="< %$ ConnectionStrings:intranetConnectionString2 %>" 
       SelectCommand="SELECT * FROM [Invoice] _
                      WHERE _
                           ([EXPIRATION_DATE] >= @EXP_BEG) _
                      AND _
                           ([EXPIRATION_DATE] < @EXP_END) _
                      AND 
                           ([EXPIRATION_CONTACT] = @EXPIRATION_CONTACT) _
                      ORDER BY _
                             EXPIRATION_DATE ">
<selectparameters>
      <asp:parameter DefaultValue="YES" Name="EXPIRATION_CONTACT" Type="String" />
      <asp:parameter Name="EXP_BEG" Type="DateTime"/>
      <asp:parameter Name="EXP_END" Type="DateTime"/>
</selectparameters>

Code behind

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
  CuMonthLbl.Text = MonthName(Date.Now.Month)     ' - current month name
  NxMonthLbl.Text = MonthName(Date.Now.Month + 1) ' - next to current month name
  Dim NxMonth As String                           ' - define next month value
      NxMonth = (Date.Now.Month + 1)
 ' - query range labels
  MoBegLbl.Text = DateTime.Now.Month.ToString() _
                    + "/1/" + DateTime.Now.Year.ToString()
  MoEndLbl.Text = NxMonth + "/1/" + DateTime.Now.Year.ToString()

' - parameters values
' - beginning of month
  ServiceExpired.SelectParameters("EXP_BEG").DefaultValue = _
                                             DateTime.Now.Month.ToString() _
                                   + "/1/" + DateTime.Now.Year.ToString() 
' - end of month
  ServiceExpired.SelectParameters("EXP_END").DefaultValue = _
                                             NxMonth _
                                   + "/1/" + DateTime.Now.Year.ToString()
End Sub

' - label
Current month: <asp:label ID="CuMonthLbl" runat="server"> -
Next monht:  <asp:label ID="NxMonthLbl" runat="server">
Records from: <asp:label ID="MoBegLbl" runat="server"> -
              <asp:label ID="MoEndLbl" runat="server">
' - output

--------------------------------------------------------
|  #  | Company Name | Location | Expire     | Contact |
--------------------------------------------------------
|  1  | Kiklop       | Europe   | 09/01/2011 | YES     |
|  2  | Acer         | Europe   | 09/09/2011 | YES     |
|  3  | King Z       | Africa   | 09/22/2011 | YES     |
|  4  | Chung Fu     | Asia     | 09/30/2011 | YES     |

Current month: September
Next month: October
Records from: 09/01/2011 - 10/01/2011
« Older Entries
Newer Entries »