1 / 44

ASP.NET Binding and an Introduction to Database Queries

ASP.NET Binding and an Introduction to Database Queries. Please use speaker notes for additional information!. Since Page_Load is run every time the page is loaded this can be used to control running only on first time. <html> <head><title>My schedule</title> <script runat="server">

keaton
Télécharger la présentation

ASP.NET Binding and an Introduction to Database Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ASP.NET Binding and an Introduction to Database Queries Please use speaker notes for additional information!

  2. Since Page_Load is run every time the page is loaded this can be used to control running only on first time. <html> <head><title>My schedule</title> <script runat="server"> sub Page_Load if Not Page.IsPostBack then dim myCourses=New ArrayList myCourses.Add("CIS13") myCourses.Add("CIS44") myCourses.Add("CIS45") myCourses.Add("CIS46") myCourses.Add("CIS47") myCourses.Add("CIS48") myCourses.Add("CIS49") myCourses.Add("CIS50") myCourses.Add("CIS53") myCourses.Add("CIS63") myCourses.Sort() crs.DataSource=myCourses crs.DataBind() end if end sub sub showSchedule(s as Object,e As EventArgs) lblSchedule.text=lblSchedule.text & " " & crs.SelectedItem.Text end sub </script> </head> <body> <form runat="server"> <asp:DropDownList id="crs" runat="server" AutoPostBack="True" onSelectedIndexChanged="showSchedule" /> <p><asp:label id="lblSchedule" runat="server" /></p> </form> </body> </html> The ArrayList is myCourses, the DropDown list has an id of crs. The DataSource property of the DropDownList is set to the Array List name and the DataBind() method of the DropDownList binds the data.

  3. <html> <head><title>My schedule</title> </head> <body> <form name="_ctl0" method="post" action="dropdownlist.aspx" id="_ctl0"> <input type="hidden" name="__EVENTTARGET" value="" /> <input type="hidden" name="__EVENTARGUMENT" value="" /> <input type="hidden" name="__VIEWSTATE" value="dDwzOD…more data...U/85vaFqJtyYA==" /> <script language="javascript"> <!-- function __doPostBack(eventTarget, eventArgument) { var theform = document._ctl0; theform.__EVENTTARGET.value = eventTarget; theform.__EVENTARGUMENT.value = eventArgument; theform.submit(); } // --> </script> <select name="crs" id="crs" onchange="__doPostBack('crs','')" language="javascript"> <option value="CIS13">CIS13</option> <option value="CIS44">CIS44</option> <option value="CIS45">CIS45</option> <option value="CIS46">CIS46</option> <option value="CIS47">CIS47</option> <option value="CIS48">CIS48</option> <option value="CIS49">CIS49</option> <option value="CIS50">CIS50</option> <option value="CIS53">CIS53</option> <option selected="selected" value="CIS63">CIS63</option> </select> <p><span id="lblSchedule"> CIS47 CIS50 CIS63</span></p> </form> </body> </html>

  4. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDB" %> <Script Runat="Server"> Sub Page_Load Dim sPath As String = Server.MapPath("\YourUserName\db\YourDB.mdb") Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & sPath & ";" Dim objConn As OleDbConnection Dim cmdSelect As OleDbCommand Dim dtrReader As OleDbDataReader Dim strResults As String objConn = New OleDbConnection( connString ) objConn.Open() cmdSelect= New OleDbCommand( "SELECT SomeField From SomeTable", objConn) dtrReader= cmdSelect.ExecuteReader() While dtrReader.Read() strResults = strResults & dtrReader("SomeField") & "<br>" End While lblResults.Text = strResults dtrReader.Close() objConn.Close() End Sub </Script> <html> <body> <asp:Label id="lblResults" Runat="Server"/> </body> </html> Suggested code from www.brinkster.com for using an Access database. “General Users can mimic the following code snipplet to connect to an MS Access database using ASP.Net (VB).”

  5. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDB" %> <Script Runat="Server"> Sub Page_Load Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & sPath & ";" Dim objConn As OleDbConnection Dim cmdSelect As OleDbCommand Dim dtrReader As OleDbDataReader Dim strResults As String objConn = New OleDbConnection( connString ) objConn.Open() cmdSelect= New OleDbCommand( "SELECT DName From Donor2000", objConn) dtrReader= cmdSelect.ExecuteReader() While dtrReader.Read() strResults = strResults & dtrReader("DName") & "<br>" End While lblResults.Text = strResults dtrReader.Close() objConn.Close() End Sub </Script> <html> <body> <asp:Label id="lblResults" Runat="Server"/> </body> </html> I uploaded donor.mdb to my site at brinkster in the db directory. This is the path. Output

  6. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDB" %> <Script Runat="Server"> Sub Page_Load Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & sPath & ";" Dim objConn As OleDbConnection Dim cmdSelect As OleDbCommand Dim dtrReader As OleDbDataReader Dim strResults As String objConn = New OleDbConnection( connString ) objConn.Open() cmdSelect= New OleDbCommand( "SELECT DIdno, DName From Donor2000", objConn) dtrReader= cmdSelect.ExecuteReader() While dtrReader.Read() strResults = strResults & dtrReader("DIdno") & " " & dtrReader("DName") & "<br>" End While lblResults.Text = strResults dtrReader.Close() objConn.Close() End Sub </Script> <html> <body> <asp:Label id="lblResults" Runat="Server"/> </body> </html>

  7. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <asp:DataGrid id="dgResults" runat="server" />

  8. Results from program on previous page. Donor2000 table in the access database donor.mdb.

  9. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT DIdno, DName, DYrFirst FROM Donor2000 where DYrFirst =" & "'1996'" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <asp:DataGrid id="dgResults" runat="server" />

  10. <table cellspacing="0" rules="all" border="1" id="dgResults" style="border-collapse:collapse;"> <tr> <td>DIdno</td><td>DName</td><td>DYrFirst</td> </tr><tr> <td>22222</td><td>Mary Wilson</td><td>1996</td> </tr><tr> <td>44444</td><td>Robert Brooks</td><td>1996</td> </tr> </table>

  11. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT DIdno, DName, DYrFirst FROM Donor2000 order by DYrFirst" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <asp:DataGrid id="dgResults" runat="server" />

  12. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT DName, DCity FROM Donor2000 Where DCity = " & "'Fall River'" & " or " & "Dcity = " & "'Braintree'" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <asp:DataGrid id="dgResults" runat="server" /> Note that I coded this all on one line and showed it on two lines only for the presentation.

  13. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT Donor2000.DIdno, DName, DAmtCont FROM Donor2000, Donation2000 Where Donor2000.DIdno = Donation2000.DIdno" Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <asp:DataGrid id="dgResults" runat="server" /> The SELECT was coded on one line, I made it two lines to fit on the slide.

  14. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT Donor2000.DIdno, DName, DAmtCont FROM Donor2000, " & _ "Donation2000 Where Donor2000.DIdno = Donation2000.DIdno" Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <asp:DataGrid id="dgResults" runat="server" />

  15. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT Donor2000.DIdno, DName, Donation2000.DDriveNo, DDriveName, DAmtCont FROM Donor2000, Donation2000, Drive2000 Where Donor2000.DIdno = Donation2000.DIdno and Donation2000.DDriveNo = Drive2000.DDriveNo" Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <asp:DataGrid id="dgResults" runat="server" /> The SELECT was coded on one line, I wrapped when I pasted it on the slide.

  16. Before Search

  17. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() end sub

  18. sub Get_Record(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000 where DName = '" & nameInput.Text & "'" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <body> <form ID="NameForm" runat="server"> Enter the Donor Name: <asp:TextBox id="nameInput" ForeColor="blue" runat="server" /> <asp:Button Text="Submit" OnClick="Get_Record" runat="server" /> </form> <asp:DataGrid id="dgResults" runat="server" />

  19. Prior to doing the add. After doing the Add and the the Display.

  20. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() end sub

  21. sub Add_Record(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "INSERT INTO Donor2000 VALUES('55555','Susan Ash', '12 Oak St', 'Braintree', 'MA', '02184','1997', 'Ann Smith')" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub Note that I coded this as one long string - I wrapped when I pasted on the slide.

  22. sub Show_Records(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "Select * from Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <body> <form ID="NameForm" runat="server"> <asp:Button Text="Add" OnClick="Add_Record" runat="server" /> <asp:Button Text="Display" OnClick="Show_Records" runat="server" /> </form> <asp:DataGrid id="dgResults" runat="server" />

  23. After the add of 55555. This shows the screen after the submit has deleted the record. I then went back and ran datatest.aspx which shows all of the records - 55555 is not there.

  24. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() end sub sub Delete_Record(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";"

  25. 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "DELETE FROM Donor2000 WHERE DIdno = '55555'" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <body> <form ID="NameForm" runat="server"> <asp:Button Text="Submit" OnClick="Delete_Record" runat="server" /> </form> <asp:DataGrid id="dgResults" runat="server" />

  26. I filled in the textboxes, clicked add and then clicked display.

  27. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() end sub sub Add_Record(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";"

  28. 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "INSERT INTO Donor2000 VALUES('" & idInput.Text & "', '" & nameInput.Text & "', '" & adrInput.Text & "', '" & cityInput.Text & "', '" & stateInput.Text & "', '" & zipInput.Text & "', '" & yrfstInput.Text & "', '" & contInput.Text & "')" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub sub Show_Records(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" I wrote this code as one long statement - easier I think to keep track of the quotes. I then wrapped when I pasted on the slide.

  29. 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "Select * from Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <body> <form ID="NameForm" runat="server"> Enter the Donor ID: <asp:TextBox id ="idInput" ForeColor="blue" runat="server" /><br /> Enter the Donor Name: <asp:TextBox id="nameInput" ForeColor="blue" runat="server" /><br /> Enter the Donor Address: <asp:TextBox id="adrInput" ForeColor="blue" runat="server" /><br /> Enter the Donor City: <asp:TextBox id="cityInput" ForeColor="blue" runat="server" /><br /> Enter the Donor State: <asp:TextBox id="stateInput" ForeColor="blue" runat="server" /><br /> Enter the Donor ZIP: <asp:TextBox id="zipInput" ForeColor="blue" runat="server" /><br /> Enter the Donor Year First: <asp:TextBox id="yrfstInput" ForeColor="blue" runat="server" /><br /> Enter the Donor Contact: <asp:TextBox id="contInput" ForeColor="blue" runat="server" /><br /> <asp:Button Text="Add" OnClick="Add_Record" runat="server" /> <asp:Button Text="Display" OnClick="Show_Records" runat="server" /> </form> <asp:DataGrid id="dgResults" runat="server" />

  30. I entered 66666 and clicked Delete. Then I clicked Display The results of the display.

  31. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() end sub

  32. sub Delete_Record(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "DELETE FROM Donor2000 WHERE DIdno = " & "'" & idInput.Text & "'" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub

  33. sub Show_Records(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "Select * from Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() end sub </script> <body> <form ID="NameForm" runat="server"> Enter the Donor ID: <asp:TextBox id ="idInput" ForeColor="blue" runat="server" /><br /> <asp:Button Text="Delete" OnClick=”Delete_Record" runat="server" /> <asp:Button Text="Display" OnClick="Show_Records" runat="server" /> </form> <asp:DataGrid id="dgResults" runat="server" />

  34. I keyed in 66666

  35. I am going to give David a middle initial of A and change his contact to Roger Brown.

  36. Changes I keyed in.

  37. This shows the changes.

  38. <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) if Not Page.IsPostBack then Show_Records(sender, e) end if end sub sub Get_Record(sender as Object, e as EventArgs) enterlabel.visible = false getidInput.visible = false submitbtn.visible = false 'idlabel.visible = true 'idInput.visible = true namelabel.visible = true nameInput.visible = true addrlabel.visible = true addrInput.visible = true citylabel.visible = true cityInput.visible = true statelabel.visible = true stateInput.visible = true ziplabel.visible = true zipInput.visible = true yrlabel.visible = true yrInput.visible = true contlabel.visible = true contInput.visible = true updatebtn.visible = true displaybtn.visible = true 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";"

  39. 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000 where DIdno = '" & getidInput.Text & "'” 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader() 'Set the textbox values objDataReader.Read() idInput.text = objDataReader("DIdno") nameInput.text = objDataReader("DName") addrInput.text = objDataReader("DStAdr") cityInput.text = objDataReader("DCity") stateInput.text = objDataReader("DState") zipInput.text = objDataReader("DZip") yrInput.text =objDataReader("DYrFirst") contInput.text = objDataReader("DContact") objDataReader.Close() 'Do the DataBinding objDataReader = objCommand.ExecuteReader() dgResults.DataSource = objDataReader dgResults.DataBind() 'Close the datareader/db connection objDataReader.Close() objConnection.Close() end sub

  40. sub Update_Record(sender as Object, e as EventArgs) 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "UPDATE donor2000 SET " & _ "DName = '" & nameInput.text & "', " & _ "DStAdr = '" & addrInput.text & "', " & _ "DCity = '" & cityInput.text & "', " & _ "DState = '" & stateInput.text & "', " & _ "DZip = '" & zipInput.text & "', " & _ "DYrFirst = '" & yrInput.text & "', " & _ "DContact = '" & contInput.text & "' " & _ "WHERE DIdno = '" & idInput.text & "'" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) objCommand.ExecuteNonQuery objConnection.Close() Show_Records(sender, e) end sub

  41. sub Show_Records(sender as Object, e as EventArgs) enterlabel.visible = true getidInput.text = "" getidInput.visible = true submitbtn.visible = true idlabel.visible = false idInput.visible = false namelabel.visible = false nameInput.visible = false addrlabel.visible = false addrInput.visible = false citylabel.visible = false cityInput.visible = false statelabel.visible = false stateInput.visible = false ziplabel.visible = false zipInput.visible = false yrlabel.visible = false yrInput.visible = false contlabel.visible = false contInput.visible = false updatebtn.visible = false displaybtn.visible = false 'Create a connection string Dim sPath As String = Server.MapPath("\pgrocer\db\donor.mdb") Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & sPath & ";" 'Open a connection Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'Specify the SQL string Dim strSQL as String = "SELECT * FROM Donor2000" 'Create a command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection)

  42. 'Get a datareader Dim objDataReader as OleDbDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Do the DataBinding dgResults.DataSource = objDataReader dgResults.DataBind() end sub </script> <body> <form ID="NameForm" runat="server"> <br /><br /> <asp:DataGrid id="dgResults" runat="server" /><br /> <asp:label id="enterlabel" Text="Enter the Donor Id: " runat="server" /> <asp:TextBox id="getidInput" ForeColor="blue" runat="server" /> <asp:Button id="submitbtn" Text="Submit" OnClick="Get_Record" runat="server" /><br /><br /> <asp:Label id="idlabel" Text="Donor ID: " runat="server"/> <asp:TextBox id ="idInput" ForeColor="blue" runat="server" /><br /> <asp:label id="namelabel" Text="DonorName: " runat="server" /> <asp:TextBox id="nameInput" ForeColor="blue" runat="server" /><br /> <asp:label id="addrlabel" Text="Donor Address: " runat="server" /> <asp:TextBox id="addrInput" ForeColor="blue" runat="server" /><br /> <asp:label id="citylabel" Text="Donor City: " runat="server" /> <asp:TextBox id="cityInput" ForeColor="blue" runat="server" /><br /> <asp:label id="statelabel" Text="Donor State: " runat="server" /> <asp:TextBox id="stateInput" ForeColor="blue" runat="server" /><br /> <asp:label id="ziplabel" Text="Donor ZIP: " runat="server" /> <asp:TextBox id="zipInput" ForeColor="blue" runat="server" /><br /> <asp:label id="yrlabel" Text="Donor Year First: " runat="server" /> <asp:TextBox id="yrInput" ForeColor="blue" runat="server" /><br /> <asp:label id="contlabel" Text="Donor Contact: " runat="server" /> <asp:TextBox id="contInput" ForeColor="blue" runat="server" /><br /> <br /> <asp:Button id="updatebtn" Text="Update" OnClick="Update_Record" runat="server" /> <asp:Button id="displaybtn" Text="Display" OnClick="Show_Records" runat="server" /> </form>

More Related