7 Replies - 356 Views - Last Post: 13 February 2013 - 05:43 PM Rate Topic: -----

#1 daimegaera  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 30
  • Joined: 17-September 10

Prompt a Msgbox if there are same entries

Posted 11 February 2013 - 08:47 AM

How can I make my program prompt a warning message box saying that the data already exist?. For example if I enter the same employee Id a message box will prompt saying the id already exist. And where should i put the code.

Here is my code for the save button

 
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Call disControl()
        'Validation
        If invalidSaveEntry() = True Then
            Call enaControl()
            Exit Sub
        End If

        If MsgBox("Are you sure you want to save this record?", CType(MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2 + MsgBoxStyle.Question, MsgBoxStyle), "Save") = MsgBoxResult.Yes Then
            'Start Save
            SQL = "Insert into tbl_info(fldid," & _
                 "fldname," & _
                 "fldate," & _
                 "fldateemployed," & _
                 "fldposition," & _
                 "fldepartment," & _
                 "fldage," & _
                 "fldsex," & _
                 "fldweight," & _
                 "fldcitizenship," & _
                 "fldateofbirth," & _
                 "fldplaceofbirth," & _
                 "fldcivilstatus," & _
                 "fldreligion," & _
                 "fldtelnumber," & _
                 "fldrplace," & _
                 "fldpplace," & _
                 "fldsssnumber," & _
                 "fldtinnumber," & _
                 "fldphnumber," & _
                 "fldpibignumber," & _
                 "fldelementary," & _
                 "fldfromto1," & _
                 "fldhighSchool," & _
                 "fldfromto2," & _
                 "fldcollege," & _
                 "fldfromto3," & _
                 "fldfthname," & _
                 "fldfage," & _
                 "fldfoccupation," & _
                 "fldmthname," & _
                 "fldmage," & _
                 "fldmoccupation," & _
                 "fldsname," & _
                 "fldsage," & _
                 "fldsoccupation," & _
                 "fldwefromto1," & _
                 "fldweposition1," & _
                 "fldweadd1," & _
                 "fldwesalary1," & _
                 "fldwefromto2," & _
                 "fldweposition2," & _
                 "fldweadd2," & _
                 "fldwesalary2," & _
                 "fldwefromto3," & _
                 "fldweposition3," & _
                 "fldweadd3," & _
                 "fldwesalary3," & _
                 "fldwefromto4," & _
                 "fldweposition4," & _
                 "fldweadd4," & _
                 "fldwesalary4," & _
                 "fldnename," & _
                 "fldnerelationship," & _
                 "fldneadd," & _
                 "fldnetel," & _
                 "photo) values('" & Me.txtID.Text.Trim & "'," & _
                  "'" & Me.txtFname.Text.Trim & "'," & _
                  "'" & Me.dtpdate.Text.Trim & "'," & _
                  "'" & Me.dtpdateemployed.Text.Trim & "'," & _
                  "'" & Me.txtposition.Text.Trim & "'," & _
                  "'" & Me.CbDepartment.Text.Trim & "'," & _
                  "'" & Me.txtAge.Text.Trim & "'," & _
                  "'" & Me.CbSex.Text.Trim & "'," & _
                  "'" & Me.TxtWeight.Text.Trim & "'," & _
                  "'" & Me.TxtCitizenship.Text.Trim & "'," & _
                  "'" & Me.DtDateofBirth.Text.Trim & "'," & _
                  "'" & Me.TxtPlaceofBirth.Text.Trim & "'," & _
                  "'" & Me.TxtCStatus.Text.Trim & "'," & _
                  "'" & Me.TxtReligion.Text.Trim & "'," & _
                  "'" & Me.TxtTelNo.Text.Trim & "'," & _
                  "'" & Me.TxtResidenceAddress.Text.Trim & "'," & _
                  "'" & Me.TxtProvincialAddress.Text.Trim & "'," & _
                  "'" & Me.TxtSSSNo.Text.Trim & "'," & _
                  "'" & Me.TxtTIN.Text.Trim & "'," & _
                  "'" & Me.TxtPhilhealthNo.Text.Trim & "'," & _
                  "'" & Me.TxtPagibig.Text.Trim & "'," & _
                  "'" & Me.TxtElementary.Text.Trim & "'," & _
                  "'" & Me.TxtFromTo1.Text.Trim & "'," & _
                  "'" & Me.TxtHighSchool.Text.Trim & "'," & _
                  "'" & Me.TxtFromTo2.Text.Trim & "'," & _
                  "'" & Me.TxtCollege.Text.Trim & "'," & _
                  "'" & Me.TxtFromTo3.Text.Trim & "'," & _
                  "'" & Me.TxtFthname.Text.Trim & "'," & _
                  "'" & Me.TxtFAge.Text.Trim & "'," & _
                  "'" & Me.TxtFOccupation.Text.Trim & "'," & _
                  "'" & Me.TxtMthname.Text.Trim & "'," & _
                  "'" & Me.TxtMAge.Text.Trim & "'," & _
                  "'" & Me.TxtMOccupation.Text.Trim & "'," & _
                  "'" & Me.TxtSName.Text.Trim & "'," & _
                  "'" & Me.TxtSAge.Text.Trim & "'," & _
                  "'" & Me.TxtSOccupation.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Fromto1.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Position1.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Add1.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Salary1.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Fromto2.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Position2.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Add2.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Salary2.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Fromto3.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Position3.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Add3.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Salary3.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Fromto4.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Position4.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Add4.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Salary4.Text.Trim & "'," & _
                  "'" & Me.TxtNE_Name.Text.Trim & "'," & _
                  "'" & Me.TxtNE_Relationship.Text.Trim & "'," & _
                  "'" & Me.TxtNE_Add.Text.Trim & "'," & _
                  "'" & Me.TxtNE_Tel.Text.Trim & "'," & _
                  "'" & Me.PictureBox1.Image.PixelFormat & "')"
            Call execComDB(SQL)     'Execute the insert query
            Me.Close()    'Close the form
            '*** Display new record
            SQL = "Select * from tbl_info " & _
                  "order by fldid desc"
            Call frmMain.dispRec(SQL)
            Call frmMain.dispRecCount() 'Display record count
            '--- End of displaying new record
        End If
    End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: Prompt a Msgbox if there are same entries

