clickable tr and Show/Hide Panel

April 28, 2009 by vijeyashobana

Sometimes we may need to code to make an entire row clickable, I mean, the user should click any cell on a tr and a required functionality should be performed as the result of this click. When you cannot use server side coding and rely entirely on client side coding, the below could be one of the approaches to this problem.

        <asp:Panel ID=”_pnlContactList” runat=”server” CssClass=”tableBorder” Height=”200px” ScrollBars=”Vertical”>
               <table cellspacing=”0″ style=”width: 970px; margin-top:5px; margin-left:10px; margin-bottom:5px; margin-right:5px” class=”tableBorder FontNameValue”>
                <tr>
                    <td align=”center” class=”FontName tableBorder tdheader”> Name</td>
                    <td align=”center” class=”tableBorder tdheader FontName”>Title</td>
                    <td align=”center” class=”FontName tableBorder tdheader”>Phone Number</td>
                    <td align=”center” class=”tableBorder tdheader FontName”>Email</td>
                </tr>
               
                <tr align=”left” onclick=”Show(1)”>
                    <td align=”left” class=”tableBorder”>Bill</td>                   
                    <td align=”left” class=”tableBorder”>Gates</td>
                    <td class=”tableBorder”>800-000-0000</td>
                    <td class=”tableBorder”>bill.gates@microsoft.com</td>
                </tr>
               
                <tr onclick=”Show(2)”>
                    <td align=”left” class=”tableBorder”>Bill</td>                      
                    <td align=”left” class=”tableBorder”>O’Reilly</td> 
                    <td class=”tableBorder”>800-000-0000</td>
                    <td class=”tableBorder”>bill.reilly@foxnews.com</td>
                </tr></table></asp:Panel>

Javascript functions inside <head>

<script type=”text/javascript”>
var nameArray = [];
nameArray[1] = “Bill Gates”;
nameArray[2] = “Bill O’Reilly”;
nameArray[3] = “Bill Clinton”;
nameArray[4] = “Donald Trump”;
nameArray[5] = “Donald Duck”;
nameArray[6] = “Donald Rumsfeld”;
nameArray[7] = “Home Foreclosure”;
nameArray[8] = “Credit Card”;
var emailArray = [];
emailArray[1] = “bill.gates@microsoft.com“;
emailArray[2] = “bill.reilly@foxnews.com“;
emailArray[3] = “bill.clinton@capitol.com“;
emailArray[4] = “donald.trump@empire.com“;
emailArray[5] = “donald.duck@disney.com“;
emailArray[6] = “donald.rumsfeld@state.com“;
emailArray[7] = “home.foreclosure@states.com“;
emailArray[8] = “credit.card@debt.com“;

 

    function Hide()
    {
        document.getElementById(‘_divPnl’).style.visibility = ‘hidden’;
    }

    function Show()
    {
        document.getElementById(‘_divPnl’).style.visibility = ‘visible’;
    }
   
    function Show(elem)
    {       
        for(var i=1; i<=8; i++)
        {
            if(elem == i) 
            {         
                document.getElementById(‘name’).innerHTML = nameArray[i];
                document.getElementById(‘email’).innerHTML = emailArray[i];
                document.getElementById(‘_divPnl’).style.visibility = ‘visible’;
            }
        }
   }

</script>

coming back to markup:

