adding record in a database

[meLiel]

Private Sub cmdAdd_Click()

  Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    Dim rsAdd As ADODB.Recordset
    Set rsAdd = New ADODB.Recordset
    Dim Query As String

    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Asus\Desktop\VBTHESIS_liel2\ListofMembers.mdb;Persist Security Info=False"
    con.CursorLocation = adUseClient

    rsAdd.Open "SELECT * FROM census WHERE ID ='" & strId & "'", con, adOpenStatic, adLockOptimistic
     'If textboxes are null then
        MsgBox "Please fill in the required details. ", vbExclamation, "Santo Niño Census Info System"
        txtEnterID.SetFocus
        Exit Sub
        'End If

       'If record exists Then
        MsgBox "Record has already exists!", vbExclamation, "Santo Niño Census Info System"
        txtEnterID.Text = ""
        txtLastName.Text = ""
        txtFirstName.Text = ""
        txtMiddleName.Text = ""
        txtAge.Text = ""
        txtSex.Text = ""
        txtStatus.Text = ""
        txtAddress.Text = ""
        txtBirthDate.Text = ""
        txtBirthPlace.Text = ""
        txtCitizenship.Text = ""
        txtReligion.Text = ""
        txtOccupation.Text = ""
        txtEnterID.SetFocus
        Else

        Query = "INSERT INTO census VALUES ('" & txtEnterID.Text & "', '" & txtLastName.Text & "', '" & txtFirstName.Text & "', '" & txtMiddleName.Text & "', '" & txtAge.Text & "', '" & txtSex.Text & "', '" & txtStatus.Text & "', '" & txtAddress.Text & "', '" & txtBirthDate.Text & "', '" & txtBirthPlace.Text & "', '" & txtCitizenship.Text & "', '" & txtReligion.Text & "', '" & txtOccupation.Text & "', '" & txtSkills.Text & "', '" & txtContactNo.Text & "')  "
        con.Execute Query, , adCmdText
        MsgBox "Record successfuly added!", vbOKOnly, "Santo Niño Census Info System"

        txtEnterID.Text = ""
        txtLastName.Text = ""
        txtFirstName.Text = ""
        txtMiddleName.Text = ""
        txtAge.Text = ""
        txtSex.Text = ""
        txtStatus.Text = ""
        txtAddress.Text = ""
        txtBirthDate.Text = ""
        txtBirthPlace.Text = ""
        txtCitizenship.Text = ""
        txtReligion.Text = ""
        txtOccupation.Text = ""
        txtSkills.Text = ""
        txtContactNo.Text = ""
        txtEnterID.SetFocus
    End If
End Sub

hi! please help me with this...

[srkv03]

Did you try putting a semi-colon after the query?

[AndreRet]

What exactly is the problem here? On which line are you getting errors?

[srkv03]

Ignore my previous reply:-|

[srkv03]

Try this, you need to specify the columns before you enter the values.

Query = "INSERT INTO census(ID, LastName, FirstName, MiddleName, Age, Sex, Status, Address, BirthDate, BirthPlace, Citizenship, Religion, Occupation, Skills, ContactNo) VALUES ( '" & txtEnterID.Text & "', '" & txtLastName.Text & "', '" & txtFirstName.Text & "', '" & txtMiddleName.Text & "', '" & txtAge.Text & "', '" & txtSex.Text & "', '" & txtStatus.Text & "', '" & txtAddress.Text & "', '" & txtBirthDate.Text & "', '" & txtBirthPlace.Text & "', '" & txtCitizenship.Text & "', '" & txtReligion.Text & "', '" & txtOccupation.Text & "', '" & txtSkills.Text & "', '" & txtContactNo.Text & "') "

[meLiel]

please see the red highlighted texts.. i dont know what's the exact syntax in that line.. i dont have problems with inserting values into my table only with filtering errors..

[AndreRet]

The first part - If textboxes is null, do the following BEFORE you open your recordset...

If txtName.Text = vbNullString Then
    MsgBox "Please enter a valid name.", vbOkOnly + vbInformation

    txtName.SetFocus
    Exit Sub
ElseIf txtSurname.Text = vbNullString Then
    MsgBox "Please enter a valid surname.", vbOkOnly + vbInformation

    txtSurname.SetFocus
    Exit Sub
    ''Do this now for all your textboxes that needs validation

        Else
    ''NOW OPEN YOUR RECORDSET
    ''ALL OF YOUR OTHER CODE GOES HERE...

    ''Recordset is now open, lets check if the user exists..
    If rsAdd.Recordcount > 0 Then
        MsgBox "User already exist. Please do something else here..."

        Exit Sub
            Else
        ''You can now add the record to your database...
    End If
End If

[RonalBertogi]

Hello, meLiel! I'll give you an example how to check whether a record exists in database or not. This is best and easily done if your database records has a field that contains a unique key or id. On your sample code, I will just assume that txtEnterID.Text holds the value that is to be to a particular field of your table. Let's just name it here EnterID.

Here I assume that con is a global variable

Public Function RecordExists(ByVal pszId As String) As Boolean
    Dim rs As ADODB.Recordset
    Set rs = con.Execute("SELECT EnterID FROM MyTable WHERE EnterID='" & pszId & "'")
    RecordExists = rs.RecordCount<>0;
End Function

About those text boxes, you create a function that will check each control if they have a valid value. It's better if you store your control in an array...

That's all for now.

[hyesin]

another way to check the record is by using a listview..by calling a record

Private Sub Text1_Change()
For i = 1 To ListView1.ListItems.Count
    If Text1.Text = ListView1.ListItems(i) Then
        MsgBox ("Already Exist")
        Text1.Text = ""
        Text1.SetFocus
    End If
Next i
End Sub