C# code is only inserting a few records. - C# Discussion Boards

Visual Studio Express 2013 Installer Package

26-Jun-15 17:33

Nope. Keyloggers are illegal in most places and there's no legitimate use for them. We're not in the habit of helping you break the law.

A guide to posting questions on CodeProject

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

Dave Kreskowiak

Member 932620926-Jun-15 15:10

Member 9326209

26-Jun-15 15:10

Hi all!

I have created a Windows Desktop application, using VS Express 2013, C#, and would like to use an installer package (i.e. msi, etc.) to distribute it to a few of my friends via USB drive where they can copy the package from the USB to their desktop or other place, run the package installer to install all necessary files that are required and executable, and be able to then run the application. I don't want to use a web installer which VS Publish seems to be for. I have never created or used an installation package for any of my applications before so this is very new to me.

My question is, how do I go about creating an installer package as I described above or is their some software out there that is free I could use? I've tried some software available online but apparently I must have a paid version of Visual Studio (i.e. professional, ultimate) to use it. Or another option I would be open to is to create my own installer but would need a step by step tutorial since this is a foreign topic to me.

I would greatly appreciate any help you could provide. By the way, Code Project is an excellent tool/source of information! Great job to the developers of this site and all who contribute to it!!!

modified 26-Jun-15 21:42pm.

Re: Visual Studio Express 2013 Installer Package

Gerry Schmitz26-Jun-15 16:43

Gerry Schmitz

26-Jun-15 16:43

Inno Seup:

http://www.jrsoftware.org/isinfo.php[^]

XmlWriter WriteString Color

haithomy25-Jun-15 7:37

haithomy

25-Jun-15 7:37

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()?

Re: XmlWriter WriteString Color

Dave Kreskowiak25-Jun-15 10:22

C# code is only inserting a few records.

25-Jun-15 10:22

A simple Google search for "C# serializable color[^]" comes up with lots of possibilities.

A guide to posting questions on CodeProject

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

Dave Kreskowiak

Norris Chappell25-Jun-15 7:15

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

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

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