MSSQLBlogProvider error with categories

Topics: ASP.NET 2.0
Jun 26, 2007 at 1:28 AM
Does anyone know why I can add a post, but can't add categories? I get the following:
ExecuteNonQuery requires an open and available connection.
Coordinator
Jun 26, 2007 at 3:16 AM
I do. You are using the release version 1.0 and there is a problem adding categories. It is a very lame bug to escaped the 1.0 release. I'm sorry.

I'd recommend getting the latest source code and the latest scheme in the setup folder of the web site if you'd like to use the MSSQL provider.
Jun 26, 2007 at 3:21 AM

RazorAnt wrote:
I do. You are using the release version 1.0 and there is a problem adding categories. It is a very lame bug to escaped the 1.0 release. I'm sorry.

I'd recommend getting the latest source code and the latest scheme in the setup folder of the web site if you'd like to use the MSSQL provider.


Thanks RazorAnt for your reply. Excuse my being new to this app and this site, but I'm not sure what setup folder you are referring to. Can you be more specific as to where I can find that?
Jun 26, 2007 at 12:22 PM
Ok. I found the setup folder you are referring to, now the next question is:
How do I place all the initial values into the tables. Like for instance the Settings table. How do I know what SettingName and what SettingValue to use?
Jun 26, 2007 at 7:42 PM
Open up the settings.xml file and then open up your database table called be_Settings. Depending on what you prefer, either Open the table for editing or create a SQL INSERT script to insert the values you find in the Settings XML file into the settings table.

You will need to add the connection string for your database in the settings table called <mssqlconnectionstring />, add the setting name "mssqlconnectionstring" to your your table and assing it the value you entered in your web.config i.e. open up your web.config file, copy the whole string from connection="......" and paste it into here.

You may get an error about the Archive control failing due to not having any blog entries present in the database. To resolve this, open up your sidebar.ascx file for your theme and then remove the section the error is referring to, this will allow you to load up the BlogEngine.NET application. Add a new blog entry and then restore the section you removed and you will find the control now works as there is a valid blog entry.

Hope that helps,

Fz
Coordinator
Jun 27, 2007 at 2:22 AM
I will go ahead and add these initial values to the script. I should have thought about that before. (I guess I should go ahead and load up the initial post as well.)

The connection string is no longer needed in the settings.xml or settings table. Once we moved settings the blog provider, we needed to have the connection string outside the storage.

I'll try to get the script updated later in the week.


Jun 27, 2007 at 2:33 AM
Thanks finalzero... those instructions got me over the hump. I went ahead and just added an initial post as well to keep the error from happening.

RazorAnt... so is it using the connectionstring in web.config now? So I can delete the string in the settings table? Can I remove the settings.xml file all together? Also, one issue I had was with htmlheader value in the settings table. It was <link rel="example" /> in the xml file, so I copied it verbatim. It then showed up exactly that way on the front page. Should that be put in the table differently (using the actual < instead of &lt)? Would the other entries that have alternate tag codes be put in as the actual tag (< instead of &lt)...like contactformmessage? I appologize for being so dense, but appreciate your help and absolutely love this application.
Coordinator
Jun 28, 2007 at 8:10 PM

smithsc wrote:

RazorAnt... so is it using the connectionstring in web.config now? So I can delete the string in the settings table? Can I remove the settings.xml file all together? Also, one issue I had was with htmlheader value in the settings table. It was <link rel="example" /> in the xml file, so I copied it verbatim. It then showed up exactly that way on the front page. Should that be put in the table differently (using the actual < instead of &lt)? Would the other entries that have alternate tag codes be put in as the actual tag (< instead of &lt)...like contactformmessage? I appologize for being so dense, but appreciate your help and absolutely love this application.


Yes. The current source code is using the connection string in the web.config. I'm really tempted to move it into a separate file (sql.config or something) so that updating the web.config for upgrading will not cause you to need to re-type your connection string. I'll likely do this soon. You can remove the settings.xml once you've moved stuff over.

Regarding the htmlheader, it should be put in the table using the actual codes.

I'll be working on the project a little tonight and again this weekend so I'll get the updates I promised in soon.
Jun 29, 2007 at 4:51 PM
If you are following the Provider Model the provider node in the web.config should contain the connection string or connection string name such as:

<BlogEngine>
<blogProvider defaultProvider="SqlBlogProvider">
<providers>
<add name="SqlBlogProvider" type="BlogEngine.Core.Providers.MSSQLBlogProvider" connectionStringName="MyConnection" />
</providers>
</blogProvider>
</BlogEngine>

<connectionStrings>
<add name="MyConnection" connectionString="..." providerName="System.Data.SqlClient"/>
</connectionStrings>
Jun 30, 2007 at 2:10 AM
I do have that in my web config, except for the connectionStringName="MyConnection", yet it seems to be working correctly. I'll go ahead and add that.

Other than that, I think it is working correctly, but I won't know for sure until I remove the settings.xml file, which is why I asked the question.

I want to thank all of you who responded. You've been amazing and I can't thank you enough.
Jul 1, 2007 at 3:18 AM
I went a different route as I encountered some wierd problems when trying to use the MSSQLBlogProvider (probably because it was still using the XmlMembershipProvider class).

I removed the XML part entirely, added a custom Provider section in the web.config to point to a new class called MSSQLMembershipProvider. Worked out much better, all I did was make a copy of the XmlMembershipProvider class ensuring I wouldn't run into any issues that way and the code remains true to the spec of the Xml version (and the provider model).

I originally tried using the out-of-the-box SqlProvider calls as featured in .NET 2.0 however I found some weird bugs like the provider model falling on its arse when trying to deal with encrypted passwords. In the end I found the solution in some obscure MSDN page about implementing the provider model (funny how much their documentation differs from section to section).

I didn't have to change the XmlMembershipProvider class too much however. I had to add in some code to deal with pulling the Membership data out of the sql database (the SqlMembership splits the info between the Membership and Users tables). To get around the password issue I created a helper function that takes the user object from the DataStore and then for the given ID pulls out the PasswordFormat integer value and thus assigns PasswordFormat.

The reason I am doing this is due to having some code that encrypts/decrypts the password if the admin has decided to encrypt the passwords. You could always just go for plain text but its not very secure.

As creating/deleting/edit etc is handled by the base class I only need to get the code sorted to deal with retrieving passwords but I am out of time at the moment!
Jul 5, 2007 at 5:22 PM
I actually wrote my own SqlBlogProvider class so that it would work more like the rest of the .NET providers. Currently, the MSSQLBlogProvider class implements the retrieval of the connection string through a private methods that requires you to have a connectionString node in your web.config file named "BlogEngine". To properly implement the provider model the SqlBlogProvider contains a connectionStringName property that allows you to supplied the named connection string. The connection string value is actually set during initialization of the provider class. Here is the Initialize method from my class:

private string _sqlConnection;
private string _sourceName;

public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
{
base.Initialize(name, config);

_sourceName = ProviderUtil.GetSourceName(config);
_sqlConnection = ProviderUtil.GetConnectionString(config);

ProviderUtil.CheckUnrecognizedAttributes(config);
}

The _sourceName is a property I added to my database schema to allow me to store content for multiple sites in one database implementation. Initialize is called by the .NET framework so all your methods can reference the _sqlConnection property and it will have been set when the class is created.

This is how other providers are implemented within the .NET framework.