'ADD a Webform NAMED Prodtestbuild to a PROJECT

'ADD A WebForm NAMED prodtestbuild TO A PROJECT

'ADD 2 ASP TextBoxes, A Button AND A Label TO IT

'RIGHT-CLICK FILE IN SOLUTION EXPLORER, View Code

'ALSO Set As Start Page ON THIS FILE

'PASTE THE FOLLOWING IMPORT LINE AT TOP OF CODE

'PASTE THE Button1 CODE INTO THE SUB FOR ITS CLICK

'PUT PRODUCT ID (E.G., 0001) IN FIRST TEXTBOX,

'QUANTITY SOLD IN OTHER

'DOWNLOAD example.mdb INTO SAME DIRECTORY AS Default.aspx

'CAN MIGRATE SAME FILE TO SQL SERVER AND REPLACE COMMENTED CODE

'RUN CODE WITH 0001 AND 1 AS DATA; IF ERRORS SEE:

'http://www.xlinesoft.com/asprunner/docs/troubleshooting__operation_must_use_an_updateable_query__errors.htm

Imports System.Data

Imports System.Data.OleDb

'Imports System.Data.SqlClient

Partial Class Default2

Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strCon As String

Dim strConInfo As String

Dim strPath As String

Dim strSQL As String

Dim strProdID As String

Dim strProdName As String

Dim intProdCount As Integer

Dim intProdSold As Integer

Dim noData As Boolean

Dim strDBName As String

Dim objCon As OleDbConnection

Dim objDA As OleDbDataAdapter

Dim objBuild As OleDbCommandBuilder

'Dim objCon As SqlConnection

'Dim objDA As SqlDataAdapter

'Dim objBuild As SqlCommandBuilder

Dim objDS As DataSet

Dim objTable As DataTable

Dim objRow As DataRow

strConInfo = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

'strConInfo = "server=(local)\SQLEXPRESS; Integrated Security=True; AttachDbFilename = "

strPath = Request.PhysicalApplicationPath 'database in same subdirectory as web page

strDBName = "example.mdb"

'strDBName = "example.mdf"

strCon = strConInfo & strPath & strDBName

objCon = New OleDbConnection(strCon)

'objCon = New SqlConnection(strCon)

objCon.Open()

strProdID = TextBox1.Text

strSQL = "select ProdID, ProdName, Price, Instock from Product "

strSQL += " where ProdID='" & strProdID & "'" 'single quotes inside double quotes

objDS = New DataSet

objDA = New OleDbDataAdapter(strSQL, objCon)

'objDA = New SqlDataAdapter(strSQL, objCon)

objDA.Fill(objDS, "Product")

objTable = New DataTable

objTable = objDS.Tables("Product")

Try

objRow = objTable.Rows(0)

Catch ex As IndexOutOfRangeException

noData = True

End Try

If noData Then

intProdCount = 0

strProdName = "No record found for ID = '" & strProdID & "'"

Else

intProdCount = CInt(objRow("Instock"))

strProdName = objRow("ProdName").ToString

intProdSold = CInt(TextBox2.Text)

intProdCount = intProdCount - intProdSold

objBuild = New OleDbCommandBuilder(objDA)

'objBuild = New SqlCommandBuilder(objDA)

objDA.UpdateCommand = objBuild.GetUpdateCommand()

objRow("Instock") = intProdCount

objDA.Update(objDS, "Product")

End If

Label1.Text = "Product = " & strProdName & ", current quantity now is " & intProdCount

End Sub

End Class