<%@ Page Language="vb" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

        Sub Page_Load(sender As Object, e As EventArgs)
            If Not Page.IsPostBack
                BindData()
            End If
        End Sub

        Sub BindData()

            '1. Start the Connection String

            Const conPub As String = ( _
                  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=c:\Inetpub\wwwroot\Learnasp\biblio2.mdb")

            Dim objConn As New OleDbConnection(conPub)
            objConn.Open() 'You must open the db connection before populating the datareader


            '2 Build the SQL query 

            Const strSQL As String =  "SELECT * FROM Publishers ORDER BY PubID DESC"
            Dim objCmd As New OleDbCommand(strSQL, objConn) 

            '3 Create/Populate the DataReader

            Dim objDR As OleDbDataReader
            objDR = objCmd.ExecuteReader()

            dgPub.DataSource = objDR
            dgPub.DataBind()
        End Sub

        Sub dgPub_Edit(sender As Object, e As DataGridCommandEventArgs)
            dgPub.EditItemIndex = e.Item.ItemIndex
            BindData()
        End Sub

        Sub dgPub_Cancel(sender As Object, e As DataGridCommandEventArgs)
            dgPub.EditItemIndex = -1
            BindData()
        End Sub

        Sub dgPub_Update(sender As Object, e As DataGridCommandEventArgs)
            ' Read in the values of the update row
            Dim iPubID As Integer = e.Item.Cells(1).Text
            Dim strPubName As String = Ctype(e.Item.Cells(2).Controls(0), Textbox).Text
            Dim strCompanyName As String = Ctype(e.Item.Cells(3).Controls(0), Textbox).Text
            Dim strAddress As String = Ctype(e.Item.Cells(4).Controls(0), Textbox).Text
            Dim strCity As String = Ctype(e.Item.Cells(5).Controls(0), Textbox).Text
            Dim strState As String = Ctype(e.Item.Cells(6).Controls(0), Textbox).Text
            Dim strZip As String = Ctype(e.Item.Cells(7).Controls(0), Textbox).Text
            Dim strTelephone As String = Ctype(e.Item.Cells(8).Controls(0), Textbox).Text
            Dim strFax As String = Ctype(e.Item.Cells(9).Controls(0), Textbox).Text
            Dim strComments As String = Ctype(e.Item.Cells(10).Controls(0), Textbox).Text

        ' Construct the SQL statement using Parameters

            Dim strSQL As String = "UPDATE [Publishers] SET [PubName] = @PubName, " & _
                                   "[CompanyName] = @CompanyName, [Address] = @Address, " & _
                                   "[City] = @City, [State] = @State, [Zip] = @Zip, " & _
                                   "[Telephone] = @Telephone, [Fax] = @Fax, [Comments] = @Comments, " & _
                                   "WHERE [PubID] = @PubID"

            '1 Create an instance of the Connection

            Const conPub As String = ( _
                  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=c:\Inetpub\wwwroot\Learnasp\biblio2.mdb")

            Dim objConn As New OleDbConnection(conPub)
            objConn.Open() 

            Dim myCommand As OleDbCommand = new OleDbCommand(strSQL, objConn)
            myCommand.CommandType = CommandType.Text

            ' Add Paramters to the SQL query

            Dim parameterPubName as OleDbParameter = new OleDbParameter("@PubName", OleDbType.VarWChar, 15)
            parameterPubName.Value = strPubName
            myCommand.Parameters.Add(parameterPubName)

            Dim parameterCompanyName as OleDbParameter = new OleDbParameter("@CompanyName", OleDbType.VarWChar, 20)
            parameterCompanyName.Value = strCompanyName
            myCommand.Parameters.Add(parameterCompanyName)

            Dim parameterAddress as OleDbParameter = new OleDbParameter("@Address", OleDbType.VarWChar, 70)
            parameterAddress.Value = strAddress
            myCommand.Parameters.Add(parameterAddress)                       

            Dim parameterCity as OleDbParameter = new OleDbParameter("@City", OleDbType.VarWChar, 20)
            parameterCity.Value = strCity
            myCommand.Parameters.Add(parameterCity)

            Dim parameterState as OleDbParameter = new OleDbParameter("@State", OleDbType.VarWChar, 20)
            parameterState.Value = strState
            myCommand.Parameters.Add(parameterState)

            Dim parameterZip as OleDbParameter = new OleDbParameter("@Zip", OleDbType.VarWChar, 10)
            parameterZip.Value = strZip
            myCommand.Parameters.Add(parameterZip)

            Dim parameterTelephone as OleDbParameter = new OleDbParameter("@Telephone", OleDbType.VarWChar, 20)
            parameterTelephone.Value = strTelephone
            myCommand.Parameters.Add(parameterTelephone)


            Dim parameterFax as OleDbParameter = new OleDbParameter("@Fax", OleDbType.VarWChar, 20)
            parameterFax.Value = strFax
            myCommand.Parameters.Add(parameterFax)

            Dim parameterComments as OleDbParameter = new OleDbParameter("@Comments", OleDbType.VarWChar, 20)
            parameterState.Value = strComments
            myCommand.Parameters.Add(parameterComments)

            myCommand.ExecuteNonQuery() 'EXECUTE the UPDATE QUERY

            objConn.Close()

            'Finally set the EditItemIndex to -1 and rebind the DataGrid

            dgPub.EditItemIndex = -1
            BindData()
        End Sub
</script>
<html>
<head>
    <title>Updating a database with .Net</title>
</head>
<body text="#000000" bgcolor="#ffffff" font face ="Verdana">
    <h2 font face ="Verdana">Changing Information about the Publishers
    </h2>
<form runat="server">


<asp:DataGrid id="dgPub" 
        Font-Name="Verdana" 
        Font-Size="8pt"
        BackColor="#CCCCFF"  
        BorderColor="black" 
        Cellpadding="3"
        runat="server"
        HeaderStyle-BackColor="#AAAADD"
        AutoGenerateColumns="False"

        OnEditCommand="dgPub_Edit"
        OnUpdateCommand="dgPub_Update"
        OnCancelCommand="dgPub_Cancel"/>

        <Columns>

        <asp:EditCommandColumn EditText=" Edit " ButtonType="PushButton"
                UpdateText="Update" CancelText="Cancel" />
        <asp:BoundColumn DataField="PubID" HeaderText="Publishers ID" ReadOnly="True" />
        <asp:BoundColumn DataField="PubName" HeaderText="Publisher" />
        <asp:BoundColumn DataField="CompanyName" HeaderText="Company Name" />
        <asp:BoundColumn DataField="Address" HeaderText="Address" />
        <asp:BoundColumn DataField="City" HeaderText="City" />
        <asp:BoundColumn DataField="State" HeaderText="State" />
        <asp:BoundColumn DataField="Zip" HeaderText="Zip" />
        <asp:BoundColumn DataField="Telephone" HeaderText="Telephone" />
        <asp:BoundColumn DataField="Fax" HeaderText="Fax" />
        <asp:BoundColumn DataField="Comments" HeaderText="Comments" />
        </Columns>        
</asp:DataGrid>
</form>
</body>
</html>