Products

Evoq Content Overview Content Creation Workflow Asset Management Mobile Responsive Personalization Content Analytics SEO Integrations Security Website PerformanceEvoq Engage Overview Community Management  Dashboard  Analytics  Member Profile Gamification Advocate Marketing Community Engagement  Ideas  Answers  Discussions  Groups  Wikis  Events Mobile ReadyDNN Support PackagesEvoq: CMS FeaturesEvoq OnDemandCompare DNN's CMS Products

Solutions

Content Management SystemMulti-Channel PublishingDigital MarketingOnline Community ManagementIntranet Software SolutionOur CustomersPrime

Resources

Test DrivesSchedule A DemoDocumentation CenterWebinarsWhite PapersProduct ManualsRequest PricingData SheetsCase StudiesEvoq Preferred Products Content Management Ecommerce Forms Identity Management and Authentication Site Management Social Themes

Partners

DNN Partners Partner Directory Become a DNN Partner Partner Program Overview  Hosting  Implementation  ISV  Training  Competencies

Community

Participate Ask a Question Forums Community Showcase DNN MVP  Program Overview  Lifetime MVPs  Honorary MVPs Subscribe to DNN Digest Advisory Groups  Awareness Advisory Group  Developer Advisory Group  Partner Advisory Group  Technology Advisory GroupLearn Documentation Wiki Community Blog Video Library Project History Development RoadmapDownload DNN Store Language Packs Manuals Nightly BuildsSecurity Policies Security Center

Blog

About

News Press Releases News Coverage Press KitCareersContact DNN

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportsPulling Data based on UserIDPulling Data based on UserID
Previous
 
Next
New Post
4/30/2007 2:02 PM
 

Joined: 1/1/0001
Posts: 0

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
 
New Post
4/30/2007 3:05 PM
 
Andrew Nurse

Joined: 4/12/2004
Posts: 433

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
 
New Post
5/1/2007 9:02 AM
 

Joined: 1/1/0001
Posts: 0

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
 
New Post
8/15/2007 3:02 PM
 
Lynne

Joined: 1/9/2007
Posts: 44

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.

 
 
New Post
8/15/2007 4:43 PM
 
Andrew Nurse

Joined: 4/12/2004
Posts: 433

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
 
 Page 1 of 2
12Next 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportsPulling Data based on UserIDPulling Data based on UserID


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, 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 DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out