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
them out of sync with COUNT(*).
that, but that approach will not fix the first problem I described above.
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.
DB do the heavy lifting.
Quote:
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\B ANKINGDB.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_pin code,acc_branch,acc_creation_date,customer_Id)
VALUES(@ACCID,@ACCNAME,@ACCNO,@ACCBALANCE,@ACCPINC ODE,@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",AccBalan ce);
cmd.Parameters.AddWithValue("@ACCPINCODE",AccPinco de);
cmd.Parameters.AddWithValue("@ACCBRANCH",Branch);
cmd.Parameters.AddWithValue("@CREATIONDATE",Creati onDate);
>
cmd.Parameters.AddWithValue("@CUSTOMERID",Customer Id);
>
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;
>
}
>
