'---------------------------------------------------------- ' USING ADO & MS ACCESS 2003 ' STEP 3 '---------------------------------------------------------- 'Step 3 lets you add new records to the grid we used in Step 2 'SPECIAL NOTES: ' This time the columns in the grid have been converted to Templates. This allows me to ' use the footer (which is normally not visible) as a new row to add a new record if they want to. ' In step 2 we didn't do that. We just used the built-in editing features of the grid ' The update event traps concurrency errors ' The template columns are created in the Property Builder. 'Updated 10/1/05 '---import the usual suspects! Imports System.Data Imports System.Data.OleDb Public Class Step_3 Inherits System.Web.UI.Page Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load '---we only want to fill the data adapter once so only fill it the first time the page is called. Try '---don't show edit or delete buttons yet DataGrid1.Columns(0).Visible = False 'column 0 is where I put the Update/Cancel buttons DataGrid1.Columns(9).Visible = False 'column 9 is where I put the delete link to remove a record DataGrid1.ShowFooter = False 'we don't let them add a new record yet If Not IsPostBack Then BindMyGrid() End If Catch '---show the label if we bomb out lblError.Visible = True End Try End Sub Private Sub BindMyGrid() '---this fills the DS using the DA fill method. We pass the name of the DS and the table name. ' To keep things simple, I always use the real name of the DB table here. OleDbDataAdapter1.Fill(DsStep31, "customers") DataGrid1.DataBind() End Sub Private Sub btnEditRecords_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditRecords.Click '---show the first column in grid which shows the edit buttons DataGrid1.Columns(0).Visible = True DataGrid1.Columns(9).Visible = True DataGrid1.ShowFooter = True '---hide this guy so they can't get into editing mode again btnEditRecords.Visible = False End Sub Private Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.EditCommand '---get the row they want to edit DataGrid1.EditItemIndex = e.Item.ItemIndex '---now show the update/cancel buttons DataGrid1.Columns(0).Visible = True BindMyGrid() End Sub Private Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.CancelCommand '---this turns off grid editing DataGrid1.EditItemIndex = -1 btnEditRecords.Visible = True BindMyGrid() End Sub Private Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.DeleteCommand BindMyGrid() '---delete the row in the dataset then update the DB DsStep31.Customers.Rows.Item(e.Item.ItemIndex).Delete() OleDbDataAdapter1.Update(DsStep31, "customers") '---turn off editing in the grid DataGrid1.EditItemIndex = -1 btnEditRecords.Visible = True '---now refill the grid from the database. This will show you that the actual DB table has been updated. BindMyGrid() End Sub Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.UpdateCommand Try '---REFILL OUR DATASET FIRST OR NONE OF THIS WORKS!!!!! BindMyGrid() '---now create a new temp table in the image/structure of our Customers Table Using our EXISTING DS. Use the ' Dataset name you reference in Solution Explorer! Dim MyTable As DsStep3.CustomersDataTable MyTable = DsStep31.Customers 'now assign it the structure Dim Row2Update As DsStep3.CustomersRow 'make a new row to hold our changes '---which row are they editing in the grid? This is passed into this sub from the DataGrid CommandEventArgs OBJECT. ' This object gives us the index number of the row being edited in the grid starting with row 0. Row2Update = MyTable.Rows(e.Item.ItemIndex) '---now grab the data from each text box and assign it to our table row 'make sure these textbox control names match what is on the grid templates. Edit the grid 'templates to view the names of the textboxes it put in for us! Row2Update.LastName = CType(e.Item.FindControl("textbox1"), TextBox).Text Row2Update.FirstName = CType(e.Item.FindControl("textbox2"), TextBox).Text Row2Update.MiddleInit = CType(e.Item.FindControl("textbox3"), TextBox).Text Row2Update.StreetAddress = CType(e.Item.FindControl("textbox4"), TextBox).Text Row2Update.City = CType(e.Item.FindControl("textbox5"), TextBox).Text Row2Update.State = CType(e.Item.FindControl("textbox6"), TextBox).Text Row2Update.Zip = CType(e.Item.FindControl("textbox7"), TextBox).Text Row2Update.CustID = CType(e.Item.FindControl("textbox8"), TextBox).Text '---make it so!!! using the update method of our DA OleDbDataAdapter1.Update(DsStep31, "customers") 'use the name of the DS object! '---turn off editing in the grid DataGrid1.EditItemIndex = -1 btnEditRecords.Visible = True '---now refill the grid from the database. This will show you that the actual DB table has been updated. BindMyGrid() Catch ex As DBConcurrencyException 'make sure two users aren't conflicting Response.Redirect("ConcurrencyError.aspx") BindMyGrid() Catch lblError.Visible = True lblError.Text = "I was unable to update records" End Try End Sub Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand '---this sub runs just before the page is actually rendered so we can trap stuff in here '---be sure to type in a new commandname property for the Add New button in the footer template. ' You can call it anything but we will refer to it here. I called mine "AddNew". '---now test whether they clicked btnAddNew and grab the stuff from each textbox. Try If e.CommandName = "AddNew" Then Dim t1 = CType(e.Item.FindControl("txtNewLast"), TextBox).Text Dim t2 = CType(e.Item.FindControl("txtNewFirst"), TextBox).Text Dim t3 = CType(e.Item.FindControl("txtNewMiddle"), TextBox).Text Dim t4 = CType(e.Item.FindControl("txtNewStreet"), TextBox).Text Dim t5 = CType(e.Item.FindControl("txtNewCity"), TextBox).Text Dim t6 = CType(e.Item.FindControl("txtNewState"), TextBox).Text Dim t7 = CType(e.Item.FindControl("txtNewZip"), TextBox).Text Dim t8 = CType(e.Item.FindControl("txtNewCustID"), TextBox).Text '---make a SQL string so the dataadapter can use it to change the database records. SQL uses the INSERT INTO not "add" Dim SQL As String SQL = "INSERT INTO customers(lastname,firstname,MiddleInit,StreetAddress,City,State,Zip,CustID)" SQL &= " values('" & t1 & "','" & t2 & "','" & t3 & "','" & t4 & "','" & t5 & "','" & t6 & "','" & t7 & "','" & t8 & "')" '---now load the command object and tell it which connection object to use Dim cmd As New OleDbCommand(SQL, OleDbConnection1) OleDbConnection1.Open() 'we have to do this manually '---we tell the DA what the insert command is. The DA made them for us from the wizard but now we are changing it ' and we want it todo a specific insert. OleDbDataAdapter1.InsertCommand = cmd cmd.ExecuteNonQuery() 'this method is used to insert, update, or delete w/o using a dataset OleDbConnection1.Close() 'we have to close manually. ALWAYS CLOSE AS SOON AS YOU CAN! btnEditRecords.Visible = True BindMyGrid() End If Catch lblError.Text = "I could not add new record..." lblError.Visible = True Finally OleDbConnection1.Close() 'this is good practice just to make sure it is closed End Try End Sub Private Sub btnCancelEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelEdit.Click '---This is the big button at the top of the grid...not the cancel button you see when they are editing '---turn off editing in the grid DataGrid1.EditItemIndex = -1 btnEditRecords.Visible = True BindMyGrid() End Sub Private Sub btnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuit.Click '---end this beauty Response.Write("") End Sub End Class