473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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;
}
Dec 28 '07 #1
4 3048
I have changed one of the lines
OleDbCommand objCmdSelect = new OleDbCommand("Select Last(" + ColumnName + ") FROM " + TableName, objConn);


Can some help please???
Dec 28 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
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.
Jan 8 '08 #3
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);
}
Jan 9 '08 #4
Oh ya that was modified from MS examples

http://support.microsoft.com/kb/306023
Jan 9 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Paul | last post by:
Hi all Arggghhh........... The problem.....I want the user to be able to create an excel document and name particular cells in the document where they want the data to be placed and then save...
9
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then...
6
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at...
1
by: Ronny Sigo | last post by:
Hello all, I am trying to import the contents of an Excel sheet into my Access Database. When clicking the button excel opens allright and does what I programmed (the cells get updated with the...
0
by: ImraneA | last post by:
Hi there Have a database, where front-end interface allows user to select a ms access database. From there, standard tables are linked. Routine, that creates a spreadsheet, for each table a...
7
by: dog | last post by:
I've seen plenty of articles on this topic but none of them have been able to solve my problem. I am working with an Access 97 database on an NT4.0 machine, which has many Access reports. I...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
1
by: garry.oxnard | last post by:
Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access...
0
by: DrewYK | last post by:
I have the project that may never end in front of me. I am creating a routine that will take SpreadSheets from Excel and bring them into Access. I am not using any "DoCmd"s because the goal...
7
by: kpresidente | last post by:
Hello all, I'm trying to transfer the value of a control on an Access form to an Excel worksheet using VBA. The Access form is a single form with all the controls disabled, so that data is "read...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.