VB.NET ASP.NET Sql Command Update Statement Source Code Example
Sql Command Update Statement
Purpose: – Illustrates using Sql Command Update Statement in VB.NET ASP.NET.
Prerequistes:
- Install Visual Web Developer 2008
- Install SQL Server Express
- Download Northwind and Pubs Databases
- Attach Northwind Database to Databases in Sql Express
- Attach pubs Database to Databases in Sql Express
Notes:
- You can build your own library of syntax examples by using same web site over and over and just add new web forms to it.
Instructions:
- Use Visual Web Developer 2008
- Create new web site;
- Click File/New Web Site
- Select ASP.NET Website Template
- Select Visual Basic for Language
- name of Web Site could be VBNET_ASPNET_Syntax.
- Add New folder named “Database_ADONET”
- Right-click project name in solution explorer;
- add new folder;
- name of folder could be: Database_ADONET
- Add Web Form Named SqlCommandUpdate to Database_ADONET folder
- Right-click Database_ADONET folder;
- add new item;
- Select Web Form
- Check place code behind in separate file
- Web Form name could be SqlCommandUpdate
- Click on copy code in code below to copy code into web form SqlCommandUpdate.aspx
- Click on copy code in second set of code below to copy code into code-behind SqlCommandUpdate.aspx.vb
- Right-click on SqlCommandUpdate.aspx in solution explorer and select view in browser
Step 1: Click on Copy Code to Cut-n-paste code into SqlCommandUpdate.aspx
< %@ Page Language="VB" AutoEventWireup="false" CodeFile="SqlCommandUpdate.aspx.vb" Inherits="Database_ADONET_SqlCommandUpdate" %> < !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <table> <tr> <td>Find this First Name: </td><td> <asp:textbox ID="txtFirstName" runat="server"></asp:textbox> </td> </tr> <tr> <td>Update Last Name with this: </td><td> <asp:textbox ID="txtLastName" runat="server"></asp:textbox> </td> </tr> </table><br /> <asp:button ID="btnUpdate" runat="server" Text="Update" /><br /> <asp:label ID="lblErrMsg" runat="server" Text="lblErrMsg" ForeColor="#FF3300" Visible="False"></asp:label><br /> <asp:gridview ID="GridView1" runat="server" DataSourceID="sdsEmployees"> </asp:gridview> <asp:sqldatasource ID="sdsEmployees" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind_ConnectionString %>" SelectCommand="SELECT [FirstName], [LastName] FROM [Employees]"> </asp:sqldatasource> </div> </form> </body> </html> |
Step 2: Click on Copy Code to Cut-n-paste code into SqlCommandUpdate.aspx.vb
Imports System Imports System.Data Imports System.Data.SqlClient Partial Class Database_ADONET_SqlCommandUpdate Inherits System.Web.UI.Page Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Dim thisConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("Northwind_ConnectionString").ConnectionString) 'Create Command object Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand() Try ' Open Connection thisConnection.Open() ' 1. Create Command ' Sql Update Statement Dim updateSql As String = _ "UPDATE Employees " & _ "SET LastName = @LastName " & _ "WHERE FirstName = @FirstName" Dim UpdateCmd As New SqlCommand(updateSql, thisConnection) ' 2. Map Parameters UpdateCmd.Parameters.Add("@FirstName", _ SqlDbType.NVarChar, 10, "FirstName") UpdateCmd.Parameters.Add("@LastName", _ SqlDbType.NVarChar, 20, "LastName") UpdateCmd.Parameters("@FirstName").Value = txtFirstName.Text UpdateCmd.Parameters("@LastName").Value = txtLastName.Text UpdateCmd.ExecuteNonQuery() Catch ex As SqlException ' Display error lblErrMsg.Text = ex.ToString() lblErrMsg.Visible = True Finally ' Close Connection thisConnection.Close() End Try GridView1.DataBind() End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load lblErrMsg.Visible = False End Sub End Class |
Step 3: Click on Copy Code to Cut-n-paste code into web.config right after the appSettings section
<connectionstrings> <add name="Northwind_ConnectionString" connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" /> <add name="Pubs_ConnectionString" connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI" /> </connectionstrings> |