I'm getting really frustrated. I'm trying to make my custom module add a role for a user, but I keep getting an "InvalidCastException" error referring to my date field. 

Here's the function that is calling "ExecuteNonQuery" in "Microsoft.ApplicationBlocks.Data".

Public Function AddUserRole(ByVal PortalId As Integer, ByVal UserId As Integer, ByVal RoleId As Integer, ByVal ExpiryDate As Date) As Integer

  Return SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & "AddUserRole", _
                                                                        New SqlParameter("@PortalId", PortalId), _
                                                                        New SqlParameter("@UserId", UserId), _
                                                                        New SqlParameter("@RoleId", RoleId), _
                                                                        New SqlParameter("@ExpiryDate", ExpiryDate) _
                                                                        )

 End Function

Can anyone tell me what I may be doing wrong?

The date passed thru to the store procedure is a string and since you did not specify an explicit format, the date will default to a format based on your local preference.  This preference is not a format that SQL understands when converting a string to a date - thus the error.

I would suggest that you define an explicit format  that SQL can convert.  (eg. ExpiryDate.toString("yyyy-MMM-dd hh:mm:ss") )

Hope this helps...

Paul.

I did away with using "SqlParameter" for the parameters too and I have the date working now.

Return SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & "AddUserRole", PortalId, UserId, RoleId, ExpiryDate.ToString("yyyy-MMM-dd hh:mm:ss"))

BUT... what I really need is to be able to make the field NULL.

The sProc has a default value of NULL (@ExpiryDate datetime = null), but if I don't pass a value in the function, I get the error "Parameter count does not match Parameter Value count."

If I pass "Null.NullDate", I get the error "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."

If I pass "Null.NullString", I get the error "String was not recognized as a valid DateTime."

When I search the DNN code to see how they do it, it looks like they use ""Null.NullDate", but it doesn't work for me. Any idea how I can pass a null parameter?

You might want to try using the appropriate controller to perform the add instead of explicitly calling the stored procedure.  The stored procedure could change in the future, but the controller class will protect you from that detail so you don't have to deal with it until a break change is declared. 

The controller - in DotNetNuke.Security.Role I beleive - has a method to add the user to a role and should allow a null to be passed. 

Hope this helps..

Paul.

This is the Method that should do what you want and will stand up to upgrades in DotnetNuke

DotNetNuke.Security.Roles.RoleController.AddUserRole

Public Sub AddUserRole(ByVal PortalID As Integer, ByVal UserId As Integer, ByVal RoleId As Integer, ByVal ExpiryDate As Date)

Summary:
Adds a User to a Role

Parameters:
PortalID: The Id of the Portal
UserId: The Id of the User
RoleId: The Id of the Role
ExpiryDate: The expiry Date of the Role membership