access

[mylogics]

hiii i have a Product.db table in which i have columns: PID:Autonumber ProductID:indexed(yes (no duplicate)) Productname: ProductDescription: ProductPrice: VendorID: m using following code to insert data n update data on button click in my table:

protected void Button4_Click(object sender, EventArgs e)
{
string str1 = "Select MAX(PID) From Product";
OleDbCommand cmd = new OleDbCommand(str1, conn);
OleDbDataReader dr = null;
conn.Open();
string max = cmd.ExecuteScalar().ToString();
if (max != "")
{
max = "PID" + max;

    }
    else
    {
        max = "PID" + 1;
    }
    conn.Close();   
    string str = "Insert Into Product(ProductID,ProductName,ProductDescription,ProductPrice)Values('"+max+"','" + txtprdtname.Text + "','" + txtprdtdescrp.Text + "','" + txtprdtprice.Text + "')";
     cmd = new OleDbCommand(str, conn);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    str = "Select \* From Product";
    cmd = new OleDbCommand(str, conn);
    OleDbDataReader dr1 = null;
    conn.Open();
    dr1 = cmd.ExecuteReader();
    DropDownList2.DataSource = dr1;
    DropDownList2.DataTextField = "ProductID";
    DropDownList2.DataValueField = "ProductID";
    DropDownList2.DataBind();
    conn.Close();

    string str3 = "Select Max(PID) From Product";
    cmd = new OleDbCommand(str3, conn);
    conn.Open();
    string max1 = cmd.ExecuteScalar().ToString();
    string max2 = "PID" + max1;
    conn.Close();
    string str2 = "Update Product Set ProductID='" + max2 + "' Where(PID=" + max1 + ")";
    cmd = new OleDbCommand(str2, conn);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

}

but when i execute it i get following error: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. Plz guide me where m i wrong....

[Coding C]

mylogics wrote:

PID:Autonumber

mylogics wrote:

string str1 = "Select MAX(PID) From Product"; OleDbCommand cmd = new OleDbCommand(str1, conn); OleDbDataReader dr = null; conn.Open(); string max = cmd.ExecuteScalar().ToString(); if (max != "") { max = "PID" + max; } else { max = "PID" + 1; }

You have set the PID:Autonumber in database? If yes then no need to include it in insert/update query. It will be automatically created by database. Just include the other fields in insert/update query except PID. HTH

Coding C# ExciteTemplate

[mylogics]

m not insertin PID in Product table but m inserting ProductID value and it is necessary as it is primary key and cannot be null.