Microsoft Excel Help notes

November 9, 2010

Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled.

    <form id=”form1″ runat=”server”>
    <asp:ScriptManager ID=”ScriptManager1″ runat=”server”>
    <asp:UpdatePanel ID=”UpdatePanel1″ runat=”server”>
    <asp:Label ID=”lbl” runat =”server”></asp:Label>
  <asp:Button ID=”Button1″ runat=”server” Text=”Button” OnClick =”Button1_Click”/>

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
 lbl.Text = “Jaganath”
    End Sub
Ans:you can not use Response.Write on your page when using AJAX.  I took it out (used a label instead) and it worked as expected.

JQuery intellisense in vs 2008

Building Cascading DropDownList in ASP.Net Using jQuery and JSON

Creating a CascadingDropDown Using AJAX

RenderControl method of Datagrid : Helps in exporting the grid data to excel.
But for dates,currencies, numbers it will automatically formats that data. The user has little or no control on that through code.
Say for eg: 123343423434 number converts into exponential number.

If the user doesnt deserves the formatting

either one has to preface the data with an apostrophe.

or select the column Format cells->General->Select the required one (Number ,Currency,Date) as per the requirement.

One cannot “set” Excel to stop thinking those are dates.

The work around is

For formatting the text use

For formatting the currency use
Response.ContentType = “application/”;
Response.Charset = “”;
this.EnableViewState = false;
Response.Write(“<html xmlns:x=\”urn:schemas-microsoft-com:office:excel\”>”);
Response.Write(“<style>  .mystyle1 ” + “\r\n” + “{mso-style-parent:style0;mso-number-format:\””+@”\@”+”\“”+”;} ” + “\r\n” + “</style>”);
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);

For example, to center and format ALL cells to text
following line should be added using response.write() at top of the page.

<style> td {mso-number-format:”\@”; text-align:center;} </style>
Virtual directory not being configured as an application in IIS       
Written by Shabdar    
Monday, 24 November 2008 12:09 
Configuration ErrorDescription: An error occurred during the processing of a configurationfile required to service this request. Please review the specific errordetails below and modify your configuration file appropriately.Parser Error Message: It is an error to use a section registered as allowDefinition=’MachineToApplication’ beyond application level.  This error can be caused by a virtual directory not being configured as an application in IIS.Source Error:Line 53:Line 54:< authentication mode=”Windows” /> Line 55: Line 56:

Change authentication mode to ‘None’ in web.config.

Go to your web.config file and change following line
< authentication mode=”Windows” />to

< authentication mode=”None” />
This should solve this problem.

If above solution does not work then make sure that the directory from where you website is running is configured as an application in IIS. To do this, you need to go into IIS and find your project folder on the server, right click to Properties. Under Application Settings click Create. 
What is TDMA and CDMA? why do we need them?
Over the years simultaneous cellular calls are increased over networks but no of accessible channels are limited. Hence there is a necessity of techniques which can solve this problem i.e increasing the number of simultaneous calls even though channels are limited. They expand the capacity of the radio channel.

There you got TDMA and CDMA. TDMA is a (Time division multiple access access)multiple access technology in which the channel is divided into sequential time slots.only a single user is using the channel at any given time. Here I would like introduce the term TDM(Time division multiplexing). Oh! then what is the difference between TDM and TDMA.
TDM is multiplexing technique and TDMA is multiple access technology. In TDM cetain time slot is dedicated forever for a given user even though he is not using it. ISDN is the example for this. In TDMA the time slot get freed up once the user finishes using the time. Here the time slots are dynamically allocated and the user may get different time slot each time accessing the network. GSM is an example for this. Of course GSM is not using ONLY TDMA.(FDMA as well clubbed with TDMA).
In TDMA the channel gets divided into 3 to 8 channels=8 simultaneous conversations.
Lets take an example for TDMA.
1) There are two stations and at each station 3 people. station1–A,B,C Station 2–D,E and F. Station1 and station 2 people want to exchange the messages.
2) The train with three carriages stops at station1 for some time.
3) All three people put the messages in three carriages.
4) The train goes to station 2 and the people there receives the messages from the carriages.
5) Again the train goes to station1 and the loop continues till the messages are completed.

