Slow Searching
hi to all programers
i am facing one prob the prob is i am working on master child form lot of data in master table n lot of in child the database is informix n server is unix base i am using three methods for data search in the table but when they retrive the data it take much time not in efficient way for data base connectivity i am using ODBC n methods r
Sub Mget_value()
Dim QRY As String
On Error GoTo Errtracing
Set mCrdt_Editable = New ADODB.Recordset
QRY = "select * from b_inv where inv_no like '%" & Trim(Me.txt_Invno.Text) & "%' order by 1"
mCrdt_Editable.Open QRY, Gcon, adOpenForwardOnly, adLockReadOnly
If mCrdt_Editable.EOF = False Then
'MsgBox mCrdt_Editable.RecordCount
Call Display
'Call populate_grid
Picture4.Enabled = True
Call NavigationScheme(1) ' you can move in forward direction
Else
Call NavigationScheme(1) ' cant move
Call ButtScheme(2)
Call ClearText
MsgBox ("No Record Found !")
End If
Exit Sub
Errtracing:
MsgBox Err.number & " | " & Err.Description
End Sub
-----------------------------------------
Sub Display()
If mCrdt_Editable.EOF = False And mCrdt_Editable.BOF = False Then
Me.txt_Invno = IIf(IsNull(Trim(mCrdt_Editable!inv_no)) = False, Trim(mCrdt_Editable!inv_no), "")
Me.txt_date.Text = IIf(IsNull(Trim(mCrdt_Editable!inv_date)) = False, Trim(Format(mCrdt_Editable!inv_date, "d-mm-yyyy")), "")
Me.txt_ptcode.Text = IIf(IsNull(Trim(mCrdt_Editable!pt_code)) = False, Trim(mCrdt_Editable!pt_code), "")
Me.txt_LCno.Text = IIf(IsNull(Trim(mCrdt_Editable!lc_number)) = False, Trim(mCrdt_Editable!lc_number), "")
Me.txt_date1.Text = IIf(IsNull(Trim(mCrdt_Editable!lc_date)) = False, Trim(Format(mCrdt_Editable!lc_date, "d-mm-yyyy")), "")
Me.txt_Marks.Text = IIf(IsNull(Trim(mCrdt_Editable!n_mark)) = False, Trim(mCrdt_Editable!n_mark), "")
Me.txt_Pkgs.Text = IIf(IsNull(Trim(mCrdt_Editable!n_pkg)) = False, Trim(mCrdt_Editable!n_pkg), "")
Me.txt_FrmEno.Text = IIf(IsNull(Trim(mCrdt_Editable!e_number)) = False, Trim(mCrdt_Editable!e_number), "")
Me.txt_Bankcode.Text = IIf(IsNull(Trim(mCrdt_Editable!bank_code)) = False, Trim(mCrdt_Editable!bank_code), "")
' 'Me.txt_Excise.Text = IIf(IsNull(Trim(mCrdt_Editable!r_ex_dty)) = False, Trim(Format(mCrdt_Editable!r_ex_dty, "#0.00")), "#0.00")
Me.txt_AWBno.Text = IIf(IsNull(Trim(mCrdt_Editable!doc_no)) = False, Trim(mCrdt_Editable!doc_no), "")
Me.txt_Carrier.Text = IIf(IsNull(Trim(mCrdt_Editable!carrier)) = False, Trim(mCrdt_Editable!carrier), "")
Me.txt_ContBasis.Text = IIf(IsNull(Trim(mCrdt_Editable!cont_basis)) = False, Trim(mCrdt_Editable!cont_basis), "")
Me.txt_Port.Text = IIf(IsNull(Trim(mCrdt_Editable!p_loading)) = False, Trim(mCrdt_Editable!p_loading), "")
Me.txt_To.Text = IIf(IsNull(Trim(mCrdt_Editable!p_discharge)) = False, Trim(mCrdt_Editable!p_discharge), "")
Me.txt_Cons1.Text = IIf(IsNull(Trim(mCrdt_Editable!Destination)) = False, Trim(mCrdt_Editable!Destination), "")
Me.txt_Cons2.Text = IIf(IsNull(Trim(mCrdt_Editable!address1)) = False, Trim(mCrdt_Editable!address1), "")
Me.txt_Cons3.Text = IIf(IsNull(Trim(mCrdt_Editable!address2)) = False, Trim(mCrdt_Editable!address2), "")
frm_menus.mainProgBar.Value = (mCrdt_Editable.AbsolutePosition / mCrdt_Editable.RecordCount) * 100
lbl_ttlrecord.Caption = "Records " & mCrdt_Editable.AbsolutePosition & " / " & mCrdt_Editable.RecordCount & ""
End If
Call populate_grid
End Sub
----------------------------
Sub populate_grid()
Dim rws As Integer
HFG_Info.Clear
HFG_Info.Rows = 2
HFG_Info.TextMatrix(0, 1) = "sta"
HFG_Info.TextMatrix(0, 2) = "Row"
HFG_Info.TextMatrix(0, 3) = "Seq"
HFG_Info.TextMatrix(0, 4) = "Art_Code"
HFG_Info.TextMatrix(0, 5) = "Description"
HFG_Info.TextMatrix(0, 7) = "Price"
HFG_Info.TextMatrix(0, 6) = "Quantity"
HFG_Info.TextMatrix(0, 8) = "Amount"
Set Child_Editable = New ADODB.Recordset
Child_Editable.Open "select quantity,art_code,description,price,amount,seq,inv _no from sb_inv where inv_no ='" & Trim(Me.txt_Invno.Text) & "' ", Gcon, adOpenForwardOnly, adLockReadOnly
If Child_Editable.EOF = False Then
HFG_Info.ColAlignment(5) = 2
For rws = 1 To Child_Editable.RecordCount
If rws <> 1 Then
HFG_Info.Rows = HFG_Info.Rows + 1
End If
HFG_Info.Row = HFG_Info.Rows - 1
HFG_Info.TextMatrix(HFG_Info.Rows - 1, 2) = HFG_Info.Row
HFG_Info.TextMatrix(HFG_Info.Rows - 1, 1) = "0"
HFG_Info.TextMatrix(HFG_Info.Rows - 1, 3) = IIf(IsNull(Trim(Child_Editable.Fields(5))) = False, Trim(Child_Editable.Fields(5)), "")
HFG_Info.TextMatrix(HFG_Info.Rows - 1, 4) = IIf(IsNull(Trim(Child_Editable.Fields(1))) = False, Trim(Child_Editable.Fields(1)), "")
HFG_Info.Col = 5
HFG_Info.CellFontName = "Lucida Console"
HFG_Info.CellFontSize = 7.5
HFG_Info.TextMatrix(HFG_Info.Rows - 1, 5) = IIf(IsNull(Child_Editable.Fields(2)) = False, Trim(Child_Editable.Fields(2)), "")
HFG_Info.TextMatrix(HFG_Info.Rows - 1, 6) = IIf(IsNull(Trim(Child_Editable.Fields(0))) = False, Trim(Child_Editable.Fields(0)), "")
HFG_Info.TextMatrix(HFG_Info.Rows - 1, 7) = IIf(IsNull(Trim(Child_Editable.Fields(3))) = False, Trim(Child_Editable.Fields(3)), "")
HFG_Info.TextMatrix(HFG_Info.Rows - 1, 8) = IIf(IsNull(Trim(Child_Editable.Fields(4))) = False, Trim(Child_Editable.Fields(4)), "")
HFG_Info.Row = HFG_Info.Rows - 1
If (HFG_Info.TextMatrix(HFG_Info.Rows - 1, 4) <> "" And HFG_Info.TextMatrix(HFG_Info.Rows - 1, 4) <> "" And HFG_Info.TextMatrix(HFG_Info.Rows - 1, 4) <> "" And HFG_Info.TextMatrix(HFG_Info.Rows - 1, 4) <> "") Then
HFG_Info.Col = 4
HFG_Info.CellBackColor = &H8000000F
HFG_Info.Col = 5
HFG_Info.CellBackColor = &H8000000F
HFG_Info.Col = 6
HFG_Info.CellBackColor = &H8000000F
HFG_Info.Col = 7
HFG_Info.CellBackColor = &H8000000F
HFG_Info.Col = 8
HFG_Info.CellBackColor = &H8000000F
End If
Child_Editable.MoveNext
Next
'MsgBox (counter & " ! value of counter after populating grid")
End If
lbl_val.Caption = Child_Editable.RecordCount
Set Child_Editable = Nothing
End Sub
so tell me the better way to retrive data data base more efficeintly i am waiting for ur replys
|
