Oct 24, 2009

Delete #REF named ranges

Macro to Delete #REF range names

This macro will enable you to delete named ranges with #REF error in it.

Sub DeleteRefErrRanges()
'Macro Purpose: To delete any named range with a "#REF!" error in it
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete
Next nm
End Sub

Oct 21, 2009

Bind XML to DataGrid

Binding XML to Datagrid :

In this code we’ll create XML and bind it to Datagrid control.
  1. Add XML file to project (By right click on project à Add à Add New Item à XML file)
2. Now create a XML file (Here I have created a file called Students.xml

3. Notice that when you have an XML file opened, the XML menu appears.
Click on the XML menu. The Create Schema will provide a graphic interface to draw the schema for the XML file. The Validate XML Schema helps you with determining if the XML content validates against a certain XML schema.
4. VS.NET tries to come up with the schema for the XML file based on its content. You can open up the schema file and graphically edit or change its definition by code.
Double-click on the Students.xsd file.

  1. By default, the Create XML Schema option defines all the fields as String. You can change it in this GUI or directly in the code representing this schema.
  2. Now add a button called “Load XML” to a webform which will populate data to gridview from xml file.
protected void btnload_Click(object sender, EventArgs e)
DataSet ds = new DataSet("Students");
ds.ReadXmlSchema(Server.MapPath(".") + "\\Students.xsd");
ds.ReadXml(Server.MapPath(".") + "\\Students.xml");
GridView1.DataSource = ds;

Oct 7, 2009

Monthly Winner - September 2009

Monthly Winner - September 2009

Just feeling great to share this. I just joined a month back on www.Dotnetspark.com and been chosen as Monthly winner of September. :-)

Prize : 2GB Pen Drive

Same with DNF. I just joined a month back on www.Dotnetfunda.com and been chosen as Monthly winner of September. :-)

Prize : Any gift of my choice (till Rs. 700) from ebay.in

Oct 2, 2009

Autcomplete combobox in Windows Application

Introduction : Autcomplete combobox in Windows Application

This code demonstrates the use of AutoComplete combobox in Windows Application

Pre-requisites :

Combobox properties to be set for achieving this :
1) AutoComplete Mode - You can select any from it, either Suggest/Append/SuggestAppend (In my case I have taken Suggest here)
2) AutoCompleteSource - Set it as CustomSource
3) AutoCompleteCustomSource - Its a collection

As shown in the Figure take a form with a combobox named cmbcity. Here I am fetching city from the database.

public partial class Form9 : Form

public string strConnection = ConfigurationManager.ConnectionStrings["test1"].ConnectionString;
AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
public Form9()

private void Form9_Load(object sender, EventArgs e)

OdbcDataReader dReader;
OdbcConnection conn = new

conn.ConnectionString = strConnection;
OdbcCommand cmd = new OdbcCommand();

cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select distinct city sample order by city";

dReader = cmd.ExecuteReader();
if (dReader.HasRows == true)

while (dReader.Read())


MessageBox.Show("Data not found");

cmbcity.AutoCompleteMode = AutoCompleteMode.Suggest;
cmbcity.AutoCompleteSource = AutoCompleteSource.CustomSource;

cmbcity.AutoCompleteCustomSource = namesCollection;