Now coming to the CDMA(Code division multiple access) technology, It uses spread spectrum technology i.e a method in which a signal generated in a bandwidth is spread in the frequency domain resulting a signal with a wider bandwidth. In CDMA each caller will be given certain unique code so that others can not understand and allow multiple users use the same physical channel. The very common example for CDMA is In a room(considering as channel) several people want to communicate each other and each user will speak in different languages (Code divided) .
Now apply the same example to TDMA so that we understand the difference between these two. In TDMA all the people will take out certain time and speak one by one till every one finish their speech. In CDMA each user will speak in different languages. In FDMA each user will speak in different pitches.
Don’t confuse this CDMA (channel accessing method) with CDMA phones which is a mobile phone standard called cdmaone and CDMA2000(Reliance CDMA phones which are famous in India). GPS is the first application of this CDMA and qualcomm’s standard IS-95 published it as cdmaone and IS2000 as CDMA2000
find number of columns in a table in sql server
WHERE id= (Select id from SYSOBJECTS where name = ‘TableName’)

In sqlserver 2008 if u want to modify the table structure in design mode it wont save and it will show an error message that file cannot be saved.


Tools ->Options -> Designers
-> Table and Database Designers
-> Uncheck “Prevent saving changes that require table re-creation”


Ternary operator in VB.Net .There is no “?” like in c# in
If you are looking for ternary operator in VB.NET similar to C++,C#, Javascript etc if(expression?truepart:falsepart) then let me tell you that VB.NET offers a similar function to do that

you can write

Dim displayName As String = “FirstName”
Dim myName As String = IIf((displayName = “FirstName”), “Abhishek”, “Tiwari”)
Console.WriteLine(myName) ‘will write “Abhishek”
 myName = IIf((displayName = “SurName”), “Abhishek”, “Tiwari”)
Console.WriteLine(myName) ‘will write “Tiwari”
 Tips for reading Excel spreadsheets using ADO.NET
 Microsoft ADO.NET provides a handy, if quirky way to access Excel spreadsheets from Windows applications. The idea is to treat spreadsheets like databases, with each worksheet represented as a “table”. Worksheets are expected to be in a table-like format with column headings in the first row and rows of data beneath. For example, the following code reads worksheet “foo” from spreadsheet file C:\BAR.XLS into a DataTable:

problem : You have strings,integers,dates etc in an excel file. And if you are reading excel file using OLEDB then columns which are formatted may not be read and the output contains blank or null at their corresponding positions.
To resolve that : Use IMEX=1 in the JET provider.

One more issue : Unspecified error Excel:  (-2147467259)

Solution : its a security issue. the application account should be a administrator account.Means it should have full permissions.

My Computer->Manage(right click) -> Computer Management -> System Tools->Groups->
Add that user in administrator group
Imports System.Data.OleDb
Imports System.IO

Partial Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim myDataset As New DataSet()

        Dim strConn As String = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Server.MapPath(“cbofile.xls”) & “;” & _
         “Extended Properties=””Excel 8.0;IMEX=1;”””
        ”You must use the $ after the object you reference in the spreadsheet
        Dim myData As New OleDbDataAdapter(“SELECT * FROM [cbofile$]”, strConn)
        myData.TableMappings.Add(“Table”, “ExcelTest”)

        DataGrid1.DataSource = myDataset.Tables(0).DefaultView

    End Sub

