Display Local database c#

0
votes

I have a probnlem displaying what's in my Database table. I've followed my class instructions but I've been stuck for 2 days without success!

Here is what I've done so far; 

string conStr = @"Data Source=C:\Users\secwp_000\documents\visual studio 2012\Projects\Module5\Module5\Orderdatabase.sdf";


SqlCeConnection con = new SqlCeConnection(conStr);
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Order", con);
SqlCeDataAdapter adapt = new SqlCeDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds, "Order");

foreach (DataTable dt in ds.Tables)
{
    foreach (DataRow row in dt.Rows)
    {
       foreach (DataColumn column in dt.Columns)
       {
            Console.WriteLine(row[column]);
       }
    }
}

When i run the code i get a error on adapt.Fill(ds, "Order"); saying An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll. Which I don't really understand.

EDIT: My DataConnections is;

Data Connections
    Orderdatabase.sdf
        Tables
            Order
                Columns
                    OrderID
                    etc..

Adding output for Nicks solution:

System.Data.SqlServerCe.SqlCeException (0x80004005): There was an error parsing the query. [ Token line number = 1,Token line offset = 21,Token in error = Order ]
   vid System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   vid System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
   vid System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   vid System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
   vid System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(Command Behavior behavior)
   vid System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable [] datatables, Int32 startRecord, Int32 maxRecords, String, srcTable, IDbCommand command, CommandBehavior behavior)
   vid System.Data.Common.DbDataAdapter.Fill(Dataset dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   vid System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   vid Module5.Program.Main(String[] args) i c:\Users\secwp_000\Documents\Visual Studio 2012\Projects\Module5\Module5\Program.cs:rad 54

EDIT: Here is a picture of what it looks like atm. http://oi61.tinypic.com/9pq70l.jpg

2
c#databasevisual-studio-2012sql-server-ceadapter
Welcome to Stack Overflow. What is the stacktrace exactly? Please read FAQ, How to Ask and help center as a start.. – Soner Gönül
Thank you! Sorry I've never encountred that word. So I don't know. – Oscar
Try putting a try catch statement around your adapt.fill. In your catch statement put your e into a message box and it will give you more information about your error. Also as a silly little thing: Are you sure that your table Order is with a capital letter in your database? Silly mistake but I've made it many times. – Nick Otten
Unfortunately I can't post images, but in the server explorer under tables the table is named "Order". I don't know what to write after catch, we have barley worked with that. – Oscar
use it like this: try { adapt.fill(ds, "Order"); } catch (Exception e) {Messagebox.Show(e.toString());} – Nick Otten

2 Answers

0
votes

You need to enclose the table name in brackets, as this is a reserved Word:

SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM [Order]", con);

And using SELECT * is never a good idea

0
votes

First of all SELECT * from Order is a huge collection of data, I suggest you try this:

        SqlConnection con = new SqlCeConnection(conStr);
        using(con)
        {
        SqlCommand cmd = new SqlCeCommand("SELECT OrderID FROM Order", con);
        string OrderID = cmd.ExecuteScalar().ToString();
        Console.WriteLine(OrderID);
        }

And if this is successful then go on to execute using foreach to access all rows and columns using SELECT * FROM ORDER