Hi Team,

I understand that there are numerous number of Articles with the following Title and I should have gone through them before posting but believe me, I've done it all. Either way, I'm not getting to a solution. Hope I get some help here..

So Here is the thing.

I'm working on this programme and I want to restrict a user from Submitting the Answers more than once.

Here's my code below, Kindly Help

Note:- This code Produces an error (Http://Tinypic.com/r/rbi4u9/8)

Imports System.Data.OleDb
Imports System.IO

Public Class QSheet

    Private Sub QSheet_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        FrmMain.Show()

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        '----------------------------------------------------------------------

            '-------------------------------------------------------------------
        Try


            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\pant.16\Desktop\Projects\Microsoft - MSV\Microsoft - MSV\bin\Debug\MSVDB.accdb")
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            cn.Open()
            ''
            ''____________________________________________
            ''
            If MsgBox("Confirm Submit?", MsgBoxStyle.YesNo Or MsgBoxStyle.Question) = MsgBoxResult.Yes Then

                Dim cmd1 As New OleDb.OleDbCommand("INSERT INTO [" + Label11.Text + "](EmpID,Answer1,Answer2,Answer3,Answer4,Answer5,Answer6,Answer7,Answer8,Answer9,Answer10) values([@EmpID],[@ans1],[@ans2],[@ans3],[@ans4],[@ans5],[@ans6],[@ans7],[@ans8],[@ans9],[@ans10]) WHERE NOT EXISTS (SELECT EmpID FROM [" + Label11.Text + "] WHERE EmpID = [" + Login.txtEmpID.Text + "])", cn)

                ' EmpID
                Dim EmpID As OleDbParameter = New OleDbParameter("@EmpID", OleDbType.VarWChar, 15)
                EmpID.Value = Login.txtEmpID.Text.ToString()
                cmd1.Parameters.Add(EmpID)

                'Answer1
                Dim ans1 As OleDbParameter = New OleDbParameter("@ans1", OleDbType.VarWChar, 50)
                ans1.Value = ComboBox1.Text.ToString()
                cmd1.Parameters.Add(ans1)

                'Answer2
                Dim ans2 As OleDbParameter = New OleDbParameter("@ans2", OleDbType.VarWChar, 50)
                ans2.Value = ComboBox2.Text.ToString()
                cmd1.Parameters.Add(ans2)

                'Answer3
                Dim ans3 As OleDbParameter = New OleDbParameter("@ans3", OleDbType.VarWChar, 50)
                ans3.Value = ComboBox3.Text.ToString()
                cmd1.Parameters.Add(ans3)

                'Answer4
                Dim ans4 As OleDbParameter = New OleDbParameter("@ans4", OleDbType.VarWChar, 50)
                ans4.Value = ComboBox4.Text.ToString()
                cmd1.Parameters.Add(ans4)

                'Answer5
                Dim ans5 As OleDbParameter = New OleDbParameter("@ans5", OleDbType.VarWChar, 50)
                ans5.Value = ComboBox5.Text.ToString()
                cmd1.Parameters.Add(ans5)

                'Answer6
                Dim ans6 As OleDbParameter = New OleDbParameter("@ans6", OleDbType.VarWChar, 50)
                ans6.Value = ComboBox6.Text.ToString()
                cmd1.Parameters.Add(ans6)

                'Answer7
                Dim ans7 As OleDbParameter = New OleDbParameter("@ans7", OleDbType.VarWChar, 50)
                ans7.Value = ComboBox7.Text.ToString()
                cmd1.Parameters.Add(ans7)

                'Answer8
                Dim ans8 As OleDbParameter = New OleDbParameter("@ans8", OleDbType.VarWChar, 50)
                ans8.Value = ComboBox8.Text.ToString()
                cmd1.Parameters.Add(ans8)

                'Answer9
                Dim ans9 As OleDbParameter = New OleDbParameter("@ans9", OleDbType.VarWChar, 50)
                ans9.Value = ComboBox9.Text.ToString()
                cmd1.Parameters.Add(ans9)

                'Answer10
                Dim ans10 As OleDbParameter = New OleDbParameter("@ans10", OleDbType.VarWChar, 50)
                ans10.Value = ComboBox10.Text.ToString()
                cmd1.Parameters.Add(ans10)

                If cmd1.ExecuteNonQuery() Then
                    cn.Close()
                    MsgBox("Your Answers Have Been Submitted Successfully", MsgBoxStyle.Information, "Information Saved")
                    Me.Close()
                Else
                    MsgBox("Your Answers Couldn't be Submitted", MsgBoxStyle.Critical, "Save Failed")
                    Return
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message(), MsgBoxStyle.Critical, "Error")
            Exit Sub
        End Try
    End Sub

    Private Sub QSheet_Load(sender As Object, e As EventArgs) Handles Me.Load
        Label11.Text = Login.inval.Text

    End Sub

    Sub comboboxdefaultvalues()
        ComboBox1.Text = "Select an Option"
        ComboBox2.Text = "Select an Option"
        ComboBox3.Text = "Select an Option"
        ComboBox4.Text = "Select an Option"
        ComboBox5.Text = "Select an Option"
        ComboBox6.Text = "Select an Option"
        ComboBox7.Text = "Select an Option"
        ComboBox8.Text = "Select an Option"
        ComboBox9.Text = "Select an Option"
        ComboBox10.Text = "Select an Option"
    End Sub

