urgent help needed - datareader

[markhall]

Hi, can anyone tell me where I'm going wrong - I'm trying to populate a Listbox with the field "sedol" from my Access database. Here is the code:-

Public Class Form4
Inherits System.Windows.Forms.Form


#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents ListBox1 As System.Windows.Forms.ListBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.TextBox1 = New System.Windows.Forms.TextBox()
Me.ListBox1 = New System.Windows.Forms.ListBox()
Me.SuspendLayout()
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point(40, 24)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(192, 20)
Me.TextBox1.TabIndex = 1
Me.TextBox1.Text = ""
'
'ListBox1
'
Me.ListBox1.Location = New System.Drawing.Point(40, 80)
Me.ListBox1.Name = "ListBox1"
Me.ListBox1.Size = New System.Drawing.Size(176, 173)
Me.ListBox1.TabIndex = 2
'
'Form4
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 273)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.ListBox1, Me.TextBox1})
Me.Name = "Form4"
Me.Text = "Form4"
Me.ResumeLayout(False)

End Sub

#End Region
Public dbcConnection As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = n:\ca team\impart\mhdb.MDB;"

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
'refresh the list box based on the current search filter

Dim sSql As String
Dim cn As New OleDb.OleDbConnection(dbcConnection)
cn.Open()

'set sql string to reflect search criteria

sSql = "SEDOL, FROM Prices_table WHERE Sedol LIKE '%"

'open the ADO.NET Datareader
Dim cmd As New OleDb.OleDbCommand(sSql, cn)
Dim drd As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)




Do While drd.Read
ListBox1.Items.Add(drd!Sedol)
Loop
drd.Close()

End Sub

End Class


My Access Database is called mhdb.mdb and the table is called prices_table with fields Sedol, Name, Narrative, Bid price, Offer Price, Currency.

Thanks - I've been stuck on this for ages.

[DevGrp]

Try this

ListBox1.Items.Add(drd("sedol").ToString()

[markhall]

Hi, I just tried but the listbox still remains blank.

Thanks

Mark.

[Pirate]

sSql = "SEDOL, FROM Prices_table WHERE Sedol LIKE '%"

Shouldn't this be :
sSql = "SELECT FROM Prices_table WHERE Sedol LIKE '%"

[markhall]

Hi, I changed that and also changed the event type to Load but still no joy with populating the list box.

Private Sub ListBox1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
'refresh the list box based on the current search filter

Dim sSql As String
Dim cn As New OleDb.OleDbConnection(dbcConnection)
cn.Open()

'set sql string to reflect search criteria

sSql = "SELECT FROM Prices_table WHERE Sedol LIKE '%"

'open the ADO.NET Datareader
Dim cmd As New OleDb.OleDbCommand(sSql, cn)
Dim drd As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)




Do While drd.Read
ListBox1.Items.Add(drd("sedol"))
Loop
drd.Close()

End Sub

[Pirate]

What about this one (without wildcard) .

sSql = "SELECT FROM Prices_table"

[Iamthewalrus15]

Does any of this throw an exception?

[Pirate]

Originally posted by Pirate
What about this one (without wildcard) .
sSql = "SELECT FROM Prices_table"

Sorry , I meant this one :
sSql = "SELECT * FROM Prices_table"

[markhall]

Hi, still nothing coming through - there are no exception errors, just a blank listbox.

[Pirate]

Why don't you try the other way which is filling a dataset with dataAdapter . I see this is the best way since it works in disconnected mode .

[Pirate]

OK , I've written this little routine that shows you how to do the other way (dataset + dataAdapter) : Just pass the two required paramters , they are self-explanatory I guess .

Public Shared Sub PopulateComb(ByVal Combox As ComboBox, ByVal TableStr As String)
Dim SQLStr = "Select * from " & TableStr
Dim MyDataSet As New DataSet 
Dim myadapter As New OleDbDataAdapter(SQLStr, MyConnection)
myadapter.Fill(MyDataSet, TableStr)
 
Dim flds(MyDataSet.Tables(0).Columns.Count - 1) As DataColumn
MyDataSet.Tables(0).Columns.CopyTo(flds, 0)
if Combox.Items.Count = 0 Then
Combox.Items.AddRange(flds)
Else
Exit Sub
End If
myadapter = Nothing
 End If
 End Sub

[markhall]

Hi, many thanks for that - I can fill the combobox that way but I was actually looking for a method of importing each of the records to manipulate the data then eventually save all the data in a new format to a text file ie Line1 of the text file would be:-

Sedol field,"F13",BidPrice field,"00000",OfferPrice field

and so on.

Can I still do this with the dataset method?

Thanks.

[Pirate]

Your Welcome !

Do you mean you want to populate the combobox with all the datarows in the database ??

[markhall]

Yes please - I'm not too worried about getting the data on the screen - I was just trying to get the data in a list/combo box initially to check that I was pulling the data through.

What I want to be able to do is for each row pull through the records and add a bit of text/numbers then output to a text file.

This is to import the data from my database into our accounting system - the database is a list of regular manual price changes but before it can be imported - extra characters need to go in front of some of the records.

Sorry if it's a bit long winded but this is the first vb program I've done with databases.

Many thanks for your help.

[Pirate]

Try this now ,thought I've not run it or tested it yet , it seems to work .

Public Shared Sub PopulateCombWithRows(ByVal Combox As ComboBox, ByVal TableStr As String)
        Dim i As Integer
        Dim SQLStr = "Select * from " & TableStr
        Dim MyDataSet As New DataSet
        Dim myadapter As New OleDbDataAdapter(SQLStr, MyConnection)
        myadapter.Fill(MyDataSet, TableStr)
 
        For i = 0 To MyDataSet.Tables(0).Rows.Count - 1
            Combox.Items.Add(MyDataSet.Tables(0).Rows(i))
        Next
        myadapter = Nothing
 
    End Sub

[Pirate]

and for exporting combobox/listbox content to text file , you can have a look at the demo I posted here . http://www.vbforums.com/showthread.p...highlight=text