End Class
If you want to process the data row by row rather than snarfing it into a DataTable, you can do it this way:
OleDbConnection dbConnection = new OleDbConnection (@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=””Excel 8.0;HDR=Yes;”””);
dbConnection.Open ();
    OleDbCommand dbCommand = new OleDbCommand (“SELECT * FROM [foo$]”, dbConnection);
    OleDbDataReader dbReader = dbCommand.ExecuteReader ();

    // Say we are interested only in the columns “YearOfBirth” and “Country”:
    int yearOfBirthIndex = dbReader.GetOrdinal (“YearOfBirth”);
    int countryIndex = dbReader.GetOrdinal (“Country”);

    while (dbReader.Read ())
 string yearOfBirth = dbReader.GetValue (yearOfBirthIndex).ToString ();
 string country = dbReader.GetValue (countryIndex).ToString ();

 // …
    dbConnection.Close ();
}But what if you don’t know the name of the sheet you want to read? As you can see from the examples, the ADO.NET interface requires you to name the worksheet – but in many cases you just want to read the first worksheet regardless of its name. It would be cool if ADO.NET provided a suitable notation like this:
OleDbCommand dbCommand = new OleDbCommand (“SELECT * FROM [0#]”, dbConnection);… but it doesn’t. You must tell ADO.NET the specific name of the sheet you want to read. The solution is therefore to read the spreadsheet schema to find out the sheet names, thus reducing the second problem to the first one, like this:
OleDbConnection dbConnection = new OleDbConnection (@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=””Excel 8.0;HDR=Yes;”””);
dbConnection.Open ();
    // Get the name of the first worksheet:
    DataTable dbSchema = dbConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
    if (dbSchema == null || dbSchema.Rows.Count < 1)
        throw new Exception (“Error: Could not determine the name of the first worksheet.”);
    string firstSheetName = dbSchema.Rows [0] [“TABLE_NAME”].ToString ();

    // Now we have the table name; proceed as before:
    OleDbCommand dbCommand = new OleDbCommand (“SELECT * FROM [” + firstSheetName + “]”, dbConnection);
    OleDbDataReader dbReader = dbCommand.ExecuteReader ();

    // And so on…
    dbConnection.Close ();
}The main quirk about the ADO.NET interface is how datatypes are handled. (You’ll notice I’ve been carefully avoiding the question of which datatypes are returned when reading the spreadsheet.) Are you ready for this? ADO.NET scans the first 8 rows of data, and based on that guesses the datatype for each column. Then it attempts to coerce all data from that column to that datatype, returning NULL whenever the coercion fails!
I suppose that makes a twisted kind of sense if you have spent a lot of time working with relational databases, but there are some unfortunate consequences of this design which aren’t obvious at first. For example, say your spreadsheet contains the following columns:
YearOfBirth    Country PostalCode
1964        USA 10005
1970        USA 10001
1952        Canada K2P1R6
1981        Canada L3R3R2
1974        USA 10013ADO.NET will correctly guess that the YearOfBirth column is numeric, and that the Country column is of type string. But what about the PostalCode column, which contains a mix of numbers and strings? In this case ADO.NET chooses the type based on the majority of the values (with a tie going to numeric). In this example 3 of the 5 postal codes are numeric, so ADO.NET will declare the column to be numeric. Therefore it will attempt to cast each cell to a number, which will fail for the Canadian postal codes – which will therefore come out as NULL values. Ha ha. Isn’t that fun?
Even more entertaining, there is absolutely no way to make this 100% reliable – although with some pain, you can improve the situation. Here’s what you need to do. First add the “IMEX=1″ option to your connection string like this:
OleDbConnection dbConnection = new OleDbConnection (@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=””Excel 8.0;HDR=Yes;IMEX=1;”””);That tells ADO.NET to honor the following registry key when reading the spreadsheet:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypesThis registry key tells ADO.NET what to do when it encounters mixed types in the first 8 rows. It can either be set to the string “Majority Type” (for the default behavior) or to “Text” (which forces the column to be of type string). Note that you are still screwed if the first 8 postal codes are numeric and the 9th is Canadian. (Not to mention that the “Text” option invokes handling that fails on strings over 255 characters, but let’s skip that for now.)
There’s also a second relevant registry setting (which is honored regardless of the IMEX option):
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRowsThat says how many rows to scan to guess the datatype. The default is 8, but you can set it anywhere from 0-16 decimal (0 meaning “scan the first 16384 rows”, and all other values meaning what they say). Putting this all together, the most reliable way to read a US/Canadian postal code is to use the following registry settings:
TypeGuessRows = 0
ImportMixedTypes = TextThat’s pretty close to perfect, although it will still fail if the first 16384 postal codes are numeric and any of the subsequent ones aren’t.
This is a Bad Design for so many reasons I don’t know where to start. First, the behavior of the spreadsheet importer should not depend on global registry settings – that is just inviting mysterious, data-dependent errors whenever other applications or users change those settings. All of those settings should be in the connect string and nowhere else. Second, there should be an option to say “I’m not sure what data is coming, but I want all of it – please coerce everything to something universal like an arbitrary-length string”. Third, the interface should be stream-based, not file-based. If you are reading the spreadsheet data from the network, you shouldn’t have to save it to a temporary file in order to parse it. Fourth, you shouldn’t have to read the spreadsheet schema if you just want to select the worksheet by index (e.g. you want to read the first worksheet, whatever it happens to be called).