in my case, i want to show a panel when a tr is clicked, this panel will contain detailed information relevant to the <tr>. this panel would be initially hidden, and when the <tr> is clicked, will be displayed.

            <div id=”_divPnl” style=”visibility:hidden”>
        <asp:Panel ID=”_pnlContactDetails” runat=”server” CssClass=”tableBorder” Height=”170px”>
               <table cellspacing=”0″ style=”width: 970px; margin-top:5px; margin-left:10px” class=”tableBorder”>
                <tr>
                    <td align=”center” class=”FontName tableBorder tdheader”>
                        Address</td>
                        <td class=”tableBorder tdheader” style=”color:Silver”>none</td>
                    <td align=”center” class=”FontName tableBorder tdheader” style=”color:Silver”>none
                        </td>
                        <td class=”tableBorder tdheader” style=”color:Silver”>none</td>
                </tr>
               
                <tr>
                    <td align=”left” class=”tableBorder FontName”>Name</td>
                    <td id=”name” align=”left” class=”tableBorder FontNameValue”></td>  //empty <td>, value will be dynamically displayed using <script> functions
                    <td class=”tableBorder FontName”>Title</td>
                    <td class=”tableBorder FontNameValue”>Mr. </td>
                </tr>
                <tr>
                    <td align=”left” class=”tableBorder FontName”>
                        Address Line 1</td>
                    <td align=”left” class=”tableBorder FontNameValue”>I-275 W
                    </td>
                    <td class=”tableBorder FontName”>Telephone Number</td>
                    <td class=”tableBorder FontNameValue”>888-890-9021</td>
                </tr>
               
                <tr>
                    <td class=”tableBorder FontName”>Address Line 2</td>
                    <td class=”tableBorder FontNameValue”>Apt O</td>
                    <td class=”tableBorder FontName”>Cell Phone Number</td>
                    <td class=”tableBorder FontNameValue”>888-888-8888</td>
                </tr>
               
                <tr>
                    <td class=”tableBorder FontName”>City</td>
                    <td class=”tableBorder FontNameValue”>Cincinnati</td>
                    <td class=”tableBorder FontName”>Fax Number</td>
                    <td class=”tableBorder FontNameValue”>800-000-0000</td>
                </tr>
               
                <tr>
                    <td class=”tableBorder FontName”>State</td>
                    <td class=”tableBorder FontNameValue”>OH</td>
                    <td class=”tableBorder FontName”>Email Address</td>
                    <td class=”tableBorder FontNameValue”><a href=”someone@somewhere.com” id=”email”></a></td>
                </tr>

                <tr>
                    <td class=”tableBorder FontName”>Zip Code</td>
                    <td class=”tableBorder FontNameValue”>00000</td>
                    <td class=”tableBorder FontName”>Contact Type</td>
                    <td class=”tableBorder FontNameValue”>Customer</td>
                </tr>
            </table>
        </asp:Panel>
        </div>

 

References:

1. http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=312

2. http://www.devx.com/tips/Tip/13638

3. http://www.tizag.com/javascriptT/javascript-getelementbyid.php

Posted in ASP.NET, Javascript | Leave a Comment »

SELECT from EXEC StoredProc

April 20, 2009 by vijeyashobana

If you want to retrieve the results of EXECuting a Stored Proc the most common way is to create a temp table and insert the results of EXEC stored proc into this table. The table designs of the temp table and the result of the EXEC storedproc should be the same.

Example:

CREATE TABLE #TEMP
(CONTACTID INT,
CONTACTTITLE VARCHAR(50),
CONTACTFIRSTNAME VARCHAR(50),
CONTACTLASTNAME VARCHAR(50),
FAX VARCHAR(20),
PHONE1 VARCHAR(20),
PHONE2 VARCHAR(20),
EMAIL VARCHAR(50),
CONTACTTYPEID INT,
LOCATIOND INT
)
INSERT INTO #TEMP EXEC wtg_ContactMasterGet 4
SELECT * FROM #TEMP

Here the stored proc wtg_ContactMasterGet  takes a parameter 4 and returns a result set. The types of the result set must match with the table design of the temp table.

 

References: http://www.sqlteam.com/article/return-recordsets-from-dynamic-queries-called-by-exec

Posted in SQL Server | Leave a Comment »

ASP.NET WebParts on SharePoint – For a Beginner

April 3, 2009 by vijeyashobana

Environment: .NET 3.5, ASP.NET, SharePoint, WSS 3.0, VS 2008, C#

With VS 2008 comes SharePoint templates. To get to this:

Step 1: New Project-> Windows -> SharePoint (LHS) -> Click on WebPart template (RHS)

Step 2: Open the WebPart1 project

Step 3: Uncomment the C# code in WebPart1.cs in CreateChildControls()

Step 4: Save. In Solution Explorer, Right click on Deploy.

Step 5: In C:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config (aka 12-hive) do the following:

5.1 <compilation> set debug = true To enable debugging on WebPart code

5.2 <customErrors> set Off. As a dev you want to see the actual error output.

5.3 <CallStack> set True. You want to see the error stack trace to pinpoint the error

5.4 <ApplicationPageLevelTrace> set True. You want to view page level tracing

Step 6: Build, Run

