Navigation

Search

Categories

On this page

Using Multiple Active Result Sets (MARS) to Execute Multiple Commands on a Connection
SQL Server Information_Schema
SQL Collation Conflict
REPLACE Function for a SQL Server Text Field
Zoundry Raven
Getting Acquainted With jQuery
Problems With Windows Live Writer and DasBlog
Highlighting Background Color in Gridview With a Checkbox
Building Custom Controls
Getting the ID value of the most recently added record

Archive

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

RSS 2.0 | Atom 1.0 | CDF

Send mail to the author(s) E-mail

Total Posts: 454
This Year: 16
This Month: 1
This Week: 0
Comments: 18

Sign In
Pick a theme:

# Tuesday, November 18, 2008
Tuesday, November 18, 2008 7:59:46 PM (GMT Standard Time, UTC+00:00) ( ASP.NET | Database | SQL )

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

Comments [0] | | # 
# Friday, November 14, 2008
Friday, November 14, 2008 4:15:26 PM (GMT Standard Time, UTC+00:00) ( SQL )


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!

Comments [0] | | # 
# Wednesday, November 12, 2008
Wednesday, November 12, 2008 8:31:28 PM (GMT Standard Time, UTC+00:00) ( SQL )
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
Comments [0] | | # 
Wednesday, November 12, 2008 6:31:11 PM (GMT Standard Time, UTC+00:00) ( SQL )


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/

Comments [0] | | # 
# Friday, October 31, 2008
Friday, October 31, 2008 1:59:18 PM (GMT Standard Time, UTC+00:00) ( )

 

I hope this works

Comments [0] | | # 
# Tuesday, September 30, 2008
Tuesday, September 30, 2008 8:47:55 PM (GMT Daylight Time, UTC+01:00) ( )

I've been trying to get up to speed using jQuery especially since Microsoft made the announcement that jQuery will be bundled with Visual Studio in the near future.  I'm going to use this post to compile some links related to jQuery.

Comments [0] | | # 
Tuesday, September 30, 2008 7:58:57 PM (GMT Daylight Time, UTC+01:00) ( )

