PLs pls help me!!!!!!!!!

I am making a project for a college, to store details of students exams and professors.
Im using adodb connection.
im having problem with search form where i have a MSHFlexGrid and im using adodb to search.
The problem is that
i have a textbox, and on entering text the grid should show the record similar to the entry.
This is not happening.
The code used is this

Code:

---

"to load the MSHFlexgrid"
Private Sub Form_Load()
  Dim conn1 As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Set conn1 = New ADODB.Connection
    conn1.ConnectionString = "Provider=MSDataShape.1;data provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\studentdetails.mdb;" & " Persist Security Info=False"
    conn1.Open
    Set rs1 = New ADODB.Recordset
    rs1.Open "shape {SELECT * from personaldetails order by ID} ", conn1, adOpenDynamic, adLockOptimistic
    Set MSHFlexGrid1.DataSource = rs1
    rs1.Close
    Set rs1 = Nothing
    conn1.Close
    Set conn1 = Nothing
    With MSHFlexGrid1
        .CollapseAll
        .BandDisplay = flexBandDisplayVertical
        .ColHeader(1) = flexColHeaderOn
        .BandIndent(1) = 2
    End With

"The textbox change code"
Private Sub Text1_Change()
conndb
Set rs2 = New ADODB.Recordset
Dim strSQL As String, sqlquery As String
Select Case Combo1.Text
Case Is = "Name"
sele = 1
MSHFlexGrid1.LeftCol = 1
sqlquery = "Select * from personaldetails"
strSQL = sqlquery & " where Student_name like '*" & Trim(Text1.Text) & "*'"
With rs2
.Open strSQL, conn1, adOpenStatic, adLockOptimistic
If .EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
Else
If Text1.Text = "" Then
.Source = "Select * from personaldetails"
Else
.Source = "Select * from personaldetails where Student_name like '*" & Trim(Text1.Text) & "*'"
.MoveNext
m2 = Text1.Text
End If
End If
End With

---

pls guide me
whenevr i enter i get the msgbox error msg.
pls help!!!!!!!!!!!!!!!!!!!!!
Any suggestions will be appreciated

Please use Code Tags when you post code, and descriptive thread titles in the future

Please use code tags around your code sections so as the formatting is kept and readability is maintained.
Just write [ code ] in front and [ /code ] at the end of the code block, only without the blanks shown here.

First thing comes to mind, in Text1_Change() the rs2 variable is not declared.
Please turn on OPTION EXPLICIT to help you finding undeclared variables.
Then, we need to know which line produces the error, and what exactly the error message is.

ok sorry for the inconvenience.ill surly keep in mind.
i tried declaring rs2 in text1_Change() and option explicit.
but no results.
do you want me to add more code from my project for better idea or any kind of explanantion?
pls tell me ill be happy to do so.thnks

To find out where the error comes in you first have to comment out the line
On Error Resume Next.
Just put a quote in front to make a comment out of it for temporarily disabling it:
'On Error Resume Next.

Then a much more detailed description of the error should come up, plus the code line where the error occured.

Otherwise, for any small project it is best you zip it up and attach it, so as we can run a so far running program to examine closer where the problem is. It should be most complete then, including at least a sample database.

Take a look at this. I've included a project as well.

Code:

---

Option Explicit


Private Sub Command1_Click()
    Dim cnLvConnection As ADODB.Connection
    Set cnLvConnection = New ADODB.Connection
    Dim rsLvRecordset As ADODB.Recordset
    Set rsLvRecordset = New ADODB.Recordset
    With cnLvConnection
        .Provider = "MSDataShape.1"
        .ConnectionString = "Data Source=" & App.Path & "\db1.mdb;" _
                          & "Data Provider=Microsoft.Jet.OLEDB.4.0;"
        .Open
        With rsLvRecordset
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
        End With
        Set rsLvRecordset = .Execute("SHAPE {SELECT c.CustomerName As Customer, c.CustomerID FROM Customers c ORDER BY c.CustomerName} As Customers" _
                                  & " APPEND ((SHAPE {SELECT oh.OrderNumber As [Order No], oh.CustomerID, oh.OrderHeaderID FROM OrderHeaders oh ORDER BY oh.OrderNumber} As OrderHeaders" _
                                  & "          APPEND ({SELECT od.OrderLine As [Line], od.OrderLineDescription As [Description], od.OrderLineQuantity As Quantity, od.OrderHeaderID FROM OrderDetails od ORDER BY od.OrderLine} As OrderDetails" _
                                  & "          RELATE OrderHeaderID TO OrderHeaderID))" _
                                  & " RELATE CustomerID TO CustomerID)")
    End With
    '  Setup Grid
    Set Me.MSHFlexGrid1.Recordset = rsLvRecordset
    Me.MSHFlexGrid1.ColWidth(1, 0) = 0 ' c.CustomerID
    Me.MSHFlexGrid1.ColWidth(1, 1) = 0 ' oh.CustomerID
    Me.MSHFlexGrid1.ColWidth(2, 1) = 0 ' oh.OrderHeaderID
    Me.MSHFlexGrid1.ColWidth(3, 2) = 0 ' od.OrderHeaderID
    '  Tidy up
    If Not rsLvRecordset Is Nothing Then
        If rsLvRecordset.State <> adStateClosed Then
            rsLvRecordset.Close
        End If
        Set rsLvRecordset = Nothing
    End If
    If Not cnLvConnection Is Nothing Then
        If cnLvConnection.State <> adStateClosed Then
            cnLvConnection.Close
        End If
        Set cnLvConnection = Nothing
    End If
End Sub

Private Sub Form_Load()
    Me.MSHFlexGrid1.FixedCols = 0
End Sub

'
'  Edit the cell, without the update

Private Sub MSHFlexGrid1_KeyPress(KeyAscii As Integer)
  If KeyAscii = vbKeyReturn Then  'enter key
    'move to next cell.
        With MSHFlexGrid1
            If .Col + 1 <= .Cols - 1 Then
                .Col = .Col + 1
            Else
                If .Row + 1 <= .Rows - 1 Then
                    .Row = .Row + 1
                    .Col = 0
                Else
                    .Row = 1
                    .Col = 0
                End If
            End If
        End With
    ElseIf KeyAscii = vbKeyBack Then  'back space key
        With MSHFlexGrid1
            'back space out the entered characters
            If Len(.Text) Then
                .Text = Left(.Text, Len(.Text) - 1)
            End If
        End With
    Else
        With MSHFlexGrid1
            .Text = .Text & Chr(KeyAscii)
        End With
    End If
End Sub

---

and a link: http://www.4guysfromrolla.com/webtech/092599-1.shtml

awsome code bro!!!
ill surly try it.....
ok ill also attach my pro with the database.
im attaching the search form and the next form to be loaded.

hey
i too im trying but still no break through.
any progress??or is anythn missing??
pls do tell me..

You have to know how your db and program works. Nobody wants to figure out WHERE the problem is, and then try to debug it for you.

Post a description of the problem that you're having, then post some code that you have tried (using CODE TAGS).

Make sure we know which LINE has the problem, as well as any error messages that you're getting.

You forgot to supply the file stu.mdb with your zip. The project wouldn't run without it, so as we cannot spot the error.
Please supply a version of stu.mdb.

ok sorry for that.
here is stu.mdb.

i understand what ur tryin to say.
the fact is tht there are no errors in the code.and thts why i feel there is a problem in the logic of Text1_Change().
The problem that im facing on debuggin is "If .EOF Then" statement where .EOF is comin true.
so its not able to find the record.

Code:

---

"The textbox change code"
Private Sub Text1_Change()
conndb
Set rs2 = New ADODB.Recordset
Dim strSQL As String, sqlquery As String
Select Case Combo1.Text
Case Is = "Name"
sele = 1
MSHFlexGrid1.LeftCol = 1
sqlquery = "Select * from personaldetails"
strSQL = sqlquery & " where Student_name like '*" & Trim(Text1.Text) & "*'"
With rs2
.Open strSQL, conn1, adOpenStatic, adLockOptimistic
If .EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
Else
If Text1.Text = "" Then
.Source = "Select * from personaldetails"
Else
.Source = "Select * from personaldetails where Student_name like '*" & Trim(Text1.Text) & "*'"
.MoveNext
m2 = Text1.Text
End If
End If
End With

