Link to home
Start Free TrialLog in
Avatar of NCSO
NCSO

asked on

Populating a ListBox from a database

Hi Experts,

I have a need to fill a listbox with information from a database.  I have been somewhat successful thus far but I am only getting the first record that matches the sql command.  What am I missing.  I need the listbox to be populated with every record that matches the sql command.  Your assistance is greatly appreciated.

    Private Sub SuspectArrestorEscapeeADD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn2 As New System.Data.SqlClient.SqlConnection("Persist Security Info=True;Server=xxx.xxx.xxx.xxx;Database=Reporting;uid=uid;network=dbmssocn;password=password")
        Dim da2 As New System.Data.SqlClient.SqlDataAdapter("Select * from tblOffense", conn2)
        Dim ds2 As New System.Data.DataSet
        Dim cmd As New System.Data.SqlClient.SqlCommand("Select (offense_number) from tblOffense where incident_ID='" & IncidentPeopleAdd.Incident_ID.Text & "' and ARN='" & IncidentPeopleAdd.ARN.Text & "'", conn2)
        Dim cmd3 As New System.Data.SqlClient.SqlCommand("Select (offensedesc) from tblOffense where incident_ID='" & IncidentPeopleAdd.Incident_ID.Text & "' and ARN='" & IncidentPeopleAdd.ARN.Text & "'", conn2)


        Try
            conn2.Open()
            ListBox1.Items.Add(cmd.ExecuteScalar() & " - " & cmd3.ExecuteScalar()(1).ToString)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn2.Close()
        End Try

    End Sub
Avatar of RJeyaPrakash
RJeyaPrakash
hai

instead of using execute scalar, use Datareader. and loop thru in it and add values from reader to ur list box.. execute scalar Executes the query, and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored..

jp
Avatar of NCSO
NCSO

ASKER

Hai,

I am fairly new to vb, could you give me an example of using a datareader rather than the execute scalar?  Thanx
ASKER CERTIFIED SOLUTION
Avatar of RJeyaPrakash
RJeyaPrakash
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RJeyaPrakash
RJeyaPrakash
hai

r u can fill dataset and bind it to list box


Dim d As SqlDataReader
        Dim ds As New DataSet
        Dim cmd As New SqlCommand
        Dim sqldatap As New SqlDataAdapter
       dim SqlConnection1 as new sqlconnection
        cmd.CommandText = "select <col1>, <col2>  from <tablename> where <condition>"
SqlConnection1.connectionstrin =  "<yr connection string>"
        cmd.Connection= sqlconnection1
        sqldatap.SelectCommand = cmd
        SqlConnection1.Open()

       
        sqldatap.Fill(ds)

        ListBox1.DataSource = ds.Tables(0)
        ListBox1.DisplayMember = "<display name>"
        ListBox1.ValueMember = "<primary key>"


        SqlConnection1.Close()

regards
jp
Avatar of idfor
idfor
Hey i tried the above code and got this error,

System.InvalidOperationException was unhandled
  Message="An error occurred creating the form. See Exception.InnerException for details.  The error is: Object reference not set to an instance of an object."
  Source="ListBoxTest"
  StackTrace:
       at ListBoxTest.My.MyProject.MyForms.Create__Instance__[T](T Instance) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 190
       at ListBoxTest.My.MyProject.MyForms.get_Form1()
       at ListBoxTest.My.MyApplication.OnCreateMainForm() in C:\Documents and Settings\Deepesh Admin\Local Settings\Application Data\Temporary Projects\ListBoxTest\My Project\Application.Designer.vb:line 35
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at ListBoxTest.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.NullReferenceException
       Message="Object reference not set to an instance of an object."
       Source="ListBoxTest"
       StackTrace:
            at ListBoxTest.Form1..ctor() in C:\Documents and Settings\Deepesh Admin\Local Settings\Application Data\Temporary Projects\ListBoxTest\Form1.vb:line 3
       InnerException: