locked
Duplicate values getting inserted in MYSQL database using ASP.NET Vb RRS feed

  • Question

  • User-1019738895 posted

    Hi,

    I am working with a web application with MySQL as the backend in asp.net.  I need to insert values in a table called ACS. The code is working fine and i am able to insert values. But the problem i am facing is the values are getting stored as dupliate ones. I have enclosed the code that i have used.

    My ACS_Details.aspx page

     

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="ACSPopup.aspx.vb" Inherits="Company" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
        <title ="PopUp"></title>
        <style type="text/css">
            .style2
            {
                text-align: center;
            }
            .style4
            {
                text-align: center;
                width: 5px;
            }
            .style5
            {
                text-align: center;
                width: 5px;
                height: 36px;
            }
            .style6
            {
                text-align: center;
                height: 36px;
            }
            .style7
            {
                text-align: center;
                width: 5px;
                height: 26px;
            }
            .style8
            {
                text-align: center;
                height: 26px;
            }
            .style10
            {
                text-align: center;
                width: 5px;
                height: 30px;
            }
            .style11
            {
                text-align: left;
                width: 135px;
                height: 30px;
            }
            .style12
            {
                width: 174px;
                height: 30px;
            }
            .style13
            {
                height: 193px;
            }
        </style>
    </head>
    <body bgcolor="#666666">
        <form id="form1" runat="server" 
        
        
        style="font-family: Tahoma; font-size: small; font-weight: normal; font-style: normal; color: #FFFFFF" 
        class="style13">
        &nbsp;&nbsp;&nbsp;
        <br />
        <table>
        <tr>
        <td class="style10"></td>
        <td class="style11">ACS Accrediation :</td>
        <td class="style12" style="text-align: left">
            <asp:TextBox ID="txtACS" 
                runat="server" Font-Names="Tahoma" 
                Font-Size="Small" Width="95%"></asp:TextBox></td>
        </tr>
        <tr>
        <td class="style10"></td>
        <td class="style11">Description:</td>
        <td class="style12" style="text-align: left"><asp:TextBox ID="txtDescription" 
                runat="server" Font-Names="Tahoma" 
                Font-Size="Small" TextMode="MultiLine" 
                Width="95%"></asp:TextBox></td>
        </tr>
        <tr>
        <td class="style7"></td>
        <td class="style8" colspan="2">
            <asp:Label ID="lblMessage" runat="server"></asp:Label>
            </td>
        </tr>
        <tr>
        <td class="style5"></td>
        <td class="style6" colspan="2">
        <asp:ImageButton ID="bttnInsertRecord" runat="server" BackColor="#666666" 
            BorderColor="#666666" Height="30px" ImageAlign="Baseline" 
            ImageUrl="~/images/Update_over.gif" 
                onclick="bttnInsertRecord_Click1" Width="74px" />
            </td>
        </tr>
        <tr>
        <td class="style4">&nbsp;</td>
        <td class="style2" colspan="2">&nbsp;</td>
        </tr>
        </table>
    
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <br />
        <br />
        </form>
    </body>
    </html>
    
    
    CodeBehind Page ACS_Details.aspx.vb
     

    Imports System.Data.Odbc
    Imports System.IO
    Imports MySql.Data.MySqlClient

    Partial Class Company
        Inherits System.Web.UI.Page

        Protected Sub bttnInsertRecord_Click1(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles bttnInsertRecord.Click
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("UKGasConnString").ConnectionString

            Using con As New OdbcConnection(ConnString)
                con.Open()
                Using cmd As New OdbcCommand("INSERT INTO tbl_ACS" & "(ACS, Description) VALUES (?,?)", con)
                    cmd.Parameters.Add("@ACS", OdbcType.VarChar, 255).Value = txtACS.Text.Trim()
                    cmd.Parameters.Add("@Description", OdbcType.VarChar, 255).Value = txtDescription.Text.Trim()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    My onnection String:
     <connectionStrings>
      <add name="UKGasConnString" connectionString="Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=db_UKGas;Uid=root;Pwd=password;pooling=false;"
       providerName="MySql.Data.MySqlClient" /> </ connectionStrings>
     

    I am really in need of help in this.  I need to complete this project for my college work. Looking forward for your help in this.

     

    Regards,

    Karthik Venkatraman

    Saturday, November 26, 2011 2:01 AM

All replies

  • User269602965 posted

    In Oracle you would use the MERGE Clause which allows determining if seleccted values are duplicate then UPDATE or DO NOTHING, and if not duplicate then INSERT as a new row.

    MySQL has a similar functionality:

    Your primary key appears to be ACS, and it must be designated as the PRIMARY KEY, ie. No duplicate values allowed.

    MYSQL has the INSERT INTO .... ON DUPLICATE KEY UPDATE clause.

    MySQL also has a REPLACE INTO function where if a duplicate on the primary key is found,

    it drops the old row and inserts the new row.

    http://dev.mysql.com/doc/refman/5.0/en/replace.html
    http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

    You will have to determine which best fits your REFERENTIAL INTEGRITY strategy.

    Sunday, November 27, 2011 10:14 AM
  • User-1019738895 posted

    Hi,

    Yes you are corect. ACS is my primary key. If i am removing that key, the web app inserts the same record twice. if i have the primary key designated i am getting the error page in my wep app. Below is my screenshot of the table.

    MySQL Table Screenshot

    I have several tables in the web app and for every table if i insert, i am getting the same error. please find the screenshot of the error.

    Error

     

    Is this something concerened with the web config?

     

    Sunday, November 27, 2011 9:37 PM
  • User269602965 posted

    Try using one of the replacements SQL clauses recommended above instead of your current INSERT statement to avoid duplicate records.

     

     

     

    Sunday, November 27, 2011 10:34 PM
  • User-1019738895 posted

    Hi,

     

    I tried using the replace into statement too. But its showing me the same error. I then removed the primary key from that table itself. Currently the table does not have any primary key. but still i am facing that error.

    Monday, November 28, 2011 11:10 AM