Data Grid Columns in Microsoft Office

Generating QR Code in Microsoft Office Data Grid Columns

FIGURE 6-10

Listing 6-11

Private Sub Page_Load(ByVal sender As SystemObject, _ ByVal e As SystemEventArgs) Handles MyBaseLoad 'Put user code to initialize the page here ' Check to see if this is the first time the page is displayed If Not IsPostBack Then Dim Dim Dim Dim Dim objConn As New OleDbOleDbConnection() strSQL As String objCMD As New OleDbOleDbCommand() objDa As New OleDbOleDbDataAdapter() objDs As New DataDataSet()

' Open the DB connection objConnConnectionString = Application("strConn") objConnOpen() ' Retrieve the unclaimed tickets strSQL = " SELECT * from unclaimed_tickets"

' Set the command connection objCMDConnection = objConn ' Set the command query objCMDCommandText = strSQL ' Set the select command objDaSelectCommand = objCMD ' Set the data source dgUnclaimedTicketsDataSource = _ objDaSelectCommandExecuteReader() ' Bind the data to the data grid dgUnclaimedTicketsDataBind() ' Close the connection objCMDConnectionClose()

Most of the work for the page is done by the Unclaimed_Tickets query in the Access database All of the data from that query is retrieved and then bound to the data grid That is it Let s now look at how the tickets are claimed and deleted Add a new web form to the project and save it as ProcClaimTicketaspx, the page of which is shown in Listing 6-12 The design of the page has the same navigation buttons as the claim tickets page In addition, there is a label control that indicates the ticket was claimed successfully

Listing 6-12

Private Sub Page_Load(ByVal sender As SystemObject, _ ByVal e As SystemEventArgs) Handles MyBaseLoad 'Put user code to initialize the page here Dim Dim Dim Dim Dim ' objConn As New OleDbOleDbConnection() strSQL As String objCMD As New OleDbOleDbCommand() objDa As New OleDbOleDbDataAdapter() objDs As New DataDataSet() Open the DB connection

objConnConnectionString = Application("strConn") objConnOpen() ' Update the ticket and change the owner to the claimed owner strSQL = "Update Ticket set " & _ "OwnerID = " & Session("UserID") & _ " where ticketid = " & RequestQueryString("ticketid") ' Set the command connection objCMDConnection = objConn ' Set the command query objCMDCommandText = strSQL ' Execute the query objCMDExecuteScalar() End Sub

To claim the ticket, the OwnerID field of the ticket is set to the current user ID of the administrator The next page handles deleting an unclaimed ticket Add a new web form to the project and save it as ProcDeleteTicketaspx Note that the delete logic is only available on the unclaimed ticket listing Active tickets can only be archived Listing 6-13 shows the code for deleting a ticket The page has the same navigation buttons as the unclaimed tickets page

Listing 6-13

Private Sub Page_Load(ByVal sender As SystemObject, _ ByVal e As SystemEventArgs) Handles MyBaseLoad 'Put user code to initialize the page here Dim objConn As New OleDbOleDbConnection() Dim strSQL As String Dim objCMD As New OleDbOleDbCommand() Dim objDa As New OleDbOleDbDataAdapter() Dim objDs As New DataDataSet() ' Open the DB connection objConnConnectionString = Application("strConn")

objConnOpen() ' Delete the status history entries for the ticket strSQL = "delete from StatusHistory where ticketid = " & _ RequestQueryString("ticketid") ' Set the command connection objCMDConnection = objConn ' Set the command query objCMDCommandText = strSQL ' Execute the query objCMDExecuteScalar() ' Open and close the connection objConnClose() objConnOpen() ' Delete the ticket strSQL = "delete from Ticket where ticketid = " & _ RequestQueryString("ticketid") ' Set the command connection objCMDConnection = objConn ' Set the command query objCMDCommandText = strSQL ' Execute the query objCMDExecuteScalar() End Sub

When the ticket is deleted, note that any related status history records need to be deleted as well Note that only status record should be for the Submitted status The processing for archiving a ticket works the same as claiming and deleting tickets Note that the archive link is shown on the claimed ticket listing Add a new web form to the project and save it as ProcArchiveTicketaspx Listing 6-14 shows the code for the page