JQuery - BlockUI & asp:Button controls

Using the BlockUI Jquery library to create a Yes/No prompt on the click event of an asp:Button control

* There were problems copying/pasting the jQuery function into here - So I had to screenshot for now

<div><asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" /></div>

<div id="question" style="display:none; cursor: default"> <h1>Would you like to contine?.</h1> <input type="button" id="yes" value="Yes" /> <input type="button" id="no" value="No" /> </div>

SSIS Web Service Task, Variables and Setting up the SQL Agent

One of the things i've found the most difficult to find any sort of good help on, is SSIS.
I intend to write up a few of the important things that ive found and that are not necessarily clear in any official Microsoft atricles or KBs.

I'll start off with one of the things that took me the longest to find - If you have an SSIS package, that contains a Web Service Task that expects parameters, how do you pass parameters to it in SSMS?

SQL Server Agent

Setting the Parameters/Variables to pass to the Web Service
 
In the Set Values tab in the Job Step Properties of the package, you can set the value of the package variables, which will be used to be passed as the parameters into the Web Service. In property path, add :

\Package.Variables[VarName].Value (Where VarName is the name of the variable defined in BIDS - In this case the variable I want to pass is UserID) into the Property Path column and and value you want to pass in the Value column

Access Schema Information in SQL

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM sysobjects


http://brainof-dave.blogspot.com/2006/11/xtype-values-in-sysobjects.html

Editing items when ASPXGridView bound to Data Table

There are plenty of resources to help you with Editing data in an ASPXGridview out there, but a majority of it is editing data that is DataBound to a Datasource (like a SQLDataSource for example)

This has been fine for me in the past, untill I started looking at Retrieving WebConfig Values into DataTable project. Basically I needed to return the web.config values into a DataTable, and display them in a Grid, where they could be edited and the config file would be updated.

So in this instance I was unable to use the a SQLDatasource and had to manually bind the Data Table to the Grid.
That was the easy part.

The next bit was to get the data I had retrieved into the Grid to be Editable. And this is where I found the fountain of information began to ebb.

The Oasis of readily available information became dribbles of partial usefulnessess. I managed to figure it out in the end, but It's taken me a lot longer than usual to find this.

The actual process is pretty simple, but if you dont know it, you dont know it. So hopefully this will help any one in the same boat as I was.

Drag an ASPXGrid onto the page in Design view. Ensure that the AutoGenerateColumns property is set to true.

Switch to the Code view. To populate the grid programmatically use the following code:

ASPxGridView1.DataSource = dt
ASPxGridView1.KeyFieldName = "UniqueID"
ASPxGridView1.DataBind()

Note You do not need to add the KeyFieldName if youre just retrieving and binding the data, but you will need it for any of the events where you will be amending the data.

To create an Edit button, use the following:

Protected Sub ASPxGridView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ASPxGridView1.DataBound
     If ASPxGridView1.Columns("Command") Is Nothing Then
        Dim cmdCol As GridViewCommandColumn = New GridViewCommandColumn("Command")
        cmdCol.EditButton.Visible = True
        ASPxGridView1.Columns.Add(cmdCol)
     End If
End Sub
 
If you have the KeyFieldName populated, when you compile & run the solution, when you click the edit button, it will put the grid into editing mode.
 
Finally, when youve updated the data, you will need to handle the event that is triggered when clicking the update button. This event is the RowUpdating event.
 
Protected Sub ASPxGridView1_RowUpdating(ByVal sender As Object, ByVal e As DevExpress.Web.Data.ASPxDataUpdatingEventArgs) Handles ASPxGridView1.RowUpdating


Dim value1 As String = e.NewValues.Item(0)
Dim value2 As String = e.NewValues.Item(1)

CallUpdateDataBaseMethod(value1, value2)

ASPxGridView1.CancelEdit()
ASPxGridView1.DataBind()

End Sub
 
In this section you can define what happens in the code. In this example, I have taken the values of the first 2 columns and call a database editing method, which requires those values.
 
A point to note is as this event is primarily used to update databound datasources, we will need to cancel the editing process to get back to our normal data view.

Retrieving Values from Web.Config into DataTable

DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available.

I have finally managed, after struggling for many, many, many hours, to get SSIS to pull data from JDE and enter it into an SQL database.

I will post the full tutorial at a later date, but I wanted to address the main issue i faced in this whole painful ordeal :

