Did you know that the VS Debugger can cause exceptions?

Yes, sadly, you read that right. I’ve only found 1 case when testing an application at work in which the application doesn’t run through to the end properly because the error raises the following exception at a specific point in processing.

The CLR has been unable to transition from COM context 0×1afb80 to COM context 0×1afcf0 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

This is a ContextSwitchDeadlock was detected Message that the Managed Debugging Assistant (MDA) throws at you.
MDAs are debugging aids that work in conjunction with the common language runtime (CLR) to provide info on the runtime state and generate useful info about runtime events that you can’t otherwise trap

(You can read more about MDAs here: http://msdn.microsoft.com/en-us/library/d21c150d(VS.80).aspx)

I’d go into it, but that’s not the focus of this post, although it is definitely helpful.

You can, however, configure MDAs in VS2005 by going to the Debug >> Exceptions… menu, and opening the Managed Debugging Assistants tree. There you’ll find a list of the MDAs that are thrown. Deselect the ContextSwitchDeadlock item and you can continue to debug the application.

I didn’t know this when I got the error, so I just published my app and ran it fine (after I’d found out that it was the debugger causing the error).

I’ll be posting more about the ContextSwitchDeadlock exception at a later stage, so keep an eye open for that.

Until next time…

-L

Google-like site search in Visual Basic

For a long time, I wondered how to organise a search feature on my site. I could never figure it out… I knew that the search needed something to search (like a database, a web page, or something), but I had no idea about how to conduct the search.

Now I’ve started working again, I’ve learnt a lot of things in the last short month. One of these things that I’ve learnt is how to make an app (web/windows forms/wpf) search for records in a database.
This’ll work very well if your website uses a SQL database as a DataSource for content.

I’ll walk you through creating a Stored Procedure in your database, and having your vb code execute that sp and return the results ^_^

So, we need a Stored Procedure to run, lets write it real quick:
You’ll need either Visual Studio (express editions will do), or SQL Server Management Studio.
I’m using Management Studio here, but there should be little difference to VS’s interface.

Connect to your server, and expand your database from the list on the left. You should find something that says “Programmability” or “Stored Procedures” in the list under your database’s name. If it’s “Programmability”, expand it, and right-click on “Stored Procedures” and select “New Stored Procedure…”

This will create a new query. In the query window, add the following code:

1	SET ANSI_NULLS ON
2	SET QUOTED_IDENTIFIER ON
3	GO
4
5	USE MyBlog
6
7	CREATE PROCEDURE [dbo].[sp_Search]
8	(
9	@SearchText varchar(50)
10	)
11	AS
12	SELECT *
13	FROM Articles
14	WHERE ArticleTitle LIKE @SearchText

This selects all the rows in the table where the ArticleTitle column’s value is similar to the search text.
Now, we need to hook this into our site/app – this is the fun bit.

We need to write code that executes our Stored Procedure. It’s tidier in your code to place the Function that we’re writing below in a class file separate to your form.
I’m naming my class file Search.vb.

1	Imports System.Data
2	Imports System.Data.SqlClient
3
4	Public Class Search
5	‘ This Region houses all the variables we’re declaring globally.
6	#Region “Global Variables”
7	‘ Connection String to be used throughout the application.
8	Public ConnectionString As String = “Data Source=.\SQLEXPRESS; “ & _
9	“Initial Catalog=MyBlog; “ & _
10	“Integrated Security=True;”
11
12	‘ Connection object.
13	Public conn As New SqlConnection(ConnectionString)
14	#End Region
15
16	‘ Execute the Stored Procedure based on input from the user in
17	’ the form of a Function Parameter.
18	Public Function SearchData(ByVal SearchText As String)
19
20	‘ Command and connection to use when processing the search.
21	Dim cmd As New SqlCommand(“[dbo].[sp_Search]“, conn)
22	cmd.CommandType = CommandType.StoredProcedure
23
24	‘ Parameters to use. This will be your search criteria.
25	cmd.Parameters.AddWithValue(“@SearchText”, SearchText)
26	Try
27	‘ Attempt to open the connection and execute the Stored Procedure.
28	conn.Open()
29	cmd.ExecuteNonQuery()
30	Catch ex As Exception
31	‘ If there’s an error with processing the above, alert the user.
32	’ If your application of this is web-based, then replace the following line
33	’ of code with this:
34	’ <Label>.Text = “An error was encountered: ” & vbCrLf & ex.ToString()
35	MsgBox(“An error was encountered: ” & vbCrLf & ex.ToString)
36	Finally
37	’ Close the connection and clear the memory blocks that the connection
38	’ Close the connection and clear the memory blocks that the connection
39	’ and command objects were using
40	conn.Close()
41	conn.Dispose()
42	cmd.Dispose()
43	End Try
44	End Function
45
46	End Class

We’re not done yet. Save your class file and then go back to your form/page’s code behind.
We need to make sure we have the controls on our form to use for the search. Typically, this is just a textbox and a button. I’ve named mine txtSearch and btnSearch respectively.
Add the following to your code behind (obviously replacing names with your own):

1	Imports System.Data
2	Imports System.Data.SqlClient
3
4	‘ Import the class file that has our stored procedure in it (Search.vb)
5	‘ Dim <variable name> As New <external class name>
6	Dim Search As New Search
7
8	Private Sub btnSearch_Click(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Me.btnSearch.Click
9	’ We want to force conversion on our textbox to make sure that the right data type is being passed to the Stored Procedure
10	Dim SearchText As String = txtSearch.Text.ToString
11
12	‘ Create the ADO Data Objects for the DataGrid to use.
13	Dim SearchDataSet As New DataSet(“ArticlesDataSet”)
14	Dim SearchDataAdapter As New SqlDataAdapter(“SELECT * FROM Articles”)
15
16	‘ Set the schema (structure definition) of the table and fill the DataAdapter
17	SearchDataAdapter.FillSchema(SearchDataSet, SchemaType.Source, “Articles”)
18	SearchDataAdapter.Fill(SearchDataSet, “Articles”)
19	’ Set the DataSource for the grid to use our newly created dataset
20	SearchGrid.DataSource = SearchDataSet.Tables(“Articles”)
21
22	’ Execute the function that runs our Stored Procedure.
23	Search.SearchData(SearchText)
24	End Sub

A good idea might be to put the code that creates and populates the DataSet in a separate procedure and just have your Event Handler execute that procedure. This will cause the DataGridView to update every time the button is clicked, meaning that the user can change search criteria and get new results all the time.
There’s nothing different that goes on with the operation of the app when doing this, it just makes the event handler a little neater:

1	Private Sub CreateADO()
2	’ We want to force conversion on our textbox to make sure that the right data type is being passed to the Stored Procedure
3	Dim SearchText As String = txtSearch.Text.ToString
4	’ Create the ADO Data Objects for the DataGrid to use.
5	Dim SearchDataSet As New DataSet(“ArticlesDataSet”)
6	Dim SearchDataAdapter As New SqlDataAdapter(“SELECT * FROM Articles”)
7
8	‘ Set the schema (structure definition) of the table and fill the DataAdapter
9	SearchDataAdapter.FillSchema(SearchDataSet, SchemaType.Source, “Articles”)
10	SearchDataAdapter.Fill(SearchDataSet, “Articles”)
11
12	’ Set the DataSource for the grid to use our newly created dataset
13	SearchGrid.DataSource = SearchDataSet.Tables(“Articles”)
14
15	’ Execute the function that runs our Stored Procedure.
16	Search.SearchData(SearchText)
17	End Sub
18
19	Private Sub btnSearch_Click(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Me.btnSearch.Click
20	CreateADO()
21
22	‘ Do something else here (if needed)
23	End Sub

OK, so, there’s a database search. I did it simply for the purposes of this post (placing search results in a DataGridView, but you can customize the output of your search as you like.

An app like this will be easier to create using LINQ Queries, but that’s for another post. I strongly recommend getting your hands on (at least) the Express Edition of VB2008 and playing around with it. It simplifies so much from VS2005, and, based on the speed at which MS is releasing new stuff, it may be a better idea to forget VS2005 altogether in favor of VS2008.

That’s it for me. For now. Hope you enjoyed reading.

-L

New User Registration

This is a follow-up to my post on a Data Driven Login System.

If you haven’t read it yet, go do that now, I’ll wait…

…

…

…

…

Done? Good.

Just to add to my data driven login system post, your form (Default.aspx) isn’t just magically going to be able to read the code from the code file (Default.aspx.vb), you have to set it to do that in the @Page directive right at the top of your form as follows:

<%@ Page Language=”VB” CodeFile=”Default2.aspx.vb” Inherits=”Default2″ %>

And then right at the top of your code file, you need to have this:

Partial Class Default2

This Partial Class Default2 is the class where all your code is going to go. Your form is going to inherit this class from the code file which means that the form (Default.aspx) is going to read all the data from the class when the page loads.

“Default2″ is the name of the class I used on the page I copied this code from, but, as long as the value of your Inherits”" field on the form is the same as the name of the class in your code file, you should be OK. You can use any name you like. 

This example isn’t going to include account activation, all we’re going to do is create an entry in the database that users can sign in with.

Just as before, I’m going to start out by designing the web form that users are going to register on:

So, the form’s going to go in Default.aspx. The code file we’re inheriting from (see above) is going to be Default.aspx.vb.

<asp:Label runat=”server” id=”lblUsername” value=”Username:”></asp:Label><br />

<asp:Textbox runat=”server” id=”txtUsername”></asp:Textbox><br />

<asp:Label runat=”server” id=“lblUsername” value=”Password:”></asp:Label><br />

<asp:Textbox runat=”server” id=”txtPassword” Textmode=”Password”></asp:Textbox><br />

<asp:Button runat=”server” id=”btnRegister” text=”Register” />

We’re just going to add the record to the same table that we logged in with in the login system, but you can change the table name in the SQL Statement if you like.

These two lines of code have to go right at the top of your code file (lines 1, and 2)

Imports System.Data

Imports System.Data.SqlClient

The next bit of code goes inside your Class Statement:

Private Sub btnRegister_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRegister.Click

Dim connection As New SqlConnection(“Data Source=.\SQLEXPRESS;” & _

“Initial Catalog=Users;” & _

“Integrated Security=True;”)

 Dim sql As String = “INSERT INTOAdmins(AdminName, AdminPass) “ + _

“VALUES(@Name, @Pass)”

Dim command As New SqlCommand(sql, connection)

 command.Parameters.Add(“@Name”, SqlDBType.varChar, 10)

 command.Parameters.Add(“@Pass”, SqlDBType.varChar, 10) 

command.Parameters(“@Name”).value = txtUsername.Text

command.Parameters(“@Pass”).value = txtPassword.Text

 connection.Open() 

 command.ExecuteNonQuery()

 connection.Close()

End Sub

That’ll do it! So lets go through what’s happening here.

The first thing we’re doing is establishing our database connection. Data Source is the SQL Server the database is on, Initial Catalog is the database we’re connecting to, and Integrated Security is the method we use to log into the server with. When set to true, Integrated Security tells SQL Server to use Windows Authentication. We’re not going to go into that now, but all you need to know is that it’s simpler to use, and more secure than other authentication types.

The 2nd thing we’re doing is declaring our SQL Statement. This is where the magic happens. The format is:

INSERT INTO Admins(AdminName, AdminPass) VALUES (@Name, @Pass)

[insert command] [table]([field1], [field2]) VALUES (‘value1’, ‘value2’)

All this is saying is that we want to insert the values @Name, and @Pass into the AdminName and AdminPass fields of the Admins table respectively.

Next we’re creating a SQLCommand object that uses our SQL Statement and connection string (sql, connection) and adding creating SQL Parameters and giving them values based on the user’s input on our form. We do it this way to stop SQL injection which is a bad thing; I’ll go into it at a later stage.

The last 3 lines before the End Sub statement open the connection to the database, execute the SQL Statement (inserting the user’s input), and closes the connection to the database.

So that’s it. I’d like to thank you for your time; I hope you enjoyed this tutorial. If anything’s still unclear, post a comment and I’ll do my best to elaborate.

-L