Re: C# code is only inserting 1 record. - C# Discussion Boards

25-Jun-15 7:15

It should be inserting each row in my GridViewRow. 251 rows. Presently I have only 104 rows.

protected void SubmitButton_Click(object sender, EventArgs e)
           {

               const string Query = "IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData) INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) VALUES ((Select StaffTracking.ID From StaffTracking where StaffTracking.CATWResourceName = @Name), @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)";
               SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);

               using (SqlCommand command = new SqlCommand(Query, conn))
               {
                   conn.Open();
                   command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
                   foreach (GridViewRow row in gvCATW.Rows)
                   {


                       command.Parameters.AddWithValue("@Name", ((TextBox)row.FindControl("txtName")).Text);
                       command.Parameters.AddWithValue("@EstimateHours", ((TextBox)row.FindControl("txtEstimateHours")).Text);
                       command.Parameters.AddWithValue("@EstimateFTE", ((TextBox)row.FindControl("txtEstimateFTE")).Text);
                       command.Parameters.AddWithValue("@ActualHours", ((TextBox)row.FindControl("txtHours")).Text);
                       command.Parameters.AddWithValue("@ActualFTE", ((Label)row.FindControl("Label1")).Text);
                       command.Parameters.AddWithValue("@Comment", ((TextBox)row.FindControl("txtComment")).Text);
                       command.Parameters.AddWithValue("@CommentBy", ((TextBox)row.FindControl("txtCommentBy")).Text);
                       command.Parameters.AddWithValue("@Period", ((TextBox)row.FindControl("txtPeriod")).Text);


                       command.ExecuteNonQuery();
                       command.Parameters.Clear();
                   }
               }

           }

   //protected void SubmitButton_Click(object sender, EventArgs e)

modified 25-Jun-15 13:46pm.

F-ES Sitecore25-Jun-15 7:23

F-ES Sitecore

25-Jun-15 7:23

If you put a breakpoint in the catch block is it being hit?

Dave Kreskowiak25-Jun-15 7:31

Dave Kreskowiak

25-Jun-15 7:31

There are a ton of problem with this code.

The major disfunctional standouts are why are you grabbing a GridViewRow, casting it to a TextBox for each column in the row?? All of these FindControl calls are garbage. Don't do this. The GridViewRow already gives you the data in the row instead of ignoring the row entirely and going to the UI grid to find controls.

You're basically finding the same controls over and over again. You're not going down the rows in the grid. You're getting the same controls in the first row of the grid.

You also seem to be inserting a value for StaffTrackingID but you're doing something awfully fishy to set this value.

The SQL statement you have is garbage. You're using string concatenation to build an SQL statement. This opens you up to SQL Injection attacks and can result in the complete loss of your database. Google for "C# SQL Parameterized queries" to find out what you're supposed to be doing.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

Norris Chappell25-Jun-15 9:12

25-Jun-15 9:12

Okay I took your advise and change my code and Parameterized my queries. I am now able to see all of my rows in the SQL database. However, I got a question for you? I need to have the users supply the period instead of it being generated in the code. Is there a way to do that Inline?

Dave Kreskowiak25-Jun-15 10:20

Dave Kreskowiak

25-Jun-15 10:20

I have no idea since you didn't define exactly what a "period" is, how the user enters such a thing and how it's represented in the database.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

Norris Chappell25-Jun-15 13:05

25-Jun-15 13:05

The Period(yyyy-MM-01) that is when the spreadsheet was produced. So if the spreadsheet was this month it would be 2015-06-01 and so forth. Right now it doesn't matter which spreadsheet I run the period is the same.

Sascha Lefèvre25-Jun-15 14:25

Sascha Lefèvre

25-Jun-15 14:25

Don't store dates as strings.

Imagine Bart Simpson writing that 100 times on the chalkboard Laugh | :laugh:

You can't calculate with strings. Dates as strings are useful when they should be read by humans but not for storing them in the database. Use the most suitable date/time datatype that the database system offers.

Apart from that, it looks like it comes from the GridView, so the user should already be able to supply it?

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

Richard Deeming25-Jun-15 8:47

25-Jun-15 8:47

Norris Chappell wrote:
IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData) INSERT INTO StaffTrackingFTEData (...

When you execute the query the first time, if there are no rows in the table, then the first row will be inserted.

For every subsequent row, that IF NOT EXISTS(...) condition will be false, since there will be at least one row in the table. Therefore, your INSERT statement will not be executed.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Norris Chappell25-Jun-15 9:16

25-Jun-15 9:16

So the "IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData)" is not needed? The reason I put that in was to eliminate duplicates. What is they submitted several times. I don't want the same data in there several times.

modified 25-Jun-15 15:45pm.

Richard Deeming25-Jun-15 9:29

25-Jun-15 9:29

It's not eliminating duplicates - it's just preventing you from having more than one row in the table.

If you want to prevent duplicates, then you'd need to add a WHERE clause to that test to check the columns which you don't want to duplicate.

SQL

IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData WHERE SomeColumn = @SomeParameter And ...)

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Norris Chappell25-Jun-15 11:03

25-Jun-15 11:03

take away IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData) I get " Cannot insert the value NULL into column 'StaffTrackingID', table 'SP2010_EDCStaffing_AppDB.dbo.StaffTrackingFTEData'; column does not allow nulls. INSERT fails.

Sascha Lefèvre25-Jun-15 14:08

Sascha Lefèvre

25-Jun-15 14:08

That just shows that you have an issue with the subquery that should provide the StaffTrackingID. The issue was previously hidden because the INSERT wasn't actually attempted.

