Stone Coast Web Design's Weblog

One of the problems with the DBDataReader object is that it keeps an open server-side connection while you are looping through the results of a query.  If you try to execute another command while the first command is still executing, you receive an 'InvalidoperationException' stating that "There is already an open DataReader associated with this connection which must be closed first."  You can avoid this exception by setting the MultipleActiveResultSets connection string option to True when connecting to SQL Server 2005 and above.

<configuration> 
<connectionStrings> 
<clear /> 
<add name="PublishersConnectionString" connectionString="Data Source=localhost;Initial Catalog=Publishers;User ID=xxx;Password=xxx" 
providerName="System.Data.SqlClient; MultipleActiveResultSets=True" />  
</connectionStrings> 

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("PublishersConnectionString").ConnectionString)

        'Create the command object, passing in the SQL string
        Dim strSQL As String = "SELECT au_id, LastName FROM Publishers"
        Dim cmd As New SqlCommand(strSQL)

        Try
            con.Open()
            cmd.Connection = con
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Dim cmdSales As SqlCommand
                cmdSales.CommandType = CommandType.Text
                cmdSales.CommandText = "SELECT  SUM(royaltyper) FROM TitleAuthor WHERE (au_id = @auID)"
                cmdSales.Parameters.AddWithValue("@au_id", reader("au_id"))
                Dim qtySales As Object = cmdSales.ExecuteScalar()
                lbl.Text += reader("LastName").ToString() + ": " + String.Format("{0:C}", qtySales) + "<br />"
            End While
        Finally
            con.Close()
            con.Dispose()
            End
        End Try
    End Sub

Another example and working demo using a TreeView:

http://stonecoastwebdesign.com/code/ShowMARS.aspx

I ran into an issue where I needed to change a list of columns from text and ntext in SQL Server 2000 to varchar(max) and nvarchar(max) fields in SQL Server 2005.  Not wanting to do this one field at a time, I poked around and found a solution using the built in Information_Schema.

To simply produce a list of table names, column names or data types you can do something like this:

SELECT table_name,column_name, data_type
FROM INFORMATION_SCHEMA.Columns
WHERE column_name like '%FirstName%'
ORDER BY table_name

To produce a list of ALTER TABLE statements that will change a column type from Text to Varchar(MAX), run this in Query Analyzer or Management Studio.

SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' VARCHAR(MAX);'
FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'TEXT'
ORDER BY TABLE_NAME

This will produce a list of ALTER TABLE statements that look like this:

ALTER TABLE AuditorFees ALTER COLUMN AuditorChangeNotes VARCHAR(MAX);
ALTER TABLE CEOCompFiguresNotes ALTER COLUMN NotesCEOContract VARCHAR(MAX);
/* etc.

Copy and paste these invidual ALTER TABLE statements in Query Analyzer or Management Studio and run them. Problem solved!

Error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation

Resolution:

SELECT 
        TAnalystAlerts.ID,
        TAnalystAlerts.Category,        
        TAnalystAlerts.Ticker,
        TAnalystAlerts.Title,
        TAnalystAlerts.DatePosted,
        TAnalystAlerts.[Description],
        TAnalystAlerts.URL,
        Companies.id_company,
        Companies.BAStatus
FROM 
        TAnalystAlerts LEFT JOIN Companies ON TAnalystAlerts.Ticker COLLATE Latin1_General_CI_AI  = Companies.Ticker COLLATE Latin1_General_CI_AI

SQL 2000:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=367&lngWId=5

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31893

http://www.sqlteam.com/article/search-and-replace-in-a-text-column

SQL 2005:

Update CompanyProfiles
SET 
CompanyProfiles.CompanyProfile = REPLACE(CAST(companyprofile as varchar(max)),'A' ,'?')
WHERE id_company = 12931

.NET Version:

http://equatorlive.com/blogs/dotnetdave/tech/sql-search-and-replace-on-text-type-columns/