• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

What is wrong with this code?

I have this code:

        Dim oConnection As New Data.SqlClient.SqlConnection("Data Source=****;User Id=****;Password=****;Initial Catalog=****;")
        oConnection.Open()
        'First, get userID from HTTPCONTEXT.
        Dim UserName As String
        UserName = HttpContext.Current.User.Identity.Name()
        Dim users As New Users
        Dim UserID As String
        UserID = users.GetUserID(UserName)
        Me.Label1.Text = UserID
        Dim strSQL As String
        strSQL = "SELECT ApplicationID, ApplicationName FROM KY_Applications WHERE UserID=@UserID"
        Dim cmd As New SqlClient.SqlCommand
        cmd.CommandText = strSQL
        cmd.Parameters.Add("@UserID", UserID)
        Dim da As New SqlClient.SqlDataAdapter
        da.SelectCommand = cmd
        Dim ds As New DataSet
        da.Fill(ds)
        Me.ddlApplications.DataSource = ds.Tables(0)
        Me.ddlApplications.DataBind()

I am trying to databind a list of applications that a user has access to onto the drop-down list on my .aspx page. I want to just have the application names from KY_Applications where the USERID matches the UserName that the user logged in with. The code to get the current user's userID is below:

        Dim oConnection As New Data.SqlClient.SqlConnection("Data Source=****;User Id=****;Password=****;Initial Catalog=*****;")
        oConnection.Open()

        Dim strSQL As String
        strSQL = "SELECT UserID FROM KY_Users WHERE UserName = @UserName"

        Dim oCommand As New Data.SqlClient.SqlCommand(strSQL, oConnection)
        oCommand.Parameters.Add("@UserName", UserName)
        Dim UserID As String
        UserID = CStr(oCommand.ExecuteScalar())
        Return UserID
        oConnection.Close()
        oConnection.Dispose()
        oCommand.Dispose()

What is wrong with either of these code snippets, because I keep getting an error (and I can't tell the exact error because It won't let me see error messages on a remote server).

Any help would be appreciated. Thanks!
0
itprochris
Asked:
itprochris
  • 2
1 Solution
 
NetworkArchitekCommented:
Well for instance, this won't work:

strSQL = "SELECT ApplicationID, ApplicationName FROM KY_Applications WHERE UserID=@UserID"

You don't use commandtext AND a stored procedure, so if you have a stored procedure the commandtext is the name of the procedure. But if you want to use that SQL you need to change that to:

strSQL = "SELECT ApplicationID, ApplicationName FROM KY_Applications WHERE UserID= " & "'" & UserID & "'"

Before you were just passing "@UserID" with no correlation to the variable named UserID.

0
 
itprochrisAuthor Commented:
Does my function to get the user's ID based on the current user that is connected look okay? I have no way of debugging because I am not hosting locally.
0
 
Diane258Commented:
is there anyway you can get to the host?

or have someone tell you the error that the host is generating?
0
 
itprochrisAuthor Commented:
I found what I needed, but I'll still give you the points if you want:-D
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now