How to insert data into a SQL Server table using asp.net web form where I have foreign key constraint and Identity set to true

Question

This is my Users table:

CREATE TABLE [dbo].[users] 
(
 [user_id] INT IDENTITY (1, 1) NOT NULL,
 [first name] NVARCHAR (50) NULL,
 [last name] NVARCHAR (50) NULL,
 [email] NVARCHAR (MAX) NULL,
 [user_password] NVARCHAR (MAX) NULL,
 [user_salt] NVARCHAR (MAX) NULL,
 PRIMARY KEY CLUSTERED ([user_id] ASC)
);

This is my FeedBack_T table:

CREATE TABLE [dbo].[FeedBack_T] 
(
 [FeedBack_Id] INT IDENTITY (1, 1) NOT NULL,
 [firstname] NVARCHAR (50) NOT NULL,
 [lastname] NVARCHAR (50) NOT NULL,
 [email] NVARCHAR (50) NOT NULL,
 [phone] NVARCHAR (50) NOT NULL,
 [subject] NVARCHAR (50) NOT NULL,
 [feedback_type] NVARCHAR (50) NOT NULL,
 [comments] NVARCHAR (300) NOT NULL,
 [yesOrNo] NVARCHAR (50) NOT NULL,
 [user_id] INT NOT NULL,
 PRIMARY KEY CLUSTERED ([FeedBack_Id] ASC),
 CONSTRAINT [FK_FeedBack_T_Users] 
 FOREIGN KEY ([user_id]) REFERENCES [users]([user_id])
); 

My SQL command

String CS = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
 con.Open();
 SqlCommand insert = new SqlCommand("INSERT INTO FeedBack_T VALUES('" + txtFeedbackName.Text + "','" + txtFeedbackLastName.Text + "','" + txtFeedbackEmail.Text + "','" + txtPhoneNumber.Text + "','" + txtFeedbackSubject.Text + "','" + ddFeedbackType.SelectedItem.Text + "','" + textAreaFeedback.InnerText + "','" + rdbText() + "')", con);
 insert.ExecuteNonQuery();
 insert.Dispose();
}
if (Page.IsValid)
{
 Server.Transfer("feedBackThankyou.aspx");
}

My table has user_id as a foreign key, I am trying to insert all the data in the SQL Server table FeedBack_T but not sure how I can accomplish this.

The data was inserted up until I added the foreign key for the user_id sorry about the sloppy description, I am not sure how else I can explain the issue I am having.

My logic is user can leave feedback after they logged into the website, and the feedback will be stored based on the user_id.

For example, a user named Ariel logged in his user Id in the database is auto-generated based on the (seed, auto-increment). let's say his user id is 1000 and he wants to leave feedback. After he fills out the form the table should look something like this.

This is what it should look like after everything successfully uploaded

[FeedBack_Id] 1,
[firstname] Ariel,
[lastname] max,
[email] [email protected],
[phone] 111-111-1111,
[subject] Something,
[feedback_type] bug report,
[comments] whatever user types,
[yesOrNo] they want to be contacted or not ,
[user_id] 1000, // User Id will be associated with the 
 users Table, I don't know how I can 
 reference this in the feedback form.

• marc_s: SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables
• VDWWD: Possible duplicate of sql insert into asp.net

Answer 1

You should really use parameters in your queries - always, no exception! This will prevent SQL injection - the OWASP #1 vulnerability on the web - and it makes it a lot easier to deal with datatypes like date and others.

So your code should look something like this:

// define connection string and insert query 
string CS = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string insertQuery = "INSERT INTO dbo.FeedBack_T (firstname, lastname, email, phone, subject, feedback_type, comments, yesOrNo, user_id) " +
 "VALUES (@firstname, @lastname, @email, @phone, @subject, @feedback_type, @comments, @yesOrNo, @UserId);";
// create connection and command for the INSERT
using (SqlConnection con = new SqlConnection(CS))
using (SqlCommand cmdInsert = new SqlCommand(insertQuery, con))
{
 // define parameters and set values
 cmdInsert.Parameters.Add("@firstname", SqlDbType.NVarChar, 50).Value = txtFeedbackName.Text;
 cmdInsert.Parameters.Add("@lastname", SqlDbType.NVarChar, 50).Value = txtFeedbackLastName.Text;
 cmdInsert.Parameters.Add("@email", SqlDbType.NVarChar, 50).Value = txtFeedbackEmail.Text;
 cmdInsert.Parameters.Add("@phone", SqlDbType.NVarChar, 50).Value = txtPhoneNumber.Text;
 cmdInsert.Parameters.Add("@subject", SqlDbType.NVarChar, 50).Value = txtFeedbackSubject.Text;
 cmdInsert.Parameters.Add("@feedback_type", SqlDbType.NVarChar, 50).Value = ddFeedbackType.SelectedItem.Text;
 cmdInsert.Parameters.Add("@comments", SqlDbType.NVarChar, 300).Value = textAreaFeedback.Text;
 cmdInsert.Parameters.Add("@yesOrNo", SqlDbType.NVarChar, 50).Value = rdbText();
 // you need to somehow find / determine / pick this value on your form
 cmdInsert.Parameters.Add("@UserId", SqlDbType.Int).Value = 1000;
 // open connection, execute query, close connection
 con.Open();
 cmdInsert.ExecuteNonQuery();
 con.Close();
}
if (Page.IsValid)
{
 Server.Transfer("feedBackThankyou.aspx");
}

Also, I recommend to always explicitly define the columns of the target table you're inserting into - this makes your insert commands more stable and less fragile, if the underlying table changes.

Here: I don't see where you get the value for user_id from - so by not specifying that column in the INSERT INTO command, your insert will still work (it will just not insert any values for user_id, obviously).

• Arifur Rahman: Its giving me error: "Cannot insert the Value Null into column user_id. Also the user_id is a foreign key of user table. I just need to reference it in the query somehow
• marc_s: @ArifurRahman: Ok, so you need some way to pick a user and gets its user_id so you can pass it to the INSERT query .....
• Arifur Rahman: Yes I am generating user ID when users signup using a sign-up form. I am just not sure to insert it into the query