|
|
private void button1_Click(object sender, EventArgs e) {
using (XmlWriter Writer = XmlWriter.Create(Application.StartupPath + "\\Designs\\" + StyleName + ".xml", XmlSettings)) {
Writer.WriteStartElement("Settings");
PropertyDescriptorCollection PropertyCollection = TypeDescriptor.GetProperties(propertyGrid2.SelectedObject);
foreach (PropertyDescriptor Prop in PropertyCollection) {
Writer.WriteStartElement(Prop.Name);
Writer.WriteString(Prop.GetValue(propertyGrid2.SelectedObject).ToString());
Writer.WriteEndElement();
}
}
}
In xml file the color was save like: <boxbackcolor>Color [A=255, R=255, G=128, B=0]
I try to read the color using ReadString() but this return string not color
how can I convert string to color?
The second question how can I save it as integer like VB.net Color.ToArgb()?
|
|
|
|
|
|
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();
}
}
}
modified 25-Jun-15 13:46pm.
|
|
|
|
|
If you put a breakpoint in the catch block is it being hit?
|
|
|
|
|
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.
|
|
|
|
|
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?
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Don't store dates as strings.
Imagine Bart Simpson writing that 100 times on the chalkboard 
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 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
|
|
|
|
|
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.
|
|
|
|
|
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.
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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();
}
}
}
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Thanks that worked. Really appreciated it.
|
|
|
|
|
Don't Clear and re-Add the Parameters for each row; just set the Values.
|
|
|
|
|
Thanks. Just set the values beforehand?
|
|
|
|
|
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
|
|
|
|
|
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);
.....
.....
.....
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
