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

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

VB6 with SQl 2005 Search

Need to have a code for the search in the database and display the result in the mshflexgrid.

The search criteria changes as the user select the options in the combo box.

The search should happen in a master table which references a few other tables for the real value. (Please find the attachment called reference.jpg for more details)

I am using VB6 as front end  and SQL 2005 as database.
Search.JPG
Referance.JPG
0
Intelops
Asked:
Intelops
  • 5
  • 2
  • 2
  • +1
1 Solution
 
radcaesarCommented:
If the data volume is large and the table count is high to search then you can go for full index search. For this you need to enable full index search on your db.

http://msdn.microsoft.com/en-us/library/ms345119%28v=sql.90%29.aspx

Else you can simply select and join the results between multiple tables.
0
 
IntelopsAuthor Commented:
its not a very large database.. but one of the table having around 100 fields, rest all are small,. and the data in the table will not grow more than 5000.

select and join the results...can you please elaborate bit more..
0
 
IntelopsAuthor Commented:
Any update friends..????
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
radcaesarCommented:
The there is no need of "full index search". Just you can achieve this using query itself.

Ex, If the user select any search type then pass that search type in the query

select * from table1 where search_type=<selectedvalueOfSearchType> (Just use string concatenation/manipulation to perform this)
0
 
judgekingCommented:
Not sure what your reference table is for, but here's something I use when searching tables and loading a MSFlexGrid:
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim oField As ADODB.Field
Dim sCriteria As String, sSQL As String

sSQL = "SELECT * FROM MasterTable"
sCriteria = ""

If txtHostName.Text <> "" Then
  If Trim(sCriteria) = "" Then sCriteria = " WHERE " Else sCriteria = sCriteria & " AND "
  sCriteria = sCriteria & " HostName LIKE '%" & txtHostName.Text & "%' "
End If

'
'Continue checking form fields for search criteria...
'

oConn.Open "Driver={SQL Server}; UID=sa; PWD=password; Server=MySQLServerName; Database=MyDBName;"
oRs.Open sSQL & sCriteria, oConn, adOpenForwardOnly, adLockReadOnly
iRow = 0

With gridResults
  Do While Not oRs.EOF
      iRow = iRow + 1
      iCol = 0
      
      If iRow > 1 Then 'First row already exists
        .AddItem ""
        .Row = iRow
        .CellAlignment = flexAlignCenterCenter
        .CellBackColor = vbButtonFace
        .CellFontBold = True
      End If

      For Each oField In oRs.Fields
        iCol = iCol + 1
        .TextMatrix(iRow, iCol) = oField.Value
      Next

      oRs.MoveNext
    Loop
  End With

  oRs.Close
  oConn.Close

Open in new window

0
 
judgekingCommented:
Intelop, any update?
0
 
judgekingCommented:
Intelop, any update?
0
 
judgekingCommented:
Intelop, any update?
0
 
judgekingCommented:
Any update?
0
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now