Step 7: http://localhost will change to http://mossdev/default.aspx?PageView=Shared. Site Actions->Edit Mode -> Click ‘Add a Web Part’. Select WebPart1 from the list. Click Add.  

Reference:

http://visualstudiomagazine.com/features/article.aspx?editorialsid=2644

Posted in ASP.NET, SharePoint | Leave a Comment »

Open a .doc or .xls as .pdf aka Convert .doc or .xls to .pdf

April 1, 2009 by vijeyashobana

One of my recent tasks was to code to open a .doc or .xls as .pdf. The file should open as .pdf and then it’s upto the user to save the .pdf. I tell you I had to scourge the internet looking for good leads. There are many articles in www.codeproject.com, many threads in asp.net forums, and one project called iTextDotNet in www.sourceforge.net, a few blogs – all related to the same topic. But it was in www.msdn.com I found what I needed. I immediately tested it and cooooollllllll……gotcha. For developers, here you go….

I did this in VS 2008 and Office 2007. I know this works good on .docx, .xlsx(Office 2007), .doc (Microsoft Office Word 97 – 2003 Document), .xls (Microsoft Office Excel 97-2003 Worksheet) – I couldn’t try this code on Office 2003 directly but I saved the files in Office 2003 Compatible format and it works good.

TODO:

1. Add namespaces

 

 

 

using MSExcel = Microsoft.Office.Interop.Excel;//Add References-> COM
 
2. Install Addin : SaveAsPDFAndXPS
 
//code
object Unknown = Type.Missing; 
 
MSWord.Application newApp = new Microsoft.Office.Interop.Word.Application();
 
//source document
object src_doc = “C:\Users\admin\Desktop\DocumentViewer\test.docx”; 
 
newApp.Documents.Open(ref src_doc, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown,  ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown);  //# of params 16, # of Unknowns 14
 
//save as PDF
object format = MSWord.WdSaveFormat.wdFormatPDF;
 
newApp.ActiveDocument.SaveAs(ref des_doc, ref format, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown);
 
object des_doc= “C:\Users\admin\Desktop\DocumentViewer\test.pdf”;
 
newApp.Quit(ref Unknown, ref Unknown, ref Unknown);
 
//now to open .xls as .pdf 

MSExcel.Application newExcelAppn = new Microsoft.Office.Interop.Excel.Application ();

MSExcel.Workbook newbook = null; 
 
string src_xls = “C:\Users\admin\Desktop\DocumentViewer\To_Bret_Conard.xlsx”; 
 object des_xls = “C:\Users\admin\Desktop\DocumentViewer\To_Bret_Conard.pdf”;
 
newbook = newExcelAppn.Workbooks.Open(src_xls, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown);
 
//specify the format. PDF 
MSExcel.XlFixedFormatType paramExportFormat = MSExcel.XlFixedFormatType.xlTypePDF; 
 
 // Save it in the target format.
newbook.ExportAsFixedFormat(paramExportFormat, des_xls, MSExcel.XlFixedFormatQuality.xlQualityStandard, true, true, Unknown, Unknown, false, Unknown);
 
newExcelAppn.Quit();

  //view the pdf in browser 

 byte[] buffer = new byte[1024]; 
 
long byteCount;
 
inStr =File.OpenRead(“C:\Users\admin\Desktop\DocumentViewer\To_Bret_Conard.pdf”); 
 
while((byteCount = inStr.Read(buffer, 0, buffer.Length)) > 0)
{
if(Context.Response.IsClientConnected)
{
Context.Response.ContentType = “application/pdf”;
Context.Response.OutputStream.Write(buffer, 0, buffer.Length);
Context.Response.Flush();
}
}
 
References:

http://msdn.microsoft.com/en-us/library/bb407651.aspx

http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/f8989c05-d04a-4b4a-be0f-fc0055691de7

Tags: , , ,
Posted in ASP.NET | Leave a Comment »

Refresh page whenever required

August 2, 2008 by vijeyashobana

To refresh/reload a page, there are many javascript ways. Here I am going to discuss about one of them. This feature is not widely available in the internet. I searched and realized!!!. The method goes thus:

ClientScript.RegisterStartupScript(GetType(),”keyString”, “javascriptString”);

string javascriptString = “<script language=’javascript’>functionToRefresh();</script>

