SqlDataAdapter bind to a listbox

[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()

[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.

[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:

[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 :)

[Naters_uk]

Thanks, i resolved it :)

[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 :).

[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.

[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??

[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?

[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

[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.