#2 TheKirk  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 58
  • Joined: 10-February 13

Re: Prompt a Msgbox if there are same entries

Posted 11 February 2013 - 08:52 AM

You should first make an query (SQL) to the database which would retrieve the row you are trying to insert, if 0 rows are returned, the data does not exists, if 1 row or more is returned then the data already exists, here's an example query:

SELECT * FROM tbl_info WHERE fldname='name you are trying to insert

Was This Post Helpful? 0
  • +
  • -

#3 daimegaera  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 30
  • Joined: 17-September 10

Re: Prompt a Msgbox if there are same entries

Posted 11 February 2013 - 09:40 AM

@The Kirk
I know may sound stupid but I can't really get what you mean because I'm really not good at programming, though I tried putting this code that I've read and so far the message box appear when I tried saving same id number. But the problem now is the program freezes after the message box has been clicked.

    Try

   
  Catch ex As Exception
            MsgBox("id number already exist")



        End Try


i put the "try" code before the Call disControl() line and "catch" code after the end if line..
Was This Post Helpful? 0
  • +
  • -

#4 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,007
  • Joined: 12-January 10

Re: Prompt a Msgbox if there are same entries

Posted 11 February 2013 - 09:47 AM

jeeze that has to be one of the biggest tables i have ever seen. You should really break that up into smaller tables for better performace. as thekirk said, you need to search through existing data with an sql query to make sure it doesnt already exist.
Was This Post Helpful? 0
  • +
  • -