---

pls help me here.
thanks

I'm not sure about the code yet. The reoson for no error coming up is, there are plenty 'On Error Resume Next' statements which suppress the errors.
During developement you should comment out these statements, allowing the actual error messages to pop up.

I can look at the code later that eveneing. Now that you sent stu.mdb, I should be able to run it at least.

Ok. I'm now aware of what you want to achieve.
I must say it would have been easier if you'd have cultivated a better style of indentation.
Now this is the relevant piece of code, which I have indented to clarify the program flow.

Code:

---

  Case Is = "Name"
      sele = 1
      MSHFlexGrid1.LeftCol = 1
      sqlquery = "Select * from personaldetails"
      strSQL = sqlquery & " where Student_name like """ & Trim(Text1.Text) & """;"
      Debug.Print strSQL
      With rs2
          .Open strSQL, conn1, adOpenStatic, adLockOptimistic
          Set MSHFlexGrid1.DataSource = rs2 '<--inserted this line
          If .EOF Then
              MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
              SendKeys "{Home}+{End}"
              'On Error Resume Next
          Else
              If Text1.Text = "" Then
                .Source = "Select * from personaldetails"
              Else
                .Source = "Select * from personaldetails where Student_name like '*" & Trim(Text1.Text) & "*'"
              End If
              .MoveNext
              m2 = Text1.Text
          End If
      End With

---

1. It is obvious that the sql query string is not working.
2. I have changed the query to "Select * from personaldetails" without the selectivity to see if something comes back.
3. Something is coming back and without your On Error Resume Next it reveals that
4. (adorecordset).Source = "Select * from ..." is not allowed for an open object.
5. therefore I have inserted the line Set MSHFlexGrid1.DataSource = rs2 which propably does what you want.
The actual remaining code could look like this:

Code:

---

  Case Is = "Name"
      sele = 1
      MSHFlexGrid1.LeftCol = 1
      sqlquery = "Select * from personaldetails"
      strSQL = sqlquery & " where Student_name like """ & Trim(Text1.Text) & """;"
      Debug.Print strSQL
      With rs2
          .Open strSQL, conn1, adOpenStatic, adLockOptimistic
          Set MSHFlexGrid1.DataSource = rs2
          If .EOF Then
              MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
              SendKeys "{Home}+{End}"
              'On Error Resume Next
              End If
              .MoveNext
              m2 = Text1.Text
          End If
      End With

---

The only thing left - and which I have not yet figured out - is why the sql statement doesn't work as expected.
I have debug.printed it and it looks like this:
Select * from personaldetails where Student_name like "c*";
respectively before I changed the quotes into double quotes it was:
Select * from personaldetails where Student_name like '*c*'
Both strings do not return any record.
And I still don't know why.
To me they look like pretty good SQL statements.
Does anybody object?

If .EOF? Try it the other way...

Code:

---

Private Sub Form_Load()
Dim dteDate    As Date
Dim adoRec      As ADODB.Recordset
    dteDate = CDate("7 March 2005 10:04:00AM")
    Set adoRec = GetRecordset(dteDate)
    With adoRec
        Do While Not .EOF
            'your code goes here
            MsgBox .Fields(0)
            .MoveNext
        Loop
        .Close
    End With
    Set adoRec = Nothing
End Sub

---

That's not the propblem. If you look at the following code segment

Code:

---

      With rs2
          .Open strSQL, conn1, adOpenStatic, adLockOptimistic
          Set MSHFlexGrid1.DataSource = rs2
          If .EOF ...

---

you'd note that immediately after the rs2.Open statement I have connected the flexgrid to the datasource of rs2. The result of the Open statement should immediately show in the grid.
At the time of open the sql line is
Select * from personaldetails where Student_name like "c*";
and the results are zero
Change the sql to
Select * from personaldetails where Student_name like "chris";
and it finds the record where Student_name is chris.
So why does the Like "c*" not find chris, too? That's here the question.

I'd anyway advise more changes to the code belonging the .EOF, but this makes no sense as long as the Like statement does not deliver records as expected.

Ok, I seem to have bumped into the solution. It is the "*" operator.
I don't know why, but if you look for the syntax of the Like operator in SQL statements you'll find two different descriptions, one denoting the "*" character as wildcard, the other one wants the "%" in this place. For some reason (maybe depending on the version of Access database) in your studentdetails.mdb the "%" sign works as a wildcard.

I have stripped down your code from redundant repetitions you did in the Case clauses to show you how the function you want is easily accomplished.
Just put this code into Text1_Change():

Code:

---

Private Sub Text1_Change()
  conndb

  Set rs2 = New ADODB.Recordset
  Dim strSQL As String, sqlquery As String
 
  sqlquery = "Select * from personaldetails"
 
  Select Case Combo1.Text
    Case Is = "Name"
        sele = 1
        MSHFlexGrid1.LeftCol = 1
        strSQL = sqlquery & " where Student_name like """ & Trim(Text1.Text) & "%"";"

    Case Is = "ID No"
        sele = 2
        MSHFlexGrid1.LeftCol = 0
        strSQL = sqlquery & " where ID like '*" & Trim(Text1.Text) & "*'"
 
    Case Is = "Last Name"
        sele = 3
        MSHFlexGrid1.LeftCol = 2
        strSQL = sqlquery & " where Last_name like '" & Trim(Text1.Text) & "*'"
  End Select
 
  With rs2
      .Open strSQL, conn1, adOpenStatic, adLockOptimistic
      Set MSHFlexGrid1.DataSource = rs2
      If .EOF Then
        MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
        SendKeys "{Home}+{End}"
        'On Error Resume Next
      End If
  End With
  'MSHFlexGrid1.Refresh
  Label2.Visible = True