End Class

Please somebody help me get to a final resolution.

Recommended Answers

All 12 Replies

There's a short way to do it, change button1_click to this:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\pant.16\Desktop\Projects\Microsoft - MSV\Microsoft - MSV\bin\Debug\MSVDB.accdb")
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            cn.Open()
            ''
            ''____________________________________________
            ''
            If MsgBox("Confirm Submit?", MsgBoxStyle.YesNo Or MsgBoxStyle.Question) = MsgBoxResult.Yes Then
                ' of course label11 should be your table name in the access file
                ' the command is like this: (insert into Mytable (columnName1,columnName2) values (MyInput1,MyInput2), cn)
                Dim cmd1 As New OleDb.OleDbCommand("INSERT INTO [" + Label11.Text + "](EmpID,Answer1,Answer2,Answer3,Answer4,Answer5,Answer6,Answer7,Answer8,Answer9,Answer10) values('" & Login.txtEmpID.Text & "','" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & ComboBox3.Text & "','" & ComboBox4.Text & "','" & ComboBox5.Text & "','" & ComboBox6.Text & "','" & ComboBox7.Text & "','" & ComboBox8.Text & "','" & ComboBox9.Text & "','" & ComboBox10.Text & "')", cn)
                cmd1.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message(), MsgBoxStyle.Critical, "Error")
            Exit Sub
        End Try
    End Sub

Good luck

VOILA!
This is amazing. I didn't know that. Now you know I'm a noob. Here, A Cookie for You :)

But Sir, That did not resolve my issue. I am still able to insert Duplicate Entries upon Submission. Any Help with that?

oh ok hah...just disable the submit button
cmd1.ExecuteNonQuery
Button1.enabled = false

I totally agree to that. However, It is not limited to this form. It's an entire programme that will be used by more than 500 people. How will I ensure if a user has submitted it before, once he logs back in to this application(Just Saying).

So, Can you make it work this way..
Upon clicking the Submit Button, there will be a verificaton check in the database for the EmpID for an existing record(If found, will return an error else will Submit the Answers).

I'm so sorry to be this blunt on asking help but Nobody is a born pro ^_^. I'll learn someday aswell*(I hope)* hahaha

of course your way works..just give me a sec.

ok this is what i came up with for now..should work though

        Dim cmd2 As New System.Data.OleDb.OleDbDataAdapter()
        cmd2 = New System.Data.OleDb.OleDbDataAdapter("select * from [" + Label11.Text + "] ", cn)
        Dim DtSet As System.Data.DataSet
        DtSet = New System.Data.DataSet
        cmd2.Fill(DtSet)
        For i As Integer = 0 To DtSet.Tables(0).Rows.Count - 2
            '' i'm guessing that your EmpID is your first column in your database
            If DtSet.Tables(0).Rows(i).Item(0).ToString = Login.txtEmpID.Text Then
                Button1.Enabled = False
            End If
        Next

Sure, Take your time. Just in case if you need more info or code on the project i'm preparing so far. Let me know :D

hmm.. you can make this even faster by selecting only your id column.
btw this "select * " means select everything

Yes!! It Works.

Just one last problem. It happens to work but before it actually disables the Submit button, I am able to save duplicate entries twice nomatter what..Any Ideas?

Edit:- And Yes, one more thing. I saved the snippet under the Form Load event.
If possible, please have a Live chat with me on this site or Skype(Postvia)

No put the check code before "cmd1.ExecuteNonQuery" in the button1_click event and change this:

            If DtSet.Tables(0).Rows(i).Item(0).ToString = Login.txtEmpID.Text Then
                Button1.Enabled = False
                Exit sub
            End If

Thank You for all your efforts. I appriciate your Patience and kindness.

All my questiones are answered.
Much Thanks!
-Mudit

Welcome :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.