SteveBarron.NET

DataGrid with variable Columns – which survive postback!

noreply@blogger.com (Steve Barron) — Thu, 28 Jul 2011 18:21:00 +0000

In April, I blogged about adding columns dynamically to a DataGrid. At the time, I was working on a report, so the dynamic columns didn't have to survive postback.

This week, I needed those columns to survive postback, so here's the updated codez:

<asp:DataGrid runat="server" ID=dgGoodBad Width=97% AutoGenerateColumns=False >
<HeaderStyle CssClass=Freezing />
<AlternatingItemStyle CssClass=gAltRowsDark />
<Columns>
    <asp:BoundColumn DataField=sys_cd_val HeaderText="ID"/>
    <asp:BoundColumn DataField=sys_cd_desc HeaderText="Formulary Status" />
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
    <asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
</Columns>
</asp:DataGrid>

I included more template columns than are ever going to really appear in the data. If you need different items in each TemplateColumn, you’d probably have to include all of the possible items in each column then show/hide them in ItemDataBound.

Here’s the Code Behind:

Partial Class GoodBad
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            BindGrid()
        End If
        Dim csType As Type = Me.[GetType]()
        ClientScript.RegisterClientScriptBlock(csType, "checkboxes", _
               "<script language=javascript>" & _
               " function check(ctl) {if(ctl.checked){ctl.style.background = '';updateCount(-1);} else {ctl.style.background = '#FF0000';updateCount(1);};}; " & _
               " function uncheck(ctl) { if (ctl.checked) { ctl.style.background = '#00FF00'; updateCount(1); } else { ctl.style.background = ''; updateCount(-1);};}; " & _
               " function updateCount(iVal) { btn = document.getElementById('" & btnSave.ClientID & "'); iCurr = parseInt(btn.value.replace(' Changes','').replace('Save ','').replace(' Change','')); iCurr = iCurr + parseInt(iVal); if (iCurr > 0) {btn.style.display='';if (iCurr == 1) {btn.value='Save 1 Change';} else {btn.value='Save ' + iCurr + ' Changes';}} else {btn.style.display='none';btn.value='0';};};" & _
               "</script>")
    End Sub

    Private Sub BindGrid(Optional ByVal cn As OleDbConnection = Nothing)
        Dim sSQL As String = ""
        Dim iCol As Integer = 2
        For Each prod In Common.GetDataTable("select * from mkt_prod_xrf where mkt_id=" & ddlMarket.SelectedValue & " order by prod_desc asc", cn).Rows
            dgGoodBad.Columns(iCol).Visible = True
            dgGoodBad.Columns(iCol).HeaderText = prod("prod_desc").ToString
            dgGoodBad.Columns(iCol).HeaderStyle.BackColor = Drawing.ColorTranslator.FromHtml(prod("dsly_clr").ToString)
            dgGoodBad.Columns(iCol).ItemStyle.BackColor = Drawing.ColorTranslator.FromHtml(prod("dsly_clr").ToString)
            sSQL &= ", (SELECT yes_no_val FROM fact_table WHERE sys_cd_val=c.sys_cd_val AND prodgrp_id=" & prod("prod_id").ToString & ") v_" & prod("prod_id").ToString & " "
            iCol += 1
        Next
        For iCol = iCol To dgGoodBad.Columns.Count - 1
            dgGoodBad.Columns(iCol).Visible = False
        Next
        Common.BindToReader("SELECT dim_id, dim_desc " & sSQL & " FROM dimension_lkp c ORDER BY dim_desc", dgGoodBad)
        If bDispose Then cn.Dispose()
        btnSave.Style("display") = "none"
        btnSave.Text = "0"

    End Sub

    Protected Sub dgGoodBad_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgGoodBad.ItemDataBound

        Select e.Item.ItemType
            Case ListItemType.Header
                Dim sMarkets As String = ""
                Dim dtOtherMarkets As New DataView(Common.GetDataTable("SELECT m.mkt_desc, mp.prodgrp_desc FROM mkt_lkp m, mkt_prod_xrf mp WHERE m.mkt_id=mp.mkt_id AND m.mkt_id != " & ddlMarket.SelectedValue), "", "mkt_desc", DataViewRowState.CurrentRows)
                For i As Integer = 2 To dgGoodBad.Columns.Count - 1
                    If dgGoodBad.Columns(i).Visible Then
                        sMarkets = ""
                        dtOtherMarkets.RowFilter = "prod_desc=" & Common.DBString(dgGoodBad.Columns(i).HeaderText)
                        If dtOtherMarkets.Count > 0 Then
                            For iMkt As Integer = 0 To dtOtherMarkets.Count - 1
                                sMarkets &= dtOtherMarkets(iMkt)("mkt_desc").ToString & vbCrLf
                            Next
                        End If
                        If sMarkets > "" Then
                            e.Item.Cells(i).ForeColor = Drawing.Color.Red
                            e.Item.Cells(i).ToolTip = sMarkets
                        End If
                    Else
                        Exit For
                    End If
                Next
            Case ListItemType.Item, ListItemType.AlternatingItem
                For i As Integer = 2 To dgGoodBad.Columns.Count - 1
                    If dgGoodBad.Columns(i).Visible Then
                        If e.Item.DataItem(i).ToString = "G" Then
                            DirectCast(e.Item.Cells(i).Controls(0), CheckBox).Checked = True
                            DirectCast(e.Item.Cells(i).Controls(0), CheckBox).Attributes.Add("onclick", "document.getElementById('" & hidDirty.ClientID & "').value += '/" & e.Item.DataItem("sys_cd_val") & "/';check(this);")
                        Else
                            DirectCast(e.Item.Cells(i).Controls(0), CheckBox).Attributes.Add("onclick", "document.getElementById('" & hidDirty.ClientID & "').value += '/" & e.Item.DataItem("sys_cd_val") & "/';uncheck(this);")
                        End If
                    Else
                        Exit For
                    End If
                Next
        End Select
    End Sub
    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim sSQL As String = "UPDATE fact_table SET yes_no_val='[value]' " & _
                             " WHERE prod_id=[prod_id] " & _
                             "   AND dim_id=[dim_id] "
        Dim cn As OleDbConnection = Nothing
        Dim dvOld As New DataView(Common.GetDataTable("SELECT f.*, p.prod_desc FROM fact_tbl f, mkt_prod_xrf p WHERE f.prod_id=p.prod_id", cn), "", "", DataViewRowState.CurrentRows)
        Dim sHolder As String = "begin" & vbCrLf
        Dim val As String = ""
        Common.MaybeOpenConnection(cn)
        For Each itm As DataGridItem In dgGoodBad.Items
            If itm.ItemType = ListItemType.Item Or ListItemType.AlternatingItem Then
                If InStr(" " & hidDirty.Value, "/" & itm.Cells(0).Text & "/") > 0 Then
                    For iCol As Integer = 2 To dgGoodBad.Columns.Count - 1
                        If dgGoodBad.Columns(iCol).Visible Then
                            dvOld.RowFilter = "prod_desc=" & Common.DBString(dgGoodBad.Columns(iCol).HeaderText) & " AND dim_id=" & itm.Cells(0).Text
                            If dvOld.Count > 0 Then
                                val = IIf(DirectCast(itm.Cells(iCol).Controls(0), CheckBox).Checked, "G", "B")
                                If val <> dvOld(0)("yes_no_val").ToString Then
                                    sHolder &= sSQL.Replace("[dim_id]", dvOld(0)("dim_id")).Replace("[prod_id]", dvOld(0)("prod_id")).Replace("[value]", val) & ";"
                                End If
                            Else
                                'Uh oh!
                            End If
                        Else
                            Exit For
                        End If
                    Next
                End If
            End If
        Next
        dvOld.Dispose()
        hidDirty.Value = ""
        Common.ExecuteNonQuery(sHolder & vbCrLf & "commit;" & vbCrLf & "end;", cn)
        BindGrid(cn)
        cn.Dispose()

    End Sub

