SqlDataAdapter bind to a listbox through clicking a dropdownbox

[natersuk]

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

 

[Bob Langlade]

You need to add a parameter to your adapter parameter collection before using it.

I have tried this code in my Data Base:

[SIZE=2][COLOR=#0000ff]
Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient

[/SIZE][SIZE=2][COLOR=#0000ff][INDENT]Public[/INDENT][/COLOR][/SIZE][INDENT][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] SQLDropDown
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] Conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlConnection
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] Ds [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] Da [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataAdapter
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] ConnectionString() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String

[/COLOR][/SIZE][INDENT][SIZE=2][COLOR=#0000ff]Return[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2].Format([/SIZE][SIZE=2][COLOR=#800000]"integrated security=SSPI;data source={0};initial catalog={1}"[/COLOR][/SIZE][SIZE=2], _
[/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.Serveur, [/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.BaseDonnée)
[/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function

[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] SQLDropDown_Load([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Load[INDENT]Conn = [/INDENT][/SIZE][INDENT][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection(ConnectionString)
Da = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter([/SIZE][SIZE=2][COLOR=#800000]"Select ct_intitule from f_comptet where ct_type like @type order by ct_intitule"[/COLOR][/SIZE][SIZE=2], Conn)
Ds = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub

[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] DropDownMovie_SelectedValueChanged([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] DropDownMovie.SelectedValueChanged[/SIZE][INDENT][SIZE=2][COLOR=#0000ff]Try

[/COLOR][/SIZE][SIZE=2][INDENT]Conn.Open()

[/INDENT][/SIZE][INDENT][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] Ds.Tables([/SIZE][SIZE=2][COLOR=#800000]"Tiers"[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]IsNot[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then

[/COLOR][/SIZE][INDENT][SIZE=2][COLOR=#008000]'Table exists, clear previous entries
[/COLOR][/SIZE][SIZE=2]Ds.Tables([/SIZE][SIZE=2][COLOR=#800000]"Tiers"[/COLOR][/SIZE][SIZE=2]).Clear()
Da.SelectCommand.Parameters([/SIZE][SIZE=2][COLOR=#800000]"@Type"[/COLOR][/SIZE][SIZE=2]).Value = DropDownMovie.SelectedItem
Da.Fill(Ds, [/SIZE][SIZE=2][COLOR=#800000]"Tiers"[/COLOR][/SIZE][SIZE=2])
[/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]Else

[/COLOR][/SIZE][INDENT][SIZE=2][COLOR=#008000]'Table not existing, add parameter and bind data
[/COLOR][/SIZE][SIZE=2]Da.SelectCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@Type"[/COLOR][/SIZE][SIZE=2], SqlDbType.SmallInt)
Da.SelectCommand.Parameters([/SIZE][SIZE=2][COLOR=#800000]"@Type"[/COLOR][/SIZE][SIZE=2]).Value = DropDownMovie.SelectedItem
Da.Fill(Ds, [/SIZE][SIZE=2][COLOR=#800000]"Tiers"[/COLOR][/SIZE][SIZE=2])
ListBox1.DataSource = Ds.Tables([/SIZE][SIZE=2][COLOR=#800000]"Tiers"[/COLOR][/SIZE][SIZE=2])
ListBox1.DisplayMember = [/SIZE][SIZE=2][COLOR=#800000]"ct_intitule"
[/COLOR][/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]Conn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MessageBox.Show(ex.Message)
[/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class
[/COLOR][/SIZE]

 

[natersuk]

Hi i do not quite get the this part. From what i see, Tiers is a table, however from your select query, you are selecting the information from f_compter table. So which is which? Also i can't get this line right in the coding, IsNotNothing is not working in the coding. Thank you.
If Ds.Tables("Tiers") IsNotNothingThen

'Table exists, clear previous entries
Ds.Tables("Tiers").Clear()
Da.SelectCommand.Parameters("@Type").Value = DropDownMovie.SelectedItem
Da.Fill(Ds, "Tiers")
​

 

[natersuk]

Thanks i resolved it!