#5 TheKirk  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 58
  • Joined: 10-February 13

Re: Prompt a Msgbox if there are same entries

Posted 11 February 2013 - 10:52 AM

Appearently he has the id column as the primary key, which should always be unique, when he tries to insert something with the same ID it should throw an error, which it does. However a more complete answer:

 
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        ' Build SQL query
        Dim SQLQ as String = "SELECT * FROM tbl_info WHERE fldid='" & Me.txtID.Text.Trim & "'"
        
        ' Execute SQL query
        Call execComDB(SQL)

        ' Get record count and check if it's above or under 0 (DO YOURSELF)


        Call disControl()
        'Validation
        If invalidSaveEntry() = True Then
            Call enaControl()
            Exit Sub
        End If

        If MsgBox("Are you sure you want to save this record?", CType(MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2 + MsgBoxStyle.Question, MsgBoxStyle), "Save") = MsgBoxResult.Yes Then
            'Start Save
            SQL = "Insert into tbl_info(fldid," & _
                 "fldname," & _
                 "fldate," & _
                 "fldateemployed," & _
                 "fldposition," & _
                 "fldepartment," & _
                 "fldage," & _
                 "fldsex," & _
                 "fldweight," & _
                 "fldcitizenship," & _
                 "fldateofbirth," & _
                 "fldplaceofbirth," & _
                 "fldcivilstatus," & _
                 "fldreligion," & _
                 "fldtelnumber," & _
                 "fldrplace," & _
                 "fldpplace," & _
                 "fldsssnumber," & _
                 "fldtinnumber," & _
                 "fldphnumber," & _
                 "fldpibignumber," & _
                 "fldelementary," & _
                 "fldfromto1," & _
                 "fldhighSchool," & _
                 "fldfromto2," & _
                 "fldcollege," & _
                 "fldfromto3," & _
                 "fldfthname," & _
                 "fldfage," & _
                 "fldfoccupation," & _
                 "fldmthname," & _
                 "fldmage," & _
                 "fldmoccupation," & _
                 "fldsname," & _
                 "fldsage," & _
                 "fldsoccupation," & _
                 "fldwefromto1," & _
                 "fldweposition1," & _
                 "fldweadd1," & _
                 "fldwesalary1," & _
                 "fldwefromto2," & _
                 "fldweposition2," & _
                 "fldweadd2," & _
                 "fldwesalary2," & _
                 "fldwefromto3," & _
                 "fldweposition3," & _
                 "fldweadd3," & _
                 "fldwesalary3," & _
                 "fldwefromto4," & _
                 "fldweposition4," & _
                 "fldweadd4," & _
                 "fldwesalary4," & _
                 "fldnename," & _
                 "fldnerelationship," & _
                 "fldneadd," & _
                 "fldnetel," & _
                 "photo) values('" & Me.txtID.Text.Trim & "'," & _
                  "'" & Me.txtFname.Text.Trim & "'," & _
                  "'" & Me.dtpdate.Text.Trim & "'," & _
                  "'" & Me.dtpdateemployed.Text.Trim & "'," & _
                  "'" & Me.txtposition.Text.Trim & "'," & _
                  "'" & Me.CbDepartment.Text.Trim & "'," & _
                  "'" & Me.txtAge.Text.Trim & "'," & _
                  "'" & Me.CbSex.Text.Trim & "'," & _
                  "'" & Me.TxtWeight.Text.Trim & "'," & _
                  "'" & Me.TxtCitizenship.Text.Trim & "'," & _
                  "'" & Me.DtDateofBirth.Text.Trim & "'," & _
                  "'" & Me.TxtPlaceofBirth.Text.Trim & "'," & _
                  "'" & Me.TxtCStatus.Text.Trim & "'," & _
                  "'" & Me.TxtReligion.Text.Trim & "'," & _
                  "'" & Me.TxtTelNo.Text.Trim & "'," & _
                  "'" & Me.TxtResidenceAddress.Text.Trim & "'," & _
                  "'" & Me.TxtProvincialAddress.Text.Trim & "'," & _
                  "'" & Me.TxtSSSNo.Text.Trim & "'," & _
                  "'" & Me.TxtTIN.Text.Trim & "'," & _
                  "'" & Me.TxtPhilhealthNo.Text.Trim & "'," & _
                  "'" & Me.TxtPagibig.Text.Trim & "'," & _
                  "'" & Me.TxtElementary.Text.Trim & "'," & _
                  "'" & Me.TxtFromTo1.Text.Trim & "'," & _
                  "'" & Me.TxtHighSchool.Text.Trim & "'," & _
                  "'" & Me.TxtFromTo2.Text.Trim & "'," & _
                  "'" & Me.TxtCollege.Text.Trim & "'," & _
                  "'" & Me.TxtFromTo3.Text.Trim & "'," & _
                  "'" & Me.TxtFthname.Text.Trim & "'," & _
                  "'" & Me.TxtFAge.Text.Trim & "'," & _
                  "'" & Me.TxtFOccupation.Text.Trim & "'," & _
                  "'" & Me.TxtMthname.Text.Trim & "'," & _
                  "'" & Me.TxtMAge.Text.Trim & "'," & _
                  "'" & Me.TxtMOccupation.Text.Trim & "'," & _
                  "'" & Me.TxtSName.Text.Trim & "'," & _
                  "'" & Me.TxtSAge.Text.Trim & "'," & _
                  "'" & Me.TxtSOccupation.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Fromto1.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Position1.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Add1.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Salary1.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Fromto2.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Position2.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Add2.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Salary2.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Fromto3.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Position3.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Add3.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Salary3.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Fromto4.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Position4.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Add4.Text.Trim & "'," & _
                  "'" & Me.TxtWE_Salary4.Text.Trim & "'," & _
                  "'" & Me.TxtNE_Name.Text.Trim & "'," & _
                  "'" & Me.TxtNE_Relationship.Text.Trim & "'," & _
                  "'" & Me.TxtNE_Add.Text.Trim & "'," & _
                  "'" & Me.TxtNE_Tel.Text.Trim & "'," & _
                  "'" & Me.PictureBox1.Image.PixelFormat & "')"
            Call execComDB(SQL)     'Execute the insert query
            Me.Close()    'Close the form
            '*** Display new record
            SQL = "Select * from tbl_info " & _
                  "order by fldid desc"
            Call frmMain.dispRec(SQL)
            Call frmMain.dispRecCount() 'Display record count
            '--- End of displaying new record
        End If
    End Sub


Was This Post Helpful? 0
  • +
  • -

#6 daimegaera  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 30
  • Joined: 17-September 10

Re: Prompt a Msgbox if there are same entries

Posted 12 February 2013 - 08:04 AM

i have not tried the code you had given me yet but still thanks in advance..
Was This Post Helpful? 0
  • +
  • -

#7 TheKirk  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 58
  • Joined: 10-February 13

Re: Prompt a Msgbox if there are same entries

Posted 13 February 2013 - 02:04 AM

Let us know if you tried it!
Was This Post Helpful? 0
  • +
  • -

#8 tycos  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 53
  • Joined: 12-February 13

Re: Prompt a Msgbox if there are same entries

Posted 13 February 2013 - 05:43 PM

Your problem: Do not allow duplicate records
Solution: Find Employee by ID before save or make them search before they enter a new record

Create a search form
Enter employee ID:
If Exists then load edit page or show result
Else if not exists show new employee page

What I would not want to do is fill out a massive form for the system to say, already exists, I would be annoyed!

What do you think of this solution, if you think this might work for you then maybe I can help you create a data class which you can use for database calls and also explain why you should parameterize you SQL
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1