[RESOLVED] Loading from a database - Too slow

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:

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

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 :ehh:

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

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

Do you want to load a column database to combox

if so , i can help you.

:wave:

Exactly :D
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

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

Code:

---

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"

---

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

knxrb

Ok, that works fine thanks and it's fast :)

How would I add the AutoCompleteSource to 'cboProductCode'?

Here's my finished code:

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

Try this

vb.net Code:

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

WOW! Thanks Hack, it works perfectly :D

knxrb

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....

Thanks :)

knxrb