DaysInMonth - get the number of days in a month

' get number of days in current month
Dim days As Integer = Date.DaysInMonth(Now.Year, Now.Month)
Me.lblDays.Text = days

Format a number with comma seperators

'Format a number to have commas
Dim bla As String = "1000000000"
Dim myNum As Integer
myNum = CType(bla, Integer)
Me.lblStrNumber.Text = Format(myNum, "n")

Login failed for user NT AUTHORITY\NETWORK SERVICE

I've just been banging my head against a brick wall, and unfortunately, trawling through a lot of nonsense written on various forums about what to do when you see this:

Cannot open database "slcms" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.


Let's say your connection string looks like this:

Data Source=MYHOST;Initial Catalog=MyDatabase;Integrated Security=True;User ID=myUser;Password=myPassword

This: Integrated Security=True; and this: User ID=myUser;Password=myPassword are mutually exclusive.

Integrated security means 'use my domain credentials'

User ID=myUser;Password=myPassword means 'use these SQL credentials'

So SQL Server can't decide between them.

Solution: Remove this bit : Integrated Security=True;

GridView - Delete Button

Here's an example of adding a Delete Button to a GridView control.

Ok so let's say you have a DataTable of Contacts bound to a GridView control called gvContacts.
Your DataTable has three column contactID,firstname, lastname.

'The Grid


> DataKeyNames="contactID"< AutoGenerateColumns="false"
<columns>
<asp:boundfield datafield="Contact" headertext="firstname">
<asp:templatefield headertext="firstname">
<itemtemplate>
<asp:label id="firstname" runat="server" text="'<%#">'></asp:Label><
</itemtemplate><
</asp:TemplateField><
<asp:templatefield headertext="lastname"><
<itemtemplate><
<asp:label id="lastname" runat="server" text="'<%#">'></asp:Label><
</itemtemplate><
</asp:TemplateField><
<asp:templatefield headertext="Delete"><
<itemtemplate><
><asp:button commandname="Delete" id="btnDelete" text="Delete Contact" commandargument=" runat="server" />
</itemtemplate><
</asp:TemplateField><
</columns><


'The Row Deleting Event is the bit that actually does the delete operation

Protected Sub gvContacts_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles gvContacts.RowDeleting

Dim contactID As Integer = Me.gvContacts.DataKeys(e.RowIndex).Value
DeleteContact(ContactID)

End Sub

'Here we add a clientside confirmation script to be kind to the user. When the Delete button is clicked an alert is triggered asking the user
' if they are sure they want to delete the record.

Protected Sub gvContacts_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvContacts.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
Dim b As Button = e.Row.FindControl("btnDelete") 'CType(e.Row.Cells(4).Controls(0), Button)
b.OnClientClick = String.Format("return confirm('Please confirm you wish to delete this record ?');")
End If

End Sub

Get Name of Month from Date - MonthName Function

God bless W3schools

I've just had a right old drama trying to work out how to get the name of a month rather than the Integer returned by Date.Month and apparently there is a built in function called MonthName.

Eg.
Dim lastmonth As String
lastmonth =
MonthName(Date.Today.Month - 1)
'so the value of lastmonth is now the name of last month.
source http://www.w3schools.com/Vbscript/func_monthname.asp

And the go-faster version:

'''

''' Gets name of previous month to current
'''

''' string
'''
Public Function GetLastMonthName() As String
Dim lastmonth As String
lastmonth = MonthName(Date.Today.Month - 1)
Return lastmonth
End Function

SQL Wildcard Search

I'm always forgetting the syntax for wildcard queries in SQL Server 2005 so here's an example:

SELECT * FROM myTable WHERE (myColumn LIKE '%' + @str + '%')

DBNull

I keep having to google this bad-boy every time it comes up so thought I'd make a note of it here.

Let's say you've got a custom object which is populated using a DataReader, unless you've got a crystal ball it's quite likely that you will end up with some NULL values in your database. Here's what to do about it:

If Not IsDBNull(rdr("Telephone")) Then

m.Tel = rdr("Telephone")
Else

m.Tel = ""
End If

Security - Stop Secure Page Displaying After Logout - Browser Back Button Drama

The title for this post should be fairly self-explanatory but here goes anyway. Let's say you've given up tearing your hair out trying to use Microsofts' new Membership / Role Provider controls set to implement forms authentication and have gone ahead and created your own custom user authentication system.

In your secure area you have a logout button. The user clicks logout and gets re-directed somewhere. Great so far, however when they click the back button in their browser the "secure" page they were looking at before they logged out can still be viewed. Aaaaaaaargh!

Well here's the solution, place the following code in your secure pages page load event and the problem should go away:

Response.Buffer = True
Response.ExpiresAbsolute = DateTime.Now.AddDays(-1)
Response.Expires = -1500
Response.CacheControl = "no-cache"

' in this case I simply check whether my custom login logic is returning true or false, you would 'need to write your own code here.

If CustomLogin = false then
response.redirect("login.aspx")
end if

PS. This doesn't seem to work in Firefox , aaaaaaaargh.

However, this fixes it:

HttpContext.Current.Response.Cache.SetNoStore()

Hallelujah!

GridView - PageIndexChanging

When you enable paging on a GridView control you are more than likely to come accross this exception: "The GridView 'gvBla' fired event PageIndexChanging which wasn't handled. "

Here's how to sort it out.

In your code behind select your GridView controls' PageIndexChanging Event and set it's PageIndex to e.NewPageIndex. Don't forget to rebind your data to the GridView also.

Protected Sub gvBla_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvgvBla.PageIndexChanging
Me.gvBla.PageIndex = e.NewPageIndex
' call your data bind sub routine here

End Sub

STORE PROCEDURE WITH SWITCH

Here's an example of wrapping an update and insert into the same stored procedure.

Let's say your table is called tblContacts with the fields id, FirstName, LastName and Email.

To create a stored procedure which performs both insert and update queries you would use the following syntax:

CREATE PROCEDURE SaveContact(@id int, FirstName varchar, LastName varchar, Email varchar)
as
if id = 0
BEGIN
INSERT INTO tblContacts(id, FirstName, LastName, Email) VALUES(@FirstName, @LastName, @Email)
END
ELSE
BEGIN
UPDATE tblContacts SET FirstName = @FirstName, LastName = @LastName, Email = @Email WHERE id = @id
END

This way you only need to write one method in your Data Access Class.