Here is the stored procedure we created in SQL Server. For this to work, there must be a schema named Venue and a role called VenueRole. The venue roll must gran exec on the Venue Schema and select and insert on the Venue table. (Sorry for naming them the same, a mental fart on my part.)
Some of the parameters have default values. this allows us to skip them. (Specifically VenueAddress1, and VenueDescription)
/*Add new a new venue Check to make sure the venue doesn't already exist */ Go --this procedure uses the venue schema --also proc is the same as procedure Create procedure Venue.usp_AddVenue @VenueName nvarchar(255), @VenueAddress1 nvarchar(255) =null, @VenueAddress2 nvarchar(255), @VenueCity nvarchar(255), @VenueState nchar(2) ='WA', @VenuePhone nchar(10), @VenueCapacity int, @VenueAgeRestriction bit =0, @VenueDescription xml =null, @VenueURL nvarchar(255) AS If not Exists (Select VenueName From Venue Where VenueAddress2=@VenueAddress2 And VenueCity=@VenueCity And VenueState=@VenueState) Begin Insert into Venue(VenueName, VenueAddress1, VenueAddress2, VenueCity, VenueState, VenuePhone, VenueCapacity, VenueAgeRestriction, VenueDescription, VenueURL) Values ( @VenueName, @VenueAddress1, @VenueAddress2, @VenueCity, @VenueState, @VenuePhone, @VenueCapacity, @VenueAgeRestriction, @VenueDescription, @VenueURL) return 1 End Else Begin Print 'The venue already exists' return 0 End Go Exec Venue.usp_AddVenue @VenueName ='Comet Tavern', @VenueAddress1=null, @VenueAddress2='922 Pike', @VenueCity='Seattle', @VenueState='Wa', @VenuePhone='2065551200', @VenueCapacity=99, @VenueAgeRestriction=true, @VenueURL='http://www.cometTavern.com' Select * From Venue Delete From Venue where VenueID=5
Next we created a web site that uses the schema. We made a form (an ugly one, to be sure) to add a new venue. It will call the stored procedure. Here is the Html and asp source code for the form
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>
<p>
<asp:Label ID="Label1" runat="server" Text="Enter Venue Name"></asp:Label>
<asp:TextBox ID="txtVenueName" runat="server"></asp:TextBox>
</p>
<p>
<asp:Label ID="Label2" runat="server" Text="Enter Venue Address"></asp:Label>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
</p>
<p>
<asp:Label ID="Label3" runat="server" Text="Enter City"></asp:Label>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
</p>
<p>
<asp:Label ID="Label4" runat="server" Text="State"></asp:Label>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox>
</p>
<p>
<asp:Label ID="Label5" runat="server" Text="Enter Venue Phone"></asp:Label>
<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox>
</p>
<p>
<asp:Label ID="Label6" runat="server" Text="Enter Venue Capacity"></asp:Label>
<asp:TextBox ID="txtCapacity" runat="server"></asp:TextBox>
</p>
<p>
<asp:DropDownList ID="ddlAgeRestriction" runat="server">
<asp:ListItem Text="Choose Age Restriction" />
<asp:ListItem Text="yes" Value="1" />
<asp:ListItem Text="no" Value="0" />
</asp:DropDownList></p>
<p>
<asp:Label ID="Label7" runat="server" Text="Enter Venue URL"></asp:Label>
<asp:TextBox ID="txtUrl" runat="server"></asp:TextBox>
</p>
<p>
<asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" /><asp:Label ID="lblResult" runat="server"
Text="Label"></asp:Label>
</p>
</div>
</form>
</body>
</html>
Here is the code that connects to the database and calls the stored procedure
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//ADO 1)make a connection
//command object passes the sql to the server
//get all the values from the form
//here we create a connection string that connects to the server
//it contains the name of the server, the database and login information
SqlConnection connect = new SqlConnection
("Data Source=localhost;initial catalog=VenueTracker;user=VenuLogin;password=password");
//the command object passes sql to the server
SqlCommand cmd = new SqlCommand();
//it is going to use the connect object to get to the database
cmd.Connection = connect;
//we are going to use a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
//here is the name of stored procedure
cmd.CommandText = "usp_AddVenue";
//We must provide a value for every parameter of the stored procedure
//which does not have a default value
cmd.Parameters.AddWithValue("@VenueName", txtVenueName.Text);
cmd.Parameters.AddWithValue("@VenueAddress2", txtAddress.Text);
cmd.Parameters.AddWithValue("@VenueCity", txtCity.Text);
cmd.Parameters.AddWithValue("@VenueState", txtState.Text);
cmd.Parameters.AddWithValue("@VenuePhone", txtPhone.Text);
cmd.Parameters.AddWithValue("@VenueCapacity", int.Parse(txtCapacity.Text));
cmd.Parameters.AddWithValue("@VenueAgeRestriction",int .Parse(ddlAgeRestriction.SelectedValue));
cmd.Parameters.AddWithValue("@VenueURL", txtUrl.Text);
//we open the connection
connect.Open();
//execute the stored procedure
int x=cmd.ExecuteNonQuery();
//check the results
if (x == 1)
lblResult.Text = "Venue successfully Entered";
else
lblResult.Text = "Problems abound";
connect.Close();
}
}
No comments:
Post a Comment