End Class

OK, that was a lot. Let’s go through what’s going on…

In BindGrid, for each product that exists in the database, I set a column to be visible, set up it’s HeaderText, etc. All of the later columns get hidden.

In ItemDataBound, I set the checked values (only for those that should be checked – the default is unchecked) and I decide which javascript is to be called: check() or uncheck(). They are very similar in that they both set the background color and increment/decrement the count of changes as displayed on the Save button. They are reversed to differentiate between checking and unchecking a box that was checked when the page was loaded vs one that was unchecked when the page was loaded.

In Javascript, I’m coloring the background of any changed checkboxes so the user knows what’s been updated. I also put the count of the number of changes into the text of btnSave to help them keep track. I also keep the user from clicking Save twice by re-hiding the button and replacing it with a Saving… message.

Some things I did to improve speed:

- You will see a number of places where I Exit For the column loop once I hit an unused/hidden column.

- hidDirty is a HiddenField that I added which is populated by javascript to contain the dimension IDs of any row that it updated. Then, when the Save button is processing, it can skip any dimension rows that were not updated, rather than checking every checkbox (I have 30 in my code)

- I added dvOld to compare the database’s value to what is currently in the grid. There’s no point in updating values that were not changed. This might not be all that important anymore because I also…

- Moved the updates to a single update at the end, with a single Commit. At one point, I was running each UPDATE statement by itself but there’s really no point. I might move the ExecuteNonQuery to inside the For each itm loop if I ever get a concern that the user might do too many updates at once and OleDB might not like it. :)

