Member Avatar for Naters_uk

I am using an SQLDataAdapter to bind information from a database table to a listbox. However, the user have to select an item from a dropdown list box, DropdownMovie before the information for that particular movie is bind to the listbox.

No errors in the codings. But when i run it, i got this error. What does it means and how i can solve it??

Server Error in '/WebApplication5' Application.
--------------------------------------------------------------------------------

An SqlParameter with ParameterName '@title' is not contained by this SqlParameterCollection.

Conn.Open()
 Dim da As SqlDataAdapter = New SqlDataAdapter("Select SeatNum from Ticketing where MovieTitle = @title ", Conn)
        da.SelectCommand.Parameters("@title").Value = DropDownMovie.SelectedValue
        Dim ds As DataSet = New DataSet
        ' Fill DataSet with the data
        da.Fill(ds, "Ticketing")
        ' Set DataSource property of ListBox as DataSet’s DefaultView
        listbox_seatsbooked.DataSource = ds.Tables("Ticketing").DefaultView

        listbox_seatsbooked.SelectedIndex = 0
        ' Set Field Name you want to get data from
        listbox_seatsbooked.DataTextField = "SeatNum"
        ' Bind the data
        Page.DataBind()

        Conn.Close()
  • 2 Contributors
  • 10 Replies
  • 408 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by f1 fan

Dani AI

Generated

Short summary: the runtime error means a parameter value was being assigned for a parameter that never existed on the command, and the later SQL error was caused by invalid WHERE syntax (commas instead of logical operators). correctly pointed out the missing parameter and the need for a proper WHERE clause, and confirmed adding a parameter fixed the first error.

Key practical checks and tips:

  • Add each parameter to the command before assigning its Value, and make sure the parameter name (including the @) exactly matches the one in the SQL. Watch for simple typos in column names (e.g., MoveTime vs MovieTime).
  • Match parameter CLR types to the column types (use Date/DateTime/Time for date/time columns, integers for numeric IDs) and include an appropriate size for text types to avoid implicit conversions. Convert/validate DropDown.SelectedValue with TryParse before assigning to a parameter.
  • Use AND (or other logical operators) in the WHERE clause when multiple conditions must all be true. Commas there are a syntax error.
  • Set DataTextField (and any other display/value fields) before calling DataBind, and only set SelectedIndex after confirming the control has items. Prefer calling the listbox’s DataBind() instead of Page.DataBind() to avoid side effects.
  • Use Using blocks (or try/finally) for connections/commands so the connection is always closed if an exception occurs.

Example pattern (VB.NET) showing safe parameter use, parsing and binding:

Dim sql = "SELECT SeatNum FROM Ticketing WHERE MovieTitle=@m AND MovieDate=@d AND MovieTime=@t"
Using cn As New SqlConnection(connString)
  Using cmd As New SqlCommand(sql, cn)
    cmd.Parameters.Add(New SqlParameter("@m", SqlDbType.VarChar, 150)).Value = DropDownMovie.SelectedValue
    Dim d As DateTime
    If DateTime.TryParse(DropDown_Date.SelectedValue, d) Then
      cmd.Parameters.Add(New SqlParameter("@d", SqlDbType.Date)).Value = d
    End If
    ' parse time similarly, then fill a DataTable and bind:
    Using adapter As New SqlDataAdapter(cmd)
      Dim tbl As New DataTable()
      adapter.Fill(tbl)
      listbox_seatsbooked.DataSource = tbl
      listbox_seatsbooked.DataTextField = "SeatNum"
      listbox_seatsbooked.DataBind()
      If listbox_seatsbooked.Items.Count > 0 Then listbox_seatsbooked.SelectedIndex = 0
    End Using
  End Using
End Using

Final notes: prefer explicit parameter types/sizes over inferred methods (AddWithValue) to avoid type-mismatch and plan-for-performance issues. Moving the query into a stored procedure is a good next step for security and maintainability, as suggested.

Recommended Answers

All 10 Replies

Member Avatar for f1 fan

You didnt add the parameter to the da select command. You referenced it and set its value but it doesnt exist. In the line before you set the value you need to add a parameter with the name "@title". The command knows nothing about your sql query so you have to tell it everything including any parameters you want.

Member Avatar for Naters_uk

You didnt add the parameter to the da select command. You referenced it and set its value but it doesnt exist. In the line before you set the value you need to add a parameter with the name "@title". The command knows nothing about your sql query so you have to tell it everything including any parameters you want.

Hi, i do not quite get the part "In the line before you set the value you need to add a parameter with the name "@title".

Before this line, da.SelectCommand.Parameters("@title").Value = DropDownMovie.SelectedValue ? How do i add the parameter?

Da.SelectCommand.Parameters.Add("@title", SqlDbType.SmallInt) << is this what u mean?
Thanks :confused:

Member Avatar for f1 fan

try replacing that line with
da.SelectCommand.Parameters.AddWithValue("@title", DropDownMovie.SelectedValue)

it will add the parameter and then set the value. The other way would have been to call Parameters.Add and set the parameter and then use your other line to set the value, but the code above does both in one line so take advantage of it :)

Member Avatar for Naters_uk

Thanks, i resolved it :)

Member Avatar for Naters_uk

da.SelectCommand.Parameters.Add("@title", SqlDbType.VarChar) works for me, but da.SelectCommand.Parameters.AddWithValue("@title", DropDownMovie.SelectedValue) doesn't cuz VB.net behind codes for ASP.NET doesn't have .addwithvalue declaration, either way, u lead a way out for me. Thanks alot :).

Member Avatar for f1 fan

Sorry but i havent used VB.NET since 2002 and not sure what isnt in there compared to C#. Glad you worked it though.

Member Avatar for Naters_uk

Er now i am stuck with a new problem. I need to ensure that the seats retrieved into the listbox not only matched with the movie title but also with the date and time as well. I tried this

Dim da As SqlDataAdapter = New SqlDataAdapter("Select SeatNum from Ticketing where MovieTitle = @title, MovieDate=@date, MoveTime=@time", Conn)
da.SelectCommand.Parameters.Add("@title", SqlDbType.VarChar)
da.SelectCommand.Parameters.Add("@date", SqlDbType.VarChar)
da.SelectCommand.Parameters.Add("@time", SqlDbType.VarChar)

da.SelectCommand.Parameters("@title").Value = DropDownMovie.SelectedValue
da.SelectCommand.Parameters("@date").Value = DropDown_Date.SelectedValue
da.SelectCommand.Parameters("@time").Value = DropDown_Time.SelectedValue

'Dim ds As DataSet = New DataSet
' Fill DataSet with the data
da.Fill(ds, "Ticketing")

Now it says Line 1: Incorrect syntax near ','.
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.SqlClient.SqlException: Line 1: Incorrect syntax near ','.

Source Error:


Line 361:
Line 362: ' Fill DataSet with the data
Line 363: da.Fill(ds, "Ticketing")


What's the problem??

Member Avatar for f1 fan

your query is wrong, it isnt comma delimited you need a where clause made of logical operators. In your case you want all the rows WHERE the movietitle = @title AND moviedate = @date AND movietime = @time. You could have used ORs, or < or > or <> etc but in your particular case you wanted the rows that met all 3 requirments hence the ANDs. Follow me?

Member Avatar for Naters_uk

Once again, thanks f1. I got it. Since i am putting in 3 constraints and it has to meet those 3 constraints hence use AND

Member Avatar for f1 fan

Youre welcome and yes you have it. I would still advise that if you get time you move it over to stored procedures. They save on performance and security etc.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.