Thread: Using OleDb in C#?

  1. #1
    Registered User
    Join Date
    Jul 2007
    Posts
    186

    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
        }
    }

  2. #2
    Registered User valaris's Avatar
    Join Date
    Jun 2008
    Location
    RING 0
    Posts
    507
    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

  3. #3
    Registered User
    Join Date
    Jul 2007
    Posts
    186
    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();
        }
    }

  4. #4
    Registered User valaris's Avatar
    Join Date
    Jun 2008
    Location
    RING 0
    Posts
    507
    And what error would that be?

  5. #5
    Registered User
    Join Date
    Jul 2007
    Posts
    186
    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.

  6. #6
    Registered User valaris's Avatar
    Join Date
    Jun 2008
    Location
    RING 0
    Posts
    507
    Because you are calling it in the body of a class. Put it in some method.

  7. #7
    Registered User
    Join Date
    Jul 2007
    Posts
    186
    Sorry I've been scripting all day and forgot that I need a main method, whoops

  8. #8
    Registered User
    Join Date
    Jul 2007
    Posts
    186
    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?

  9. #9
    Registered User valaris's Avatar
    Join Date
    Jun 2008
    Location
    RING 0
    Posts
    507
    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.

  10. #10
    Registered User
    Join Date
    Jul 2007
    Posts
    186
    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 "";
                }
            }

  11. #11
    Registered User valaris's Avatar
    Join Date
    Jun 2008
    Location
    RING 0
    Posts
    507
    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.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. OLEDB Provider Properties
    By IfYouSaySo in forum Windows Programming
    Replies: 0
    Last Post: 04-26-2006, 12:26 AM
  2. Writing OLEDB Provider
    By IfYouSaySo in forum Windows Programming
    Replies: 0
    Last Post: 04-06-2006, 04:55 PM
  3. OLEDB Connection String to Excel
    By gozlan in forum C# Programming
    Replies: 4
    Last Post: 07-06-2003, 01:42 PM