Monday, 23 November 2009

Accessing an output paramater from a stored procedure - getting the id of an inserted row

What I want to do is access the id of the row I just inserted into the database. To do this I use the itemInserted event of the datasource.

Here I'm inserting using a SqlDataSource configured with the following output parameter within the InsertParameters tags:

<asp:parameter direction="Output" name="answerID" type="Int32"></asp:parameter>
and here's the inserted event handler
Protected Sub answerInserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs) Handles InsertAnswerDS.Inserted
theValueIWant = Convert.ToInt32(e.Command.Parameters("@answerID").Value)
End Sub
And what's going on in the stored procedure? It looks like this:
CREATE PROCEDURE [dbo].[InsertAnswer]
@questionID integer,
@answerText varchar(50),
@answerID integer output


INSERT INTO [Answers] (



Set @answerID = scope_identity()

Thursday, 12 November 2009

Zip up a directory and download to client

I googled about this and found some pretty contrived ways of doing it, especially when it came to honouring subdirectories and their structure. Eventually I discovered the FastZip class of the ICSharpCode.SharpZipLib.Zip library that makes it very easy.

And here's my code. Send it the path of the dir to be zipped and the desired name of the resulting zip file:

Public Sub ZipAndDownload(ByVal strPath As String, ByVal strFileName As String)

HttpContext.Current.Response.ContentType = "application/octet-stream"
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" & strFileName & ".zip")

'FastZip - zip all the file and folders
'and stream it through the Response OutputStream
Dim fz As New ICSharpCode.SharpZipLib.Zip.FastZip()
fz.CreateZip(HttpContext.Current.Response.OutputStream, strPath, True, Nothing, Nothing)

End Sub

Tuesday, 3 November 2009

Using the Ajax HTMLEditor 'Lite top toolbar' sample with

I thought I'd share this as it took some time for me to work this one out.

I wanted to use the AJAX Control Toolkit HTML Editor, but wanted less functionality than the standard implementation. In fact, the Lite top toolbar without bottom toolbar was what I wanted.

To do this you need to copy the HTMLEditor.Samples.cs into your App_Code directory. You'll find it in Ajax Control Toolkit\SampleWebSite\App_Code\.

If, like me, you use VB rather than C#, then you'll need to convert it. I did this using the amazing tool at Obvisously, change the file extension to .vb too.

Then at the top of your aspx page you need to register it:
<%@ Register
Namespace="AjaxControlToolkit.HTMLEditor.Samples" %>
I'm assuming here that you've already used the AjaxControlToolkit in your project, otherwise you'll have to register the main assembly too.

And finally you can add your control like so:
<customEditors:LiteNoBottom ID="myEditor" runat="server" Height="300px" Width="100%" TabIndex="2" NoScript="true" NoUnicode="true" />
Works for me!

Monday, 2 November 2009

Send javascript alert to client from codebehind

This is definitely really useful. I (mostly) love ASP.NET!
ScriptManager.RegisterClientScriptBlock(Me, GetType(Page), "clientAlert", "alert('This is the alert text');", True)
or in C#:
ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "clientAlert", "alert('This is the alert text');", true)

Ajax autocomplete example

Auto completion of a text box is a great way to either suggest and/or restrict user input. After typing a specified number of characters an asynchronous call is made to the database to search for entries that match the input, and they are then displayed at the client in a selectable list beneath the input box.

The ajax control toolkit gives us a neat way of achieving this. I did however, for some reason (probably user error!) have trouble implementing the example they provide. I eventually got it all working and I thought I'd post it here for my reference.

Here's the web service code. In this case my service was called questionCategoryTags:

Public Class questionCategoryTags
Inherits System.Web.Services.WebService

Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()

Dim stringArray() As String
Dim sds As New SqlDataSource
Dim ds As New DataView

sds.ConnectionString = ConfigurationManager.ConnectionStrings.Item("myConnectionString").ToString
sds.DataSourceMode = SqlDataSourceMode.DataSet

sds.SelectCommand = "SELECT DISTINCT Tag FROM Tags WHERE (Tag like '" & prefixText & "%')"
ds = CType(sds.Select(DataSourceSelectArguments.Empty()), DataView)

Dim stringList As New List(Of String)

For Each thing As DataRowView In ds

stringArray = stringList.ToArray

Return stringArray

End Function

End Class

And don't forget to have this line at the top of your web service. It won't work without it:

And here's the page source:

<ajaxcontrolkit:autocompleteextender completionsetcount="12" delimitercharacters=", " id="AutoCompleteExtender1" minimumprefixlength="2" runat="server" servicemethod="GetCompletionList" servicepath="questionCategoryTags.asmx" showonlycurrentwordincompletionlistitem="true" targetcontrolid="TextBox1" CompletionInterval="1000">