<%@ Debug="True" %>
<%@ Import Namespace="System.data" %>
<%@ Import Namespace="System.data.oleDB" %>

<script runat="Server">
Dim strConn as String=replace(ConfigurationSettings.AppSettings("biblio"),"biblio.mdb",server.mappath("biblio.mdb"))
'Add the following to your web.config file
'<appSettings>
' <add key="northwind" value="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=biblio.mdb;" />
'</appSettings>
Sub Page_Load
    If not IsPostBack Then
        doDatabind ("")
    End IF

    'Turn on/off Edit Columns
    If 1=2 THen
        myDatagrid.Columns(1).visible = False
    End IF

End Sub

Sub doDatabind (addSQL as String)
    If AddSQL="" THEN
        addSQL = " where state='NY'"
    END IF
    Dim strSQL as String = "Select PubID, Name, [Company Name] as Company, Address, City, State, Zip, Telephone, Fax, Comments from Publishers " & addSQL
    
    trace.write ("SQL",strSQL )

    Dim objConn as New OleDBConnection(strConn)
    Dim objCmd as New OLEDBCommand(strSQL, objConn)
    objConn.open
    myDataGrid.DataSource = objCmd.ExecuteReader()
    DataBind
    objConn.Close
End Sub

Sub doEdit (s as Object, e as DataGridCommandEventArgs )
    Dim PubID as Integer
    PubID = myDatagrid.datakeys (e.item.itemindex )
    myDataGrid.EditItemIndex = 0
    myDataGrid.BackColor = System.Drawing.Color.Aqua
    doDataBind(" where PubID = " & PubID)
End Sub

Sub doCancel (s as Object, e as DataGridCommandEventArgs )
    myDataGRid.EditItemIndex = -1
    myDataGrid.BackColor = System.Drawing.Color.White
    doDataBind("")
End Sub

Sub doUpdate (s as Object, e as DataGridCommandEventArgs )
    dim PubID as Integer
    Dim Name as textbox
    Dim Company as textbox
    Dim Address as textbox
    Dim Street as textbox
    Dim City as textbox
    Dim State as textbox
    Dim Zip as textbox
    Dim Fax as textbox
    Dim Telephone as textbox
    Dim Comments as textbox
    

    PubID = MyDataGrid.DataKeys ( e.item.itemindex )
    Name = e.item.cells(0).controls ( 1 )
    Company = e.item.cells(0).controls ( 3 )
    Address = e.item.cells(0).controls ( 5 )
    City = e.item.cells(0).controls ( 7 )
    State = e.item.cells(0).controls ( 9 )
    Zip = e.item.cells(0).controls ( 11 )
    Fax = e.item.cells(0).controls ( 13 )
    Telephone = e.item.cells(0).controls ( 15 )
    Comments = e.item.cells(0).controls ( 17 )
        

    Dim strSQL as String
    strSQL = "Update Publishers Set " & _
    "Name='" & Name.Text.Replace("'","''") & "', " & _
    "[Company Name] = '" & Company.Text.Replace("'","''") & "', " & _
    "Address='" & Address.Text.Replace("'","''") & "', " & _
    "City='" & City.Text.Replace("'","''") & "', " & _
    "State='" & State.Text.Replace("'","''") & "', " & _
    "Zip='" & Zip.Text.Replace("'","''") & "', " & _
    "Fax='" & Fax.Text.Replace("'","''") & "', " & _
    "Telephone='" & Telephone.Text.Replace("'","''") & "', " & _
    "Comments='" & Comments.Text.Replace("'","''") & "' " & _
    "WHERE PubID=" & cInt(PubID)
    
    trace.write("SQLstr", strSQL)
    Dim objConn as New OleDBConnection(strConn)
    Dim objCmd as New OLEDBCommand(strSQL, objConn)

    objConn.open
    objCmd.ExecuteNonQuery()
    objConn.Close

    myDataGRid.EditItemIndex = -1
    myDataGrid.BackColor = System.Drawing.Color.White
    doDataBind("")
End Sub

</Script>

<Form runat="Server">
<ASP:Datagrid
  autoGenerateColumns = "False"
  id="myDataGrid"
  datakeyfield="PubID"
  onEditCommand = "doEdit"
  onCancelCommand = "doCancel"
  onUpdateCommand = "doUpdate"
  ShowHeader = "False"
  runat="Server">

  <Columns>

    <ASP:TemplateColumn>

      <ItemTemplate> <table>
        <tr><td><B>Name:</B></td><td><%# container.DataItem ("Name") %></td></tr>
        <tr><td><B>Company Name:</B></td><td><%# container.DataItem ("Company") %></td></tr>
        <tr><td><B>Address:</B></td><td><%# container.DataItem ("Address") %></td></tr>
        <tr><td><B>City:</B></td><td><%# container.DataItem ("City") %></td></tr>
        <tr><td><B>State:</B></td><td><%# container.DataItem ("State") %></td></tr>
        <tr><td><B>Zip:</B></td><td><%# container.DataItem ("Zip") %></td></tr>
        <tr><td><B>Fax:</B></td><td><%# container.DataItem ("Fax") %></td></tr>
        <tr><td><B>Telephone:</B></td><td><%# container.DataItem ("Telephone") %></td></tr>
        <tr><td><B>Comments:</B></td><td><%# container.DataItem ("Comments") %></td></tr>
        </table>
      </ItemTemplate>

      <EditItemTemplate> <table>
        <tr><td><B>Name:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("Name") %>'
              runat="server" />
        </td></tr>
        <tr><td><B>Company Name:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("Company") %>'
              runat="server" />
        </td></tr>
        <tr><td><B>Address:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("Address") %>'
              runat="server" />
        </td></tr>
        <tr><td><B>City:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("City") %>'
              runat="server" />
        </td></tr>
        <tr><td><B>State:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("State") %>'
              runat="server" />
        </td></tr>
        <tr><td><B>Zip:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("Zip") %>'
              runat="server" />
        </td></tr>
        <tr><td><B>Fax:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("Fax") %>'
              runat="server" />
        </td></tr>
        <tr><td><B>Telephone:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("Telephone") %>'
              runat="server" />
        </td></tr>
        <tr><td><B>Comments:</B></td><td>
            <ASP:textbox text='<%# container.DataItem ("Comments") %>'
              runat="server" />
        </td></tr>
        
        </table>
      </EditItemTemplate>
    </ASP:TemplateColumn>
    <ASP:EditCommandCOlumn buttontype="PushButton" EditText="Edit!" UpdateText="Update" 
CancelText="Cancel"/>
  </Columns>
</ASP:Datagrid>
</form>