jQueryUI AutoComplete AutoPostback

noreply@blogger.com (Steve Barron) — Thu, 16 Jun 2011 19:54:00 +0000

I was recently ~~playing~~ experimenting with jQueryUI autocomplete with some help from an article on DotNetCurry but it didn't do everything I wanted it to do. Once the user chose an entry from the list, I wanted that entry selected and posted back to the server.

Now, I also used my own webservice and a real database instead of their sample data, so there could be a few things that look different from their code.

First, in the "success" setting, I had it return 2 answers:  the ID and the name.
success: function (data) {

   response($.map(data.d, function (item) {
      return {
         value: item.name,
         id: item.id
      }
   }))
},

Then, toward the end of the autopostback call, after minLength: 2,I added a select setting to override the default action.
select: function (event, ui) {

   $("#hidID").val(ui.item.id);
   $("#tbAuto").val(ui.item.value);
   __doPostBack('btnGo', '');
   return true;
}

- hidID wasn't in the original so that was a new asp:HiddenField that I added to pass the ID from the
   webservice
- btnGo is an asp:Button that does the actual work. It looks up the information from the database.
- __doPostBack wasn't available at first. I realized that I had to add something with an autopostback option,
so I also added a bogus asp:DropDownList.

But I didn't want to see btnGo or the bogus DropDownList so I hid them at the top of the JavaScript:
$("#ddl").replaceWith(" ")

$("#btnGo").replaceWith(" ")

I could probably get rid of the button, now, and have the work done in the postback of the ddl but this was just a proof-of-concept so I didn't do that (yet).

Sending Nice Emails from Oracle

noreply@blogger.com (Steve Barron) — Wed, 27 Apr 2011 13:07:00 +0000

After a few years of our (ASP.NET, Oracle back end) system sending emails to users, I decided it was time to make the emails nicer. Previously, an email was either just un-formatted text or was a hand-coded table. There was also the option to pass a SQL statement but it turned out never to have been used. Also previously, some emails came from the .NET side (if they were generated by the .NET code) and others from Oracle (if they were generated by the batch cycle).