I love Live Writer for posting content to my blog (DasBlog I'm not so thrilled with). Occasionally, Live Writer will stop posting content to the site - I receive no error messages at all; it just won't post.  I've discovered that Live Writer creates a folder under /dasblog/content/binary/WindowsLiveWriter/<title of blog post>/ when it encounters a problem. Deleting this folder seems to fix the problem.

Comments [0] | | # 
Tuesday, September 30, 2008 7:51:07 PM (GMT Daylight Time, UTC+01:00) ( )

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Untitled Page</title> <script language="javascript" type="text/javascript"> function changeColor(obj) { var rowObject = getParentRow(obj); if(obj.checked) { rowObject.style.backgroundColor = 'Yellow'; } else { rowObject.style.backgroundColor = ''; } } // This method returns the parent row of the object function getParentRow(obj) { do { if(isFireFox()) { obj = obj.parentNode; } else { obj = obj.parentElement; } } while(obj.tagName != "TR") return obj; } function isFireFox() { return navigator.appName == "Netscape"; } </script> </head> <body> <form id="form1" runat="server"> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/email.mdb" SelectCommand="SELECT [Name], [Email] FROM [tblEmail]"> </asp:AccessDataSource> <div> <asp:GridView runat="server" ID="gvFulfillment" AutoGenerateColumns="False" DataSourceID="AccessDataSource1"> <Columns> <asp:TemplateField HeaderText="Select:"> <ItemTemplate> <asp:CheckBox ID="chkSelect" runat="server" onClick="changeColor(this)" /> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /> </Columns> <HeaderStyle CssClass="contentGridHeader" HorizontalAlign="center" /> <RowStyle CssClass="content" /> <SelectedRowStyle CssClass="contentGridSelected" /> <PagerStyle CssClass="content" /> </asp:GridView> </div> </form> </body> </html>
Comments [0] | | # 
# Friday, September 26, 2008
Friday, September 26, 2008 7:44:25 PM (GMT Daylight Time, UTC+01:00) ( )


The two basic types of controls are fully rendered and composite controls.  You must start from scratch to build a fully rendered control and a composite control builds upon existing controls.

  • System.Web.UI.Control
  • System.Web.UI.Control.WebControls.WebControl
  • System.Web.UI.Control.WebControls.CompositeControl

For example, the CompositeControl class inherits from the WebControl class, which inherits from the Control class. Each of these classes add additional functionality. The base class for all controls in the ASP.NET framework is the System.Web.UI.Control class. Every control ultimately derives from this control. The System.Web.UI.Control.UI.WebControls.CompositeControl class should be used as the base class for any composite control.

All Web controls inherit from the base System.Web.UI.WebControls.WebControl class. 

This is an example of a fully rendered control.  When creating these you take on the responsibility of specifying all of the HTML content that the control renders to the browser. This example derives from the base Control class

** FullyRenderedControl.vb **

Imports System.Web.UI
 
Namespace myControls
    Public Class FullyRenderedControl
     Inherits Control
        Private _Text As String
 
        Public Property Text() As String
            Get 
                 Return _Text
            End Get
            Set (ByVal Value As String) 
                 _Text = value
            End Set
        End Property
 
        Protected Overrides  Sub Render(ByVal writer As HtmlTextWriter)
            writer.Write(_Text)
        End Sub
    End Class
End Namespace

** ShowFullyRenderedControl.aspx ** and demo

<%@ Page Language="VB" %>
<%@ Register TagPrefix="custom" Namespace="myControls" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Fully Rendered Control</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <custom:FullyRenderedControl
        ID="FullyRenderedControl1"
        Text="Hello World!" 
        runat="Server" />
    
    </div>
    </form>
</body>
</html>

In the next example, rather than inherit from the base Control class, you can create a fully rendered control by inheriting a new control from the base WebControl class. When inheriting from the WebControl class, you override the RenderContents() method instead of the Render() method.  Notice that the BackColor, BorderStyle, and Font properties are set because it derives from the base WebControl class.

** FullyRenderedWebControl.vb **

Imports System.Web.UI
Imports System.Web.UI.WebControls

Namespace myControls
    Public Class FullyRenderedWebControl
        Inherits WebControl

        Private _Text As String

        Public Property Text() As String
            Get
                Return _Text
            End Get
            Set(ByVal Value As String)
                _Text = value
            End Set
        End Property

        Protected Overrides Sub RenderContents(ByVal writer As HtmlTextWriter)
            writer.Write(_Text)
        End Sub

    End Class
End Namespace

** ShowFullyRenderedWebControl.aspx ** and demo

<%@ Page Language="VB" %>
<%@ Register TagPrefix="custom" Namespace="myControls" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Fully Rendered WebControl</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <custom:FullyRenderedWebControl
        ID="FullyrenderedWebControl1"
        Text="Hello World"
        BackColor="Yellow"
        BorderStyle="Dashed" 
        Font-Size="32px"   
        Runat="Server" />
    
    </div>
    </form>
</body>
</html>

A WebControl, unlike a control, renders an enclosing <span> tag by default.

<span id="FullyrenderedWebControl1" style="display:inline-block;background-color:Yellow;border-style:Dashed;font-size:32px;">Hello World</span>

You can specify a different tag by overriding the WebControl's TagKey property. This control renders its contents within an HTML <div> tag.

** Glow.vb **

Imports System.Web.UI
Imports System.Web.UI.WebControls 

Namespace myControls
    Public Class Glow
        Inherits WebControl
        Private _Text As String 

        Public Property Text() As String
            Get
                Return _Text
            End Get
            Set(ByVal Value As String)
                _Text = value
            End Set
        End Property 

        Protected Overrides ReadOnly Property TagKey() As HtmlTextWriterTag
            Get
                Return HtmlTextWriterTag.Div
            End Get
        End Property 

        Protected Overrides Sub AddAttributesToRender(ByVal writer As HtmlTextWriter)
            writer.AddStyleAttribute(HtmlTextWriterStyle.Filter, "glow(Color=#ffd700,Strength=10)")
            MyBase.AddAttributesToRender(writer)
        End Sub 

        Protected Overrides Sub RenderContents(ByVal writer As HtmlTextWriter)
            writer.Write(_Text)
        End Sub 

        Public Sub New()
            Me.Width = Unit.Parse("500px")
        End Sub 

    End Class
End Namespace

** ShowGlowWebControl.aspx ** and demo
<%@ Page Language="VB" %>
<%@ Register TagPrefix="custom" Namespace="myControls" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Fully Rendered WebControl</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>    
    <custom:Glow
        ID="FullyGlowControl"
        Text="Hello World"
        Font-Size="32px"   
        Runat="Server" />
    
    </div>
    </form>
</body>
</html>
Comments [0] | | # 
# Friday, September 19, 2008
Friday, September 19, 2008 9:52:16 PM (GMT Daylight Time, UTC+01:00) ( ASP.NET | SQL )


The built-in functions @@Identity() and Scope_Identity() are designed to retrieve the most recently added record's autoincrement identity value from Access and Sql Server respectively. Here are some usage examples.

Access and @@Identity()

The Jet 4.0 provider supports @@Identity(), which means that developers no longer need to use Select Max(ID) or some other method. The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.

Dim query As String = "Insert Into Categories (CategoryName) Values (?)"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=|DataDirectory|Northwind.mdb"
Using conn As New OleDbConnection(connect)
  Using cmd As New OleDbCommand(query, conn)
    cmd.Parameters.AddWithValue("", Category.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
    cmd.CommandText = query2
    ID = cmd.ExecuteScalar()
  End Using
End Using

SQL Server and Scope_Identity()

While Sql Server also supports @@Identity(), the recommended method for obtaining identity values on this platform is Scope_Identity(),

Dim query As String = "Insert Into Categories (CategoryName) Values (@CategoryName);" & _
    "Select Scope_Identity()"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" & _
    "Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.Parameters.AddWithValue("@CategoryName", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

Alternatively, you may prefer to use an output parameter from a stored procedure, and ExecuteNonQuery().

Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID")
    cmd.Parameters("@CategoryID").Direction = ParameterDirection.Output
    conn.Open()
    cmd.ExecuteNonQuery()
    ID = cmd.Parameters("@CategoryID").Value
  End Using
End Using
CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category nvarchar(15),
  @CategoryID int OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  Insert Into Categories (CategoryName) Values (@Category)
  Set @CategoryID = Scope_Identity()
END

Finally, you can create a stored procedure that contains no output parameter, but ends with 'Select Scope_Identity()'. This version requires ExecuteScalar(), and requires less ADO.NET code and a shorter Stored Procedure.

Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

The stored procedure:

CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category nvarchar(15)
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  Insert Into Categories (CategoryName) Values (@Category)
  Select Scope_Identity()
END
Comments [0] | | #