Solved

SQL CE access with C# (CF)

Posted on 2004-10-30
631 Views
Last Modified: 2008-05-07
Hi,

I'm developping app on VS.NET2003 with C#, for Small Devices (PocketPC).

I have difficulties to access an SQL CE database (note: SQL CE is correctly installed on device, I've successfully added records to database with Query Analyser.)

Here's the code:
--------------------------------------------------------------------------------------------------------------
using System.Data.SqlServerCe;
[...]

 1            private void Form4_Load(object sender, System.EventArgs e)
 2            {
 3                  if (File.Exists ("pp.sdf"))
 4                  {
 5                        SqlCeConnection      conn = null;
 6
 7                        SqlCeEngine      engine = new SqlCeEngine("Data Source =      pp.sdf");
 8                        engine.CreateDatabase();
 9      
10                        try      
11                        {
12                              conn = new SqlCeConnection ("Data Source = pp.sdf");
13                              conn.Open();
14      
15                              SqlCeCommand cmd = conn.CreateCommand();
16                              cmd.CommandText      = "SELECT * FROM employes";
17      
18                              SqlCeDataReader      rdr      = cmd.ExecuteReader();
19                              rdr      = cmd.ExecuteReader();
20                              while (rdr.Read())
21                              {
22                                    lstEmployes.Items.Add(rdr.GetString(1));
23                              }
24                        }
25                        catch (SqlCeException sce)
26                        {
27                              ShowErrors(sce);
28                        }
29                        finally
30                        {
31                              if(conn.State == ConnectionState.Open)
32                                    conn.Close();
33                        }
34                  }
35            }
--------------------------------------------------------------------------------------------------------------

I took this code from MSDN in Walkthrough sample: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_walkthroughs_705.asp?frame=true

I have this error : "An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll -- Additional information: SqlCeException" on line 13.

Any help and/or accurate code sample, tutorials, howtos on the web is welcome.  Thanks to all.

P.S.: I'm disappointed that we now need a premium subscription to see solutions... maybe someone already asked something similar.
0
Question by:elbaid
    5 Comments
     
    LVL 14

    Expert Comment

    by:existenz2
    Is het pp.sdf in the correct dir? With "Data Source = pp.sdf" you imply that the data source is in the same dir as your application. During debug, this is your debug dir.
    0
     

    Author Comment

    by:elbaid
    In fact, I've tried different ways to point to DB file in lines 7 and 12 like ("Data Source = \\Program Files\\AppName\\pp.sdf"), and the same error occurs.  And the if statement checks if file exists.  The file pp.sdf is in the same directory of executable.
    0
     
    LVL 7

    Assisted Solution

    by:God_Ares
    hi,. i asume your using vs.net 2003? you should be able to click on the pp.sdf and some sort of progam should launch yes? ok now exit NOT BY the cross button but from the pull down menu.

    i see in my code :
    pSqlCeConnection = new SqlCeConnection("Data Source = "+ConnectStr);
    where ConnectStr = @"\Program Files\appname\pp.sdf"

    so this should be ok,... //@"is just to quit escaping chars, handy :) \r\n"

    also,.. and this is verry important. THERE CAN BE 1 CONNECTION TO A DATABASE. so make shure no other app is using your db,.. looking trough your code is seems a bit add.. create new database and retrieve data from an unknow table??!!

    just prepare 1 db whit the app you have seen when you clicked on the db file. and skip the createdatabase crap. Also ALWAYS use a full path in your connectionstring.

    hope this helps

    Floris
    0
     
    LVL 3

    Accepted Solution

    by:
    Try this one, I already tested it.

    private void Form1_Load(object sender, System.EventArgs e)
    {
    if (File.Exists (@"\Program Files\pp.sdf"))
    {
    SqlCeConnection conn = null;

    try                                        
    {
    conn = new SqlCeConnection("Data Source = \\Program Files\\pp.sdf;ssce:database password='yourpassword'");
    conn.Open();
    SqlCeCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM tableName";
    SqlCeDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
          MessageBox.Show(rdr.GetString(1));
    }
    }
                                                                    
    catch (SqlCeException sce)
    {
    }
                                                           
    finally
    {
    if(conn.State == ConnectionState.Open)
           conn.Close();
    }
    }
    }

    Hope this helps.
    Good luck.
    Chintan.
    0
     

    Author Comment

    by:elbaid
    Thank you to everyone, now it works !  Well, almost, now I have to deal with data types !! :)

    I hardly need a reference, books or something, maybe ".NET Compact Framework Programming with C#" (3osoft.com/go.php?u=10), but don't know if it covers the SQL Server CE very well.  If someone have a suggestion, I'll appreciate it.

    By the way, don't know if someone wants the points, if it's the case, I'll give them to the author of next comment/contribution.  Intervention of both Chintan and God Ares has helped me.  Thank you.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now