Wednesday, January 23, 2008

Setting Visual Studio conditional breakpoints

I had to figure out if my logic statement was executing correctly for a particular object in a 1,000 item collection.  I remembered something in the Visual Studio docs about setting “conditional breakpoints” and it turns out they are SUPER EASY to use.  Just add a breakpoint like you would normally do and set the condition in the breakpoint window (see Figure 1).

Important life skills

DataTable Select returning a DataTable

I needed to create a web service for an application to expose some of the data to another system.  I typically like to return DataTables from web service calls for the flexibility you get.  I had some existing data layer code that I wanted to reuse for this web service, but I needed to enhance the code slightly to allow the caller to filter the data before it is returned – a typical WHERE clause.  Rather than modify the working production code, I decided to just add a bit of logic in the web service code behind page, but was having trouble getting the DataTable.Select() to return a DataTable.  I ended up being able to do it using the DataTable.Clone() method:

 

    Public Function GetComponentList(ByVal componentTypes As String) As System.Data.DataTable

        Dim x, y As DataTable

        x = ComponentManager.RetrieveAllComponents()

 

        If componentTypes <> "" Then

 

            y = x.Clone()

 

            For Each row As DataRow In x.Select("Type IN(" & componentTypes & ")")

 

                y.ImportRow(row)

            Next

            If y.Rows.Count > 0 Then

                x = y

            End If

        End If

        x.TableName = "ComponentList"

 

        Return x

    End Function

 

Friday, January 11, 2008

A button that doesn't post back

I just created a button that doesn’t post back:

btnOpenReport.Attributes("onclick") = "openReportWindow(" & ddlExtractsList.ClientID & "); return false;"

The “return false;” is the part the stops the postback.  The function call openReportWindow explains why you might want to do this…you need the “ClientID” of a Server Control.

 

Wednesday, January 9, 2008

Using ADO.Net to query an Excel spreadsheet

After nearly pulling my hair out in frustration, I was finally able to get the Excel query working.  The error I was getting: “Could not find installable ISAM” turned out to be my stupidity.  The connection string needs to have the Extended Properties value quoted (notice double-quotes below: 

        ' Create connection string variable. Modify the "Data Source"

        ' parameter as appropriate for your environment.

        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _

         "Data Source=" + Server.MapPath("./AMR_AppList.xls") + ";" + _

         "Extended Properties=""Excel 8.0;"""

 

        txtResult.Text = sConnectionString

        ' Create connection object by using the preceding connection string.

        Dim objConn As New OleDbConnection(sConnectionString)

 

        ' Open connection with the database.

        objConn.Open()

 

        ' The code to follow uses a SQL SELECT command to display the data from the worksheet.

 

        ' Create new OleDbCommand to return data from worksheet.

        Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)

 

        ' Create new OleDbDataAdapter that is used to build a DataSet

        ' based on the preceding SQL SELECT statement.

        Dim objAdapter1 As New OleDbDataAdapter()

 

        ' Pass the Select command to the adapter.

        objAdapter1.SelectCommand = objCmdSelect

 

        ' Create new DataSet to hold information from the worksheet.

        Dim objDataset1 As New DataSet()

 

        ' Fill the DataSet with the information from the worksheet.

        objAdapter1.Fill(objDataset1)

 

 

Friday, January 4, 2008

OnClientClick Eval confirmation message

I needed to add a parameter to the delete confirmation message I added to OnClientClick, but Eval(“Name”, “abc {0}”) does not appear to work in VB.Net, so I went old school and used DataBinder.Eval.  I know it’s fallen out of favor, but it WORKS!

 

<asp:TemplateField>

    <ItemTemplate>

        <asp:ImageButton ID="delete" runat="server" OnClientClick='<%#  DataBinder.Eval(Container.DataItem, "Name", "return confirm(""Are you sure you want to delete [{0}] ?"");")  %>'

            CommandName="Delete" AlternateText="Delete" ImageUrl="~/images/delete.gif" />

    </ItemTemplate>

</asp:TemplateField>

 

Thursday, January 3, 2008

PL SQL Raise Application Error

http://www.camden.rutgers.edu/HELP/Documentation/Oracle/server.815/a67842/06_errs.htm#943

 

raise_application_error(-20101, 'Procedure REMOVE_COMPONENT: Missing or unrecognized component type');

PL SQL primer: If Exists, Constants, and Cursors

Helpful SQL statements…

 

DECLARE

  CNT  INT;

  -- Declare a constant

  IN_COMPONENT_ID  CONSTANT INT := 142;

BEGIN

  SELECT COUNT(* )

  INTO   CNT

  FROM   TABLE1

  WHERE  BASE_COMPONENT_ID = IN_COMPONENT_ID;

 

  -- Similar to If Exists in T-SQL

  IF CNT > 0 THEN

    DBMS_OUTPUT.PUT_LINE('found');

   

    DECLARE

      CURSOR C1 IS

        SELECT PLATFORM_ID

        FROM   TABLE1

        WHERE  COMPONENT_ID = IN_COMPONENT_ID;

    BEGIN

        -- Implicit For row declaration

      FOR APP_REC IN C1 LOOP

        DBMS_OUTPUT.PUT_LINE('id: ' || APP_REC.PLATFORM_ID);

      END LOOP;

    END;

  END IF;

END;

 

Wednesday, January 2, 2008

Used HttpContext.Items for deleting gridview row and String.TrimEnd()

There’s probably a better way, but I’m short on time so I ended up using a templated column to fire a command event that sets the argument based on the key field.  I have a feeling this is done automatically if you use a “delete button” but I wasn’t sure about the confirm prompt and I just needed this done, so I used the context to pass the value from the GridView_RowDeleting event to the ObjectDataSource.Deleting event where it is trivial to set the input parameters…

 

        Dim compId As Object = gvwComponents.DataKeys.Item(e.RowIndex).Values.Item(0)

        Context.Items.Add("CompId", compId)

 

    Protected Sub odsComponentList_Deleting(ByVal sender As Object, ByVal e As ObjectDataSourceMethodEventArgs) Handles odsComponentList.Deleting

 

        Dim compId As Object = Context.Items.Item("CompId")

 

        If CanDeleteComponent(compId) Then

            e.InputParameters.Item("componentId") = compId

        Else

            e.Cancel = True

        End If

    End Sub

Note, I discovered a cool new string function called TrimEnd():

 

            For Each row As DataRow In relatedComps.Rows

                compList += " " & row("Name") & ","

            Next

            compList = compList.TrimEnd(",")