Using OleDb in C#?

I'm trying to read excel files using C# following some tutorials I found online but I think I'm forgetting something because the method names don't seem to be recognized. Do I need to import something?

Code:

---

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication1
{
    class ExcelReader
    {
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\user\My Documents\test.xls;Extended Properties=Excel 8.0");
        OleDbCommand com  = new OleDbDataAdapter("select * from table", con);
        con.Open(); //Error is here
    }
}

---

Intellisense is doing that to tell you that you have an error somewhere. Your error seems to happen one line up when you assign a new'd OleDbDataAdapater to an OleDbCommand reference.

OleDbConnection Class

Whoops. It's OleDbCommand now but still the same error

Code:

---

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication1
{
    class ExcelReader
    {
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jcafaro\My Documents\test.xls;Extended Properties=Excel 8.0");
        OleDbCommand com  = new OleDbCommand("select * from table", con);
        con.Open();
    }
}

---

And what error would that be?

Invalid token '(' in class, struct, or interface member declaration
It's pointing to con.Open(); I think that means it just doesn't know what con is.

Because you are calling it in the body of a class. Put it in some method.

Sorry I've been scripting all day and forgot that I need a main method, whoops

Thanks for the help. I started playing around with using different commands. I'm new to sql but I've looked up some stuff. I have a table with column headers A,B,C, and D. I can insert items into the table by doing:

Code:

---

insert into table1 values (1,2,3,4)

---

but I think I should be able to do that by doing:

Code:

---

insert into table1 (A,B,C,D) values (1,2,3,4)

---

but I can't. I also can't add in things that aren't numbers so this doesn't work either

Code:

---

insert into table1 values ("hi",2,3,4)

---

or

Code:

---

insert into table1 values (hi,2,3,4)

---

Is C# limited in the sql statements it can execute?

C# has nothing to do with what statements can be executed. It depends on the sql that the oledb module requires (which I am specifically unfamiliar with). With the string I would assume you need to nest the string within 's or "s as in most sql languages. To do this , prepend it with a \. So "insert into table1 values (\"Hi\", 2, 3, 4)";.

Other then that I would need specific errors to help further.

In my code I just say to read in the command from user input. I've tried entering it in a few different ways:

Code:

---

Enter Command: insert into table1 values (\"hi\",2,3,4)
Invalid Command: Syntax error (missing operator) in query expression '\"hi\"'.
Enter Command: insert into table1 values (\'hi\',2,3,4)
Invalid Command: Syntax error (missing operator) in query expression '\'hi\''.
Enter Command: insert into table1 values ('hi',2,3,4)
Invalid Command: Data type mismatch in criteria expression.
Enter Command: insert into table1 values ("hi",2,3,4)
Invalid Command: Data type mismatch in criteria expression.

---

Code:

---

static String EnterCommand()
        {
            Console.Write("Enter Command: ");
            String result = Console.ReadLine();
            if (result.Equals("q"))
                return result;
            else if (result.Equals("c"))
            {
                ShowColumns(dt);
                return "";
            }
            else
            {
                OleDbCommand com = new OleDbCommand(result, con);  //Prepares the command
                dat.SelectCommand = com;    // Links the table to the command
                try
                {
                    dt.Clear(); // Clear the temporary table
                    dat.Fill(dt); // "Fill" up the temporary table with whatever is in dat as a result of the command
                    ShowResults(dt);
                }
                catch (Exception e)
                {
                    Console.WriteLine("Invalid Command: " + e.Message);
                }
                return "";
            }
        }

---

Place a breakpoint at the point you get your string and see if that string is a known working sql query that you have tested. If it's not then you know what to fix.