[RESOLVED] Search From The Database

[met0555]

Hi, I'm trying to make a program that will identify any file extension but I'm blocked here, I made the database with MS Access 2003 in 2000 format but then when the identify button is clicked i wasn't able to make it search from the database and show the result, can someone give me some help to make it work?

thanks

[zeezee]

ok, I ll tell you, my crystal ball is out of order today, could please post your code?


ps: dont get angry, just kiddin

[met0555]

i didn't write any code, normally i use this code on vb.net but now on vb6 i don't know where to start.

Private Sub butOk_Click(ByValsender As System.Object, ByVal e As System.EventArgs) Handles but_ok.Click
    Dim strConnection As String = "Data Source=your SQL data source;Initial Catalog=your database; Integrated Security=True"
    Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection(strConnection)
    Dim ds As New DataSet
    Dim strSelect As String
    'strSelect As String 
    strSelect = "SELECT * FROM " & YourTable & " WHERE [Search Field] = '" & Me.txtName.Text & "'"
    Dim dscmd As New SqlClient.SqlDataAdapter(strSelect, cn)
    dscmd.Fill(ds, "your table")
    Me.Datagrid1.DataSource = ds
    Me.Datagrid1.DataMember = "your table"
    Dim con As Integer
    con = Me.BindingContext(ds, "your table").Count
    If con = 0 Then
    MessageBox.Show("Recourd could not be found")
    End If
    End Sub

[Mark Gambo]

. . . I'm trying to make a program that will identify any file extension but I'm blocked here, . . .

What does this statment mean???

Additionally, the code you posted is VB.Net not VB6.

[zeezee]

Oh. I m sorry , I dont know vb.net. but in vb6 you can use a ADO connection and recordset to get data. I ll put some code.

to make a connection

    Public dbcon As adodb.Connection
    Set dbcon = New adodb.Connection
    dbcon.CursorLocation = adUseClient
    dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\ControlList.mdb;"

to retrive data from your table and show in a data grid,

Private Sub datagrid_load()
    DataGrid1.Visible = True
    Dim op_grid_rs As New adodb.Recordset
    Dim strsql As String
    
    
    op_grid_rs.CursorLocation = adUseClient
    
       strsql = " SELECT Control_UserLevel.*,  Controls.ContainerID " & _
             " FROM Controls INNER JOIN Control_UserLevel ON Controls.ControlName = Control_UserLevel.ControlName " & _
             " ORDER BY Controls.ContainerID"
             
    op_grid_rs.Open strsql, dbcon, adOpenStatic, adLockOptimistic
    op_grid_rs.Sort = "UserLevel"
    
    Set DataGrid1.DataSource = op_grid_rs
    
    
End Sub

Hope this helps.

[Mark Gambo]

Take a look at this LINK

[met0555]

Hi, i've tried your code like this , but when i'm searching for a record that exist the form just freez and the button disable but it don't show the record.??

Option Explicit
       
Private strDataBaseName As String
         Private strDBCursorType As String
         Private strDBLockType As String
         Private strDBOptions As String
         Private rs As ADODB.Recordset
         Private cn As ADODB.Connection
         Private strSQL As String
          
  
      Private Sub Command1_Click()
        Dim b As Long
      On Error GoTo Command1_Click_Error
        Me.Command1.Enabled = False
       strDBCursorType = adOpenDynamic  'CursorType
       strDBLockType = adLockOptimistic   'LockType
        strDBOptions = adCmdText         'Options
        
        Set cn = New ADODB.Connection
        Me.MousePointer = 11
         
      cn.Open ConnectString()
          
            With cn
               .CommandTimeout = 0
                .CursorLocation = adUseClient
            End With
  
        
        
            Set rs = New ADODB.Recordset       'Creates record set
         
            strSQL = "SELECT * "
            strSQL = strSQL & "FROM filei "
           strSQL = strSQL & "WHERE [File Extension] ='" & Me.Text1.Text & "' "
          strSQL = strSQL & "ORDER BY [File Extension];"
  
        
          rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
  
         
          If Not rs.EOF Then
                For b = 1 To rs.RecordCount
                       '<=== Do whatever you need to do with the recordset here
                       rs.MoveNext
                Next b
           Else
              MsgBox "The recordset contained no Records"
  
          End If
      
  
      Beep
  
       
  
      rs.Close
       Set rs = Nothing
        cn.Close
        Set cn = Nothing
  
       
  
      On Error GoTo 0
        Exit Sub
  
       
  
Command1_Click_Error:
           MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command1_Click of Form " & Me.Name
        End Sub
  
             Private Function ConnectString()
            ConnectString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Fido-Dido\Desktop\File_Identifier.mdb;"
         
        End Function

[Mark Gambo]

Could you print a sample of the data contained in the "filei" Table? Additionally, how many records are contained in that table?

[met0555]

there are file extensions there are about 2000 records

[Mark Gambo]

Have you tried stepping through the code to see where it hangs up?

[met0555]

I've tried with break points but i wasn't able to see where is the problem on the form i've only putted a button and a textbox does it need something else?

thanks

[Mark Gambo]

I've tried with break points but i wasn't able to see where is the problem on the form i've only putted a button and a textbox does it need something else?

thanks

So you were able to step through the code and it didn't hang up?

[Hack]

This is part of Mark's initial example

If Not rs.EOF Then
                For b = 1 To rs.RecordCount
                       '<=== Do whatever you need to do with the recordset here
                       rs.MoveNext
                Next b
           Else
              MsgBox "The recordset contained no Records"
  
          End If

What did you replace

 "'<=== Do whatever you need to do with the recordset here"

with?

[met0555]

it's because i didn't change it with the right code, but anyway i used another code but i still get an error, i will post on a new post.
thanks