End Sub

---

You'll note that the Select Case is only used to determine the SQL string (using "%" as wildcard) and then the database is accessed. You need not repeat this code three times, too.
Also the complete ELSE clause of the If .EOF seemed useless, yet even wrong, so I left it away.
I think this is what you attempted and I'm sure you can now go on with it.

And please also note how easy it is to read and understand code if you keep indendation logically structured. ;)

wow i didnt see ya last post sorry!!!
u r genius.........
it works thnks bro!!!!!!!!!!!!!!!!!!
u were right ill surly follow ur instructions....................

thnks a lot.u saved me..
can i consult ya if there are any further probs???

Quote:

---

Originally Posted by WoF

And please also note how easy it is to read and understand code if you keep indendation logically structured. ;)

---

I noticed that I had to use & to get a phone number to work with LIKE, but names worked with *

@analyse300: Sure feel free to come back any time.

@David:
What intrigues me is the two different explanations.
The Like statement in VB works with the "*" as a wildcard, sure. That's in the manual.

But then you lookup the SQL Like statement and find two different explanations.
One says "*" is the wildcard, the other says "%" is the wildcard.
After my testing with the database of the OP, most obviously the "%" is the wildcard.
The "*" produces an emty recordset

The starnge thing is, when opening that particular database with access and create an adequate query, you have to use "*" as a wildcard to produce a result.
Doing the same thing via ADO.Recordset.Open method you HAVE TO use the "%". I tried to copy the query from Access (with the "*") and it didn't work. Changing * to % worked immediately.

I'm a bit baffled about that one and have not yet worked out the reason for it. Are there different versions of SQL connected with the ADO library?

Might be different versions of the ADOC library. I learned the hard way a few years ago, thanks to pete (wonder where he went?)

Quote:

---

Originally Posted by dglienna

Might be different versions of the ADOC library. I learned the hard way a few years ago, thanks to pete (wonder where he went?)

---

I have seen that in more modern languages like C# they always use '%' as a wildcard while when I was working with older versions of ADODB I always had to use '*' as wildcard. But thats simple my experience

Hello,

May I ? ===>>
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx
The "The ADO monkey wrench" paragraph is the one we have to read with more attention. Other wildcard characters changed, too...

OK. A bit older than ADO versions, it's the old DAO library in Access 97 (and VB6)

Well, this all sounds as if we would have to run a testroutine to determine which character is wild.
If Select * from table where name like "*" returns no record, we have to try "%"?
Interesting to hear about the ALike operator. Never heard before. And this one always uses "%"?