Without changing the calls in the .NET code (or at least not many of them), I wanted them to have a uniform appearance, nicer than before, that used a color scheme similar to the (intranet) web app, a title and a link to the system. The few calls that we decided to change were merely to enhance the appearance of some of the emails that already included tables.

The .NET code was pretty easily converted to become a wrapper for the Oracle procedure call. It's easy enough to get .NET to call Oracle. It would have been a lot trickier to get Oracle to call the .NET code, though I guess a webservice would have done it.

Everyone in our company has Outlook so sending HTML-only emails is acceptable but it wouldn't be hard to change this up to include both plain text and HTML segments.

PROCEDURE sendmail

 (

    p_to IN VARCHAR2 DEFAULT NULL,

    p_cc IN VARCHAR2 DEFAULT NULL,

    p_subject IN VARCHAR2 DEFAULT NULL,

    p_html IN VARCHAR2 DEFAULT NULL

  )

IS

  l_crlf          varchar2(2)     := CHR(13) || CHR(10);  --Yes, I'm still a VB programmer at heart!

  l_boundary      VARCHAR2(15)    :='a1b2c3d4e3f2g1';     --This can be any nonsense string that will

  l_offset        NUMBER          := 1;                   --  never accidentally appear in a real message.

  l_chunk         NUMBER          := 1000;  

  l_header        VARCHAR2(32767) := '';

  l_body          VARCHAR2(32767) := p_html;

  l_smtp_hostname VARCHAR2(30)    := 'SMTPHOST.XXX.COM';

  l_to            VARCHAR2(100)   := '';

  l_subj_suffix   varchar2(5)     := '';

  l_connection    utl_smtp.CONNECTION;    

