· 10 years ago · Oct 25, 2015, 10:03 AM
1<%@ Import Namespace="System.Data" %>
2<%@ Import Namespace="System.Data.SqlClient" %>
3<%@ Import Namespace="System.Text"%>
4
5<html>
6<script language="VB" runat="server" >
7 Dim MyConnection As SqlConnection
8
9 Sub Page_Load(Src As Object, e As EventArgs)
10 ' Create a connection to the "pubs" SQL database located on
11 ' the local computer.
12 myConnection = New SqlConnection("server=localhost;" _
13 & "database=pubs;Trusted_Connection=Yes")
14 ' Check whether this page is a postback. If it is not
15 ' a postback, call a custom BindGrid function.
16 If Not IsPostBack Then
17 BindGrid()
18 End If
19 End Sub
20
21 ' Implement an AddAuthor_Click function. This function does some data
22 ' validation on the input form and builds a parameterized command containing
23 ' all the fields of the input form. Then it executes this command to the
24 ' database and tests (using the try command) whether the data was added.
25 ' Finally, it rebinds the DataGrid to show the new data.
26 Sub AddAuthor_Click(Sender As Object, e As EventArgs)
27 Dim myCommand As SqlCommand
28 Dim insertCmd As String
29 ' Check that four of the input values are not empty. If any of them
30 ' is empty, show a message to the user and rebind the DataGrid.
31 If (au_id.Value = "" Or au_fname.Value = "" Or au_lname.Value = "" _
32 Or phone.Value = "") Then
33 Message.InnerHtml = "ERROR: Null values not allowed for " _
34 & "Author ID, Name or Phone"
35 Message.Style("color") = "red"
36 BindGrid()
37 Exit Sub
38 End If
39 ' Build a SQL INSERT statement string for all the input-form
40 ' field values.
41 insertCmd = "insert into Authors values (@Id, @LName, @FName," _
42 & "@Phone, @Address, @City, @State, @Zip, @Contract);"
43 ' Initialize the SqlCommand with the new SQL string.
44 myCommand = New SqlCommand(insertCmd, myConnection)
45 ' Create new parameters for the SqlCommand object and
46 ' initialize them to the input-form field values.
47 myCommand.Parameters.Add(New SqlParameter("@Id", _
48 SqlDbType.VarChar, 11))
49 myCommand.Parameters("@Id").Value = au_id.Value
50 myCommand.Parameters.Add(New SqlParameter("@LName", _
51 SqlDbType.VarChar, 40))
52 myCommand.Parameters("@LName").Value = au_lname.Value
53 myCommand.Parameters.Add(New SqlParameter("@FName", _
54 SqlDbType.VarChar, 20))
55 myCommand.Parameters("@FName").Value = au_fname.Value
56 myCommand.Parameters.Add(New SqlParameter("@Phone", _
57 SqlDbType.Char, 12))
58 myCommand.Parameters("@Phone").Value = phone.Value
59 myCommand.Parameters.Add(New SqlParameter("@Address", _
60 SqlDbType.VarChar, 40))
61 myCommand.Parameters("@Address").Value = address.Value
62 myCommand.Parameters.Add(New SqlParameter("@City", _
63 SqlDbType.VarChar, 20))
64 myCommand.Parameters("@City").Value = city.Value
65 myCommand.Parameters.Add(New SqlParameter("@State", _
66 SqlDbType.Char, 2))
67 myCommand.Parameters("@State").Value = state.Value
68 myCommand.Parameters.Add(New SqlParameter("@Zip", _
69 SqlDbType.Char, 5))
70 myCommand.Parameters("@Zip").Value = zip.Value
71 myCommand.Parameters.Add(New SqlParameter("@Contract", _
72 SqlDbType.VarChar,1))
73 myCommand.Parameters("@Contract").Value = contract.Value
74 myCommand.Connection.Open()
75 ' Test whether the new row can be added and display the
76 ' appropriate message box to the user.
77 Try
78 myCommand.ExecuteNonQuery()
79 Message.InnerHtml = "<b>Record Added</b><br>" & insertCmd
80 Catch ex As SqlException
81 If ex.Number = 2627 Then
82 Message.InnerHtml = "ERROR: A record already exists with " _
83 & "the same primary key"
84 Else
85 Message.InnerHtml = "ERROR: Could not add record, please " _
86 & "ensure the fields are correctly filled out"
87 Message.Style("color") = "red"
88 End If
89 End Try
90
91 myCommand.Connection.Close()
92 BindGrid()
93 End Sub
94
95 ' BindGrid connects to the database and implements a SQL
96 ' SELECT query to get all the data in the "Authors" table
97 ' of the database.
98 Sub BindGrid()
99 Dim myConnection As SqlConnection
100 Dim myCommand As SqlDataAdapter
101 ' Create a connection to the "pubs" SQL database located on
102 ' the local computer.
103 myConnection = New SqlConnection("server=localhost;" _
104 & "database=pubs;Trusted_Connection=Yes")
105 ' Connect to the SQL database using a SQL SELECT query to get all
106 ' the data from the "Authors" table.
107 myCommand = New SqlDataAdapter("SELECT * FROM authors", _
108 myConnection)
109 ' Create and fill a new DataSet.
110 Dim ds As DataSet = New DataSet()
111 myCommand.Fill(ds)
112 ' Bind the DataGrid control to the DataSet.
113 MyDataGrid.DataSource = ds
114 MyDataGrid.DataBind()
115 End Sub
116</script>
117
118<body style="font: 10pt verdana">
119 <form runat="server">
120 <h3><font face="Verdana">Inserting a Row of Data</font></h3>
121 <table width="95%">
122 <tr>
123 <td valign="top">
124 <ASP:DataGrid id="MyDataGrid" runat="server"
125 Width="700"
126 BackColor="#ccccff"
127 BorderColor="black"
128 ShowFooter="false"
129 CellPadding=3
130 CellSpacing="0"
131 Font-Name="Verdana"
132 Font-Size="8pt"
133 HeaderStyle-BackColor="#aaaadd"
134 EnableViewState="false"
135 />
136 </td>
137 <td valign="top">
138 <table style="font: 8pt verdana">
139 <tr>
140 <td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">
141 Add a New Author:</td>
142 </tr>
143 <tr>
144 <td nowrap>Author ID: </td>
145 <td><input type="text" id="au_id" value="000-00-0000"
146 runat="server"></td>
147 </tr>
148 <tr>
149 <td nowrap>Last Name: </td>
150 <td><input type="text" id="au_lname" value="Doe"
151 runat="server"></td>
152 </tr>
153 <tr nowrap>
154 <td>First Name: </td>
155 <td><input type="text" id="au_fname" value="John"
156 runat="server"></td>
157 </tr>
158 <tr>
159 <td>Phone: </td>
160 <td><input type="text" id="phone" value="808 555-5555"
161 runat="server"></td>
162 </tr>
163 <tr>
164 <td>Address: </td>
165 <td><input type="text" id="address"
166 value="One Microsoft Way" runat="server"></td>
167 </tr>
168 <tr>
169 <td>City: </td>
170 <td><input type="text" id="city" value="Redmond"
171 runat="server"></td>
172 </tr>
173 <tr>
174 <td>State: </td>
175 <td>
176 <select id="state" runat="server">
177 <option>CA</option>
178 <option>IN</option>
179 <option>KS</option>
180 <option>MD</option>
181 <option>MI</option>
182 <option>OR</option>
183 <option>TN</option>
184 <option>UT</option>
185 </select>
186 </td>
187 </tr>
188 <tr>
189 <td nowrap>Zip Code: </td>
190 <td><input type="text" id="zip" value="98005"
191 runat="server"></td>
192 </tr>
193 <tr>
194 <td>Contract: </td>
195 <td>
196 <select id="contract" runat="server">
197 <option value="0">False</option>
198 <option value="1">True</option>
199 </select>
200 </td>
201 </tr>
202 <tr>
203 <td></td>
204 <td style="padding-top:15">
205 <input type="submit" OnServerClick="AddAuthor_Click"
206 value="Add Author" runat="server">
207 </td>
208 </tr>
209 <tr>
210 <td colspan="2" style="padding-top:15" align="center">
211 <span id="Message" EnableViewState="false"
212 style="font: arial 11pt;" runat="server"/>
213 </td>
214 </tr>
215 </table>
216 </td>
217 </tr>
218 </table>
219 </form>
220</body>
221</html>
222
223[C#]
224<%@ Import Namespace="System.Data" %>
225<%@ Import Namespace="System.Data.SqlClient" %>
226<%@ Import Namespace="System.Text"%>
227<html>
228<script language="C#" runat="server">
229 SqlConnection myConnection;
230 protected void Page_Load(Object Src, EventArgs E)
231 {
232 // Create a connection to the "pubs" SQL database located on
233 // the local computer.
234 myConnection = new SqlConnection("server=localhost;" +
235 "database=pubs;Trusted_Connection=Yes");
236 // Check to see whether this page is a postback. If it is not
237 // a postback, call a custom BindGrid function.
238 if (!IsPostBack) BindGrid();
239 }
240 // Implement an AddAuthor_Click function. This function does some data
241 // validation on the input form and builds a parameterized command containing
242 // all the fields of the input form. Then it executes this command to the
243 // database and tests (using the try command) whether the data was added.
244 // Finally, it rebinds the DataGrid to show the new data.
245 public void AddAuthor_Click(Object sender, EventArgs E)
246 {
247 // Check that four of the input values are not empty. If any of them
248 // is empty, show a message to the user and rebind the DataGrid.
249 if (au_id.Value == "" || au_fname.Value == "" ||
250 au_lname.Value == "" || phone.Value == "")
251 {
252 Message.InnerHtml = "ERROR: Null values not allowed for" +
253 " Author ID, Name or Phone";
254 Message.Style["color"] = "red";
255 BindGrid();
256 return;
257 }
258 // Build a SQL Insert statement string for all the input-form
259 // field values.
260 String insertCmd = "insert into Authors values (@Id," +
261 " @LName, @FName, @Phone, @Address, @City, @State," +
262 " @Zip, @Contract)";
263 // Initialize the SqlCommand with the new SQL string
264 // and the connection information.
265 SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
266 // Create new parameters for the SqlCommand object and
267 // initialize them to the input-form field values.
268 myCommand.Parameters.Add(new SqlParameter("@Id",
269 SqlDbType.VarChar, 11));
270 myCommand.Parameters["@Id"].Value = au_id.Value;
271 myCommand.Parameters.Add(new SqlParameter("@LName",
272 SqlDbType.VarChar, 40));
273 myCommand.Parameters["@LName"].Value = au_lname.Value;
274 myCommand.Parameters.Add(new SqlParameter("@FName",
275 SqlDbType.VarChar, 20));
276 myCommand.Parameters["@FName"].Value = au_fname.Value;
277 myCommand.Parameters.Add(new SqlParameter("@Phone",
278 SqlDbType.Char, 12));
279 myCommand.Parameters["@Phone"].Value = phone.Value;
280 myCommand.Parameters.Add(new SqlParameter("@Address",
281 SqlDbType.VarChar, 40));
282 myCommand.Parameters["@Address"].Value = address.Value;
283 myCommand.Parameters.Add(new SqlParameter("@City",
284 SqlDbType.VarChar, 20));
285 myCommand.Parameters["@City"].Value = city.Value;
286 myCommand.Parameters.Add(new SqlParameter("@State",
287 SqlDbType.Char, 2));
288 myCommand.Parameters["@State"].Value = state.Value;
289 myCommand.Parameters.Add(new SqlParameter("@Zip",
290 SqlDbType.Char, 5));
291 myCommand.Parameters["@Zip"].Value = zip.Value;
292 myCommand.Parameters.Add(new SqlParameter("@Contract",
293 SqlDbType.VarChar,1));
294 myCommand.Parameters["@Contract"].Value = contract.Value;
295 myCommand.Connection.Open();
296 // Test whether the new row can be added and display the
297 // appropriate message box to the user.
298 try
299 {
300 myCommand.ExecuteNonQuery();
301 Message.InnerHtml = "<b>Record Added</b><br>" + insertCmd;
302 }
303 catch (SqlException e)
304 {
305 if (e.Number == 2627)
306 Message.InnerHtml = "ERROR: A record already exists with" +
307 " the same primary key";
308 else
309 Message.InnerHtml = "ERROR: Could not add record, please " +
310 " ensure the fields are correctly filled out";
311 Message.Style["color"] = "red";
312 }
313 myCommand.Connection.Close();
314 BindGrid();
315 }
316 // BindGrid connects to the database and implements a SQL
317 // SELECT query to get all the data in the "Authors" table
318 // of the database.
319 public void BindGrid()
320 {
321 // Create a connection to the "pubs" SQL database located on
322 // the local computer.
323 SqlConnection myConnection = new SqlConnection("server=localhost;" +
324 "database=pubs;Trusted_Connection=Yes");
325 // Connect to the database with a SELECT query on the
326 // "Authors" table.
327 SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM" +
328 " Authors", myConnection);
329 // Create and fill a new DataSet.
330 DataSet ds = new DataSet();
331 myCommand.Fill(ds);
332 // Bind MyDataGrid to the DataSet.
333 MyDataGrid.DataSource=ds;
334 MyDataGrid.DataBind();
335 }
336</script>
337
338<%-- Display the DataGrid in the body of the page, and
339 create and display the input form. --%>
340<body style="font: 10pt verdana">
341 <form runat="server">
342 <h3><font face="Verdana">Inserting a Row of Data</font></h3>
343 <table width="95%">
344 <tr>
345 <td valign="top">
346 <%-- Put the DataGrid in the first column. --%>
347 <ASP:DataGrid id="MyDataGrid" runat="server"
348 Width="700"
349 BackColor="#ccccff"
350 BorderColor="black"
351 ShowFooter="false"
352 CellPadding=3
353 CellSpacing="0"
354 Font-Name="Verdana"
355 Font-Size="8pt"
356 HeaderStyle-BackColor="#aaaadd"
357 EnableViewState="false"
358 />
359 </td>
360 <%-- Create a second table column. --%>
361 <td valign="top">
362 <%-- Put the input form (a second table) in the second
363 column. --%>
364 <table style="font: 8pt verdana">
365 <tr>
366 <td colspan="2" bgcolor="#aaaadd"
367 style="font:10pt verdana">
368 Add a New Author:
369 </td>
370 </tr>
371 <tr>
372 <td nowrap>Author ID:</td>
373 <td><input type="text" id="au_id" value="000-00-0000"
374 runat="server"></td>
375 </tr>
376 <tr>
377 <td nowrap>Last Name:</td>
378 <td><input type="text" id="au_lname" value="Doe"
379 runat="server"></td>
380 </tr>
381 <tr nowrap>
382 <td>First Name:</td>
383 <td><input type="text" id="au_fname" value="John"
384 runat="server"></td>
385 </tr>
386 <tr>
387 <td>Phone: </td>
388 <td>
389 <input type="text" id="phone" value="808 555-5555"
390 runat="server">
391 </td>
392 </tr>
393 <tr>
394 <td>Address:</td>
395 <td>
396 <input type="text" id="address" value =
397 "One Microsoft Way" runat="server">
398 </td>
399 </tr>
400 <tr>
401 <td>City:</td>
402 <td><input type="text" id="city" value="Redmond"
403 runat="server"></td>
404 </tr>
405 <tr>
406 <td>State: </td>
407 <td>
408 <select id="state" runat="server">
409 <option>CA</option>
410 <option>IN</option>
411 <option>KS</option>
412 <option>MD</option>
413 <option>MI</option>
414 <option>OR</option>
415 <option>TN</option>
416 <option>UT</option>
417 </select>
418 </td>
419 </tr>
420 <tr>
421 <td nowrap>Zip Code:</td>
422 <td><input type="text" id="zip" value="98005"
423 runat="server"></td>
424 </tr>
425 <tr>
426 <td>Contract: </td>
427 <td>
428 <select id="contract" runat="server">
429 <option value="0">False</option>
430 <option value="1">True</option>
431 </select>
432 </td>
433 </tr>
434 <tr>
435 <td></td>
436 <td style="padding-top:15">
437 <input type="submit" OnServerClick=
438 "AddAuthor_Click" value="Add Author"
439 runat="server">
440 </td>
441 </tr>
442 <tr>
443 <td colspan="2" style="padding-top:15"
444 align="center">
445 <span id="Message" EnableViewState="false"
446 style="font: arial 11pt;" runat="server"/>
447 </td>
448 </tr>
449 </table>
450 </td>
451 </tr>
452 </table>
453 </form>
454</body>
455</html>