Error binding in GetColumnNumber at row 1

Question

I got this OfficeWriter error when debugging the console application. I used methods to retrieve config details for the database used in the coding from the master database, and ended up having this error.

Error binding in GetColumnNumber at row 1

Attached here is partial coding for my work. Anyone can explain me what the error is?

    SqlDataReader rdSource = getSource();
    SqlDataReader rdDestination = getDestination();
    SqlDataReader rdCode = getCode();

    while (rdSource.Read() && rdDestination.Read())
    {
        string src = rdSource["Value"].ToString();
        string dest = rdDest["Value"].ToString();

        ExcelTemplate XLT = new ExcelTemplate();
        XLT.Open(src);
        DataBindingProperties dataProps = XLT.CreateBindingProperties();
        XLT.BindData(rdCode, "Code", dataProps);
        XLT.Process();
        XLT.Save(dest);
    }

    //rdCode method
    SqlDataReader rdConnection = getConnection(); //method for getting connection from master
    while (rdConnection.Read())
    {
        string con = rdConnection["Value"].ToString();
        SqlConnection sqlCon = new SqlConnection(con);

        string SQL = "SELECT * FROM Sales.Currency";
        sqlCon.Open();
        SqlCommand cmd = new SqlCommand(SQL, sqlCon);
        cmd.ExecuteReader();
        sqlCon.Close();
    }
    return rdConnection;

    //getConnection method
    string strCon = ConfigurationManager.ConnectionStrings["Master"].ConnectionString;
    SqlConnection sqlCon = new SqlConnection(strCon);
    string cSQL = "SELECT Value FROM dbo.COMMON_CONFIG WHERE Value = 'Data Source=localhost;Initial Catalog=Test;Integrated Security=True'";
    SqlCommand cmd = new SqlCommand(cSQL, sqlCon);
    sqlCon.Open();
    return new SqlCommand(cSQL, sqlCon).ExecuteReader(CommandBehavior.ConnectionString);

    //getSource & getDestination methods
    string strCon = ConfigurationManager.ConnectionStrings["Master"].ConnectionString;
    SqlConnection sqlCon = new SqlConnection(strCon);
    string srcPath = @"FILE PATH NAME"; //change to destPath for getDestination
    string sSQL = "SELECT Value FROM dbo.COMMON_CONFIG WHERE Value = '" + srcPath + "'";
    SqlCommand cmd = new SqlCommand(cSQL, sqlCon);
    sqlCon.Open();
    return new SqlCommand(cSQL, sqlCon).ExecuteReader(CommandBehavior.ConnectionString);

I see that your code is binding a single record from the data set to the template and generating a new output file for every record in your data set. Did you want mean to generate an output for each record in the data set? If you look at our documentation: http://wiki.softartisans.com/display/EW8/ExcelTemplate.BindData%28System.Data.IDataReader%2C+String%2C+DataBindingProperties%29, you only need to pass the SqlDataReader to the BindData method and we will take care of importing all of the records. — AlisonB, Jul 17 '13 at 11:52
No I want to bind all the records from the database and generate a new output file. But the error is the "Error binding in GetColumnNumber at row 1". Here is the partial coding, did not put the whole chunk of coding for reading convenience. — CHOCOx33, Jul 18 '13 at 01:29

score 0 · Accepted Answer · answered Jul 26 '13 at 16:02

The error is a generic message that is thrown by ExcelWriter when it is unable to bind data to the template.

I think this might be caused by your getCode() method. In getCode(), you use a SQLDataReader to retrieve the connection string for the database:

    SqlDataReader rdConnection = getConnection(); //method for getting connection from master

Then you execute a SQL query against that database, but you don't actually get a handle on the SqlDataReader that is executing the SQL query to return the data.

    SqlCommand cmd = new SqlCommand(SQL, sqlCon);
    cmd.ExecuteReader(); //Note: This returns the SqlDataReader that contains the data

Then you return rdConnection, which is the SQLDataReader for the connection string - not the data you are trying to import. rdConnection contained 1 row and you already called Read(), so there are no records left to read.

 SqlDataReader rdCode = getCode();
 ...
 XLT.BindData(rdCode, "Code", dataProps);

The SQL reader you are binding is the used 'connection string', rather than your sales data. I would recommend the following:

Return the new SqlDataReader that is generated by cmd.ExecuteReader() in getCode(), rather than rdConnection.
Do not close the connection to this new SqlDataReader. ExcelWriter needs to be able to read the data reader in order to bind the data. If you close the connection, ExcelWriter will not be able to bind data correctly.

Note: I work for SoftArtisans, makers of OfficeWriter. This user was assisted through one of our support channels and confirmed that making the changes suggested above resolved the data binding issue. — AlisonB, Jul 30 '13 at 12:59

Error binding in GetColumnNumber at row 1

1 Answers1