将数据插入复合键表 [英] Insert data into compound keys table
问题描述
我正在尝试在编辑帐户页面上执行upsert命令，用户可以在其中编辑其信息。当用户登录时，他们将被重定向到我的帐户页面，该页面将显示他们的所有信息。
编辑帐户页面将访问/插入数据到3个表：成员，地址和地址_成员。
当我执行插入命令时，插入了新数据，但我不知道如何将ID插入Address_Member信息相互链接并最终显示在我的帐户页面上的表格。
我也收到此错误：System.Data.SqlClient.SqlException（0x80131904）：必须声明标量变量@AddressID
我尝试过：
1.会员
I am trying to perform an upsert command on 'edit account' page where users can edit their information. When a user logs in, they will be redirected to a 'My Account' page which will display all their information.
The edit account page will be accessing/inserting data into 3 tables: Member, Address and Address_Member.
When I perform an insert command, new data was inserted but I do not know how to insert the IDs into the Address_Member table where the information are linked with each other and eventually display on my account page.
I also received this error: System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@AddressID"
What I have tried:
1. Member
CREATE TABLE [dbo].[Member]
( [MemberID] INT IDENTITY (1, 1) NOT NULL,
[MemberName] VARCHAR (30) NOT NULL,
[Phone] BIGINT NOT NULL,
[Email] VARCHAR (50) NOT NULL,
[Username] VARCHAR (50) NOT NULL,
[Password] NCHAR (30) NOT NULL,
PRIMARY KEY CLUSTERED ([MemberID] ASC) );
2.地址
2.Address
CREATE TABLE [dbo].[Address]
( [AddressID] INT IDENTITY (1, 1) NOT NULL,
[HouseNumber] VARCHAR (10) NOT NULL,
[AddressLine1] VARCHAR (30) NOT NULL,
[AddressLine2] NCHAR (30) NOT NULL,
[City] NCHAR (20) NOT NULL,
[PostCode] NCHAR (7) NOT NULL,
[AddressType] NVARCHAR (MAX) NOT NULL,
PRIMARY KEY CLUSTERED ([AddressID] ASC) );
3.Address_Member
3.Address_Member
CREATE TABLE [dbo].[Address_Member]
( [MemberID] INT NOT NULL,
[AddressID] INT NOT NULL,
PRIMARY KEY CLUSTERED ([MemberID] ASC, [AddressID] ASC),
CONSTRAINT [FK_Address_Member_Address] FOREIGN KEY ([AddressID]) REFERENCES [dbo].[Address] ([AddressID]), CONSTRAINT [FK_Address_Member_Member] FOREIGN KEY ([MemberID]) REFERENCES [dbo].[Member] ([MemberID]) );
以下是editaccount.aspx.cs中的代码：
The following is the code in editaccount.aspx.cs:
public partial class EditAccount : System.Web.UI.Page
{
public SqlConnection conn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlCommand com;
string str;
conn.Open();
str = "SELECT Address.HouseNumber, Address.AddressLine1, Address.AddressLine2, Address.City, Address.PostCode, Address.AddressType, Address_Member.MemberID, Address_Member.AddressID, Member.MemberName, Member.Phone, Member.Email, Member.Username FROM Address INNER JOIN Address_Member ON Address.AddressID = Address_Member.AddressID RIGHT JOIN Member ON Address_Member.MemberID = Member.MemberID WHERE Member.Email = '" + Session["Email"] + "'";
com = new SqlCommand(str, conn);
SqlDataReader reader = com.ExecuteReader();
if (reader.Read())
{
TxtEName.Text = reader["MemberName"].ToString();
LblEUser.Text = reader["Username"].ToString();
TxtEEmail.Text = reader["Email"].ToString();
TxtEPhone.Text = reader["Phone"].ToString();
TxtEType.Text = reader["AddressType"].ToString();
TxtEHouse.Text = reader["HouseNumber"].ToString();
TxtEA1.Text = reader["AddressLine1"].ToString();
TxtEA2.Text = reader["AddressLine2"].ToString();
TxtECity.Text = reader["City"].ToString();
TxtEPostcode.Text = reader["PostCode"].ToString();
reader.Close();
conn.Close();
}
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
SqlDataAdapter adapter;
DataTable dt = new DataTable();
DataTable dadt = new DataTable();
DataTable da = new DataTable();
try
{
conn.Open();
string query = "SELECT Address.HouseNumber, Address.AddressLine1, Address.AddressLine2, Address.City, Address.PostCode, Address.AddressType, Address_Member.MemberID, Address_Member.AddressID, Member.MemberName, Member.Phone, Member.Email, Member.Username FROM Address INNER JOIN Address_Member ON Address.AddressID = Address_Member.AddressID INNER JOIN Member ON Address_Member.MemberID = Member.MemberID WHERE Member.Email = '" + Session["Email"] + "'";
SqlCommand cmd = new SqlCommand(query, conn);
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
adapter.Fill(dadt);
adapter.Fill(da);
if (dt.Rows.Count > 0)
{
string membercmd = "UPDATE Member SET MemberName=@MemberName,Email=@Email,Phone=@Phone";
SqlCommand Member = new SqlCommand(membercmd, conn);
Member.Parameters.AddWithValue("@MemberName", TxtEName.Text);
Member.Parameters.AddWithValue("@Email", TxtEEmail.Text);
Member.Parameters.AddWithValue("@Phone", TxtEPhone.Text);
Member.ExecuteNonQuery();
}
if (da.Rows.Count > 0)
{
string Addresscmd = "UPDATE Address SET HouseNumber ='" + TxtEHouse.Text + "', AddressLine1 = '" + TxtEA1.Text + "', AddressLine2 = '" + TxtEA2.Text + "', City = '" + TxtECity.Text + "',PostCode = '" + TxtEPostcode.Text + "', AddressType = '" + TxtEType.Text + "'";
SqlCommand Address = new SqlCommand(Addresscmd, conn);
Address.ExecuteNonQuery();
}
else
{
string AddressInsert = "INSERT INTO Address (HouseNumber, AddressLine1, AddressLine2, City, Postcode, AddressType) VALUES (@HouseNumber, @AddressLine1, @AddressLine2, @City, @Postcode, @AddressType)";
SqlCommand Address1 = new SqlCommand(AddressInsert, conn);
Address1.Parameters.AddWithValue("@HouseNumber", TxtEHouse.Text);
Address1.Parameters.AddWithValue("@AddressLine1", TxtEA1.Text);
Address1.Parameters.AddWithValue("@AddressLine2", TxtEA2.Text);
Address1.Parameters.AddWithValue("@City", TxtECity.Text);
Address1.Parameters.AddWithValue("@Postcode", TxtEPostcode.Text);
Address1.Parameters.AddWithValue("@AddressType", TxtEType.Text);
Address1.ExecuteNonQuery();
string AddressMember = "INSERT INTO Address_Member (AddressID,MemberID) VALUES (@AddressID,@MemberID)";
SqlCommand Address_Member = new SqlCommand(AddressMember, conn);
Address_Member.ExecuteNonQuery();
}
conn.Close();
Response.Write("<script>alert('Changes saved')</script>");
Response.Redirect("~/MyAccount.aspx");
}
catch (Exception ex)
{
Response.Write("<script>alert('Changes not saved')</script>" + ex);
conn.Close();
}
}
}
推荐答案
不是你问题的解决方案，但是你有另一个问题。
Not a solution to your question, but another problem you have.
string Addresscmd = "UPDATE Address SET HouseNumber ='" + TxtEHouse.Text + "', AddressLine1 = '" + TxtEA1.Text + "', AddressLine2 = '" + TxtEA2.Text + "', City = '" + TxtECity.Text + "',PostCode = '" + TxtEPostcode.Text + "', AddressType = '" + TxtEType.Text + "'";
为什么使用的代码受制于当 INSERT 受到保护时， UPDATE 中的SQL注入？
永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。
名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。
SQL注入 - 维基百科 [ ^ ]
SQL注入 [ ^ ]
按示例进行SQL注入攻击 [ ^ ]
PHP：SQL注入 - 手册 [ ^ ]
SQL注入预防备忘单 - OWASP [ ^ ]
Why are you using code subject to SQL injection in the UPDATE when the INSERT is protected against ?
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
一些观察结果
- 正如已经指出的那样，总是使用参数。在某些部分，您已经使用它们，因此您知道它们是如何工作的。只需在任何地方使用它们。
- 使用使用块在不再需要时正确处理对象
- 要获得插入地址表后新插入的密钥，可以使用 @@ IDENTITY（Transact-SQL）| Microsoft Docs [ ^ ]
Some observations
- As already pointed out, use parameters, always. In some parts you already use them so you know how they work. Just use them everywhere.
- Useusingblocks to properly dispose the objects when not needed anymore
- To get the newly inserted key after inserting intoAddresstable, you can use @@IDENTITY (Transact-SQL) | Microsoft Docs[^]
这篇关于将数据插入复合键表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
