C# code is only inserting a few records.

[Norris Chappell]

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.

[Norris Chappell]

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 Lefevre]

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

[Sascha Lefevre]

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

[Sascha Lefevre]

Don't store dates as strings. Imagine Bart Simpson writing that 100 times on the chalkboard :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

[Norris Chappell]

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 Chappell]

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

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); 

.....
.....
.....

[Norris Chappell]

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 Deeming]

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 Chappell]

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.

[Richard Deeming]

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 Chappell]

Thanks that worked. Really appreciated it.

[PIEBALDconsult]

Yes, that is not corect.