asp.net - insert - delete -update datatables (disconnected datasets) shopping basket
TRANSCRIPT
![Page 1: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/1.jpg)
ASP.NET- insert- delete-update
DataTables (disconnected datasets)
Shopping Basket
![Page 2: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/2.jpg)
Insert
• Using the INSERT SQL statement with data hard-wired into it
• Using the INSERT SQL statement with placeholder variables to be filled by user input. – Parameters.Add used to fill placeholders
![Page 3: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/3.jpg)
Insert using SQL commandOpen connection to database as usual, then:
Dim dothis As OleDbCommandDim strInsert As String
strInsert = "INSERT INTO Company (CompanyName, City) VALUES ('Blue Bird', 'Leeds')"
dothis= New OleDbCommand(strInsert, objConn)dothis.ExecuteNonQuery
![Page 4: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/4.jpg)
Insert using Add method of OleDbCommandDim dothis As OleDbCommandDim strInsert As String
strInsert= "INSERT INTO Company (CompanyName, City) VALUES (@Company, @City)"
dothis= New OleDbCommand(strInsert, objConn)dothis.Parameters.Add("@Company",
txtCompany.Text)dothis.Parameters.Add("@City", txtCity.Text)
Dothis.ExecuteNonQuery
![Page 5: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/5.jpg)
Delete
• SQL DELETE command
• SQL DELETE with Parameters.Add
![Page 6: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/6.jpg)
Delete using SQLDim dothis As OleDbCommandDim strDelete As String
strDelete = “DELETE FROM” &;_ “Company WHERE CompanyName = ‘Blue Bird’”
dothis= New OleDbCommand(strDelete, objConn)
dothis.ExecuteNonQuery
![Page 7: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/7.jpg)
Delete using Add method of OleDbCommand
strDelete = “DELETE FROM Company WHERE CompanyName = @CompanyName”
dothis= New OleDbCommand(strDelete, objConn)
dothis.Parameters.Add(“@CompanyName”, txtCompanyName.Text)
dothis.ExecuteNonQuery
![Page 8: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/8.jpg)
Update
• Executing an SQL statement with the UPDATE command against the database.
• SQL UPDATE with Parameters.Add
![Page 9: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/9.jpg)
Update using SQL
strUpdate = “UPDATE JobList SET EstimatedSalary = EstimatedSalary * 1.10 WHERE JobTitle = ‘Network Administrators’”
dothis= New OleDbCommand(strUpdate, objConn)
dothis.ExecuteNonQuery
![Page 10: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/10.jpg)
Update using Add method of OleDbCommand
strUpdate = “Update JobList SET EstimatedSalary = @EstimatedSalary WHERE JobTitle = @JobTitle”
dothis= New OleDbCommand(strUpdate, objConn)dothis.Parameters.Add(“@EstimatedSalary”,txtEstimatedSalary.Text)dothis.Parameters.Add(“@JobTitle”, txtJobTitle.Text)dothis.ExecuteNonQuery
![Page 11: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/11.jpg)
DataTables
• Table stored in server memory• Disconnected DataSet
• Used for– Manipulating subsets of data– Shopping basket
![Page 12: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/12.jpg)
Defining a datatable1 – define columns
Dim objDT As System.Data.DataTable
objDT = New System.Data.DataTable("Cart")
objDT.Columns.Add("Product", GetType(String))objDT.Columns.Add("Quantity", GetType(Integer))objDT.Columns.Add("Cost", GetType(Decimal))
![Page 13: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/13.jpg)
Data Table - Header Row
Product Quantity Cost
After step 1
![Page 14: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/14.jpg)
Step 2 Adding a new row
Dim objDR As System.Data.DataRow
objDR = objDT.NewRow
Part A – create a new blank row that copies the table
![Page 15: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/15.jpg)
After Step 2a
Data Table (objDT)- Header Row
Product Quantity Cost
DataRow (objDR)
![Page 16: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/16.jpg)
2b – fill the new row
objDR("Quantity") = txtQuantity.Text
objDR("Product") = ddlProducts.SelectedItem.Text objDR("Cost") = Decimal.Parse(ddlProducts.SelectedItem.Value)
![Page 17: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/17.jpg)
After step 2b
Data Table (objDT)- Header Row
Product Quantity Cost
DataRow (objDR)
Something some number some price
![Page 18: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/18.jpg)
Step 3 – put the new row in the table
objDT.Rows.Add(objDR)
![Page 19: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/19.jpg)
After step 3
Data Table (objDT)
Product Quantity Cost
Something some number some price
Header Row
Row 1
![Page 20: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/20.jpg)
Shopping Basket
• User not logged in*– Session– Datatable stored in memory
• User logged in– Session– Datatable to & from database
* This version for coursework
![Page 21: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/21.jpg)
Shopping Basket
Initialise basket
1. Create a datatable2. Store it in memory (Session)
Session(“cart”)=objDT
![Page 22: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/22.jpg)
Shopping basket (cont 2)
When “add to cart” clicked1. Retrieve the datatable from memory2. Create a new row3. Fill in the data4. Put the new row on the bottom of the
datatable
basket = Session(“cart”)
![Page 23: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/23.jpg)
Shopping basket (cont 3)
View Basket
1.Retrieve table from memory2.Bind table to DataGrid
BasketGrid.DataSource()=Session(“cart”)
![Page 24: ASP.NET - insert - delete -update DataTables (disconnected datasets) Shopping Basket](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfa81a28abf838c9928f/html5/thumbnails/24.jpg)
TO DO• Oasisplus
– ASP.NET : INSERT, DELETE, UPDATE– ASP.NET : DataList– Shopping basket example code on discussion
board
– Or start your coursework
• NEXT WEEK– SHOPPING BASKET
• Where we take items from a list retrieved from the database