Here is my tables,i can update ad,soyad and email but when i try to update telefon it conflicts with the foreign key [userFk],i already have foreign key update on cascade so,i can't figure out the issue here.Thanks in advance
这是我的表格,我可以更新ad、soyad和电子邮件,但是当我试图更新telefon时,它与外键[userFk]冲突,我已经在cascade上有外键更新,所以我无法在这里解决这个问题。谢谢提前
CREATE TABLE [dbo].[ogrenci] (
[ogrenciNo] INT NOT NULL,
[ad] NVARCHAR (20) NOT NULL,
[soyad] NVARCHAR (20) NOT NULL,
[email] NVARCHAR (50) NOT NULL,
[fakulte_no] INT NOT NULL,
[bolum_ad] NVARCHAR (30) NOT NULL,
[bolum_no] INT DEFAULT ((1)) NOT NULL,
[telefon] NVARCHAR (50) DEFAULT ((1)) NOT NULL,
PRIMARY KEY CLUSTERED ([ogrenciNo] ASC),
UNIQUE NONCLUSTERED ([ogrenciNo] ASC),
UNIQUE NONCLUSTERED ([email] ASC),
CONSTRAINT [bolumFk] FOREIGN KEY ([bolum_no]) REFERENCES [dbo].[bolum] ([bolumNo]) ON DELETE CASCADE,
CONSTRAINT [fakulteFk1] FOREIGN KEY ([fakulte_no]) REFERENCES [dbo].[fakulte] ([fakulteId]) ON DELETE CASCADE,
CONSTRAINT [userFk] FOREIGN KEY ([telefon]) REFERENCES [dbo].[loginusers] ([upassword]) ON DELETE CASCADE ON UPDATE CASCADE
);
);
and the second one,
第二个,
CREATE TABLE [dbo].[loginusers] (
[username] NVARCHAR (50) NOT NULL,
[upassword] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([upassword] ASC)
);
);
and here is the update button,
这是更新按钮,
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
if (txtOgrenciNo.Text.Length != 0 && txtAd.Text.Length != 0 && txtSoyad.Text.Length != 0 && txtEmail.Text.Length != 0 && txtTelefon.Text.Length != 0)
{
string query = "UPDATE ogrenci SET ogrenciNo=@ogrenciNoVal,ad=@adVal,soyad=@soyadVal,email=@emailVal,telefon=@telefonVal WHERE ogrenciNo=@ogrenciNoVal";
string query1 = "UPDATE loginusers SET username=@emailVal,upassword=@telefonVal WHERE username=@telefonVal";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
using (SqlCommand cmd = new SqlCommand(query1, connection))
{
connection.Open();
command.Parameters.AddWithValue("@ogrenciNoVal", txtOgrenciNo.Text);
command.Parameters.AddWithValue("@adVal", txtAd.Text);
command.Parameters.AddWithValue("@soyadVal", txtSoyad.Text);
command.Parameters.AddWithValue("@emailVal", txtEmail.Text);
command.Parameters.AddWithValue("@telefonVal", txtTelefon.Text);
cmd.Parameters.AddWithValue("@emailVal", txtEmail.Text);
cmd.Parameters.AddWithValue("@telefonVal", txtTelefon.Text);
command.ExecuteNonQuery();
cmd.ExecuteNonQuery();
gridDoldur();
}
}
else
{
MessageBox.Show("Öğrenci bilgileri boş girilemez.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
2 个解决方案
#1
2
As upassword
column is primarykey in your loginusers
table, if you want to update telefon
on ogrenci
with update cascade property
, so you need to remove ,telefon=@telefonVal
code from update ogrenci
query, like this
因为upassword列是loginusers表中的primarykey,如果您想用update cascade属性更新ogrenci上的telefon,那么您需要从update ogrenci查询中删除telefon=@telefonVal代码,如下所示
string query = "UPDATE ogrenci SET ogrenciNo=@ogrenciNoVal,ad=@adVal,soyad=@soyadVal,email=@emailVal WHERE ogrenciNo=@ogrenciNoVal";
Your second query
will update table ogrenci
too
第二个查询也将更新表ogrenci
WARNING: It will not good if some student will think like this - "If my password is my telephone, so lets try login as another student with his/her telephone number as password and do something" :)
警告:如果有学生这样想,那就不好了——“如果我的密码是我的电话,那么让我们试着以他/她的电话号码作为密码登录,然后做点什么”
EDIT:
编辑:
Your second query
where clause
is wrong I think,
我认为你的第二个问题是,
string query1 = "UPDATE loginusers SET username=@emailVal,upassword=@telefonVal WHERE username=@telefonVal";
It should change to this
它应该变成这个
string query1 = "UPDATE loginusers SET username=@emailVal,upassword=@telefonVal WHERE upassword=@oldtelefonVal ";
#2
0
To resolve your problem you need to insert your row in loginusers (not update) after that you need to update your table ogrenci , in the end you can suppress your loginusers row .
要解决这个问题,需要在loginusers(而不是update)中插入行,然后需要更新表ogrenci,最后可以抑制loginusers行。
#1
2
As upassword
column is primarykey in your loginusers
table, if you want to update telefon
on ogrenci
with update cascade property
, so you need to remove ,telefon=@telefonVal
code from update ogrenci
query, like this
因为upassword列是loginusers表中的primarykey,如果您想用update cascade属性更新ogrenci上的telefon,那么您需要从update ogrenci查询中删除telefon=@telefonVal代码,如下所示
string query = "UPDATE ogrenci SET ogrenciNo=@ogrenciNoVal,ad=@adVal,soyad=@soyadVal,email=@emailVal WHERE ogrenciNo=@ogrenciNoVal";
Your second query
will update table ogrenci
too
第二个查询也将更新表ogrenci
WARNING: It will not good if some student will think like this - "If my password is my telephone, so lets try login as another student with his/her telephone number as password and do something" :)
警告:如果有学生这样想,那就不好了——“如果我的密码是我的电话,那么让我们试着以他/她的电话号码作为密码登录,然后做点什么”
EDIT:
编辑:
Your second query
where clause
is wrong I think,
我认为你的第二个问题是,
string query1 = "UPDATE loginusers SET username=@emailVal,upassword=@telefonVal WHERE username=@telefonVal";
It should change to this
它应该变成这个
string query1 = "UPDATE loginusers SET username=@emailVal,upassword=@telefonVal WHERE upassword=@oldtelefonVal ";
#2
0
To resolve your problem you need to insert your row in loginusers (not update) after that you need to update your table ogrenci , in the end you can suppress your loginusers row .
要解决这个问题,需要在loginusers(而不是update)中插入行,然后需要更新表ogrenci,最后可以抑制loginusers行。