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.

WARNING your code is vulnerable to sql injection attacks! — Daniel A. White
– Daniel A. White, Commented Mar 6, 2012 at 17:45
The fix is to use parameters with your SQL command, as mentioned in my answer below. — ashes999
– ashes999, Commented Mar 6, 2012 at 17:48
why using btw ExecuteReader() instead of ExecuteNonQuery? — Hector Sanchez
– Hector Sanchez, Commented Mar 6, 2012 at 21:11

ashes999 · Accepted Answer · 2012-03-06 17:47:38Z

5

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.

answered Mar 6, 2012 at 17:47

ashes999

10.2k19 gold badges81 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Guthrie Over a year ago

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 Over a year ago

@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 Over a year ago

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.

JCherryhomes · Accepted Answer · 2012-03-06 17:50:54Z

4

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

answered Mar 6, 2012 at 17:50

JCherryhomes

4264 silver badges12 bronze badges

Comments

Mike Guthrie · Accepted Answer · 2012-03-06 17:47:54Z

4

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

answered Mar 6, 2012 at 17:47

Mike Guthrie

4,0592 gold badges27 silver badges48 bronze badges

Comments

eouw0o83hf · Accepted Answer · 2012-03-06 17:46:47Z

3

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.

answered Mar 6, 2012 at 17:46

eouw0o83hf

9,6085 gold badges57 silver badges78 bronze badges

1 Comment

Kiwimoisi Over a year ago

Add to use selectedvalue ... It was binded .

Zo Has · Accepted Answer · 2012-03-06 18:09:58Z

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)

answered Mar 6, 2012 at 18:09

Zo Has

13.1k24 gold badges93 silver badges151 bronze badges

Collectives™ on Stack Overflow

SQL Server Insert query

5 Answers 5

3 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related