On a different note: Adding @StaffTrackingID as output parameter doesn't serve a purpose.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

Norris Chappell25-Jun-15 15:20

25-Jun-15 15:20

command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
If I didn't declare that variable it was giving me an error that I needed to declare it.

Norris Chappell29-Jun-15 3:44

29-Jun-15 3:44

Richard,

I updated my sql query and it is giving me this error: (Must declare the scalar variable "@StaffTrackingID")

protected void SubmitButton_Click(object sender, EventArgs e)
           {

               const string Query = "IF NOT EXISTS (SELECT StaffTrackingID, Period FROM StaffTrackingFTEData where StaffTrackingID = @StaffTrackingID and Period = @Period) INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) VALUES ((Select StaffTracking.ID From StaffTracking where StaffTracking.CATWResourceName = @Name), @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)";
               SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);

               using (SqlCommand command = new SqlCommand(Query, conn))
               {
                   conn.Open();
                   command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
                   foreach (GridViewRow row in gvCATW.Rows)
                   {


                       command.Parameters.AddWithValue("@Name", ((TextBox)row.FindControl("txtName")).Text);
                       command.Parameters.AddWithValue("@EstimateHours", ((TextBox)row.FindControl("txtEstimateHours")).Text);
                       command.Parameters.AddWithValue("@EstimateFTE", ((TextBox)row.FindControl("txtEstimateFTE")).Text);
                       command.Parameters.AddWithValue("@ActualHours", ((TextBox)row.FindControl("txtHours")).Text);
                       command.Parameters.AddWithValue("@ActualFTE", ((Label)row.FindControl("Label1")).Text);
                       command.Parameters.AddWithValue("@Comment", ((TextBox)row.FindControl("txtComment")).Text);
                       command.Parameters.AddWithValue("@CommentBy", ((TextBox)row.FindControl("txtCommentBy")).Text);
                       command.Parameters.AddWithValue("@Period", ((TextBox)row.FindControl("txtPeriod")).Text);


                       command.ExecuteNonQuery();
                       command.Parameters.Clear();
                   }
               }

           }

Richard Deeming29-Jun-15 3:49

29-Jun-15 3:49

That's because you're not passing a parameter called @StaffTrackingID.

Try changing your query to:

const string Query = @"DECLARE @StaffTrackingID int;

SELECT @StaffTrackingID = ID FROM StaffTracking WHERE CATWResourceName = @Name;
If @@ROWCOUNT = 0 RAISERROR('No staff tracking record found for resource ''%s''.', 16, 1, @Name);

IF NOT EXISTS (SELECT 1 FROM StaffTrackingFTEData WHERE StaffTrackingID = @StaffTrackingID And Period = @Period) 
    INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) 
    VALUES (@StaffTrackingID, @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)
";

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Norris Chappell29-Jun-15 4:42

29-Jun-15 4:42

That worked but if no record is found for someone can I just skip it and continue? Your revised sql does exactly what I wanted to do except it gives an error and the users wouldn't know what to do with it. The problem is that we have some employee that are suppliers and others direct and vise versa. this is what I am getting.
No staff tracking record found for resource 'ALISON GREEN'.
Cannot insert the value NULL into column 'StaffTrackingID', table 'SP2010_EDCStaffing_AppDB.dbo.StaffTrackingFTEData'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The Field in StaffTracking to determine if a person is a direct or Supplier is PersonnelResourceType. I changed the above code to include in the where clause SELECT @StaffTrackingID = ID FROM StaffTracking WHERE CATWResourceName = @Name and PersonnelResourceType = 'Supplier'; but still getting that message.

modified 29-Jun-15 10:51am.

Richard Deeming29-Jun-15 4:44

29-Jun-15 4:44

const string Query = @"DECLARE @StaffTrackingID int;

SELECT @StaffTrackingID = ID FROM StaffTracking WHERE CATWResourceName = @Name;

If @@ROWCOUNT != 0 And NOT EXISTS (SELECT 1 FROM StaffTrackingFTEData WHERE StaffTrackingID = @StaffTrackingID And Period = @Period)
    INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period])
    VALUES (@StaffTrackingID, @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)
";

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Norris Chappell29-Jun-15 5:04

29-Jun-15 5:04

Thanks that worked. Really appreciated it.

PIEBALDconsult25-Jun-15 10:10

PIEBALDconsult

25-Jun-15 10:10

Don't Clear and re-Add the Parameters for each row; just set the Values.

Norris Chappell25-Jun-15 10:35

25-Jun-15 10:35

Thanks. Just set the values beforehand?

Sascha Lefèvre25-Jun-15 14:14

Sascha Lefèvre

25-Jun-15 14:14

No - create the SqlParameters just once, before the foreach-loop and capture them in variables. Then inside the foreach-loop assign new values to these instead of recreating new SqlParameters for each loop-iteration.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

Norris Chappell25-Jun-15 15:49

25-Jun-15 15:49

Can you provide a snippet of what you are talking about?
So this is not correct?

<pre lang="c#"> protected void SubmitButton_Click(object sender, EventArgs e)
            {

                const string Query = "INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) VALUES ((Select StaffTracking.ID From StaffTracking where StaffTracking.CATWResourceName = @Name), @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)";
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);

                using (SqlCommand command = new SqlCommand(Query, conn))
                {
                    conn.Open();
                    command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
                    foreach (GridViewRow row in gvCATW.Rows)
                    {


                        command.Parameters.AddWithValue("@Name", ((TextBox)row.FindControl("txtName")).Text); 
.....
.....
.....