SQL Server 2005 database diagram support objects error

Sql 2005 Database diagram support objects cannot be installed because this database does not have a valid owner...

Solution:
1. Right Click on your database, choose properties
2. Goto the Options Page
3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
4. Goto the Files Page
5. Enter "sa" in the owner textbox.
6. Hit OK

Reference: http://geekswithblogs.net/shahed/archive/2007/11/19/116940.aspx

Incorrect Syntax Near sproc ...

I got this 'incorrect syntax near spoc (stored procedure)' error from the following:

        string connectionString = getConnectionString("ConnectionString");
        SqlConnection conn = new SqlConnection(connectionString);
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_aspnet_StocksInLists_RemoveStocksFromList", conn);
        
        try
        {
            conn.Open();
            command.Parameters.AddWithValue("@ListId", ddlList.SelectedValue);
            iRes = command.ExecuteNonQuery();
            if (iRes < 0) throw new Exception("Error removing stocks from list");
        }

The bug fix is to set the command type:

        string connectionString = getConnectionString("ConnectionString");
        SqlConnection conn = new SqlConnection(connectionString);
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_aspnet_StocksInLists_RemoveStocksFromList", conn);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        try
        {
            conn.Open();
            command.Parameters.AddWithValue("@ListId", ddlList.SelectedValue);
            iRes = command.ExecuteNonQuery();
            if (iRes < 0) throw new Exception("Error removing stocks from list");
        }

Get the Current Time for Database Storage

To get the current datetime in UTC format in .net use following:

DateTime dateUTC = DateTime.Now.ToUniversalTime();

You can then store this date in your database.
If you want to display it back to local time use:

DateTime dateLocal = dateUTC.ToLocalTime();

Reference: http://forums.asp.net/t/1217716.aspx

The Difference Between NVARCHAR and VARCHAR

The main difference between NVARCHAR and VARCHAR is that NVARCHAR extra space allows for easier multi-language support. Therefore, I switched my database's VARCHARs to NVARCHARS.

References: http://july-code.blogspot.com/2008/03/differences-between-varchar-and.html

Using a GridView with Edit and Delete that use stored procedures

Here is how to use a GridView with Edit and Delete that use stored procedures for update and delete. First, the aspx:

        <asp:GridView ID="gvEmail" runat="server" AutoGenerateDeleteButton="True" 
        AutoGenerateEditButton="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
        AllowPaging="True" OnRowDeleting="gvEmail_RowDeleting" PageSize="99" OnRowUpdating="gvEmail_OnRowUpdating">
            <Columns>
                <asp:BoundField DataField="email" HeaderText="email" SortExpression="email" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT [email] FROM [vw_Email]"
            DeleteCommand="sproc_DeleteEmail" DeleteCommandType="StoredProcedure"
            UpdateCommand="sproc_UpdateEmail" UpdateCommandType="StoredProcedure"
            >
            <DeleteParameters><asp:Parameter Name="email" Type="String" /></DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="email" Type="String" />
                <asp:Parameter Name="newEmail" Type="String" />
            </UpdateParameters>
            </asp:SqlDataSource>

Now the code behind.

    protected void gvEmail_RowDeleting(object sender, GridViewDeleteEventArgs e) //DELETE
    {
        SqlConnection conn = new SqlConnection(SqlDataSource1.ConnectionString);
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_DeleteEmail", conn);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        conn.Open();
        string email = string.Empty;
        DataControlFieldCell cell = gvEmail.Rows[e.RowIndex].Cells[1] as DataControlFieldCell;
        gvEmail.Columns[0].ExtractValuesFromCell(
            e.Keys,
            cell,
            DataControlRowState.Normal,
            true);
        email = e.Keys[0].ToString();
        if (!string.IsNullOrEmpty(email))
        {
            command.Parameters.AddWithValue("@email", email);
            command.ExecuteNonQuery();
        }
        command.Parameters.Clear();
        conn.Close();
    }
    protected void gvEmail_OnRowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        SqlConnection conn = new SqlConnection(SqlDataSource1.ConnectionString);
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_UpdateEmail", conn);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        conn.Open();
        string newEmail = e.NewValues[0] as string;
        string email = e.OldValues[0] as string;
        if (!string.IsNullOrEmpty(email))
        {
            command.Parameters.AddWithValue("@email", email);
            command.Parameters.AddWithValue("@newEmail", newEmail);
            command.ExecuteNonQuery();
        }
        command.Parameters.Clear();
        conn.Close();
    }   

Note: Exception handling left off for brevity.
Reference: http://www.developerfusion.co.uk/show/91/7/

Configure SQL Database for User Login

If you try and set up User Login without configuring the web.config file and try to login from a hosted site you might get an error similar to :" An attempt to attach an auto-named database for file C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. "
To configure it for User Login, I first added the user database to the SQL server using this method.

Next, I changed the web.config file to include:

  <connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=my.host.net;Database=myDatabase;uid=myUserId;pwd=myPassword" providerName="System.Data.SqlClient"/>
  </connectionStrings>
    <system.web>
        <authentication mode="Forms" />
    
    <membership defaultProvider="AspNetSqlMembershipProvider">
      <providers>
        <clear/>
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServersm" requiresQuestionAndAnswer="true" requiresUniqueEmail="true" passwordFormat="Hashed" minRequiredNonalphanumericCharacters="0" minRequiredPasswordLength="3" applicationName="/" enablePasswordRetrieval="false" enablePasswordReset="true" maxInvalidPasswordAttempts="3"/>
      </providers>
    </membership>
    
  <roleManager enabled="true" />

Just change the myX fields to your fields. And now your configuration manager should work to add users and roles.

Giving Stored Procedure Permissions

To give the required permissions in SQL Server Management Express SQL 2005 database, go to Security -> Schemas. Select the target schema. Right click for properties and Add the target user to the Execute Permission.

SQL Server Management Studio Express Review

SQL Server Management Studio Express is fine GUI for manipulating your database. Unfortunately, the problems I've been having are due to the complete lack of robustness. Try copying over 500 records to your table via the cut and paste Excel method and you should expect the program to freeze up and maybe have success. I would recommend using your own stored procedure (SPROC) for data uploading tasks. It is more cumbersome, but at least it works for large data handling.

C# to run stored procedure after parsing text

       char[] sSeparators = { ',', ' ' };
       string[] sStocks = txtStocks.Text.Split(sSeparators);
       SqlConnection conn = new SqlConnection("AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;User Instance=True;Data Source=.\\SQLEXPRESS;Integrated Security=True;");
       System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_aspnet_CreateStockInList", conn);
       command.CommandType = System.Data.CommandType.StoredProcedure;
       conn.Open();
       foreach (string _stock in sStocks)
       {
           command.Parameters.AddWithValue("@ListId", txtListId.Text.Trim());
           command.Parameters.AddWithValue("@Symbol", _stock.Trim());
           command.ExecuteNonQuery();
           command.Parameters.Clear();
       }
       conn.Close();