VB.NET ASP.NET Sql Command Select Statement Source Code Example
Sql Command Select Statement Source Code Example
Purpose: – Illustrates using Sql Command Select Statement in VB.NET ASP.NET.
Prerequistes:
- Install Visual Web Developer 2008
- Install SQL Server Express
- Download Northwind Database
- 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 SqlCommandSelect 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 SqlCommandSelect
- Click on copy code in code below to copy code into web form SqlCommandSelect.aspx
- Click on copy code in second set of code below to copy code into code-behind SqlCommandSelect.aspx.vb
- Right-click on SqlCommandSelect.aspx in solution explorer and select view in browser
Step 1: Click on Copy Code to Cut-n-paste code into SqlCommandSelect.aspx
< %@ Page Language="VB" AutoEventWireup="false" CodeFile="SqlCommandSelect.aspx.vb" Inherits="Database_ADONET_SqlCommandSelect" %> < !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <head> <title>Sql Command Select VB.NET ASP.NET</title> </head> <body> <form id="Form1" runat="server"> <p> Find: <asp:textbox id="TextBox1" runat="server" Text="*"></asp:textbox> <asp:button id="Button1" onclick="Button1_Click" runat="server" Text="Go"></asp:button> </p> <p> <asp:datagrid id="DataGrid1" runat="server" BorderWidth="5px" BorderColor="Black" CellPadding="5"> <headerstyle font-size="X-Small" font-names="Arial Narrow" font-bold="True" forecolor="White" backcolor="Black"></headerstyle> <alternatingitemstyle backcolor="Silver"></alternatingitemstyle> <itemstyle font-size="X-Small" font-names="Arial Narrow"></itemstyle> </asp:datagrid> </p> </form> </body> </html> |
Step 2: Click on Copy Code to Cut-n-paste code into SqlCommandSelect.aspx.vb
Imports System Imports System.Data Imports System.Data.SqlClient Partial Class Database_ADONET_SqlCommandSelect Inherits System.Web.UI.Page Function FindByTitle(ByVal search As String) As System.Data.DataSet Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Pubs_ConnectionString").ConnectionString) Dim queryString As String = "" If search = "*" Then queryString = "SELECT [titles].[title], [titles].[price], [titles].[notes], [titles].[pubdate] F" & _ "ROM [titles]" Else queryString = "SELECT [titles].[title], [titles].[price], [titles].[notes], [titles].[pubdate] F" & _ "ROM [titles] WHERE ([titles].[title] like @search + '%')" End If Dim sqlCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, con) If search <> "*" Then sqlCmd.Parameters.Add("@search", System.Data.SqlDbType.NVarChar).Value = search End If Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCmd) Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet) Return dataSet End Function Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Dim sTitle As String = TextBox1.Text DataGrid1.DataSource = FindByTitle(sTitle) DataGrid1.DataBind() 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="Nortwind_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> |