2009 June | The Final Guide

Fetch the incremented Identity value from MS-SQL Server

Wednesday, June 10th, 2009

The Microsoft SQL Server Database provides an Identity value which is generated automatically when a record is added in such tables which contains IDENTITY. We can fetch the auto incremented value from MS-SQL Server database to our applications using built in function @@IDENTITY( ) (used in MS-Access) or SCOPE_IDENTITY( ) which is the recommended method for obtaining identity value on SQL Server. Scope_Identity( ) retrieves the last identity value created in the current scope which is a single module that can be a stored procedure, batch, function or trigger. We can implement the coding to retrieve the Scope_Identity in many ways.
1. Using batch command with two sql queries seperated with a semicolor, with ExecuteScalar( ) against the batch command to return single value.

[C#]
string strSQL = "INSERT INTO PRODUCTS (PRODUCT_NAME) VALUES(@PRODUCT_NAME); SELECT SCOPE_IDENTITY()";
int intID;
string strSqlCon = @"Server=.SQLEXPRESS;User Id=sa;Password=sa;Database=MyDb";
using(SqlConnection dbCon = new SqlConnection(strSqlCon))
{
      dbCon.Open();
      using(SqlCommand Cmd = new SqlCommand())
      {
           Cmd.Connection = dbCon;
           Cmd.CommandType = CommandType.Text;
           Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
           intID = (int)Cmd.ExecuteScalar( );
      }
      dbCon.Close( );
}

2. We can use output parameter from a stored procedure and ExecuteNonQuery( ).
Stored Procedure declaration

CREATE PROCEDURE dbo.SP_PRODUCT_ADD
(@PRODUCT_NAME VARCHAR(250), @PRODUCT_ID INT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO PRODUCTS (PRODUCT_NAME) VALUES (@PRODUCT_NAME);
    SET @PRODUCT_ID = SCOPE_IDENTITY( );
END

Our Application Coding…

[C#]
int intID;
string strSqlCon = @"Server=.SQLEXPRESS;User Id=sa;Password=sa;Database=MyDb";
using(SqlConnection dbCon = new SqlConnection(strSqlCon))
{
      dbCon.Open( );
      using(SqlCommand Cmd = new SqlCommand( ))
      {
           Cmd.Connection = dbCon;
           Cmd.CommandType = CommandType.StoredProcedure;
           Cmd.CommandText = "SP_PRODUCT_ADD";
           Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
           SqlParameter parm1 = new SqlParameter( );
           param1.ParameterName = "@PRODUCT_ID";
           param1.SqlDbType = SqlDbType.BigInt;
           param1.Direction = ParameterDirection.Output;
           Cmd.Parameters.Add(param1);
           Cmd.ExecuteNonQuery( );
           intID = Convert.ToInt32(param1.Value);
      }
      dbCon.Close( );
}

10.06.2009 Database, SQL Server No Comments

Fetch the incremented AutoNumber value from MS-Access

Wednesday, June 10th, 2009

The Microsoft Access Database provides AutoNumber fieldtype which is generated automatically when a record is added in such tables which contains AutoNumber. We can fetch the auto incremented value from MS-Access database to our applications using built in function @@IDENTITY( ).

The Jet 4.0 provider supports @@Identity( ) to obtain new Identity value from Access .mdb file which returns the value of an autoincrement column that is generated on the same connection. That means the Connection object used for the Insert query must be re-used without closing it and opening it up again to fetch the Identity value as shown in the below example.

[C#]
string strSQL = "INSERT INTO PRODUCTS (PRODUCT_NAME, PRODUCT_MODEL) VALUES (@PRODUCT_NAME,@PRODUCT_MODEL)";
int intID;
string strAcsCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|MyDb.mdb";
using (OleDbConnection dbCon = new OleDbConnection(strAcsCon))
{
     dbCon.Open( );
     using (OleDbCommand Cmd = new OleDbCommand())
     {
          Cmd.Connection = dbCon;
          Cmd.CommandType = CommandType.Text;
          Cmd.CommandText = strSQL;
          Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
          Cmd.Parameters.AddWithValue("@PRODUCT_MODEL", txtProdModel);
          Cmd.ExecuteNonQuery( );
          strSQL = "SELECT @@IDENTITY";
          Cmd.CommandText = strSQL;
          intID = (int)Cmd.ExecuteScalar( );
     }
     dbCon.Close( );
}

The above built-in function @@IDENTITY( ) can be used to retrieve Identity value from SQL Server too with the same above coding.

10.06.2009 ASP.Net, Web No Comments

Error: The Operation must use an updatable query

Tuesday, June 9th, 2009

When we work with an ASP.Net application with MS-Access as backend database, we often face the error “The operation must use an updatable query”. Even though our code compiles and builds without any errors, when we run the application, when the page tries to INSERT or UPDATE records the above error will be raised. We’ll see the details of the error root cause and the easy solution.

When working with MS-Access database in ASP.Net web application, the MS-Access database.mdb file will be placed in the App_Data folder. When we develop ASP.Net application in multi-user environment(like Windows XP, Windows 2003, Windows 2008), a.ldb file will be created whenever the database is opened. This particular .ldb file contains details of the user who opened the file, and prevents writing the records which are opened by another user.

All ASP.Net applications use the “ASPNET” machine account in Windows XP and “NETWORK SERVICE” account in Windows Server 2003, 2008 and Vista machines to access the file system of Operating System. Inorder to create, write and delete the required .ldb file, the relevant user “ASPNET” or “NETWORK SERVICE” account must needs MODIFY / WRITE permissions on the folder where the .mdb file resides which is App_Data folder in our ASP.Net Application.

To enable MODIFY / WRITE permissions on the App_Data folder, open the ASP.Net web application project folder in Windows Explorer, right click on the App_Data folder and select properties. When we reach properties we can see several tabs, among the tabs, go to Security tab which contains User Names section as shown below.

click the Edit button which will show UserNames window as shown below.

and then click Add which shows as follows

Type the account and click “Check Names” to verify the account name typed and click “OK” to add the user account. Upon clicking add, we’ll reach to Security Permission Edit Window, then click “Modify” for the account we added and click ok and Run the ASP.Net web application which can insert & update in the Microsoft Access Database.

The above solution can even fix the errors like The Microsoft Jet database engine cannot open the file (unknown), It is already opened exclusively by another user, or you need permission to view its data.

09.06.2009 ASP.Net, Programming, Technology, Web, Windows No Comments

Highlight ASP.Net Gridview Row on Mouse Hover

Monday, June 8th, 2009

It’ll be very attractive & meaningful UI to highlight a GridViewRow when mouse pointer hovers which can be achieved with the following code.

[C#]
    protected void gvItems_DataBound(object sender, EventArgs e)
    {
        try
        {
            foreach (GridViewRow gvRow in gvItems.Rows)
            {
                gvRow.Attributes["onmouseover"] = "JavaScript:" + gvRow.ClientID + ".style.backgroundColor = '#CCEE99'";
                gvRow.Attributes["onmouseout"] = "JavaScript:" + gvRow.ClientID + ".style.backgroundColor = '#FFFFFF'";
            }
        }
        catch (Exception ex)
        { throw New Exception("Error : " + ex.Message); }
    }

[VB]
    Protected Sub gvItems_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvItems.DataBound
        Try
            For Each gvRow As GridViewRow In gvItems.Rows
                gvRow.Attributes("onmouseover") = "JavaScript:" + gvRow.ClientID + ".style.backgroundColor='#CCEE99'"
                gvRow.Attributes("onmouseout") = "JavaScript:" + gvRow.ClientID + ".style.backgroundColor='#FFFFFF'"
            Next
        Catch ex As Exception
            Throw New Exception("Error: " & ex.Message)
        End Try
    End Sub

08.06.2009 ASP.Net, Programming, Web No Comments

Adding CheckBox Control to ASP.Net GridView

Monday, June 8th, 2009

While working with inventory screens or multiple items of any category, we often need to face situation of representing the items in ASP.Net GridView on a webpage providing CheckBox to select single or multiple items. CheckBox control can’t be placed in a column of GridView directly, in which case it must be embedded in an ItemTemplate column of the ASP.Net Gridview.

Design Source

<asp:GridView ID="grvItems" runat="server" AutoGenerateColumns="False" DataKeyNames="ItemNo">
  <Columns>
    <asp:BoundField DataField="ItemName" HeaderText="Name" />
    <asp:BoundField DataField="Description" HeaderText="Description" />
    <asp:TemplateField HeaderText="No" InsertVisible="False">
      <ItemTemplate>
         <asp:CheckBox ID="chkItemNo" runat="server" Text='<%# Eval("ItemNo") %>' />
       </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>

Coding

C#]
foreach (GridViewRow gvRow in grvItems.Rows)
{
CheckBox chk = (CheckBox)gvrRow.FindControl("chkItemNo");
if (chk.Checked)
  {
    Do_Something_edit_delete_update(grvItems.DataKeys[gvRow.RowIndex].Value);
  }
}

[VB]
For Each gvRow As GridViewRow In grvItems.Rows
  Dim chk As CheckBox = DirectCast(gvRow.FindControl("chkItemNo"), CheckBox)
  If chk.Checked Then
    Do_Something_edit_delete_update(grvItems.DataKeys(gvRow.RowIndex).Value)
  End If
Next

08.06.2009 ASP.Net, Programming, Web No Comments

Make Windows XP to shutdown faster

Friday, June 5th, 2009

Windows XP will be shutting down in a normal pace even on high speed machines also as it tries to kill all the processes. We can use registry tweaks to enhance the windows shutdown mechanism.

Open Registry Editor by typing Regedit command in Run of windows Start button.

Navigate to HKEY Current User\Control Panel\Desktop
Double click on the AutoEndTasks entry and replace the 0 with 1
Check for entry WaitToKillAppTimeout exists or not, if doesn’t exist then create and edit the value to 2000
Check for entry HungAppTimeout exists or not, if doesn’t exist then create and edit the value to 1000

Reboot your pc and you can realize your windows xp shuts down faster than earlier.

Note: Please save your applications before you go for shutdown otherwise it wont prompt you to save the work done in your applications. This tweak is to reduce the shutdown time by killing the processes running in background.

05.06.2009 Technology, Windows No Comments

Download and Install ASP.Net Ajax for Visual Studio

Thursday, June 4th, 2009

AJAX stands for Asynchronous JavaScript and XML. Microsoft ASP.NET AJAX enables us to quickly create Web pages that are rich responsive and familiar user interface (UI) elements. ASP.Net Ajax is integrated to ASP.Net 2.0 sever based development platform. ASP.NET AJAX contains various client-script libraries that incorporate cross-browser ECMAScript (JavaScript) and dynamic HTML (DHTML) technologies. By using ASP.NET AJAX, we can improve the user experience and the efficiency of our Web applications.

Few Advantages of Ajax:

web page is improved by performing significant parts the web page.
Mostly used UI elements like progress indicators, tooltips, and pop-up windows.
Partial-page updates that refresh only the parts of the Web page that have changed.

Availability and Download

Ajax Library 3.5 for ASP.Net framework 3.5 (will be installed with Visual Studio 2008)
ASP.Net 2.0 Ajax Extensions 1.0 (add on for Visual Studio 2005)

ASP.Net 2.0 Ajax Extensions 1.0 can be downloaded from

http://download.microsoft.com/download/5/4/6/5462bcbd-e738-45fa-84ca-fa02b0c4e1c2/ASPAJAXExtSetup.msi

Once you install ASP.Net 2.0 Ajax Extensions, you can install optional ASP.Net Ajax Control Toolkit. The ASP.NET AJAX Control Toolkit is an open-source project built on top of the Microsoft ASP.NET AJAX framework. The AJAX Control Toolkit contains more than 30 controls that enable you to easily create rich, interactive web pages. Some examples are Calendar control, Modal Popup control etc.

ASP.Net Ajax Control Toolkit can be downloaded from here

http://webscripts.softpedia.com/scriptDownload/AJAX-Control-Toolkit-Download-14014.html

The Ajax Control Toolkit comes in zip file which contains AjaxControlExtender.vsi file (in AjaxControlExtender folder) which is a visual studio content installer. Upon clicking the intaller it adds AjaxControl ToolKit to Visual studio in a seperate tab in Tools.

04.06.2009 ASP.Net, Programming, Web No Comments

Remote Assistance Windows XP

Thursday, June 4th, 2009

Using Remote Assistance the Novice users can request help from Expert users on Windows XP machines over the Internet. With this tool, one user, called the “Expert,” can view the desktop of another user, the “Novice.” With the Novice’s permission, the Expert can even share control of the Novice’s computer to resolve issues remotely.

Requirements for Remote Assistance

Same version of Windows XP operating system must be installed on the both novice’s computer and the expert’s computer or either Windows Server 2003 on both novice’s computer and expert’s computer.
Expert’s computer and Novice’s computer must be connected using a common network, Internet can serve as this common network.

Sending a Remote Assistance Invitation

Novice user can send Invitation to an expert user by clicking Start, and then clicking Help and Support.
Click the Invite a friend to connect to your computer with Remote Assistance under Ask for Assistance.
Click Invite someone to help you when the Remote Assistance page is displayed.
Available options for sending the Remote Assistance invitation: Windows Messenger, email, or saving the invitation as a file.
Using the email or Save as Invitation methods, Expert’s connecting session will be provided with a password. Noice user must give a time period within which the invitation will be automatically expired and the Novice user can expire any invitation (invitations which are valid also) by clicking the View invitation status.
Expert will be able to connect to Novice’s computer when he/she receives the invitation. While connecting the Expert has to enter the password which was set by Novice. When the connecting credentials are authenticated the Expert can initiate the Remote Assistance session.
The Invitation status is made sure by the Novice computer whether the invitation is still open, otherwise session will be terminated. If the invitation is open and the password is correct, the Novice receives a notification stating that the Expert wants to start the session now and the Novice is prompted to start the Remote Assistance session.
If the Novice user accepts to start the session then the Remote Assistance Novice chat dialog box will open on the Novice’s computer and the Remote Assistance Expert console opens on the Expert’s computer. At this point, the Expert can see everything on the Novice computer, in real time.
The Expert can request to take control of the Novices computer at this point by clicking the Take Control button on the Expert console which sends a message to the Novice’s computer notifying the Novice that the Expert is requesting to take control of the computer. The message provides the following three methods by which the Novice can stop the Experts control of their computer:
Press the ESC key.
Hold down the CTRL key, and then press the C key.
Click the Stop Control button next to the Novice’s chat window.
If the Novice chooses to give control of the computer to the Expert, the Novice and the Expert share control of the keyboard and the mouse. It is best if the Novice does not move the mouse or type when the Expert has control because the session responds to both users inputs, which causes the mouse to behave erratically. If the Novice stops control, the Remote Assistance session continues and the Expert can still see the Novice’s desktop.

04.06.2009 Technology, Windows No Comments

How to download online flash videos

Wednesday, June 3rd, 2009

Online videos is a great choice of delivering information now-a-days. Especially education related information being presented in online videos format. These videos deliver great and valuable information using which we can learn new technology with ease. These videos will work so great like a lecturer. Some videos will be available for free and some may be commercial depending on the content of the educational material. Moreover the first requirement to view these educational videos is the lattest flash player along with the internet browser.

Everybody is fond of these online educational videos. Everybody needs and everybody wants to have these videos permanently to be stored on their pcs rather than searching for them online everytime they want to follow. But these flash videos cannot be downloaded as they are embedded into the flash object within the script of the webpage. Even we go through the scripting of the webpage, we can’t get the source url hidden for the education video file as it’ll be embedded within the flash object. I was wondering whether possibility is there to save these videos. Then my Proj. Mgr. Mr. Saravanan showed me the way to download the online flash videos using the lattest Real Player.

Just install the lattest version of Real Player. Then open the online video file and start watching. When you place the mouse over the video at any region, then at the top right corner you can get a drop down menu item saying “Download This Video” as shown below.

Upon clicking Download this video button, video will be downloaded to the Real Player videos directory which will be in User Documents depending on the windows version we use.

Once the download is finished, you can play either in the Real Player or your own choice of Video Player. Even you can right click on the downloaded file and choose “locate file on disk ” and can copy to your preferred location or media.

03.06.2009 Technology No Comments

The Final Guide

Fetch the incremented Identity value from MS-SQL Server

Fetch the incremented AutoNumber value from MS-Access

Error: The Operation must use an updatable query

Highlight ASP.Net Gridview Row on Mouse Hover

Adding CheckBox Control to ASP.Net GridView

Make Windows XP to shutdown faster

Download and Install ASP.Net Ajax for Visual Studio

Remote Assistance Windows XP

Requirements for Remote Assistance

Sending a Remote Assistance Invitation

How to download online flash videos

Our Newsletter

Recent Posts

Facebook Page

Categories

Recent Comments