[RESOLVED] Loading from a database - Too slow

[knxrb]

Hi guys, I've got a database that's 13,000 lines long and I'm trying to load this into a string array when the program loads but it takes approx 5-6 minutes for it to load it.

I've got a MS Access database or an Excel file that I can load this information from, each has two rows: SVONo, Description

This is my current MS Access code:

        Try
            cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\products.mdb" & ";")
            cn.Open()
            cmd = New OleDbCommand("SELECT * FROM tblStock ORDER BY SVONO ASC", cn)
            dr = cmd.ExecuteReader
            Dim i As Integer = 0
            While dr.Read()
                strProducts(i) = dr(1)
                strDescriptions(i) = dr(2)
                maxProducts += 1
                i += 1
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        dr.Close()
        cn.Close()

Does anyone have any idea of how to make it faster?
Or does anyone have a better way?

I did have the idea of loading it by letting the user select the first letter and then only loading the ones that begin with that letter but I've got 4000-5000 'S' ones and it still takes about 2-3 minutes to load it

All I want is to load the data from the database or the excel file and then put the products into 'cboProducts' and the 'cboProducts AutoCompleSourceList' and the descriptions into the 'txtDescriptions' AutoCompleteSourceList and a string array.
Then when the user selects a product using 'cboProducts' the description will automatically be put into 'txtDescription'.

Thanks in advance, knxrb

[manhit45]

Dear

i have finished many solution with access and excel database

but never spend 5 - 6 miniutes to load them

why dont you use datagridview,dataview, listbox or another control to load them into.

you can use displaymember properties for listbox or datagridview . let try and tell us your trouble . Dear

[knxrb]

I've explained what my problem is and what I want it to do in my first post?

It has to load into a combobox as this needs to be as simple to use as possible, so selecting from the combobox is easier than selecting from a datagridview, and the people this is intended for will know what to do with a combobox and it's unlikely they will know what to do with a datagridview.

knxrb

[su ki]

hey knxrb
why you are loading database in string array

you may use datatable for storing data or if you want array sort of thing use list of string it will be faster

or you may use displaymember and valuemember for directly load from database as @manhit45 also suggested

[manhit45]

Do you want to load a column database to combox

if so , i can help you.

[knxrb]

Exactly
The first column needs to go into a combobox and also into the combobox AutoCompleteSource but it needs to be fast as well if that's possible

knxrb

[manhit45]

OK
Very easy , i am using sql, you only instead sql into OLE

Dim objDataAdapterPT As New SqlDataAdapter("SELECT * from Kho_PT ", objConnection)
Dim PTdataview as new dataview
Dim PT dataset as new dataset

        objDataAdapterPT.Fill(PTdataset, "PT")

         PTdataview = New DataView(PTdataset.Tables("PT"))

       combox1.DataSource = PTdataview
        combox1.DisplayMember = "STT"

[knxrb]

Ah, thank you, I'll try that now
I didn't realise that the DisplayMember bit existed :P

knxrb

[knxrb]

Ok, that works fine thanks and it's fast

How would I add the AutoCompleteSource to 'cboProductCode'?

Here's my finished code:

        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strProductsDatabase & ";;")
        Dim objDataAdapterPT As New OleDbDataAdapter("SELECT * FROM tblStock ", cn)
        cn.Open()
        Dim PTdataview As New DataView
        Dim PTdataset As New DataSet
        objDataAdapterPT.Fill(PTdataset, "tblStock")
        PTdataview = New DataView(PTdataset.Tables("tblStock"))
        AddData.cboProductCode.DataSource = PTdataview
        AddData.cboProductCode.DisplayMember = "SVONo"
        AddData.cboDescription.DataSource = PTdataview
        AddData.cboDescription.DisplayMember = "Description"
        cn.Close()

knxrb

[Hack]

Try this

Public Class Form1
    Private Sub AutoCompleteCombo(ByVal MyCombo As ComboBox, ByVal KeyCodes As KeyEventArgs)
        Dim strSearchText As String
        Dim intIndex As Integer
        Dim objFound As Object
        Dim strFound As String
        Dim strAddText As String
 
        Select Case KeyCodes.KeyCode
            Case Keys.Back, Keys.Left, Keys.Right, Keys.Up, Keys.Delete, Keys.Down
                Return
        End Select
 
        strSearchText = MyCombo.Text
        intIndex = MyCombo.FindString(strSearchText)
 
        If intIndex >= 0 Then
            objFound = MyCombo.Items(intIndex)
            strFound = MyCombo.GetItemText(objFound)
            strAddText = strFound.Substring(strSearchText.Length)
            MyCombo.Text = strSearchText & strAddText
            MyCombo.SelectionStart = strSearchText.Length
            MyCombo.SelectionLength = strAddText.Length
        End If
 
    End Sub
 
    Private Sub cboProductCode_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles cboProductCode.KeyUp
        AutoCompleteCombo(cboProductCode, e)
    End Sub
End Class

[knxrb]

WOW! Thanks Hack, it works perfectly

knxrb

[Bruce Fox]

As for obtaining the data, you may be able to gain a performance increase if you adopt one of these options.
Note, they are structured for SQL, just re do as OleDb. My guess is that you can get away from a DataSet per se, and just use a DataTable, or, just a DataReader....

[knxrb]

Thanks

knxrb