none
Not Sure How to Define Parameterize Query RRS feed

  • Question

  • Must declare the scalar variable "@User_ID".

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.OleDb.OleDbException: Must declare the scalar variable "@User_ID".

    Source Error:

    Line 43:         cmdMbrs.Parameters.AddWithValue("@User_ID", User_ID)
    Line 44: 
    Line 45:         rdrMbrs = cmdMbrs.ExecuteReader
    Line 46:         gridMbrs.DataSource = rdrMbrs
    Line 47:         gridMbrs.DataBind()


     

    Here is my code:

     

    Session("User_ID") = Request.QueryString("User_ID")

    Session("Subscription_exp") = Request.QueryString("Subscription_exp")

    Label1.Text = Session("User_ID")

    Dim User_ID As String

    User_ID = Session("User_ID")

     

    Dim conClasf As OleDbConnection

    Dim cmdClasf As New OleDbCommand

    Dim strClasf As String

    strClasf = "removed for security reasons"

    conClasf = New OleDbConnection(strClasf)

    conClasf.Open()

    Dim cmdMbrs As OleDbCommand

    Dim rdrMbrs As OleDbDataReader

    cmdMbrs = New OleDbCommand("SELECT DISTINCT [CustomerID], [Password] FROM [Customer] WHERE ([CustomerID] = @User_ID)", conClasf)

    cmdMbrs.Parameters.AddWithValue("@User_ID", User_ID)

    rdrMbrs = cmdMbrs.ExecuteReader

    gridMbrs.DataSource = rdrMbrs

    gridMbrs.DataBind()

    rdrMbrs.Close()

    cmdMbrs.Dispose()

    conClasf.Close()

     

    I'm new to ASP.NET and I've spent the last couple days reading ADO.NET examples but I cannot figure this out.  I'm simply taking a User_ID from the querystring, saving it as a session variable and trying to read from the datase some data about the user with the User_ID.

     

    Any help would be appreciated...

     

    Thanks

    Saturday, August 18, 2007 5:38 AM

Answers

  • I believe you cannot use named parameters with OleDbProvider. You need to replace @User_ID in your query with question mark, like

     

    SELECT DISTINCT ....... WHERE CustomerID=?

     

    Everything else in code should be the same

    Saturday, August 18, 2007 1:23 PM
    Moderator

All replies

  • I believe you cannot use named parameters with OleDbProvider. You need to replace @User_ID in your query with question mark, like

     

    SELECT DISTINCT ....... WHERE CustomerID=?

     

    Everything else in code should be the same

    Saturday, August 18, 2007 1:23 PM
    Moderator
  • Thanks - that was exactly the problem.  That and I had two OleDbCommand statements and only needed one.

     

    But this raises another question for me:

    I'm currently passing one parameter into the SQL Statement.  However, what if I wanted to pass multiple parameters into the SQL Statement.  Do you still use a "?" to reference the parameter?  If so how does the dotnet runtime know which parameter value goes with which "?" in the SQL Statament?

     

    Thanks,

    Alan

    Saturday, August 18, 2007 5:37 PM
  • You have to add the parameters to the collection to match the order that the question marks appear in the query.  Yes, that's fragile.

     

    Saturday, August 18, 2007 6:20 PM
  • It is inconvenient, but you have to add parameters to your collection in exact same order as your parameters in a query. You should pay attention to it. Unfortunately this is how OLEDB was designed.

     

    Sunday, August 19, 2007 11:38 AM
    Moderator