Washington State University



left43850600Navigating Rows and Editing Database Records – Featherman ?This program connects a webpage to the feathermanAnalytics.POSCustomers table so that customer master data (data which does not change often) can be updated. Three different interfaces are used, in each case one row of data is retrieved onto screen in a list selectionindex changed procedure, and another procedure runs a SQL UPDATE SET command to push any edits/updates from the webpage controls back to the correct customer record in the SQL Sewrver database table in Pullman WA.The SQL DB table schema is shown with its current 4 customers.Imports System.DataImports System.Data.SqlClientPartial Class NavigatingRecords Inherits System.Web.UI.PageAdd a connection here#End Region 'This program shows 3 different ways to edit and update SQL Database values for one customer at a time. 102871010900 'View #1 - The webpage (shown above) uses a dropdown list to select one customer. In the DDL.selectedindex changed procedure several columns of data are retrieved for the selected customer and displayed in webpage controls. A second button procedure takes the control values and saves them back to the database using a command builder and data adapter update methodology. 'View #2 - The webpage uses 'VCR controls' to move amongst the records. You can edit the selected record. The classic SQL UPDATE SET command is used with parameters to update any column in the database table. 'View #3 - The webpage uses a radiobuttonlist to select one customer record and pull that record from the SQL database into webpage controls for update. The classic SQL UPDATE SET command is used to update the customer record. 'You get more familiar with SQL SELECT commands, SQL UPDATE SET commands, and the pulling of data from a datatable into webform screen controls, and how to scrape the values from the webform controls and push them back to the SQL Server database. This data adapter is used to retrieve 1 customer record68580152400 Public Shared gdaOneCustomer As New SqlDataAdapter("SELECT * from POSCustomers WHERE Customer_ID = @p1", con) 'Here we add the SQLCommandBuilder object that generates the UPDATE SET command with no SQL code. If you are updating values in one table, the dataAdapter.Update command is ueful (even just for a prototype). If you are updating values in a different table then use the SQLCommand and an UPDATE SET SQL statement. The SQLCOmmandbuilder builds SQL INSERT/UPDATE/DELETE statements that can take the changes from the in-memory datatable and push them back onto the SQL database. Public Shared cbUpdateCustomer As New SqlCommandBuilder(gdaOneCustomer) Public Shared gdtOneCustomer, gdtOneCustomerView3 As New DataTable Public Shared gdtCustomers, gdtCustomersView3 As New DataTable Public Shared gintRow, gintCustomer As Integer 'used to navigate records.#Region "Page initialization - load list controls" Protected Sub Page_Init(sender As Object, e As EventArgs) Handles Me.Init 'this procedure runs a SQL SELECT statement to retrieve all the data from the POSCustomers on Featherman's SQL Server database table into a dropdownlist on View#1 and radiobuttonList on View #3 Dim daGetCustomers As New SqlDataAdapter("SELECT * FROM POSCustomers", con) 'clear out any possible prior data from the on-screen table If gdtCustomers.Rows.Count > 0 Then gdtCustomers.Rows.Clear() Try 'this next line of code runs the SQL SELECT statement created in the data adapter code above daGetCustomers.Fill(gdtCustomers) With DropDownList1 .DataSource = gdtCustomers .DataTextField = "CustomerName" .DataValueField = "Customer_ID" .DataBind() .Items.Insert(0, "Select a customer") End With With RadioButtonList4 .DataSource = gdtCustomers .DataTextField = "CustomerName" .DataValueField = "Customer_ID" .DataBind() End With Catch ex As Exception Response.Write(ex.Message) End Try 'set up some formatting of the link buttons LinkButton1.BorderStyle = BorderStyle.None LinkButton1.Font.Bold = True LinkButton2.BorderStyle = BorderStyle.None LinkButton2.Font.Bold = True LinkButton3.BorderStyle = BorderStyle.None LinkButton3.Font.Bold = True End Sub '--------------------------------- View #1 Table and SQL Update Set Procedures -------------------------------------------#Region "View #1 - Retrieving data for 1 customer" Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged 'if no customer was selected in the dropdown list then stop the program If DropDownList1.SelectedIndex <= 0 Then Exit Sub 'clear out the prior customer's info from the global data table. This is important because when the datatable is global, it will retain rows of data from prior processing. If gdtOneCustomer.Rows.Count > 0 Then gdtOneCustomer.Rows.Clear() 'We want to retrieve just one row of data for one customer from the SQL Database table. Our SQL SELECT statement will be parameterized so this next code adds the parameter that will pull the value the program user selects from the dropdown list and inserts that value into the WHERE portion of the SQL SELECT statement. With gdaOneCustomer.SelectCommand.Parameters .Clear() .AddWithValue("@p1", DropDownList1.SelectedValue) End With 'the red code in the global dataAdapter is the SQL statement that will be executed against Featherman's SQL server table named POSCustomers. In effect we retrieve one row of data from the database into the first row of a data table. and display it onscreen. The sytax again is ' "SELECT * from POSCustomers WHERE Customer_ID = @p1. One row of data is retrieved and copied into a datatable and then displayed in a gridview, and some of the columns are displayed in webform controls that enable editing of the values. Try gdaOneCustomer.Fill(gdtOneCustomer) GridView1.DataSource = gdtOneCustomer GridView1.DataBind() 'Here we alter the webpage depending on the value in the VIP column. If the selected customer is a VIP then a popup checkbox with the label is shown. This checkbox was a requirement, as the program user is trained to spark up product promotions for VIP customers. With gdtOneCustomer.Rows(0) If .Item("VIP") = True Then chkVIP.Checked = True chkVIP.Visible = True Else chkVIP.Checked = False chkVIP.Visible = False End If 'take the values in the columns and assign them to textboxes TextBox1.Text = .Item("CustomerName") TextBox2.Text = .Item("Address") TextBox3.Text = .Item("City") TextBox4.Text = .Item("State") TextBox5.Text = .Item("Zip") End With Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "View #1 - Pushing webform changes back to database with dataAdapter.Update" Protected Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click 'this procedure is for an employee that only has read/write access to the demographic information columns not the rewards level or analytics columns Try With gdtOneCustomer.Rows(0) .Item("CustomerName") = TextBox1.Text .Item("Address") = TextBox2.Text .Item("City") = TextBox3.Text .Item("State") = TextBox4.Text .Item("Zip") = TextBox5.Text End With 'this next line will update the SQL database table. We do not have to create the UPDATE SET SQL statement gdaOneCustomer.Update(gdtOneCustomer) 'the next row shows a nice informational message to the program user. take note of how you can access and use a columns's value from the datatable Label6.Text = "Data for " & gdtOneCustomer.Rows(0).Item("CustomerName") & " updated" Label6.Visible = True 'after changing the value in the AvailableCredit column, we need to refresh the datasource of the gridview to show the changes. With GridView1 .DataSource = Nothing .DataSource = gdtOneCustomer .DataBind() End With Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region '--------------------------------- View #2 SQL Update Set Procedures -------------------------------------------#Region "View #2 - Part 1 Using VCR Next/Previous Link buttons to Select Data"365888900 'This set of 5 procedures is a) the major workhorse procedure named Showdata() and b) 4 nearly identical smaller procedures that call the major procedure to do their purpose of changing row numbers in the datatable (which has the effect of displaying the customer information for a different customer. The 4 procedures are used to navigate from row to row in the data table, showing the data for one customer on screen so it can be edited. After waiting for the program user to make changes to the customer record, the button3 procedure below is a button on the webform that uses a parameterized UPDATE SET SQL statement to save the current values in the webform controls back to the database. The trick to learning this is to take note of the row number. When you increment the row number for example +=1 or -=1 then the major workhorse Showdata() procedure is called to display the next row of data from the table into the on screen controls Private Sub ShowData() 'This procedure pulls values from a row of data into the controls on a webpage. Textboxes and checkboxes are safest to use. (Not shown here but) you can also use a radiobuttonlist if you use its' .selected property rather than the .checked property shown below. This code takes the column values for the current row and displays them in controls. The row# for the current row is passed in as a global variable which is genious. With gdtCustomers.Rows(gintRow) Label1.Text = .Item("Customer_ID") txtName.Text = .Item("CustomerName") txtAddress.Text = .Item("Address") txtCity.Text = .Item("City") txtState.Text = .Item("State") txtZip.Text = .Item("Zip") 'pretty cool you can set whether a checkbox is checked or not depending on the value in the database column CheckBox1.Checked = .Item("VIP") 'this is how you take the value from the database column (whose values are stored as 1,2,3) and use that to set which item is selected in a radiobutton list. We have to subtract 1 from the database values since the index of the items is 0-based RadioButtonList2.SelectedIndex = Convert.ToInt16(.Item("RewardsLevel")) - 1 Label4.Text = Nothing Label4.Visible = False End With End Sub Protected Sub lnkFirst_Click(sender As Object, e As EventArgs) Handles lnkFirst.Click 'the first row of the datatable is row 0. Setting this here ensures we show data for the first customer gintRow = 0 ShowData() End Sub Protected Sub lnkLast_Click(sender As Object, e As EventArgs) Handles lnkLast.Click 'if we use the datatable.rows.count property we can show the data for the last row. We have to subtract one from the count because the row positions start at 0 gintRow = gdtCustomers.Rows.Count - 1 ShowData() End Sub Protected Sub lnkPrevious_Click(sender As Object, e As EventArgs) Handles lnkPrevious.Click 'show the previous row of data unless you are at the first row already. If you are already at the first row (first customer) row 0, then show the last row of data when the program user presses the back button. If gintRow = 0 Then gintRow = gdtCustomers.Rows.Count - 1 ShowData() Else gintRow -= 1 ShowData() End If End Sub Protected Sub lnkNext_Click(sender As Object, e As EventArgs) Handles lnkNext.Click 'show the next row of data unless you are at the bottom row of the datatable, then 'wrap around' to show the first row If gintRow = gdtCustomers.Rows.Count - 1 Then gintRow = 0 ShowData() Else gintRow += 1 ShowData() End If End Sub#End Region#Region "View #2 - Part 2 - Using UPDATE SET SQL transactions to update Customer Master Data" Protected Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click 'Take the values from the controls on the 2nd tab, pass them into the parameters then push the values back to the database, to update one row of data. The updated and current column values from the webpage controls are passed back to the SQL Server database table using the UPDATE SET SQL statement. The UPDATE SET command is explained elsewhere, but essentially reads UPDATE tablename SET column1 value to the value passed in from the paramter1, repeat...WHERE the customer_ID is the value show in the radiobutton list. Dim cmdUpdate As New SqlCommand("UPDATE POSCustomers SET CustomerName = @p1, Address = @p2, City = @p3, State = @p4, Zip = @p5, VIP = @p6, RewardsLevel = @p7 WHERE Customer_ID = @p8", con) 'this is how you pull the values from the webform into the parameters (into variables) With cmdUpdate.Parameters .Clear() .AddWithValue("@p1", txtName.Text) .AddWithValue("@p2", txtAddress.Text) .AddWithValue("@p3", txtCity.Text) .AddWithValue("@p4", txtState.Text) .AddWithValue("@p5", txtZip.Text) .AddWithValue("@p6", CheckBox1.Checked) .AddWithValue("@p7", RadioButtonList2.SelectedValue) .AddWithValue("@p8", Label1.Text) End With Try If con.State = ConnectionState.Closed Then con.Open() cmdUpdate.ExecuteNonQuery() Label4.Text = "Data for " & txtName.Text & " updated" Label4.Visible = True Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try End Sub#End Region '--------------------------------------View #3 SQL Update Set Procedures -------------------------------------3658182900#Region "View #3 - Retrieving One Data Record" Private Sub RadioButtonList4_SelectedIndexChanged(sender As Object, e As EventArgs) Handles RadioButtonList4.SelectedIndexChanged 'This procedure pulls data from the database onto the webform depending on which customer is selected in the radiobuttonlist. Dim daGetOneCustomer As New SqlDataAdapter("SELECT * from POSCustomers WHERE Customer_ID = @p1", con) 'if a customer was selected then clear the prior customer's data from the datatable, otherwise do nothing If RadioButtonList4.SelectedIndex = -1 Then Exit Sub Else gdtOneCustomerView3.Rows.Clear() End If With daGetOneCustomer.SelectCommand.Parameters .Clear() .AddWithValue("@p1", RadioButtonList4.SelectedValue) End With 'open the connection pull the data into the global datatable then assign the values to the controls on view 3 Try If con.State = ConnectionState.Closed Then con.Open() daGetOneCustomer.Fill(gdtOneCustomerView3) With gdtOneCustomerView3.Rows(0) Label2.Text = .Item("Customer_ID") txtName0.Text = .Item("CustomerName") txtAddress0.Text = .Item("Address") txtCity0.Text = .Item("City") txtState0.Text = .Item("State") txtZip0.Text = .Item("Zip") CheckBox2.Checked = .Item("VIP") RadioButtonList3.SelectedIndex = Convert.ToInt16(.Item("RewardsLevel")) - 1 End With Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try Label5.Text = Nothing Label5.Visible = False End Sub#End Region#Region "View #3 - Updating and Displaying One Data Record" Protected Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click 'this command takes the values from the screen (that may have been updated) and assignes the values to the parameters, and then pushed back to the database Dim cmdUpdate As New SqlCommand("UPDATE POSCustomers SET CustomerName = @p1, Address = @p2, City = @p3, State = @p4, Zip = @p5, VIP = @p6, RewardsLevel = @p7 WHERE Customer_ID = @p8", con) 'this data adapter will go fetch the updated table of data (well just one row changed) and is run AFTER the UPDATE set command is run Dim daGetCustomers As New SqlDataAdapter("Select * from POSCustomers", con) 'f there is no data on the form then stop the processing, as no customer was selected If txtName0.Text = Nothing Then Exit Sub With cmdUpdate.Parameters .Clear() .AddWithValue("@p1", txtName0.Text) .AddWithValue("@p2", txtAddress0.Text) .AddWithValue("@p3", txtCity0.Text) .AddWithValue("@p4", txtState0.Text) .AddWithValue("@p5", txtZip0.Text) .AddWithValue("@p6", CheckBox2.Checked) .AddWithValue("@p7", RadioButtonList3.SelectedValue) .AddWithValue("@p8", RadioButtonList4.SelectedValue) End With Try 'first run the update command to update the underlying SQL data If con.State = ConnectionState.Closed Then con.Open() cmdUpdate.ExecuteNonQuery() con.Close() 'clear out the datatable and gridview, then fill it with the updated data, all the rows are retrieved. If gdtCustomersView3.Rows.Count > 0 Then gdtCustomersView3.Rows.Clear() daGetCustomers.Fill(gdtCustomersView3) GridView2.DataSource = gdtCustomersView3 GridView2.DataBind() Label5.Text = "Data record for " & txtName0.Text & " updated" Label5.Visible = True ClearView3() Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "Clearing Views" Protected Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click 'clear the webform's view #1. Deleting any rows from the datatable and rebinding the gridview has the effect of clearing the gridview If gdtOneCustomer.Rows.Count > 0 Then gdtOneCustomer.Rows.Clear() GridView1.DataSource = gdtOneCustomer GridView1.DataBind() 'clear the form's controls DropDownList1.SelectedIndex = -1 TextBox1.Text = Nothing TextBox2.Text = Nothing TextBox3.Text = Nothing TextBox4.Text = Nothing Label6.Visible = False Label6.Text = Nothing chkVIP.Visible = False End Sub Private Sub Clear() 'a slightly different procedure to clear out the controls on view #1 chkVIP.Visible = False DropDownList1.SelectedIndex = -1 TextBox1.Text = Nothing Label6.Visible = False Label6.Text = Nothing gdtOneCustomer.Rows.Clear() GridView1.DataBind() End Sub Private Sub ClearView3() 'clear out the webform controls on View #3 txtName0.Text = Nothing txtAddress0.Text = Nothing txtCity0.Text = Nothing txtState0.Text = Nothing txtZip0.Text = Nothing CheckBox2.Checked = False RadioButtonList3.SelectedIndex = -1 RadioButtonList4.SelectedIndex = -1 Label2.Text = Nothing End Sub#End Region#Region "Linkbuttons used to switch amongst the views" Protected Sub LinkButton1_Click(sender As Object, e As EventArgs) Handles LinkButton1.Click 'Switch to the first View, clear the data from the screen controls, MultiView1.ActiveViewIndex = 0 gdtOneCustomer.Rows.Clear() Clear() TextBox1.Text = Nothing TextBox2.Text = Nothing TextBox3.Text = Nothing TextBox4.Text = Nothing 'put some formatting onto the first linkbutton only to show it has been selected LinkButton1.Font.Bold = True LinkButton1.BorderStyle = BorderStyle.Dashed LinkButton2.Font.Bold = False LinkButton2.BorderStyle = BorderStyle.None LinkButton3.Font.Bold = False LinkButton3.BorderStyle = BorderStyle.None End Sub Protected Sub LinkButton2_Click(sender As Object, e As EventArgs) Handles LinkButton2.Click 'Switch to the second View MultiView1.ActiveViewIndex = 1 'show the data for the first customer gintRow = 0 ShowData() 'this subprocedure parses out the columns of data to the appropriate comtrols on the webform, checkbox, radiobuttonlist and textboxes. Be sure to go find it and take a look at what it does 'put some formatting onto the second linkbutton only to show it has been selected LinkButton1.Font.Bold = False LinkButton1.BorderStyle = BorderStyle.None LinkButton2.Font.Bold = True LinkButton2.BorderStyle = BorderStyle.Dashed LinkButton3.Font.Bold = False LinkButton3.BorderStyle = BorderStyle.None End Sub Protected Sub LinkButton3_Click(sender As Object, e As EventArgs) Handles LinkButton3.Click 'switch to view #3 and clear the screen MultiView1.ActiveViewIndex = 2 'when switching to View #3 clear out any prior data records RadioButtonList4.SelectedIndex = -1 ClearView3() 'clear out the datatable and gridview so the form is fully clear. gdtCustomersView3.Rows.Clear() GridView2.DataSource = gdtCustomers GridView2.DataBind() 'put some formatting onto the third linkbutton only to show it has been selected LinkButton1.Font.Bold = False LinkButton1.BorderStyle = BorderStyle.None LinkButton2.Font.Bold = False LinkButton2.BorderStyle = BorderStyle.None LinkButton3.BorderStyle = BorderStyle.Dashed LinkButton3.Font.Bold = True End Sub#End RegionEnd Class ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download