| |
|
|
|
|
|
|
|
|
|
 |  | |
| | |
| | |
| | |
 | | |
|
|
|
| Pulling Data based on UserID |
|
|
Can someone please tell me why the @UserID command is trying to convert the information from nvchar to int. Here is my SQL Query:
SELECT ('DomainName\' + LANUserID) as UserID
FROM MyDatabase.dbo.tblXXXX
WHERE UserID = @UserID
The query runs, gathers the information from the Database in question but then tries to convert the data type. I am at a loss...
The data I am querying is in a different database than the default DNN 4 db. This might have something to do with it, but not sure. I am having to add the DomainName to the front of the LaNUserID since that is how DNN grabs the information from the Active Directory. If I put a specific user name it runs fine and grabs the data I need, but I need it to be able to display the data based on who is logged in. I am using the Active Directory authentication and not native logins to DNN4.
Any help here would be greatlyh appreciated.
Thanks
Dean |
|
|
|
| |
|
|
| Re: Pulling Data based on UserID |
|
|
The problem here is that the WHERE clause is executed after the SELECT clause. So when you say UserID, without qualifiying which UserID you mean (the one produced in the SELECT, or the one in tblXXXX), it assumes the one in the SELECT, which is an nvarchar (because you are using String concatenation).
This query should do what you want:
SELECT ('DomainName\' + LANUserID) as UserID
FROM MyDatabase.dbo.tblXXXX as tbl
WHERE tbl.UserID = @UserID
This tells SQL Server that you want to query against the UserID column in tblXXXX, rather than the one produced by the SELECT clause. I'm not 100% sure if that's what you're trying to do, but hopefully it helps.
Also, If you are trying to get the User Name from DNN using the Reports module, you have to use the Users table (in an Inner Join on UserId) and extract the UserName from there. A future release will include more detailed parameters such as direct access to the UserName and Profile Properties for the current user.
Andrew Nurse
DotNetNuke Core Team Member and Reports Module Project Lead
Microsoft Certified Professional Developer |
|
|
|
| |
|
|
| Re: Pulling Data based on UserID |
|
|
Andrew,
Thanks for the quick response.
The query should be taking the firstname.lastname from our internal db and comparing it to the UserName field in the DNN db. I have to concatenate the DomainName on to the LANUserID since that is how DNN records it in the DNN db. We do not use the DomainName in our internal db.
However I am still having issues. Here is the error I am getting as of now: (User Name changed to "first.last" for anonymity...) It is grabbing the first entry in our db and trying to do a convert at that point. Both fields in each db are nvchar, 100. Not sure what else to do at this point. Will keep plugging away and hopefully I can figure it out soon.
An error has occurred.
DotNetNuke.Services.Exceptions.ModuleLoadException: Syntax error converting the nvarchar value 'anthony.truong' to a column of data type int. ---> System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value 'first.last' to a column of data type int. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) at System.Data.DataTable.Load(IDataReader reader) at DotNetNuke.Modules.Reports.ReportsController.ExecuteReport(ReportInfo objReport, Int32 cacheDuration, Boolean bypassCache, SqlParameter[] parameters) at DotNetNuke.Modules.Reports.VisualizerControlBase.EnsureResults() at DotNetNuke.Modules.Reports.VisualizerControlBase.get_ReportResults() at DotNetNuke.Modules.Reports.Visualizers.Grid.Visualizer.DataBind() at DotNetNuke.Modules.Reports.Visualizers.Grid.Visualizer.Page_Load(Object sender, EventArgs e) --- End of inner exception stack trace ---
Thanks Again,
Dean |
|
|
|
| |
|
|
| Re: Pulling Data based on UserID |
|
|
Did you get past this? How? Here's my dilema - 'Lynne' is the username not the userid...
I'm trying to convert an existing asp1.1/dnn2.0 to asp2.0/dnn4.x... it's some code that when logged in a user can become another user (ie home office associate can log in as a customer and see the site the way the customer would see it).
I'm lost. Here's my error
System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'Lynne' to data type int. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at DotNetNuke.BecomeaUser.IDCheck() at DotNetNuke.BecomeaUser.Page_Load(Object sender, EventArgs e)
I tried implementing you GetCurrentUserInfo()
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load' Obtain PortalSettings from Current Context
Try
DotNetNuke.Entities.Users.UserController.GetCurrentUserInfo()
'Dim _portalSettings As ProviderSettings = CType(Context.Items("PortalSettings"), ProviderSettings)
IDCheck()
' Log User Off from Cookie Authentication System
Dim vUserId As String
' Dim vPass As String
If (Request.Params("Agentid") <> "") Then
vUserId = Request.Params(
Session(
Response.Cookies(
"Agentid")"pRet") = vAgentid"Sub").Value = "555555555"
Response.Cookies(
"Sub").Path = "/"
Else
vUserId = Session(
Session(
"pRet").ToString"pRet") = ""
End If
Dim objSecurity As New Security.PortalSecurityDim blnLogin As Boolean = True
If blnLogin Then
' Attempt to Validate User Credentials
Dim userId As String = vUserId 'objSecurity.UserLogin(vUserId, vPass, 0, 0, 0, 0)
If userId >= 0 Then
formsAuthentication.SignOut()
' expire cookies
Response.Cookies(
"portalid").Value = Nothing
Response.Cookies(
"portalid").Path = "/"
Response.Cookies(
Response.Cookies(
"portalid").Expires = DateTime.Now.AddYears(-30)"portalroles").Value = Nothing
Response.Cookies(
"portalroles").Path = "/"
Response.Cookies(
Response.Cookies(
"portalroles").Expires = DateTime.Now.AddYears(-30)"Sub").Value = Nothing
Response.Cookies(
"Sub").Path = "/"
Response.Cookies(
formsAuthentication.SetAuthCookie(Convert.ToString(userId),
Response.Redirect(
"Sub").Expires = DateTime.Now.AddYears(-30)False)"~/" & "tabid/36/Default.aspx")Else
Response.Write(
"This User is not yet created to be a Web User.2")End If
End If
Response.Write(ex.ToString)
Catch ex As ExceptionEnd Try
End Sub
I'm not having much luck searching so I'm asking in this thread. PLEASE... even a search suggestion would help.
|
|
|
|
| |
|
|
| Re: Pulling Data based on UserID |
|
|
I think there is some confusion here as to what the @UserID parameter contains. It does NOT contain the user name of the current user, it contains a number. This number is the User's unique ID number. To get the user name for a user given this number you need to look it up in the DotNetNuke Users table, for example (assuming you have an objectQualifier of "dnn_"):
SELECT UserName
FROM dnn_Users
WHERE UserID = @UserID
Andrew Nurse
DotNetNuke Core Team Member and Reports Module Project Lead
Microsoft Certified Professional Developer |
|
|
|
| |
| | |
| | |
| | |
|  |
| |
|
|
|
These Discussion Forums are dedicated to the discussion of the DotNetNuke Web Application Framework.
For the benefit of the community and to protect the integrity of the project, please observe the following posting guidelines:
1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DotNetNuke.
2. Discussion or promotion of DotNetNuke product releases under a different brand name are strictly prohibited.
3. No Flaming or Trolling.
4. No Profanity, Racism, or Prejudice.
5. Site Moderators have the final word on approving/removing a thread or post or comment.
6. English language posting only, please.
|
| |
|
|
|
|
|
ADefWebserver.com
DotNetNuke® Module Development Help Website
ADefWebserver.com
|
Get Smarter Mail, SmarterStats, SmarterTickets
Windows mail server, web log analytics, and customer service management software - Free Editions Available!
www.smartertools.com
|
DotNetNuke Modules, Skins, Training and Consulting
If you want DotNetNuke done right then look no further. Developed Solutions provides module development, skin design, user and developer training and consulting. Based in Adelaide, Australia, we offer our services worldwide.
www.developedsolutions.com.au
|
|
|
|