Member Avatar for samsundar

hi, iam having 2 listboxes.In one listbox i am populating products from Msacess.Then after clicking any of the product it should populate corresponding schedules in the other listbox.Below is my code Kindly reply me as soon as possible..

Dim sql As String
con = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Data Source=D:\Inetpub\wwwroot\WebApplication12\WRMdatabase.mdb")
sql = "select distinct scheduleid from die1 where productid =' " & ListBox1.SelectedItem.Value & " ' "
Response.Write(sql)
cmd = New OleDbCommand(sql, con)
con.Open()
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' ListBox2.DataValueField = "ScheduleId"
While reader.Read
ListBox2.Items.Add(reader(0))
End While
ListBox2.Items.Clear()
ListBox2.DataSource = reader
ListBox2.DataBind()
reader.Close()
  • 3 Contributors
  • 2 Replies
  • 98 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by Fenerbahce

Dani AI

Generated

Diagnosis and concise fixes for

Two root causes are visible in the posted code: the ListBox2 population logic and the SQL construction. As pointed out, a DataReader is forward-only and can only be read once; trying to read it, then clear items, then DataBind the same reader will fail. Also avoid building SQL with embedded quotes and stray spaces — use parameters instead (Access uses positional parameters, i.e. ?). For background on forward-only readers see retrieving data with a DataReader.

Two safe approaches

  1. Fill by iterating a DataReader (clear before adding and do not DataBind the reader):
Using cn As New OleDb.OleDbConnection(connectionString)
    Using cmd As New OleDb.OleDbCommand("SELECT DISTINCT scheduleid FROM die1 WHERE productid = ?", cn)
        cmd.Parameters.AddWithValue("?", ListBox1.SelectedValue)
        cn.Open()
        Using rdr As OleDb.OleDbDataReader = cmd.ExecuteReader()
            ListBox2.Items.Clear()
            While rdr.Read()
                ListBox2.Items.Add(rdr("scheduleid").ToString())
            End While
        End Using
    End Using
End Using
  1. Bind a DataTable (easier when you want DataTextField/DataValueField):
Using cn As New OleDb.OleDbConnection(connectionString)
    Using da As New OleDb.OleDbDataAdapter("SELECT DISTINCT scheduleid FROM die1 WHERE productid = ?", cn)
        da.SelectCommand.Parameters.AddWithValue("?", ListBox1.SelectedValue)
        Dim dt As New DataTable()
        da.Fill(dt)
        ListBox2.Items.Clear()
        ListBox2.DataSource = dt
        ListBox2.DataTextField = "scheduleid"
        ListBox2.DataValueField = "scheduleid"
        ListBox2.DataBind()
    End Using
End Using

Notes and troubleshooting

  • If this runs in WebForms, ensure ListBox1.AutoPostBack = true and use SelectedIndexChanged to trigger population.
  • Never call Clear() after you add items. Clear first, then populate.
  • Prefer parameterized queries to avoid quoting bugs and injection. See the OleDb adapter pattern for details: OleDbDataAdapter reference.
  • On 64-bit hosts the Jet provider may fail; use the ACE provider or compile to x86 if you hit provider errors.

Recommended Answers

All 2 Replies

Member Avatar for plazmo

Well you are using your datareader to write your items to the listbox. then you are clearing your items. After that you are trying to assign the values from the reader to the listbox with databinding. But that is not possible because you can only read through a datareader ONCE.
And I normally use a dataset for databinding controls, so im not sure if it can work the same way with a datareader.

But anyways, i can only assume your problem is that your items are not getting populated because you didnt post your problem.

Member Avatar for Fenerbahce

selam samsunlu,

did i understand right?

you have 2 listboxes. when you click on 1st listbox, that action will fill 2nd listbox with sql code to bring data into 2nd listbox.

yes ? no ?

thanks

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.