CODEWITHSUNDEEP
asp.netms-access
user1983152
user1983152

Reputation: 77

Adding duplicate rows of data to my access database

Hey there Im having difficulties adding a single row of data to my database when I submit my form it insert two rows of data to my mdb database any suggestions samples or help will work ill really appreciate it Thanks

Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
    Dim conCoaxis As OleDbConnection
    Dim strInsert As String
    Dim cmdInsert As OleDbCommand


    conCoaxis = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\site\App_Data\sabersolutions.mdb")
    strInsert = "INSERT INTO register (Name, Email, Newsletter) Values (?, ?, ?)"
    cmdInsert = New OleDbCommand(strInsert, conCoaxis)


    cmdInsert.Parameters.Add("@Name", OleDbType.VarWChar, 255).Value = txtName.Text
    cmdInsert.Parameters.Add("@Email", OleDbType.VarWChar, 255).Value = txtEmail.Text
    cmdInsert.Parameters.Add("@Newsletter", OleDbType.Boolean, 1).Value = ckNews.Checked

    Try
        conCoaxis.Open()
        cmdInsert.ExecuteNonQuery()
        conCoaxis.Close()
        Response.Write("Updated Successfully!<p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p>")
    Catch
        conCoaxis.Close()
    End Try

Upvotes: 0

Views: 798

Answers (1)

hollystyles
hollystyles

Reputation: 5039

Your code looks fine. It looks to me more like you have the sub-routine Button3_Click assigned as the handler more than once. For example in the aspx page you have something like

<asp:Button runat="server" ID="Button3" Text="Submit" OnClick="Button3_Click" />

See the OnClick attribute? that wires the click event to call Button3_Click Then somewhere else, possibly in Page_Load in the .vb code-behind, you also have:

AddHandler Button3.Click, AddressOf Me.Button3_Click

So ONE click event will end up calling the same function twice. Get rid of the AddHandler code you don't need to manually wire-up click handlers, it's done for you.

If that's not your problem you may of course be clicking your button twice, this is a well known issue with HTML forms. You can Google many solutions. My preferred solution is to always do a 'SELECT' first to check if the record already exists, or wrap your insert command in a 'IF NOT EXISTS' (I think this works for MS Access, I know it dows for MS Sql Server)

strInsert = "IF NOT EXISTS (SELECT 1 FROM register WHERE Name = @Name AND Email = @Email AND Newsletter = @Newsletter) BEGIN INSERT INTO register (Name, Email, Newsletter) Values ( @Name, @Email, @Newsletter) END"

Another option is:

strInsert = "INSERT INTO register (Name, Email, Newsletter) SELECT TOP 1 @Name, @Email, @Newsletter FROM register WHERE NOT EXISTS (SELECT 1 FROM register WHERE Name = @Name AND Email = @Email AND Newsletter = @Newsletter)"

This latter statement only works if 'register' has at least one record in it, MS Access Jet database requires a table name in the statement, see here for more info. Seriously though, drop Access and use a proper database like SQL Server, then you can use the first statement directly or via a stored procedure a much more professional solution.

Upvotes: 1

Related Questions