1

I'm an newbie who is trying to get learn some web programming. I'm making my site in VS 2012 using C#. I've got a database connected in the App_Data folder, using SQL Server CE 4.0. I'm attempting to connect to it as follows:

SqlCeCommand cmd1 = new SqlCeCommand("SELECT Admin FROM SystemUsers WHERE Email=" + user.Email);
SqlCeDataReader admin = null;
SqlCeConnection conn = new SqlCeConnection();
conn.ConnectionString = "Data Source=MyData.sdf;Persist Security Info=False;";
conn.Open();
admin = cmd1.ExecuteReader();

When I execute this, I get the following error:

An exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll but was not handled in user code

Any thoughts as to what I'm doing wrong? I've been trying to figure this out for hours.

E Crux
  • 83
  • 3
  • 11
  • You should be using `using` with those objects, such as `using(var cmd = new sqlCommand()) { }`, so they dispose properly and the connection is closed (anything that implements IDisposable). Also, never use string concatenation for SQL statements; your code is vulnerable to SQL injection; you should use SQL parameters. – Brad M May 21 '13 at 20:39
  • 1
    Try stepping through the code in the debugger and when the exception pops up, you should be able to drill into it to see the Message and possible InnerException.Message. The message might be a bit vague, but it is a starting point. – AaronLS May 21 '13 at 20:39
  • At some point, you should catch the exception and display it somewhere via `ex.ToString()` – John Saunders May 21 '13 at 20:41
  • Once you get the exception error, we might be more help. Also, if you're a newbie, you might want to look into using MVC 4 if you're going to do web dev as it introduces you to some more up-to-date principles especially having to do with databasing, etc. – Ryan May 21 '13 at 20:41
  • You said that your database is in the APP_Data directory, but your connection string assumes that is in the root directory of your site – Steve May 21 '13 at 20:43
  • Ryan - Thanks, I will look into that. I'm using MVC 4 for the website login from a tutorial I followed. I'll drill in and grab the exception error. Thanks for the security tip Brad. – E Crux May 21 '13 at 20:46

1 Answers1

1

You said that your database is in the APP_Data directory, but your connection string assumes that is in the root directory of your site.

Try with 

conn.ConnectionString = @"Data Source|DataDirectory|\MyData.sdf;Persist Security Info=False;";

The |DataDirectory| is a placeholder string that the NET Framework changes to a predefined location where you data files are supposed to stay

And this is how I would change your code

using(SqlCeConnection conn = new SqlCeConnection(@"Data Source|DataDirectory|\MyData.sdf;Persist Security Info=False;"))
using(SqlCeCommand cmd1 = new SqlCeCommand("SELECT Admin FROM SystemUsers WHERE Email=@mail", conn))
{
    conn.Open();
    cmd1.Parameters.AddWithValue("@mail", user.Email);
    SqlCeDataReader admin = cmd1.ExecuteReader();
    while(admin.Read())
    {
      .....
    }
}

As you can see, I have made this changes:

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Thanks for the answer Steve. Unfortunately its still spitting out the same error. I did extract the exception, it is the filepath. ""The path is not valid. Check the directory for the database. [ Path = ~\\APP_DATA\\MainDb.sdf ]"}" – E Crux May 21 '13 at 20:53
  • MainDb.sdf is the database name, i changed your connection string suggestion accordingly. – E Crux May 21 '13 at 21:00
  • You could also try with the substitution string `"Data Source=|DataDirectory|\MainDb.sdf;......"` – Steve May 21 '13 at 21:03
  • That got rid of the initial error, thanks! Now its saying "{"ExecuteReader: Connection property has not been initialized."}" I'm doing something else wrong as well I suppose. – E Crux May 21 '13 at 21:07
  • I think your connection string should be `@"Data Source=|DataDirectory|\MyData.sdf;...`. – Dour High Arch May 21 '13 at 21:21