Can help me check this why can get data from the sql

[gingank]

Hi guys i dont know why i can get the datas from the sql 7.0
Can help me check this code>>>

If txtFBadgeID = "" Then
       
       Exit Sub
       
    Else
        sql = "select from staff_badgeTrackingNew INNER JOIN staff_badgeTracking"
        End If
        Exit Sub
        
        Set rsTrack = New ADODB.Recordset
        With rsTrack
            .ActiveConnection = cnUnisemsql
            .CursorType = adOpenStatic
            .Open sql
        End With
        
        If rsTrack.EOF = True Then
         MsgBox "Badge ID '" & txtFBadgeID.Text & "' does not exist ! "
            txtFBadgeID.Text = ""
            txtFBadgeID.SetFocus
            Exit Sub
        
        Else
          txtFBadgeID.SetFocus

[vb5prgrmr]

6th line down has an exit sub right after the end if on line 5. From what I can tell this is executed so the rest of the code is not. Also, your inner join query is incomplete and will/should raise errors when you attempt to try to open it.

Good Luck

[gingank]

6th line down has an exit sub right after the end if on line 5. From what I can tell this is executed so the rest of the code is not. Also, your inner join query is incomplete and will/should raise errors when you attempt to try to open it.

Good Luck

Here is the new code i do please help me to check this ok

Private Sub txtFBadgeID_KeyDown(KeyCode As Integer, Shift As Integer)

    
    If txtFBadgeID = "" Then
       
       Exit Sub
       
    Else
      sql = " Select * from staff_badgeTracking inner join staff_badgeTrackingNew On staff_badgeTracking.Badge_ID =staff_badgeTrackingNew.Badge_ID"
        
        Set rsTrack = New ADODB.Recordset
        With rsTrack
            .ActiveConnection = cnUsersql
            .CursorType = adOpenStatic
            .LockType = adLockOptimistic
            .Open sql
            
        End With
        
        If rsTrack.EOF = True Then
         MsgBox "Badge ID '" & txtFBadgeID.Text & "' does not exist ! "
            txtFBadgeID.Text = ""
            txtFBadgeID.SetFocus
            Exit Sub
        
    
          End If
        End If
        
  
End Sub

[vb5prgrmr]

Looks good but query should not be in keydown event as every keypress will make your query run. Use any of the following so the msgbox does not pop up.

1) test the length in the textbox to make sure the minimum number of required character are entered (in this keydown event)

2) use lost focus event of text box

3) use command button to do the search

Good Luck

[gingank]

Looks good but query should not be in keydown event as every keypress will make your query run. Use any of the following so the msgbox does not pop up.

1) test the length in the textbox to make sure the minimum number of required character are entered (in this keydown event)

2) use lost focus event of text box

3) use command button to do the search

Good Luck

I follow you advise give change to lost focus and set the length but i did not use the cmd search only.
the code is run fine until it go to the .open sql then is said got error.
I already declare sql database using the module but still have the problem so can help me with this?

[vb5prgrmr]

It looks fine to me but here are a couple of things to check before you execute your SQL statement...

1) Check the connection to make sure it is open
2) Remove the space before select (really should not have to but what the he.. if you know what I mean).
3) check in help files to make sure that a static rs is valid with optimistic locking

Good Luck

[gingank]

It looks fine to me but here are a couple of things to check before you execute your SQL statement...

1) Check the connection to make sure it is open
2) Remove the space before select (really should not have to but what the he.. if you know what I mean).
3) check in help files to make sure that a static rs is valid with optimistic locking

Good Luck

Hi still have problem need you to check why like this i using the data grid but the data grid did not show anything? Came out error

ption Explicit
Dim rsTrack As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim sql As String

Private Sub cmdSearch_Click()
    Dim rs As ADODB.Recordset
    Dim rsTrack As ADODB.Recordset
    Dim sql As String
    
    If txtFBadgeID = "" Then
       Exit Sub
       
    Else
      sql = " Select * from staff_badgeTracking " _
      & " inner join staff_badgeTrackingNew " _
      & " On staff_badgeTracking.Badge_ID = staff_badgeTrackingNew.Badge_ID"
        
        Set rsTrack = New ADODB.Recordset
        With rsTrack
            .ActiveConnection = cnUnsql
            .CursorType = adOpenStatic
            .LockType = adLockOptimistic
            .Open sql
            
        End With
        
    dgData.AllowAddNew = True
    dgData.AllowUpdate = True
    dgData.AllowDelete = True
    
    
    If Not rs.EOF Then
        Set dgData.DataSource = rs
        dgData.DataChanged = True
        dgData.Refresh
        
    Else
        Set dgData.DataSource = rs
      
        dgData.Refresh
    End If
    End If

    

End Sub
Private Sub cmdNew_Click()

frmBadgeAdd.Show


End Sub

Private Sub cmdUp_Click()

frmBadgeUpdate.Show


End Sub
Private Sub cmdExit_Click()
End
End Sub

[vb5prgrmr]

Well, I don't see where you are opening the recordset rs but I do see rstrack being opened. Is that the recordset object you want to assign to your grid???

Good Luck

[gingank]

Well, I don't see where you are opening the recordset rs but I do see rstrack being opened. Is that the recordset object you want to assign to your grid???

Good Luck

yes the recordset i need to assign it into the data grid

[gingank]

Well, I don't see where you are opening the recordset rs but I do see rstrack being opened. Is that the recordset object you want to assign to your grid???

Good Luck

I solve the problem already now i have another problem with the data grid

my sql statement is like this:
sql = " Select * from staff_badgeTracking " _
& " inner join staff_badgeTrackingNew " _
& " On staff_badgeTracking.Badge_ID = staff_badgeTrackingNew.Badge_ID"

the output something like this

Badge ID Emp_ID Emp_Name Badge_ID Emp ID Emp_Name
1234 5566 Nana 1234 7788 Nana

If i dun to show like that can show then show like this way in the data grid:
Badge ID Emp_ID Emp_Name
1234 5566 Nana
1234 7788 Nana

[vb5prgrmr]

What you can do is create a temporary table by inserting into (you've seen this before) and then retrieving your records from that. Then delete the table or just keep it around and when you are done delete the records from it. Now, since you are using SQL 7.0 you could create a stored proceedure to accomplish this by using a memory table (a table created in memory, filled with information, and passed back to your application) however, I would suggest that you use http://www.dbforums.com for them to help you get the correct syntax you will need.

Good Luck