BEGIN    

  IF p_to IS NOT NULL THEN

    IF p_subject IS NOT NULL OR p_html IS NOT NULL THEN

      --Don't send email to real recipient if this is not Production

      SELECT decode(global_name, 'PROD.WORLD',p_to, 'sharedmailbox@xxx.com') INTO l_to FROM global_name;

      --Append the word Test to the subject if this is not Production 

      SELECT decode(global_name, 'PROD.WORLD','', '-Test') INTO l_subj_suffix FROM global_name;

      l_header := 'MIME-Version: 1.0' || l_crlf || 'To: ' || p_to || l_crlf || 'From: Please Do Not Reply ' 

               || l_crlf || 'Subject: ' || p_subject || l_subj_suffix || l_crlf 

               || 'Content-Type: multipart/alternative; boundary=' || CHR(34) || l_boundary || CHR(34) 

               || l_crlf || l_crlf || l_crlf || '--' || l_boundary || l_crlf || 'content-type: text/html;' 

               || l_crlf || l_crlf;    

      --Open Connection to SMTP server

      l_connection := utl_smtp.open_connection(l_smtp_hostname);

      utl_smtp.helo(l_connection, l_smtp_hostname);

      --Recipients

      utl_smtp.mail(l_connection, 'sharedmailbox@xxx.com');

      utl_smtp.rcpt(l_connection, l_to);

      IF p_cc IS NOT NULL THEN

        utl_smtp.rcpt(l_connection, p_cc);        

        l_header := 'MIME-Version: 1.0' || l_crlf || 'To: ' || p_to || l_crlf || 'cc: ' || p_cc || l_crlf

                 || 'From: Please Do Not Reply ' || l_crlf || 'Subject: ' || p_subject || l_subj_suffix 

                 || l_crlf || 'Content-Type: multipart/alternative; boundary=' || CHR(34) || l_boundary 

                 || CHR(34) || l_crlf || l_crlf || l_crlf || '--' || l_boundary || l_crlf 

                 || 'content-type: text/html;' || l_crlf || l_crlf;      

      END IF;

      utl_smtp.rcpt(l_connection, 'sharedmailbox@xxx.com');

      --Headers

      utl_smtp.open_data(l_connection);      

      utl_smtp.write_data(l_connection, l_header);

      utl_smtp.write_data(l_connection, '

        <html>

          <head>

            <style>

              table, tr, td {font:10pt black;font-family:Calibri} 

              table {border:1px solid black;border-collapse:collapse;width:90%} 

              .gHeader {font:bold 10pt black;font-family:Calibri;BACKGROUND-COLOR:#A0CF67} 

              .gAltRowsDark {font:10pt black;font-family:Calibri;BACKGROUND-COLOR:#EEF6E3}

            </style>

          </head>

          <body>    

            <table align=center >

              <tr class=gHeader>

                <td>WebApp Message</td>

                <td></td>

                <td style=text-align:right><a href=http://webapp.xxx.com>Go to WebApp</a></td>

              </tr>

              <tr>

                <td colspan=3><br/>');

      --Body

      while length(l_body) > 0

      loop

        l_chunk := least(l_chunk, length(l_body));

        utl_smtp.write_data(l_connection, SUBSTR(l_body, 1, l_chunk));        

        l_body := substr(l_body,l_chunk+1);        

        dbms_output.put_line(l_body);

      end loop;

      -- Cleanup      

      utl_smtp.write_data(l_connection, '

                </td>

              </tr>

            </table>

          </body>

         </html>' || l_crlf || '--' || l_boundary || '--' || CHR(13));            
      utl_smtp.close_data(l_connection);
      utl_smtp.quit(l_connection);
    END IF;
  END IF;
END sendmail;

Some of this isn't as efficient as I'd like it to be but it works. Every email now gets a nice table around it with a title and a link to the web app.

This is, of course, a system message and not one the users get - or it would have had some nicer text, etc., but you get the idea.

Every email is also cc:ed to our group shared mailbox. If we're not in Production, the shared mailbox is the only place the email goes - which is handy in preventing Test emails from getting to real users. Also, the subject also has "-Test" appended to it so I can tell which emails in the shared mailbox are real and which are not. They still appear to have their original recipients (which is good for testing) because the MIME header has the original recipient but the recipient isn't added to the recipient list.

DataGrid with variable Columns

noreply@blogger.com (Steve Barron) — Tue, 12 Apr 2011 15:27:00 +0000

A number of times in my career, I have been asked to create a "grid" of data. Users like a single report to let them compare a product across customers and also a customer across products.

Because I started with "Classic" ASP many years ago, my knowledge of HTML & CSS made it a natural to create this as an HTMLTable object with multiple rows, each with multiple cells, each of which could have multiple controls. Certainly, some of the more advanced requirements I was given lent themselves to this hand-crafted method.

But, sometimes, the grid is very simple and just needs rows and columns of data. Binding a DataGrid (yes, I'm still using DataGrids instead of GridViews) with dynamic columns. Even some slightly advanced requirements can be handled with this method - the example below shows a secondary fact in the grid for some products and also colors for each product from the database (stored as hex).

Let's start by creating the DataGrid itself in the ASPX page:

<asp:DataGrid runat="server" ID="dgMain" AutoGenerateColumns=false EnableViewState=false>
  <HeaderStyle CssClass="Header" />
  <AlternatingItemStyle CssClass="AltRows" />
  <Columns>
    <asp:BoundColumn DataField="acct_nm" ReadOnly="true" />
  Columns>
<asp:DataGrid>

Nothing tricky there. I've added the Account Name column to label the row and set up the styles. In the code, we need to add the columns and build the SQL statement.

The For Each goes through the list of products, adding columns to the data grid and building columns (the "col" column) for the real SELECT clause to come.

See some of my other blog posts for details about the Common class.

     Private Sub BuildGrid(Optional ByRef cn As OleDbConnection = Nothing)
        Dim bDispose As Boolean = Common.MaybeOpenConnection(cn)
        Dim bc As BoundColumn
        Dim sSQL As String = "SELECT acct_nm,"
        For Each dr As DataRow In Common.GetDataTable("select cmptr_ind, dsly_clr, prod_desc, prod_id, " & _
                                "       '(select fact from fact_tbl where acct_id=a.acct_id and prod_id=' || prod_id || ')) f1_' || prod_id || ',' || decode(cmptr_ind,'N','(select fact2 from fact_tbl2 where acct_id=a.acct_id and prod_id=' || prod_id || ') f2_' || prod_id || ',','') col " & _
                                "  from prod_tbl " & _
                                "  /* prod filter? */ " & _
                                " order by cmptr_ind desc, prod_desc", cn).Rows
            sSQL &= dr("col").ToString
            bc = New BoundColumn
            bc.DataField = "f1_" & dr("prod_id").ToString
            bc.HeaderText = dr("prod_desc").ToString & "Main Fact"
            bc.HeaderStyle.BackColor = Drawing.ColorTranslator.FromHtml(dr("dsly_clr"))
            bc.HeaderStyle.VerticalAlign = VerticalAlign.Bottom
            dgMain.Columns.Add(bc)
            If dr("cmptr_ind").ToString = "N" Then  'Our product - show second fact                
                bc = New BoundColumn
                bc.DataField = "f2_" & dr("prod_id").ToString
                bc.HeaderText = dr("prod_desc").ToString & "Second Fact"
                bc.HeaderStyle.BackColor = Drawing.ColorTranslator.FromHtml(dr("dsly_clr"))
                bc.HeaderStyle.VerticalAlign = VerticalAlign.Bottom
                bc.ItemStyle.HorizontalAlign = HorizontalAlign.Right
                dgMain.Columns.Add(bc)
            End If
        Next
        sSQL = sSQL.Substring(0, sSQL.Length - 1)
        Common.BindToReader(sSQL & " FROM acct_tbl a /* acct filter? */", dgMain, cn)
        If bDispose Then cn.Dispose()
    End Sub

The second-to-last line runs the built SQL to get the data and bind it to the DataGrid. The sSQL variable could probably benefit from being a StringBuilder, rather than doing all of those concatenations, but I didn't have any performance issues. Your SQL may vary. :)

VB.NET Line Continuation Characters in VS2010

noreply@blogger.com (Steve Barron) — Mon, 13 Sep 2010 19:00:00 +0000

Once again, the marketing geniuses at Microsoft have lied to us. How this time?

They said that line continuation characters are no longer needed on long lines of code. That may be true - and they are honest in saying that it works most of the time. But here's the rub. This code:

Case InvalidCreateInquiryErrorType.INVALID_CLIENT_ID, _
InvalidCreateInquiryErrorType.INVALID_ORG_ID, _
InvalidCreateInquiryErrorType.INVALID_PRODUCT_CODE, _
InvalidCreateInquiryErrorType.REQUESTOR_EMAIL_TOO_LARGE, _
InvalidCreateInquiryErrorType.REQUESTOR_COMMENT_TOO_LARGE, _
InvalidCreateInquiryErrorType.REQUESTOR_DOCUMENT_TOO_LARGE, _
InvalidCreateInquiryErrorType.INVALID_PRODFORMU_STATUS_CODE_NEW

...ran just fine without the line continuation characters. BUT, the project would not build and gave no indication as to what the problem was. The build reported failure but no errors, either!

Fortunately, I found something in the blogosphere that suggested changing the Visual Studio settings to have more verbosity in the Output window. Once I did that, it took only seconds to guess at the actual error and insert the underscores.

Caveat emptor!

Oracle SQL Developer 2.1

noreply@blogger.com (Steve Barron) — Thu, 18 Feb 2010 17:30:00 +0000

Oracle released a new version of their FREE SQL Developer in December 2009.

Some Oracle developers prefer one of the products from Quest Software - SQL Developer or TOAD. I have used all 3 over the years – each for years at a time. Each had it's strengths and weaknesses. If you are using an older version of any of these tools, though, and don’t want to lay out money for new versions, give SQLDeveloper a try. Did I mention it’s FREE?

There is no install, per se. I moved my old install into a folder call Backup underneath the C:\Program Files\SQLDeveloper folder I had been running in. I extracted the ZIP (complete with JDK1.6._11) into that folder. My old shortcuts continued to work. The first time I ran, it asked if I wanted to import my settings from 1.5 and it did.

So far, I feel like it crashes/freezes a LOT less than the 1.5 version I had been using. That could be the biggest benefit. A few other things are nicer than before.

There’s a nice Start Page with links to useful parts of Oracle’s developer site.

I like the autocomplete that adds and updates the group by clause for your select statements.
I like that the whole thing doesn’t freeze up when I have a long-running process and accidentally ask for something else from that same connection. It merely delays those requested actions (usually expanding something in the tree) but allows the rest of the app to continue working.
My previous version recently started to randomly freeze cells while I was scrolling around query results with arrow keys. Going to the top/left and using scroll bars to navigate would fix it some of the time. I suspect it was a JDK version issue but, either way, it appears to be gone – Yay!
I like the refresh option on each query result tab and the pin, but I had to make pinning not be the default. :)
This isn’t new, but it seems like it should work with several different non-Oracle databases, too. Maybe I’ll install it at home, too, for SQL Server things I do there.

Common.vb

noreply@blogger.com (Steve Barron) — Tue, 16 Feb 2010 17:30:00 +0000

Common.vb [Download (23k)]contains a number of helper functions. This is copy-paste code sharing as new systems use the latest possible version. Fortunately, this file has stabilized pretty well and no changes have been made for the last several systems.

Region: DBFormats
These are mostly just formatter functions but this first one also helps protect from SQL injection attacks.

DBString takes a string, wraps it in single quotes, doubles any internal quotes and replaces an empty string with NULL.

DBDate takes a date or a string and returns either NULL or a string formatted with the Oracle to_date function for this format: mm/dd/yyyy

DBDateTime takes a date and returns either NULL or a string formatting with the Oracle to_date function for this format: mm/dd/yyyy hh24:mi

DBNumber takes a string and returns either NULL (if it’s not a number) or the numeric value of the string. This is rarely used and might even be included in the framework somewhere by now.

Region: Database Helpers
Again, the intention is to isolate some database code and implement the rule of threes.

ExecuteNonQuery is a wrapper for the OleDBCommand object's method of the same name. It accepts a SQL statement (DML), an optional OleDBConnection and an optional OleDBTransaction.

GetDataTable is mostly a wrapper OleDBDataAdapter object's Fill method. It accepts a SQL statement and an optional OleDBConnection and returns a DataTable named Temp. It is often used inside other functions (e.g., New DataView()). Use .Rows(0) if you want to make sure you get a single DataRow.

AddDataTable is a lot like GetDataTable but the resulting DataTable is put into a DataSet. It accepts a SQL statement, table name, DataSet, an optional OleDBConnection and an optional OleDBParameterCollection. This is a SUB, not a function. The passed-in DataSet will contain a new table, named what you've passed in. This is rarely used.

ExecuteScalar is a wrapper for the OleDBCommand object's method of the same name. It accepts a SQL statement and an optional OleDBConnection and returns the answer as a string. This is over-used. I suspect a good number of these could/should be combined into a single GetDataTable().Rows(0) but as requirements change, sometimes these are added into the code for expediency's sake.

GetNextVal gets the NextVal from an Oracle Sequence. It accepts the sequence name and an optional OleDBConnection. This is useful when you are about to save a parent record and need to know the ID to also save child record(s).

MaybeOpenConnection comes in two flavors - a Sub and a Function (which wraps the Sub). The Sub accepts an OleDBConnection and a boolean called CloseIt. The function accepts just the OleDBConnection and returns the boolean. If the OleDBConnection is Nothing, the boolean returns True, implying that the calling code should dispose the OleDBConnection when it's done. If the OleDBConnection is not Nothing, the boolean returns false, implying that the current context should NOT dispose OleDBConnection because it was already open. Most of the database helpers and BindToReaders use MaybeOpenConnection. It is very handy for BindGrid subs on pages - the first Page_Load would call BindGrid() but certain other calls which are already using an OleDBConnection can call BindGrid(cn) so as not to have to open a separate connection.

Region: BindToReader
has several overloads of the same function for different control types. The intention is to isolate a good portion of the database code and implement the rule of threes.

BindToReader takes a SQL statement, a control and an optional OleDBConnection object. The version for DropDownList also takes an optional string for SpecialFirstItem (e.g., [ALL]).

An alternative to the SQL statement, for standard lookup tables is to pass in “@obj” which will expand to “SELECT obj_id, obj_desc from obj_lkp where audt_del_dt is null. Of course, this assumes you have a table with those columns.

The control types accepted are DropDownList, Repeater, BaseDataList (DataList, DataGrid) or ListControl (Checkboxlist, ListBox, RadioButtonList).

Region: Miscellaneous

SelectItems handles pre-selecting items in CheckBoxLists and ListBoxes. Once you've done BindToReader to populate the items, SelectItems accepts a SQL statement, ListControl and optional OleDBConnection and selects all of the ListItems in the ListControl whose values match the IDs from the SQL statement. This is not used all that often but it's very handy when this is what you want done.

SendMail sends an email. It accepts a From Address, To Address, Subject and Body. While testing, you can put a EmailOverRideRecipient in web.config who will receive the emails instead of the normal recipient. The To Address string can alternately be a SQL select statement. The first column returned is used as the address, the second (if it exists) is used as the Display Name of the recipient. This Sub requires SMTPServer in web.config.

ControlToHTML is a wrapper for RenderControl. It is often used when exporting to Excel/Word when you would Request.Clear, then ControlToHTML(the control you want to export).

Region: SimpleConversions
These are all very small and some are rarely used, but they come in handy when this is what you want!

TF2YN accepts a boolean and returns a db-quoted string - 'Y' or 'N'.
TF2YN accepts a checkbox and returns a db-quoted string - 'Y' or 'N'.
YN2TF accepts a string (Y, y, N or N) and returns a boolean.
IntToHex accepts a number and returns a string of it's Hex value. This is mostly for HTML color codes.

There are a few other Functions and Subs in Common but many of those have been commented out for years or are pretty rarely used.

SmartPhone 2003 Games

noreply@blogger.com (Steve Barron) — Wed, 18 Mar 2009 00:12:00 +0000

This post was migrated from my old blog so it is still accessible to anyone who wants either the CABs or the ZIP containing the Source Code to all of the games.

Poker No, it's not real gambling, but it is classic Video Poker for your Smartphone.		Hold 'Em Today's hottest Poker style - for your Smartphone.
Heads Up Hold 'Em In Head's Up Hold 'em, you play against "Colin" the callin' fool. Best hand wins!		Royals Happen! :)
OK, it's very, very similar to my other Hold 'em, below, it just partially solves the fact that the original was scored against a standard Video Poker pay table. This one is scored against another player. The drawback? He has no mind of his own and will always "call", or bet the same amount as you (and you are always on the big blind). It's still not the same as playing against real people but it fits in your pocket! :) Try them both and see which you like better!
Let's Ride Let it ride or pull it back with Let's Ride Poker for your Smartphone.		Island Poker Get a taste of the islands with Island Poker for your Smartphone.
Yacht Game Popular dice game, invented by some bored yacht owners - now for your Smartphone.		Black Cat If you like Red Dog, you'll love Black Cat for your Smartphone.