data not fetched after getting inserted in access database

[Shilpa Jain]

Hi All,

I am inserting data in access database using Vb6. Data is getting inserted but when I try to fetch it again, it returns an empty row.I have an idea that I have to use update/refresh statement but not getting where to use it. Please help!!!

Below is the code :

Option Explicit

Private Sub SubmitCmd_Click()

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim sConnString  As String
Dim msg As Integer
Dim sSql As String

'Making Connection to DB
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Tool\P_and_E\P_and_E.mdb"
conn.Open sConnString
Set cmd.ActiveConnection = conn


If IsNull(Me.EmpNoTxt) Or Me.EmpNoTxt = "" Then
    MsgBox "Employee number cannot be empty!", vbOKOnly + vbExclamation, "Empty Field"
    Me.EmpNoTxt.SetFocus
End If

    sSql = "INSERT INTO Personal_Info VALUES ('" & EmpNoTxt.Text & "', " _
    & "'" & NameTxt.Text & " ','" & LocCmb.Text & "','" & NidTxt.Text & "', " _
    & "'" & PassprtTxt.Text & "','" & NidTxt.Text & "','" & DOBCal.Value & "', " _
    & "'" & AetJD.Value & "','" & IDTxt.Text & "','" & ResTxt.Text & "', " _
    & "'" & OffTxt.Text & "','" & MobTxt.Text & "','" & AddTxt.Text & "','Allocated')"

    cmd.CommandText = sSql
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute(sSql)

    'Set cmd = Nothing
    conn.Close
    Set conn = Nothing
    
    msg = MsgBox("The Details have been added successfully", 0, "Msg")

Unload Me

End Sub

[selvaganapathy]

Hi,
You are given the Update coding but your problem is " data not fetched after getting inserted in access database
", so post the fetching codes.

In this update coding if EmpNoTxt is Null or "" then also a record will be inserted, because you should stop the flow of the control with in the if statement

Replace

If IsNull(Me.EmpNoTxt) Or Me.EmpNoTxt = "" Then
    MsgBox "Employee number cannot be empty!", vbOKOnly + vbExclamation, "Empty Field"
    Me.EmpNoTxt.SetFocus
    [B]Exit Sub[/B]
End If

instead

If IsNull(Me.EmpNoTxt) Or Me.EmpNoTxt = "" Then
MsgBox "Employee number cannot be empty!", vbOKOnly + vbExclamation, "Empty Field"
Me.EmpNoTxt.SetFocus
End If

[Shilpa Jain]

Hi,

Thanks for replying. I gave this code where I am inserting data because I thought that I need to update the database using this code only.Data is returned when I try to fetch the old values. The problem is only with new inserted data.
I have 3 search queries where I am trying to fetch data. Here is one of them:

Private Sub SearchCmd_Click()

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sConnString  As String
Dim sNID As String
Dim sSql, sSql1 As String
Dim rs, rs1 As Recordset

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

sNID = NidTxt.Text

If IsNull(sNID) Or sNID = "" Then
    MsgBox "Please enter NID", vbOKOnly + vbExclamation, "Empty Field"
    Me.NidTxt.SetFocus

Else

'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


'Query to fetch details from Personal_Info table
sSql = " SELECT Personal_Info.Emp_No, Personal_Info.Name, Personal_Info.Location, " _
& "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 ((trim(Personal_Info.NID)='" & sNID & "'))"

rs.Open sSql, conn, adOpenStatic, adLockReadOnly

'MsgBox (rs.RecordCount)

If Not rs.BOF Then

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

Else
    MsgBox "Record not found for personal info!!"

End If

rs.Close
'Query to fetch details from Project_Info table
sSql1 = " SELECT Project_Info.Unit, Project_Info.Proj_Join_Date, " _
& "Project_Info.Application, Project_Info.Supp_Level " _
& "From Project_Info " _
& "WHERE (((Project_Info.NID)='" & sNID & "'))"


rs1.Open sSql1, conn, adLockReadOnly

'MsgBox (rs1.RecordCount)

If Not rs1.BOF Then
'Display values from database in form
    frmSearchResult.DepTxt.Text = rs1.Fields("Unit").Value & ""
    frmSearchResult.PrjJDTxt.Text = rs1.Fields("Proj_Join_Date").Value & ""
    frmSearchResult.AppTxt.Text = rs1.Fields("Application").Value & ""
    frmSearchResult.LvlTxt.Text = rs1.Fields("Supp_Level").Value & ""
Else
MsgBox "Record not found for Project info!!"

End If

rs1.Close

Set rs = Nothing
Set rs1 = Nothing


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

End If

End Sub

regards,
Shilpa

[Shilpa Jain]

Hi ,

Thanks all. This issue is resolved now. :)