Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Steffen Jørgensen 14 posts 34 karma points
    Nov 18, 2012 @ 00:44
    Steffen Jørgensen
    0

    Access to Umbraco database denied

    Hi fellow umbraconians :-)

    I've extending Umbraco to handle some data, that the administrator should be able to access and modify. My Umbraco is running on the SQLCE database and I've added a few tables to the database. To access and modify the new tables I have created a usercontrol and added a custom section - so far so good.

    The problem is when I access my custom section and enter some data in the usercomtrol, which will save it to the database, I get the following exception:

    SqlCeException (0x80004005): Database already opened by a different user

    The code that saves to the DB looks like this:

    protected void CreateEventButton_Click(object sender, EventArgs e)
    {
    string sql = "INSERT INTO Events (StartTime, Headline, Description, ShowOnFrontpage) VALUES (@StartTime, @Headline, @Description, @ShowOnFrontpage)";
    List<IParameter> sqlParams = new List<IParameter>();
    sqlParams.Add(umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@StartTime", DateTime.Parse(StartDate.Text)));
    sqlParams.Add(umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@Headline", Headline.Text));
    sqlParams.Add(umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@Description", Description.Text));
    sqlParams.Add(umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@ShowOnFrontpage", ShowOnFrontpage.Checked));
    umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(sql, sqlParams.ToArray());
    ClearFields();
    }

    As far as I can think, the runtime see Umbraco as one user and my usercontrol as another user. I guess I'm using the Umbraco API wrong but the documentation is somewhat thin on this - maybe some of you can help a noob Umbraco developer?

    Take care,

    Steffen

  • Richard Terris 273 posts 715 karma points
    Nov 18, 2012 @ 01:17
    Richard Terris
    0

    I think this may be to do with how you are trying to perform the query, which seems to be a mixture of inline SQL and stored procedure.

    Can't see all your code you don't appear to be opening a connection anywhere. 

    I think you want to create a stored procedure and remove the inline SQL part, you're almost there with the rest as you've aready set up parameters.

    Have you tried stepping through this to see where it's falling over?

  • Steffen Jørgensen 14 posts 34 karma points
    Nov 18, 2012 @ 21:39
    Steffen Jørgensen
    0

    Hi Richard

    Initially I tried make a conventional SqlCeConnection, but got the same error. That's why I tried digging into the Umbraco API and using that instead. When I use the regular SqlCeConnection, the exception is thrown when I try to open the connection.

     

  • Richard Terris 273 posts 715 karma points
    Nov 18, 2012 @ 23:14
    Richard Terris
    0

    I don't use SqlCE so I don't really know much about it, but I'd say it's either how you're trying to connect to the db or more of a SQLCE issue than an Umbraco one. 

    From the code posted I don't see a connection string and I don't see a connection to the database being opened.

    I'd expect to see something like:

    <code>

    var connStr = ConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;

    var conn = new SqlConnection(connStr);

    var comm = new SqlCommand("StoredProcedureName", conn) {CommandType = CommandType.StoredProcedure};

    comm.Parameters.AddWithValue("@StartTime",DateTime.Parse(StartDate.Text));

    conn.Open();

    comm.ExecuteNonQuery();

    conn.Close();

    conn.Dispose();

    </code>

    Something like that; Since you're using a non umbraco table I don't think the API use is necessary for what you're doing.

    Unless working with SqlCE is different to SQL Express, as I said I don't use it.

  • Steffen Jørgensen 14 posts 34 karma points
    Nov 25, 2012 @ 18:01
    Steffen Jørgensen
    0

    I tried that as my first step, which initially led me to using the SqlCeConnection.

    I ended up migrating from SqlCe to MSSQL, using the "Migrate" function in WebMatrix. Please be aware that the web.config is not modified correctly using WebMatrix. You need to update the following line to match your DB-connection:

    <add key="umbracoDbDSN" value="server=your_db_server_here;database=your_database_name_here;user id=your_user_id_here;password=your_password_here" />

Please Sign in or register to post replies

Write your reply to:

Draft