Last post Dec 29, 2005 09:18 PM by adefwebserver
Dec 28, 2005 05:41 PM|chrissuttman|LINK
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) _
Can anyone tell me what I may be doing wrong?
Dec 28, 2005 08:23 PM|PScarlett|LINK
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...
Dec 29, 2005 10:50 AM|chrissuttman|LINK
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?
Dec 29, 2005 04:37 PM|PScarlett|LINK
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..
Dec 29, 2005 09:18 PM|adefwebserver|LINK
This is the Method that should do what you want and will stand up to upgrades in DotnetNuke
Public Sub AddUserRole(ByVal PortalID As Integer, ByVal UserId As Integer, ByVal RoleId As Integer, ByVal ExpiryDate As Date)
Adds a User to a Role
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