[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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
0
NCSO
Asked:
NCSO
  • 3
1 Solution
 
RJeyaPrakashCommented:
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
0
 
NCSOAuthor Commented:
Hai,

I am fairly new to vb, could you give me an example of using a datareader rather than the execute scalar?  Thanx
0
 
RJeyaPrakashCommented:
hai

first u combine yr query like this

 Dim cmd As New System.Data.SqlClient.SqlCommand("Select offense_number, offensedesc from tblOffense where incident_ID='" & IncidentPeopleAdd.Incident_ID.Text & "' and ARN='" & IncidentPeopleAdd.ARN.Text & "'", conn2)

 conn2.Open()

Dim myReader As SqlDataReader = cmd .ExecuteReader()

  Do While myReader.Read()
      ListBox1.Items.Add(myReader.Item("offense_number") & "- " & myReader.Item("offensedesc") )
  Loop


myReader.Close()
 conn2.Close()

You should always call the Close method when you have finished using the DataReader object.



regards
jp


0
 
RJeyaPrakashCommented:
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
0
 
idforCommented:
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:
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now