// string keyString – this keyString serves as a key to the ‘javascriptString’. This means I can use one keystring for the same function anywhere in the code to call the page refresh javascript function.

The ‘functionToRefresh()’ should be entered in the .aspx page and the above code should be entered in the .aspx.cs. Thats it. I include these 2 snippets in any page I want to refresh. Whenever after any task is performed, if i want a page refresh I’ll iclude these 2 code pieces.

Posted in ASP.NET, Javascript | 2 Comments »

Money and Date display

August 2, 2008 by vijeyashobana

Very often we are required to display date and money values on the webpages. In the SQL Server database, the date is stored as SQLDBType.Datetime and the currency values are stored as SBLDBType.Money.

In the C# code, the  SQLDBType.Datetime should be converted to System.DateTime and SBLDBType.Money to System.decimal:

Datetime d = Convert.ToDateTime( SBLDBType.DateTime variable);

decimal m = Convert.ToDecimal( SBLDBType.Money variable);

In the Source page, these values can be displayed as:

<%# DataBinder.Eval(Container.DateItem, “Money”, “{0:c}” %> //here Money is column name

<%# DataBinder.Eval(Container.DateItem, “date”, “{0:d}” %> //here date is column name

provided the values are coming from database.

In the code-behind, these values can be defined as:

label1.Text = String.Format(“{0:c}”, SQLmoneyVariable)

label2.Text = String.Format(“{0:d}”, SQLdateVariable)

This will cause the labels to display money and date values on the webpage. String.Format is used to return the date and currency values as string in the required format.

Posted in ASP.NET, SQL Server | Leave a Comment »

Data-binding expressions

July 4, 2008 by vijeyashobana

When working with Databound controls, very often we are required to work with templates, may be they are ItemTemplates, EditItemTemplates, InsertItemTemplates etc.  In these situations usually we’ll use data-binding expressions.

There are 3 types of commonly used Data-binding expressions.

1. Type I – <%# Eval(“column_name”) %>

The above expression is a 2-way expression, meaning you can read/write values. Commonly used in TextBoxes.

<asp:TextBox text = ‘<%# Eval(“LastName”) %>

2. Type II – <%# Bind(column_name) %>

The above expression is a 1-way expression, meaning you can only read values. Commonly used in DropDownLists, Listboxes, labels.

<asp:DropDownList SelectedValue = <%# Bind(“LastName”) %>

3. Type III – <%# Function() || variable %>

Examples: <%# SomeMethod() %>

<%# firstName %>

<%# (firstname)+’ ‘+(lastname) %>

Reference: http://msdn.microsoft.com/en-us/library/ms178366(VS.80).aspx, http://bhaidar.net/cs/archive/2006/08/04/473.aspx

Tags: , ,
Posted in ASP.NET | Leave a Comment »

JOIN in DataSets – Good idea or bad idea?

July 4, 2008 by vijeyashobana

Very often we programmers are needed to use JOIN statements in our code.  We use JOINs to configure DataSource controls, populate Datasets, etc.

SELECT statements would be used to join 2,3 or more tables. So far so good. But problem arises when using UPDATE, DELETE, INSERT statements. It is not possible to update 2 or more tables in one statement. Also UPDATE, INSERT, DELETE statements would not be automatically generated when configuring the data source. The programmer has to code these statements.

To avoid these difficulties and to make our lives easy, it is better to use subqueries in SELECT statements. When configuring the data source, there is an easy way to make our lives better:

1. Use a SELECT statement on one table.

2. Click ‘Advanced’ tab and Check ‘Automatically Generate INSERT, UPDATE, DELETE statements’.

Now Configure the same datasource again. Now use 1or more SELECT subqueries within the original subquery.

This would give us the same desired result like using JOINs. Also, INSERT, UPDATE, DELETE statements aer automatically generated for us.

SQL QUERY USING JOINS:

USE NORTHWIND

SELECT TOP 5 PDT.PRODUCTNAME, CAT.CATEGORYNAME, SUP.COMPANYNAME FROM CATEGORIES CAT
INNER JOIN PRODUCTS PDT ON CAT.CATEGORYID=PDT.CATEGORYID
INNER JOIN SUPPLIERS SUP ON SUP.SUPPLIERID = PDT.SUPPLIERID

Now the same result can be produced using the below SQL query:

USE NORTHWIND

SELECT TOP 5 PRODUCTNAME,
(SELECT CATEGORYNAME FROM CATEGORIES WHERE CATEGORIES.CATEGORYID=PRODUCTS.CATEGORYID) AS CATEGORYNAME,
(SELECT COMPANYNAME FROM SUPPLIERS WHERE SUPPLIERS.SUPPLIERID = PRODUCTS.SUPPLIERID) AS SUPPLIERNAME
FROM PRODUCTS

Reference: Scott Mitchell’s Data Access Tutorials in www.asp.net

Tags: , ,
Posted in ASP.NET, SQL Server | 1 Comment »

GridView and DataSource

July 4, 2008 by vijeyashobana

GridView is built to work with DataSource. This DataSource can be either dataset or a DataSource control.

When using GV with DSet, you have to handle the Paging, Editing, Sorting functions.

When using GV with DSrc, the above functions are built-in for you. If you have a strict 3-tier architecture, use ObjectDataSource control.

Suppose I don’t use a primary key column in the SELECT statement for SqlDataSource. What would happen in that case?

I tried it and in the UPDATE command i set the condition to check for a specific primary key value but the catch is there is no PK in SELECT statement. When I tried to run this code, I didn’t get any error. I was able to click ‘EDIT’ button of GridView, enter new information and UPDATE it. However the thing is no info was updated. It didn’t show any error but it didnt give the desired result either.

So have one column as PK. If you didn’t select any PK column, at least set the DataKeys value to a column name. This way the column will be made a PK column for the datatable.

Tags: ,
Posted in ASP.NET | Leave a Comment »

DataSet Editing

June 30, 2008 by vijeyashobana

It seems so easy to say the data in dataset can be edited and updated to the database. But in reality there are abundant problems surrounding this single concept. This is because there are numerous different situations pertaining to this simple concept. Some are, what if the underlying table has no primary key? what if the underlying table is a result of join on 2 different tables? what if the underlying table does have a primary key? Will the dataset created in a Button_Click be carried over to the other events of the gridview/ any databound control for that matter? What about AcceptChanges(), BeginEdit()?

We will analyze every question one by one here.

what if the underlying table has no primary key?

if the dataTable of Dataset has no PK , manually set a PK on any column of the dataTable.

dset.Tables[0].PrimaryKey = new System.Data.DataColumn[] { dset.Tables[0].Columns[1] };

To have more than 1 column as a PK, i.e, composite PK, use :

dset.Tables[0].PrimaryKey = new System.Data.DataColumn[] { dset.Tables[0].Columns[1] , dset.Tables[0].Columns[2]};


Remember, PrimaryKey property returns a DataColumn[]. So you need to set a DataColumn array value to it otherwise it throws an error.

what if the underlying table is a result of join on 2 different tables?

if the Dtbl is a result of join, then the DataTable will not have any PK on it. In this case, you have to manually set it.

what if the underlying table does have a primary key?

This usually happens if the DataTable is a result of some SELECT statement and the SELECT statement gives a column set as PK. So in this case, we have to make sure the CONSTRAINTS info is updated in the dataset. This is because when you call a SELECT statement, the schema info is not added to the dataset. It should be done manually thus:

da.FillSchema(dset, SchemaType.Source, “Authors”);

if you didn’t use the above statement, you can also use the below one:

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

If you manually set the PK on the underlying DaTbl, donot use the above code. If you use both, it will throw exception.

Will the dataset created in a Button_Click be carried over to the other events of the gridview/ any databound control for that matter?

No. the dset will not be carried over. So you have to have a method to fill the dataset and call that method in your other DataBound events.

What about AcceptChanges(), Begin/EndEdit()?

To make changes in any DataRow, I need to create a new DataRow. But I cannot use DataRow() because this cannot be used. You need to use

DataRow dr = dt.NewRow();

and assign the dr with the required row,

dr = dt.Rows.Find(“Aria Cruz”);

Following the above,

dr.BeginEdit();
dr["EMPLOYEE_NAME"] = “Aria Tamil”; //changed the original name to Aria Tamil
dr.EndEdit();
dset.AcceptChanges(); //committing the dataset with the changes
da.Update(dset); //pass the changed dataset to d/b

Tags: , , , , ,
Posted in ASP.NET | Leave a Comment »

« Older Entries