SQL Server Insert query

Question

I am wondering why I get an exception on the SqlDataReader reader2 = theCommandInsert.ExecuteReader();

This is the code I use :

var conString = ConfigurationManager.ConnectionStrings["LocalSqlServer"];
string strConnString = conString.ConnectionString;
SqlConnection dbConnection = new SqlConnection(strConnString);
dynamic queryString = ("INSERT INTO FOLDERS (Name) VALUES ('" + txtBoxFolderLabel.Text + "' ) ");
int param = CheckBoxList2.SelectedIndex;
param = param + 1;
dynamic queryStringInsert = ("INSERT INTO GROUPS_FOLDERS (Folder_Id, Group_Id) VALUES(IDENT_CURRENT('Folders') , " + param + " )");
SqlCommand theCommand = new SqlCommand(queryString, dbConnection);
SqlCommand theCommandInsert = new SqlCommand(queryStringInsert, dbConnection);
//Connection opening and executing
if (string.IsNullOrEmpty(txtBoxFolderLabel.Text) | CheckBoxList2.SelectedIndex.ToString() == null)
{
 Response.Write("Empty fields !");
}
else if (Functions.IsNumeric(txtBoxFolderLabel.Text))
{
 Response.Write("No numerics !");
}
else
{
 dbConnection.Open();
 SqlDataReader reader = theCommand.ExecuteReader();
 string folderName = txtBoxFolderLabel.Text;
 // Create folder 
 System.IO.Directory.CreateDirectory("C://inetpub//wwwroot//Files//" + folderName);
 dbConnection.Close();
 dbConnection.Open();
 SqlDataReader reader2 = theCommandInsert.ExecuteReader();
 dbConnection.Close();

This is the exception I get:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_GROUPS_FOLDERS_Groups". The conflict occurred in database "9B15719DF48C3E2301D7F965674A6F93_VISUAL STUDIO 2010\PROJECTS\CLIENTPORTAL\APPLICATIONUI\WEBSITE\CLIENTPORTAL\APP_DATA\DATAUI.MDF", table "dbo.Groups", column 'Id'.

I did check in the database, there is no Id duplicated or something ..

Could you help me please ?

Cheers.

• Daniel A. White: WARNING your code is vulnerable to sql injection attacks!
• ashes999: The fix is to use parameters with your SQL command, as mentioned in my answer below.
• Hector Sanchez: why using btw ExecuteReader() instead of ExecuteNonQuery?

Answer 1

You're at risk of a SQL injection attack. You need to use parameters to pass in values from the text box -- not just concatenate the strings.

Is there a reason your query strings are dynamic types?

The problem is with a foreign key violation, not a primary key or uniqueness violation. It seems like your foreign key FK_GROUPS_FOLDERS_Groups doesn't allow duplicating one of the two fields you're inserting.

• Mike Guthrie: Pointing out the SQL injection attack is valuable, but the solution is actually wrong. The error is due to a missing value from the Groups table that is trying to insert into GROUPS_FOLDERS table, rather than a duplicate on any of the fields.
• ashes999: @GuthMD if you're right, then you'll get +1 from me for it :) as long as your answer is the accepted answer, we're good.
• Mike Guthrie: It could still be that he'll get duplicate entry errors on GROUPS_FOLDERS once he fixes the reference error, but since the "conflict occurred in" statement mentions Groups table, the issue here is absent ID from what he is trying to insert. Your initial statement is correct, the problem is a foreign key violation.

Answer 2

Why are you using dynamic for your query string? It's a string, define it as such.

You are also setting yourself up to allow someone to perform a SQL Injection attack on your site.

string queryString = ("INSERT INTO FOLDERS (Name) VALUES ('" + txtBoxFolderLabel.Text + "' ) ");

Is not a good idea, you should be using Sql Parameters.

string queryString = ("INSERT INTO FOLDERS (Name) VALUES (@folder) ");

You can define your parameter after you create your command

theCommand.Parameters.AddWithValue("@folder", txtBoxFolderLabel.Text);

As for your specific error it looks like you have a foreign key defined on that table and you are not meeting the requirements of the foreign key

Answer 3

It's not talking about a duplicated id, but rather a Foreign Key constraint. Would recommend that you look at the GROUPS_FOLDERS table, and you'll probably see a FK constraint on Group_Id pointing at your Groups table. Check that the value you are trying to insert for param is an existing value in Groups.Id

Answer 4

It's not a problem with a duplicate ID; the row you're trying to insert into Groups_Folders is being given a GroupId that does not exist in the database. It looks like

int param = CheckBoxList2.SelectedIndex;
param = param + 1;

is not actually properly generating the GroupId that you want. Try looking over the param value and checking to make sure it's actually what you want it to be.

• Kiwimoisi: Add to use selectedvalue ... It was binded .

Answer 5

1. You are trying to insert a value in database that does not exist in the parent table. It is coming due the forign key constraint on table 2.

Please use a parametrized query to avoid a SQL injection & use parameters to bind your values (it will make your code more readable & safe)