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:

<InsertParameters>
<asp:parameter direction="Output" name="answerID" type="Int32"></asp:parameter>
</InsertParameters>

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

AS
BEGIN

INSERT INTO [Answers] (
[questionID],
[answerText]
)

VALUES 
(
@questionID,
@answerText
)

END

SELECT SCOPE_IDENTITY()
Set @answerID = scope_identity()

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

Using the Ajax HTMLEditor 'Lite top toolbar' sample with VB.net

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 developerfusion.com. Obvisously, change the file extension to .vb too.

Then at the top of your aspx page you need to register it:

<%@ Register
    TagPrefix="customEditors"
    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!

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
            stringList.Add(thing.Row.Item("Tag"))
        Next

        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:

system.web.script.services.scriptservice()

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">
</ajaxcontrolkit:autocompleteextender>