Backend C# Loop with SQL Insert issue

Question

I have a comma delimited textbox and a loop which parses it out and loops to create a series of SQL INSERT statements. However, only the first SQL INSERT statement in [Message_Bridge] is working. The foreach statement is working fine the first time through, but subsequent iteration do not INSERT. No errors are thrown.

dataCommand.CommandText = "INSERT INTO [Messages]([Subject], [Body], [Date_Time], [Originator], [Canceled], [Message_Index]) VALUES(@Subject, @Body, GETDATE(), @Originator, 0, 0); SELECT SCOPE_IDENTITY();";

dataCommand.Parameters.Clear();
dataCommand.Parameters.AddWithValue("@Subject", ComposeSubjectTexbox.Text);
dataCommand.Parameters.AddWithValue("@Body", ComposeBodyTexbox.Text);
dataCommand.Parameters.AddWithValue("@Originator", 506);
var Message_ID = dataCommand.ExecuteScalar();

string[] Recipients = ComposeToTextBox.Text.Split(',');

dataCommand.CommandText = "INSERT INTO [Message_Bridge]([Message_ID], [User_ID]) SELECT TOP 1 @Message_ID, [User_ID] FROM [Users] WHERE [User_Name] = @User_Receiver; ";
dataCommand.Parameters.Clear();
dataCommand.Parameters.AddWithValue("@Message_ID", Message_ID);
dataCommand.Parameters.Add("@User_Receiver", SqlDbType.NVarChar);

foreach (string User_Receiver in Recipients)
{
    dataCommand.Parameters["@User_Receiver"].Value = User_Receiver;
    dataCommand.ExecuteNonQuery();
}

myConnection.Close();

just to be sure, do all of the values for User_Receiver exist in the Users table? — Jeff Ogata
– Jeff Ogata, Commented Dec 25, 2012 at 2:07
Suggest you to use sql profiler to see whether insert command is executed. If not, try to create new instanst for dataCommand in loop — Teddy
– Teddy, Commented Dec 25, 2012 at 2:08
When you .Add the parameter for @User_Receiver try supplying the maximum length as the third argument. — HABO
– HABO, Commented Dec 25, 2012 at 2:50

user1927493 · Accepted Answer · 2012-12-26 20:14:20Z

1

I discovered that my Split() function was adding a space to the end of each User_Receiver. This caused only the first INSERT function to work and the subsequent ones to not go thru. The INSERT-SELECT function was set up correctly but the data I fed into it wasn't correct. Thank you for the help guys.

answered Dec 26, 2012 at 20:14

user1927493

213 bronze badges

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

Comments

EKS · Accepted Answer · 2012-12-25 03:18:52Z

0

Seems like you should use bulkinserter, it will be quicker and is made for bulk inserting :)

answered Dec 25, 2012 at 3:18

EKS

5,6317 gold badges48 silver badges62 bronze badges

1 Comment

user1927493 Over a year ago

I tried Initializing he data command inside of the loop, but no effect. I also explicitly stated the maximum length in the AddWithValue() functions; no effect. I would like to try bulkinserter, but I know that it's used for transactions where there are 1000's of rows whereas I only have about 10. Shouldn't what I have work? Is this a bug?

Collectives™ on Stack Overflow

Backend C# Loop with SQL Insert issue

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related