[OLE DB Destination [1070]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". 




You need to make sure the Data Conversion datatypes match the expected datatypes AND length exactly for the destination, for example, if the SQL field is a type of varchar with a length of 50 - the Datatype conversion in the Data Conversion Transformation Editor must be set to string[DT_STR] with a length of 50

Retrieving values from Web.Config

  VB.net
ConfigurationManager.AppSettings("configValue")

Web.Config





      

DevExpress Controls - Handling client side events

After much struggling with using the client side events of most of the DevExpress controls i've tried using, I came across this video from Mehul Harry.

It gives a brief run-through of how the client side events work, which for someone who is used to working on Server side events complete with Intelli-Sense, is a real help.

Retrieving cell data from AspxGridView using focused rows

This is very useful for instances where there is a Child grid with a datasource that requires a value from the Parent grid.

In this example, we will begin with a Parent grid (gridParent) which is databound to a SQL Database. We are retrieving several values, but the most important for this demo is AppID

In the SettingsBehaviour properties we will need to set AllowFocusedRow to True. Also, we need to set ProcessFocusedRowChangedOnServer to True, as this stops the event being handled client side, and will process the event on the server side.

Next, we need to add the method to handle the FocusedRowChanged event. To get the AppID field we need to feed into the gridChild, we need to call the GetRowValues method, which requires a row index and a column ID.

To get the row index, we create a new aspxGridView instance from the Sender object arguement, which will give us the ability to call the FocusedRowIndex() method.

Then we need to give it the name of the column we require, in this case "AppID"

AspxGridview Custom buttons and actions

This post is to describe the process of creating a custom button in a Dev Express AspxGridview and to create a code behind event that will handle it.





The example I am going to use is a simple 'Admin' function which will return a list of Role IDs that the user has. We also want to be able to give our Admins the ability to remove one or more roles from the user's profile.





From a technical side, we will be using an ASPXGridview grid databound to a SQL database, as shown below

To Create the "Remove" button, you will need to add a CustomButton column, which inherits from the GridViewCommandColumn class. This can be done by switching to the ASPX Source view, and adding the following between your <column> tags

<dxwgv:GridViewCommandColumn VisibleIndex="0">

        <CustomButtons>

            <dxwgv:GridViewCommandColumnCustomButton   ID="Remove" Text="Remove">

            </dxwgv:GridViewCommandColumnCustomButton>

        </CustomButtons>

</dxwgv:GridViewCommandColumn>

The entire code for the Grid is :

 

<dxwgv:ASPxGridView runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" ID="gridUserRoles" EnableCallBacks="False"><Columns>
    <dxwgv:GridViewCommandColumn VisibleIndex="0">
        <CustomButtons>
            <dxwgv:GridViewCommandColumnCustomButton ID="Remove" Text="Remove">
            </dxwgv:GridViewCommandColumnCustomButton>
        </CustomButtons>
    </dxwgv:GridViewCommandColumn>
    <dxwgv:GridViewDataTextColumn FieldName="RoleID" VisibleIndex="1">
    </dxwgv:GridViewDataTextColumn>
</Columns>
</dxwgv:ASPxGridView>

Next -

We need to define the Event in the code behind. Create a new CustomButtonCallBack Method, which will serve as a handler for any custom actions that fire.

Finally, we use the Event Args of the Gridview custom button, to find our newly created button. The Event args are referenced when then new CustomButtonCallBack method is created, so we dont even need to worry about creating that.

Protected Sub gridUserRoles_CustomButtonCallback(ByVal sender As Object, ByVal e As DevExpress.Web.ASPxGridView.ASPxGridViewCustomButtonCallbackEventArgs) Handles gridUserRoles.CustomButtonCallback

       If e.ButtonID <> "Remove" Then
           Return

     Else

    ‘Code for removing users role

Saving Changes is not permitted (SQL 2008)

Ive recently upgraded to SQL 2008 and the first major difference i noticed was the inability to save changes to a table schema without dropping the contents of the table first (see dialog box below).

This is unacceptable in most cases, as I need to make changes to existing tables which contain large amounts of data.

After much googling I found a solution from Henry Cordes which did the trick. 

Goto Tools > Options

And then goto Designers > Table and Database Designers and deselect the option 'Prevent saving changes that require table re-creation'

Using Enterprise Library's Data Access Block to retrieve dataset

This is basic boilerplate code for the use of the Microsoft Enterprise Library Data Access Block (EntLib DAB) using VB.net to Retrieve data using a Stored procedure.


Imports Microsoft.Practices.EnterpriseLibrary.Data

Try
 'Declare Database - All you need to do is enter the connection string referenced in the  Web.Config. 
 Dim db As Database = DatabaseFactory.CreateDatabase("connectionString")

'Set up the database command and enter the name of the stored procedure you want to use
 Dim dbCmd As DbCommand = db.GetSqlStringCommand("usp_RemoveIndividualSystemID")


'It is important to set the "commandType" to StoredProcedure so it will look for a proc, rather than interpret the String as a SQL statement
dbCmd.CommandType = CommandType.StoredProcedure


'Add the parameters that are required for the proc, in this case, it expects a value called @param, and we are assinging as a type of 'String' and giving it a value of "Parameter Value"

db.AddInParameter(dbCmd, "@param", DbType.String, "Parameter Value")

'Declare a dataset and Execute the db.ExecuteDataSet command, which will fill ds with the data returned from the database
 Dim ds As DataSet = db.ExecuteDataSet(dbCmd)

Catch ex As Exception

'Exception Handling

End Try