The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ATM_Accounts"

[weird0]

I have two tables accounts and ATM and i am trying to insert a tuple
in ATM with accountId as foreign key. But even this simple work,I
encounter the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_ATM_Accounts". The conflict occurred in database "G:\DOCUMENTS AND
SETTINGS\ADMINISTRATOR\DESKTOP\VERSION2\APP_DATA\BANKINGDB.MDF", table
"dbo.Accounts", column 'acc_Id'.

please sugggest how can i fix it. I would appreciate if you rewrite
the code for me.
Here is the code:

public static bool InsertIntoAccounts(string AccName,string
AccNo,string AccBalance,string AccPincode,string Branch,string
CreationDate,string CustomerId)
{
string Query="SELECT COUNT(*) FROM ACCOUNTS";
SqlConnection sqlconnection=new
SqlConnection(connectionString);
SqlCommand cmd=new SqlCommand(Query,sqlconnection);
sqlconnection.Open();
int count=(int)cmd.ExecuteScalar();
count++;
AccId = count.ToString();

Query = "INSERT INTO
ACCOUNTS(acc_Id,acc_name,acc_#,acc_balance,acc_pincode,acc_branch,acc_creation_date,customer_Id)
VALUES(@ACCID,@ACCNAME,@ACCNO,@ACCBALANCE,@ACCPINCODE,@ACCBRANCH,@CREATIONDATE,@CUSTOMERID)";
cmd.Connection = sqlconnection;
cmd.CommandText = Query;
cmd.Parameters.AddWithValue("@ACCID",AccId);
cmd.Parameters.AddWithValue("@ACCNAME",AccName);
cmd.Parameters.AddWithValue("@ACCNO",AccNo);
cmd.Parameters.AddWithValue("@ACCBALANCE",AccBalance);
cmd.Parameters.AddWithValue("@ACCPINCODE",AccPincode);
cmd.Parameters.AddWithValue("@ACCBRANCH",Branch);
cmd.Parameters.AddWithValue("@CREATIONDATE",CreationDate);

cmd.Parameters.AddWithValue("@CUSTOMERID",CustomerId);

int result=cmd.ExecuteNonQuery();
sqlconnection.Close();
if (result>0)
return true;
else
return false;
}

static public bool Insert_ATM_Information(string Pincode)
{
string Query2 = "INSERT INTO ATM(atm_id,acc_Id,atm_Status)
VALUES(NEWID(),@ACCID,1)";

SqlConnection sqlconnection = new
SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlconnection;
cmd.CommandText = Query2;
sqlconnection.Open();
/*
cmd.Parameters.AddWithValue("@PINCODE", Pincode);
sqlconnection.Open();
AccId=(string)cmd.ExecuteScalar();
*/
/*
string Query2 = "SELECT COUNT(*) FROM ATM";
cmd.Connection = sqlconnection;
cmd.CommandText = Query2;
int atmId = (int)cmd.ExecuteScalar(); */
cmd.Parameters.AddWithValue("@ACCID", AccId);
int result=cmd.ExecuteNonQuery();
sqlconnection.Close();
if (result > 0)
return true;
else
return false;

}

 

[Mark Rae]

I have two tables accounts and ATM and i am trying to insert a tuple
in ATM with accountId as foreign key. But even this simple work,I
encounter the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_ATM_Accounts". The conflict occurred in database "G:\DOCUMENTS AND
SETTINGS\ADMINISTRATOR\DESKTOP\VERSION2\APP_DATA\BANKINGDB.MDF", table
"dbo.Accounts", column 'acc_Id'.

This means exactly what it says it means... The record you're trying to
insert into the ATM table has a value in the foreign key field which doesn't
exist in the referenced field in the Accounts table...

 

[Bob Grommes]

Dear Weird,

I don't have the brain cells tonight to read through all of this but I
smell trouble in the first few lines. You are generating an AccId based
on the COUNT(*) plus one. This is inherently fragile for several
reasons. If someone else does an insert between the COUNT(*) query and
your subsequent usage, for example, you'll have a duplicate key. If
someone EVER deletes a record in the table, the algorithm you're using
will also fail because now the keys are no longer consecutive, putting
them out of sync with COUNT(*).

You eliminate that last problem by querying MAX(AccId) and incrementing
that, but that approach will not fix the first problem I described above.

I would not be surprised to find that your duplicate key issue is being
caused by this.

This is what identity fields are for in SQL Server. I've never had to
deal with Access DBs, but ISTR there is an autoincrement field type.
Every DB has a mechanism for this; save yourself headaches and use it.
Yes, it's a little more work to get back the generated key, but let the
DB do the heavy lifting.

--Bob

 

[Jon Skeet [C# MVP]]

I have two tables accounts and ATM and i am trying to insert a tuple
in ATM with accountId as foreign key. But even this simple work,I
encounter the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_ATM_Accounts". The conflict occurred in database "G:\DOCUMENTS AND
SETTINGS\ADMINISTRATOR\DESKTOP\VERSION2\APP_DATA\BANKINGDB.MDF", table
"dbo.Accounts", column 'acc_Id'.

please sugggest how can i fix it. I would appreciate if you rewrite
the code for me.

You're trying to use the number of entries in the *atm* table to
specify the *account* ID to use. At least you would if you uncommented
the relevant bit of code in Insert_ATM_Information. That sounds like a
very bad idea to me. As it is though, we've no idea what the value of
AccId will be. I assume it's a member variable somewhere, but you
haven't said what's setting it up. It feels like it should be a
parameter to the call to Insert_ATM_Information, to be honest.

However, as a general point, it seems unlikely that the "current" count
of a table (which could change before you actually get to insert
anything) is a good value for an ID though. Either use autogenerated
IDs, GUIDs or something equally robust.