How to access one value from a Excel sheet using a SQL statement and C#

My spreadsheet has 2 tabs with data. In one of the tabs I named the table LeadsTable and the column I want to get data from is Lead_ID. I want to check the column to see what was the last number it had so I am trying to do a select statement like this Select Last(Lead_ID) AS highest_age FROM LeadsTable
But it isn't retrieving any value. Can anyone help me ?

Here is the calling function:
string temp = GetLastID("Lead_ID", "LeadsTable");

Here is the function:
private string GetLastID(String ColomnName, String TableName)
{
string sLastID = null;
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
"LeadConsistencyScorecard.xls;Extended Properties=Excel 8.0;");


OleDbCommand objCmdSelect = new OleDbCommand("Select Last(" + ColomnName + ") AS highest_age FROM " + TableName, objConn);

objConn.Open();
sLastID = objCmdSelect.ExecuteScalar().ToString();
objConn.Close();


return sLastID;
}

re: How to access one value from a Excel sheet using a SQL statement and C#

I have changed one of the lines
OleDbCommand objCmdSelect = new OleDbCommand("Select Last(" + ColumnName + ") FROM " + TableName, objConn);


Can some help please???

re: How to access one value from a Excel sheet using a SQL statement and C#

I am moving this to the .NET forum as you are programming in C#. You are unlikely to get an answer in the Access/VBA forum.

re: How to access one value from a Excel sheet using a SQL statement and C#

I gave up on using the Last() function and made it get the whole column

private int GetLastLeadID()
{
//If there are no exixting id there is a 1 returned to start the table with
double lastid=0;
// Connect to the data source.
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +"LeadConsistencyScorecard.xls;Extended Properties=Excel 8.0;");
objConn.Open();

// Execute a command to retrieve all records from the Employees table.
System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand(
"Select Lead_ID FROM [Leads$]", objConn);
System.Data.OleDb.OleDbDataReader objReader;
objReader = objCmd.ExecuteReader();

while (objReader.Read())
{
if (!objReader.IsDBNull(0) )
{
if(objReader.GetDataTypeName(0) == "DBTYPE_R8")
lastid = objReader.GetDouble(0);

if(objReader.GetDataTypeName(0) == "DBTYPE_WVARCHAR")
lastid = Convert.ToDouble(objReader.GetString(0));

}

}

// Close the connection.
objConn.Close();

return Convert.ToInt32(lastid);
}

re: How to access one value from a Excel sheet using a SQL statement and C#

Oh ya that was modified from MS examples

http://support.microsoft.com/kb/306023