Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <%@ Import Namespace="System.Data" %>
- <%@ Import Namespace="System.Data.SqlClient" %>
- <%@ Import Namespace="System.Text"%>
- <html>
- <script language="VB" runat="server" >
- Dim MyConnection As SqlConnection
- Sub Page_Load(Src As Object, e As EventArgs)
- ' Create a connection to the "pubs" SQL database located on
- ' the local computer.
- myConnection = New SqlConnection("server=localhost;" _
- & "database=pubs;Trusted_Connection=Yes")
- ' Check whether this page is a postback. If it is not
- ' a postback, call a custom BindGrid function.
- If Not IsPostBack Then
- BindGrid()
- End If
- End Sub
- ' Implement an AddAuthor_Click function. This function does some data
- ' validation on the input form and builds a parameterized command containing
- ' all the fields of the input form. Then it executes this command to the
- ' database and tests (using the try command) whether the data was added.
- ' Finally, it rebinds the DataGrid to show the new data.
- Sub AddAuthor_Click(Sender As Object, e As EventArgs)
- Dim myCommand As SqlCommand
- Dim insertCmd As String
- ' Check that four of the input values are not empty. If any of them
- ' is empty, show a message to the user and rebind the DataGrid.
- If (au_id.Value = "" Or au_fname.Value = "" Or au_lname.Value = "" _
- Or phone.Value = "") Then
- Message.InnerHtml = "ERROR: Null values not allowed for " _
- & "Author ID, Name or Phone"
- Message.Style("color") = "red"
- BindGrid()
- Exit Sub
- End If
- ' Build a SQL INSERT statement string for all the input-form
- ' field values.
- insertCmd = "insert into Authors values (@Id, @LName, @FName," _
- & "@Phone, @Address, @City, @State, @Zip, @Contract);"
- ' Initialize the SqlCommand with the new SQL string.
- myCommand = New SqlCommand(insertCmd, myConnection)
- ' Create new parameters for the SqlCommand object and
- ' initialize them to the input-form field values.
- myCommand.Parameters.Add(New SqlParameter("@Id", _
- SqlDbType.VarChar, 11))
- myCommand.Parameters("@Id").Value = au_id.Value
- myCommand.Parameters.Add(New SqlParameter("@LName", _
- SqlDbType.VarChar, 40))
- myCommand.Parameters("@LName").Value = au_lname.Value
- myCommand.Parameters.Add(New SqlParameter("@FName", _
- SqlDbType.VarChar, 20))
- myCommand.Parameters("@FName").Value = au_fname.Value
- myCommand.Parameters.Add(New SqlParameter("@Phone", _
- SqlDbType.Char, 12))
- myCommand.Parameters("@Phone").Value = phone.Value
- myCommand.Parameters.Add(New SqlParameter("@Address", _
- SqlDbType.VarChar, 40))
- myCommand.Parameters("@Address").Value = address.Value
- myCommand.Parameters.Add(New SqlParameter("@City", _
- SqlDbType.VarChar, 20))
- myCommand.Parameters("@City").Value = city.Value
- myCommand.Parameters.Add(New SqlParameter("@State", _
- SqlDbType.Char, 2))
- myCommand.Parameters("@State").Value = state.Value
- myCommand.Parameters.Add(New SqlParameter("@Zip", _
- SqlDbType.Char, 5))
- myCommand.Parameters("@Zip").Value = zip.Value
- myCommand.Parameters.Add(New SqlParameter("@Contract", _
- SqlDbType.VarChar,1))
- myCommand.Parameters("@Contract").Value = contract.Value
- myCommand.Connection.Open()
- ' Test whether the new row can be added and display the
- ' appropriate message box to the user.
- Try
- myCommand.ExecuteNonQuery()
- Message.InnerHtml = "<b>Record Added</b><br>" & insertCmd
- Catch ex As SqlException
- If ex.Number = 2627 Then
- Message.InnerHtml = "ERROR: A record already exists with " _
- & "the same primary key"
- Else
- Message.InnerHtml = "ERROR: Could not add record, please " _
- & "ensure the fields are correctly filled out"
- Message.Style("color") = "red"
- End If
- End Try
- myCommand.Connection.Close()
- BindGrid()
- End Sub
- ' BindGrid connects to the database and implements a SQL
- ' SELECT query to get all the data in the "Authors" table
- ' of the database.
- Sub BindGrid()
- Dim myConnection As SqlConnection
- Dim myCommand As SqlDataAdapter
- ' Create a connection to the "pubs" SQL database located on
- ' the local computer.
- myConnection = New SqlConnection("server=localhost;" _
- & "database=pubs;Trusted_Connection=Yes")
- ' Connect to the SQL database using a SQL SELECT query to get all
- ' the data from the "Authors" table.
- myCommand = New SqlDataAdapter("SELECT * FROM authors", _
- myConnection)
- ' Create and fill a new DataSet.
- Dim ds As DataSet = New DataSet()
- myCommand.Fill(ds)
- ' Bind the DataGrid control to the DataSet.
- MyDataGrid.DataSource = ds
- MyDataGrid.DataBind()
- End Sub
- </script>
- <body style="font: 10pt verdana">
- <form runat="server">
- <h3><font face="Verdana">Inserting a Row of Data</font></h3>
- <table width="95%">
- <tr>
- <td valign="top">
- <ASP:DataGrid id="MyDataGrid" runat="server"
- Width="700"
- BackColor="#ccccff"
- BorderColor="black"
- ShowFooter="false"
- CellPadding=3
- CellSpacing="0"
- Font-Name="Verdana"
- Font-Size="8pt"
- HeaderStyle-BackColor="#aaaadd"
- EnableViewState="false"
- />
- </td>
- <td valign="top">
- <table style="font: 8pt verdana">
- <tr>
- <td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">
- Add a New Author:</td>
- </tr>
- <tr>
- <td nowrap>Author ID: </td>
- <td><input type="text" id="au_id" value="000-00-0000"
- runat="server"></td>
- </tr>
- <tr>
- <td nowrap>Last Name: </td>
- <td><input type="text" id="au_lname" value="Doe"
- runat="server"></td>
- </tr>
- <tr nowrap>
- <td>First Name: </td>
- <td><input type="text" id="au_fname" value="John"
- runat="server"></td>
- </tr>
- <tr>
- <td>Phone: </td>
- <td><input type="text" id="phone" value="808 555-5555"
- runat="server"></td>
- </tr>
- <tr>
- <td>Address: </td>
- <td><input type="text" id="address"
- value="One Microsoft Way" runat="server"></td>
- </tr>
- <tr>
- <td>City: </td>
- <td><input type="text" id="city" value="Redmond"
- runat="server"></td>
- </tr>
- <tr>
- <td>State: </td>
- <td>
- <select id="state" runat="server">
- <option>CA</option>
- <option>IN</option>
- <option>KS</option>
- <option>MD</option>
- <option>MI</option>
- <option>OR</option>
- <option>TN</option>
- <option>UT</option>
- </select>
- </td>
- </tr>
- <tr>
- <td nowrap>Zip Code: </td>
- <td><input type="text" id="zip" value="98005"
- runat="server"></td>
- </tr>
- <tr>
- <td>Contract: </td>
- <td>
- <select id="contract" runat="server">
- <option value="0">False</option>
- <option value="1">True</option>
- </select>
- </td>
- </tr>
- <tr>
- <td></td>
- <td style="padding-top:15">
- <input type="submit" OnServerClick="AddAuthor_Click"
- value="Add Author" runat="server">
- </td>
- </tr>
- <tr>
- <td colspan="2" style="padding-top:15" align="center">
- <span id="Message" EnableViewState="false"
- style="font: arial 11pt;" runat="server"/>
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table>
- </form>
- </body>
- </html>
- [C#]
- <%@ Import Namespace="System.Data" %>
- <%@ Import Namespace="System.Data.SqlClient" %>
- <%@ Import Namespace="System.Text"%>
- <html>
- <script language="C#" runat="server">
- SqlConnection myConnection;
- protected void Page_Load(Object Src, EventArgs E)
- {
- // Create a connection to the "pubs" SQL database located on
- // the local computer.
- myConnection = new SqlConnection("server=localhost;" +
- "database=pubs;Trusted_Connection=Yes");
- // Check to see whether this page is a postback. If it is not
- // a postback, call a custom BindGrid function.
- if (!IsPostBack) BindGrid();
- }
- // Implement an AddAuthor_Click function. This function does some data
- // validation on the input form and builds a parameterized command containing
- // all the fields of the input form. Then it executes this command to the
- // database and tests (using the try command) whether the data was added.
- // Finally, it rebinds the DataGrid to show the new data.
- public void AddAuthor_Click(Object sender, EventArgs E)
- {
- // Check that four of the input values are not empty. If any of them
- // is empty, show a message to the user and rebind the DataGrid.
- if (au_id.Value == "" || au_fname.Value == "" ||
- au_lname.Value == "" || phone.Value == "")
- {
- Message.InnerHtml = "ERROR: Null values not allowed for" +
- " Author ID, Name or Phone";
- Message.Style["color"] = "red";
- BindGrid();
- return;
- }
- // Build a SQL Insert statement string for all the input-form
- // field values.
- String insertCmd = "insert into Authors values (@Id," +
- " @LName, @FName, @Phone, @Address, @City, @State," +
- " @Zip, @Contract)";
- // Initialize the SqlCommand with the new SQL string
- // and the connection information.
- SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
- // Create new parameters for the SqlCommand object and
- // initialize them to the input-form field values.
- myCommand.Parameters.Add(new SqlParameter("@Id",
- SqlDbType.VarChar, 11));
- myCommand.Parameters["@Id"].Value = au_id.Value;
- myCommand.Parameters.Add(new SqlParameter("@LName",
- SqlDbType.VarChar, 40));
- myCommand.Parameters["@LName"].Value = au_lname.Value;
- myCommand.Parameters.Add(new SqlParameter("@FName",
- SqlDbType.VarChar, 20));
- myCommand.Parameters["@FName"].Value = au_fname.Value;
- myCommand.Parameters.Add(new SqlParameter("@Phone",
- SqlDbType.Char, 12));
- myCommand.Parameters["@Phone"].Value = phone.Value;
- myCommand.Parameters.Add(new SqlParameter("@Address",
- SqlDbType.VarChar, 40));
- myCommand.Parameters["@Address"].Value = address.Value;
- myCommand.Parameters.Add(new SqlParameter("@City",
- SqlDbType.VarChar, 20));
- myCommand.Parameters["@City"].Value = city.Value;
- myCommand.Parameters.Add(new SqlParameter("@State",
- SqlDbType.Char, 2));
- myCommand.Parameters["@State"].Value = state.Value;
- myCommand.Parameters.Add(new SqlParameter("@Zip",
- SqlDbType.Char, 5));
- myCommand.Parameters["@Zip"].Value = zip.Value;
- myCommand.Parameters.Add(new SqlParameter("@Contract",
- SqlDbType.VarChar,1));
- myCommand.Parameters["@Contract"].Value = contract.Value;
- myCommand.Connection.Open();
- // Test whether the new row can be added and display the
- // appropriate message box to the user.
- try
- {
- myCommand.ExecuteNonQuery();
- Message.InnerHtml = "<b>Record Added</b><br>" + insertCmd;
- }
- catch (SqlException e)
- {
- if (e.Number == 2627)
- Message.InnerHtml = "ERROR: A record already exists with" +
- " the same primary key";
- else
- Message.InnerHtml = "ERROR: Could not add record, please " +
- " ensure the fields are correctly filled out";
- Message.Style["color"] = "red";
- }
- myCommand.Connection.Close();
- BindGrid();
- }
- // BindGrid connects to the database and implements a SQL
- // SELECT query to get all the data in the "Authors" table
- // of the database.
- public void BindGrid()
- {
- // Create a connection to the "pubs" SQL database located on
- // the local computer.
- SqlConnection myConnection = new SqlConnection("server=localhost;" +
- "database=pubs;Trusted_Connection=Yes");
- // Connect to the database with a SELECT query on the
- // "Authors" table.
- SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM" +
- " Authors", myConnection);
- // Create and fill a new DataSet.
- DataSet ds = new DataSet();
- myCommand.Fill(ds);
- // Bind MyDataGrid to the DataSet.
- MyDataGrid.DataSource=ds;
- MyDataGrid.DataBind();
- }
- </script>
- <%-- Display the DataGrid in the body of the page, and
- create and display the input form. --%>
- <body style="font: 10pt verdana">
- <form runat="server">
- <h3><font face="Verdana">Inserting a Row of Data</font></h3>
- <table width="95%">
- <tr>
- <td valign="top">
- <%-- Put the DataGrid in the first column. --%>
- <ASP:DataGrid id="MyDataGrid" runat="server"
- Width="700"
- BackColor="#ccccff"
- BorderColor="black"
- ShowFooter="false"
- CellPadding=3
- CellSpacing="0"
- Font-Name="Verdana"
- Font-Size="8pt"
- HeaderStyle-BackColor="#aaaadd"
- EnableViewState="false"
- />
- </td>
- <%-- Create a second table column. --%>
- <td valign="top">
- <%-- Put the input form (a second table) in the second
- column. --%>
- <table style="font: 8pt verdana">
- <tr>
- <td colspan="2" bgcolor="#aaaadd"
- style="font:10pt verdana">
- Add a New Author:
- </td>
- </tr>
- <tr>
- <td nowrap>Author ID:</td>
- <td><input type="text" id="au_id" value="000-00-0000"
- runat="server"></td>
- </tr>
- <tr>
- <td nowrap>Last Name:</td>
- <td><input type="text" id="au_lname" value="Doe"
- runat="server"></td>
- </tr>
- <tr nowrap>
- <td>First Name:</td>
- <td><input type="text" id="au_fname" value="John"
- runat="server"></td>
- </tr>
- <tr>
- <td>Phone: </td>
- <td>
- <input type="text" id="phone" value="808 555-5555"
- runat="server">
- </td>
- </tr>
- <tr>
- <td>Address:</td>
- <td>
- <input type="text" id="address" value =
- "One Microsoft Way" runat="server">
- </td>
- </tr>
- <tr>
- <td>City:</td>
- <td><input type="text" id="city" value="Redmond"
- runat="server"></td>
- </tr>
- <tr>
- <td>State: </td>
- <td>
- <select id="state" runat="server">
- <option>CA</option>
- <option>IN</option>
- <option>KS</option>
- <option>MD</option>
- <option>MI</option>
- <option>OR</option>
- <option>TN</option>
- <option>UT</option>
- </select>
- </td>
- </tr>
- <tr>
- <td nowrap>Zip Code:</td>
- <td><input type="text" id="zip" value="98005"
- runat="server"></td>
- </tr>
- <tr>
- <td>Contract: </td>
- <td>
- <select id="contract" runat="server">
- <option value="0">False</option>
- <option value="1">True</option>
- </select>
- </td>
- </tr>
- <tr>
- <td></td>
- <td style="padding-top:15">
- <input type="submit" OnServerClick=
- "AddAuthor_Click" value="Add Author"
- runat="server">
- </td>
- </tr>
- <tr>
- <td colspan="2" style="padding-top:15"
- align="center">
- <span id="Message" EnableViewState="false"
- style="font: arial 11pt;" runat="server"/>
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table>
- </form>
- </body>
- </html>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement