Sql Data Reader Scalar Variable @username Not Working

[joshbula]

Hi, please help a newbie...

Visual Basic 2008 web application, this is the Login page, and I'm trying to lookup the schoolID and schoolName for the user based on their username. (from the Asp.Net MembershipUser thing)

When I change the SELECT statement to put an actual known username instead of the @username variable, everything works fine, but when I try to put that same known user in the variable, I get the "Must declare the scalar variable "@username" error.

I'm also experiencing problems with the MembershipUser type, not being able to convert it to a String. So, if any of you have a better suggestion as how to accomplish this, please point me in the right directions.

My ultimate goal is to be able to set session variables with the users' schoolID and schoolName when they log in. If there is an easier way to do that, please advise.

Here's the code to my Login.aspx.vb code-behind:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Security


Partial Class Login
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then
            If Request.IsAuthenticated AndAlso Not String.IsNullOrEmpty(Request.QueryString("ReturnUrl")) Then
                Response.Write("You Are Not Authorized to View That Page!  Login Below or hit your Browser's Back Button")
            End If

        End If
    End Sub

    Protected Sub Login1_LoggedIn(ByVal sender As Object, ByVal e As System.EventArgs) Handles Login1.LoggedIn
        GetSchoolID()
 

    End Sub


    Private Sub GetSchoolID()
        Dim schoolid As String
        Dim schoolname As String
        'Dim username As MembershipUser = Membership.GetUser
        Dim username As String = "testuser8"
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("FBA-MPA2ConnectionString").ConnectionString
        Dim selectSql As String = "SELECT DISTINCT s.txtschoolid, s.txtschoolname FROM mpa2_DirectorInfo AS d INNER JOIN aspnet_Users AS u ON d.UserID = u.UserId INNER JOIN tblschool AS s ON d.School = s.txtschoolid WHERE (u.UserName = @username)"

        'Try
        Using MyConnection As New SqlConnection(connectionString)
            MyConnection.Open()
            Dim myCommand As New SqlCommand(selectSql, MyConnection)
            Dim datareader As SqlDataReader
            datareader = myCommand.ExecuteReader()

            datareader.Read()
            myCommand.Parameters.Add("@username", System.Data.SqlDbType.NVarChar)
            myCommand.Parameters("@username").Value = username
            schoolid = datareader("txtschoolid").ToString()
            schoolname = datareader("txtschoolname").ToString()

            'just testing the dataReader here...
            Response.Write(schoolid)
            Response.Write(schoolname)
            Response.End()

            datareader.Close()
            MyConnection.Close()

            myCommand.ExecuteNonQuery()
            MyConnection.Close()



        End Using


        'Catch x As Exception

        'End Try
    End Sub

End Class

 

[RiverGuy]

You need to add the parameters before you execute the command.

 

[joshbula]

Thank you... I can't believe I didn't catch that.