Data not fetched while running the code

[Shilpa Jain]

Hi All,

I am making a tool in VB using MS access database. When I run the query in access it returns me the data but in VB it shows 0 recordcount. Any idea what could be the reason for this?

One nore thing to ask. As multiple people can have same name, so how do I show all the names and their data on the form as only one record can be shown at a time?

Here is the code:

Private Sub SearchCmd_Click()

Option Explicit

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sConnString  As String
Dim sName As String
Dim iCount As Byte
Dim sSql, sSql1, sSql2, sSql3 As String
iCount = 0
sName = NameTxt.Text

Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset

'Connecting to the Database
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Tool\P_and_E\P_and_E.mdb"

conn.Open sConnString

If Me.PrjOpt = True Then

'Query to fetch details from Personal_Info table
sSql = " SELECT Personal_Info.Emp_No, Personal_Info.NID, Personal_Info.Passport_No, " _
& "Personal_Info.PIN, Personal_Info.[D-O-B], Personal_Info.Aet_Join_Date, " _
& "Personal_Info.Infy_Mail_Id, Personal_Info.Ph_Res, Personal_Info.Ph_Off, " _
& "Personal_Info.Ph_Cell, Personal_Info.Address, Personal_Info.Status " _
& "From Personal_Info " _
& "WHERE (((Personal_Info.Name) Like '*" & sName & "*'))"

rs.Open sSql, conn, adOpenStatic, adLockReadOnly

MsgBox (rs.RecordCount)

If Not rs.EOF And rs.BOF Then


        'Load form to display details
        Form7.Show

        'Display values from database in form
        Form7.EmpTxt.Text = rs.Fields("Emp_No").Value
        Form7.NameTxt.Text = CStr(sName)
        Form7.LocTxt.Text = rs.Fields("Location").Value
         Form7.NidTxt.Text = rs.Fields("NID").Value & ""
        Form7.PassportTxt.Text = rs.Fields("Passport_No").Value
        Form7.PinTxt.Text = rs.Fields("PIN").Value & ""
        Form7.DOBTxt.Text = rs.Fields("D-O-B").Value & ""
        Form7.AetJDTxt.Text = rs.Fields("Aet_Join_Date").Value
        Form7.IDTxt.Text = rs.Fields("Infy_Mail_Id").Value & ""
        Form7.ResTxt.Text = rs.Fields("Ph_Res").Value & ""
        Form7.OffTxt.Text = rs.Fields("Ph_Off").Value & ""
        Form7.MobTxt.Text = rs.Fields("Ph_Cell").Value & ""
        Form7.AddTxt.Text = rs.Fields("Address").Value & ""
        Form7.StatusTxt.Text = rs.Fields("Status").Value & ""

Else

    MsgBox "Record not found for Personal Info!! "
    
End If

End If

Set rs = Nothing
'Close connection to database
conn.Close
Set conn = Nothing

End Sub

[selvaganapathy]

I am making a tool in VB using MS access database. When I run the query in access it returns me the data but in VB it shows 0 recordcount. Any idea what could be the reason for this?

Hi,
Sometimes it happens, i dont know the reason. but Move to the Last record will solve the problem

rs.MoveLast
  rs.MoveFirst
  MsgBox (rs.RecordCount)

As multiple people can have same name, so how do I show all the names and their data on the form as only one record can be shown at a time?

You can use Grid Control to View multiple records instead of TextBoxes.

[Shilpa Jain]

Hi,

I tried the solution suggested by selvaganapathy. But, if I am using

rs.MoveLast
 rs.MoveFirst
 MsgBox (rs.RecordCount)

It is giving me error either BOF or EOF is true and if I use it inside the

If Not rs.EOF And rs.BOF Then
rs.movelast
\\ rest of the code
End if

data is not being fetched. Is there any other way this problem can be sorted out?

Regards,
Shilpa

[Shilpa Jain]

Hi All,

Thanks for replying. Problem is resolved now.:)

regards,
Shilpa