Here is the scenario; imagine 25 jugs that can be filled with water, in any order and at any time with an indicator showing the amount of water in each jug.
The application; to generate a menu with 25 links (steps) and a corresponding value (progress).
After clicking on a step, the user can input data into a form, that inserts into a database table.
They can then logout and later log back in and by clicking on the respective step they can update the data for that step.
In the database table I have 7 fields one of which is a progress field that allows an integer value from a form field on the UI, this should show up next to each step.
The problem; generate the 25 step menu when the page loads and assign the relevant progress value to the relevant step.
At present my code is only generating a step for the amount of rows in the table, not for 25 steps. e.g
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT * FROM step WHERE user_id='" + QID + "'ORDER BY step";
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
lit_progress.Text = "";
sqlDa.Fill(dt);
rowcount = dt.Rows.Count;
if (rowcount < 25)
{
diff = 25 - rowcount;
}
foreach (DataRow row in dt.Rows)
{
//assign the respective data of the row to two arrays - progress and step
progress_array[dt.Rows.Count] = Int32.Parse(row["progress"].ToString());
step_array[dt.Rows.Count] = Int32.Parse(row["step"].ToString());
//if the value of the step array matches that of the loop variable (i) then display the progress array value in the link
if (step_array[dt.Rows.Count] == i)
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + progress_array[dt.Rows.Count] + "</li>";
}
//if the value of step array does not match the value of the loop variable (i) then display a value of 0 for the link
else if (step_array[dt.Rows.Count] != i)
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + 0 + "</li>";
}
//some counter bit of text output, not important
Literal1.Text = "There are "+rowcount+" steps that have been filled in and "+diff.ToString()+ " steps still to be filled in";
The reason your code is only generating the number of steps equal to the number of rows you have in your database is because the for loop in your code (see below) that does the iterations for you will only execute the amount of times directly equivalent
to the number of records returned by your query
foreach (DataRow row in dt.Rows) // if your table contains three records this loop will only execute three times
{
}
To create the remaining steps you will need to add an addition loop after your loop to create the additonal steps e.g.
rowcount = dt.Rows.Count;
for(int i=rowcount +1; i <= 25; i++) //this loop will starting from the number of records returned until it reaches 25
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + 0 + "</li>";
}
Hope this helps
Please mark as answer if this resolved your problem
When I added the code after the database loop it did generate the 25 steps but without a relevant progress value (all progress values in second loop were outputed as 0)
I modified your code and now it pulls a progress value from the database based on the first row and the last row and not the rows that are in between?
Please replace the loops in your code with the following code:
int [] step_array = new int [26];
int [] progress_array = new int [26];
for(int i=0; i < =25; i++) //this loop will set all inital values of these arrays to zero
{
progress_array[i] = 0;
step_array[i] = 0;
}
foreach (DataRow row in dt.Rows)
{
//assign the respective data of the row to two arrays - progress and step
int stepNum = Int32.Parse(row["step"].ToString()); // contains step number stored in the database
progress_array[stepNum] = Int32.Parse(row["progress"].ToString());
step_array[stepNum] = Int32.Parse(row["step"].ToString());
}
for(int i=1; i < =25; i++) //this loop will execute 25 times
{
I think what you need is a fresh approach here. I think you're making life harder for yourself.
I'd be a little worried that your logic is tied up to 25 steps since this would prove quite inflexible should they decide to change that requirement. I know that's a not priority for you, but you could approach this problem in two ways which would give the
solution you're looking for, plus add that bit of flexibility there:
1)have a steps table on your database with an id and a StepNumber field of whatever is a relevant name for it. then just build the relationship on a separate table much like the one you have now by having a surrogate key, step id, user id and progress. On
the client code query a join on the steps lookup table and the relationship table. It should always pull all available steps and the associated data for that user and you can populate all that in one loop. this way not only your code if vastly simplified but
you also have the option of adding more steps or archiving steps by adding a column and filtering them out in a query in the future, etc.
2)If you for some reason cannot alter the database schema or prefer to constraint your solution to the backend code, then the simplest and cleanest way of tacking this would be to first set up your data structure then attempt to "render" it. I think one
of the problems you have here is that you are mixing data processing with what you ultimately want to display on the screen.
Create a Step object containg Id, StepNumber, UserId and Progress properties. initialize a collection such as List<Step>. Now loop through the database records and create a Step object with each iteration. match the step number with the loop counter by checking
for if(i == row["step"]) . if it matches then populate the object with the data from the database otherwise give it defaults (zero to progress and the set the user id to the one in question). Now create another method that takes the List<Step> as a parameter
and call it RenderSteps or something of the sort. On that method create your array and do a foreach on each Step in the collection setting the html with values extracted from the Step object.
Incidentally, if you have any control over the architecture I would actually change the data layer approach to be more OOP and only return objects and then pass the List<Step> to the web form (I'm guessing this is web forms?) and let it loop through it and
do the rendering. Also, if at all possible, I would consider fitting in Ling-to-Sql or EF which would bring massive improvements to your data layer.
Let me know if any of this makes sense or if nothing really helps I can try to help you figure something else out.
Please replace the loops in your code with the following code:
int [] step_array = new int [26];
int [] progress_array = new int [26];
for(int i=0; i < =25; i++) //this loop will set all inital values of these arrays to zero
{
progress_array[i] = 0;
step_array[i] = 0;
}
foreach (DataRow row in dt.Rows)
{
//assign the respective data of the row to two arrays - progress and step
int stepNum = Int32.Parse(row["step"].ToString()); // contains step number stored in the database
progress_array[stepNum] = Int32.Parse(row["progress"].ToString());
step_array[stepNum] = Int32.Parse(row["step"].ToString());
}
for(int i=1; i < =25; i++) //this loop will execute 25 times
{
corrado123
Member
58 Points
53 Posts
Assign a database field value to a menu item (step)
Mar 31, 2012 01:18 PM|LINK
Hi,
I have a simple problem but no simple solution.
Here is the scenario; imagine 25 jugs that can be filled with water, in any order and at any time with an indicator showing the amount of water in each jug.
The application; to generate a menu with 25 links (steps) and a corresponding value (progress).
After clicking on a step, the user can input data into a form, that inserts into a database table.
They can then logout and later log back in and by clicking on the respective step they can update the data for that step.
In the database table I have 7 fields one of which is a progress field that allows an integer value from a form field on the UI, this should show up next to each step.
The problem; generate the 25 step menu when the page loads and assign the relevant progress value to the relevant step.
At present my code is only generating a step for the amount of rows in the table, not for 25 steps. e.g
Step 1 - Progress 30
Step 2 - Progress 0
Step 3 - Progress 0
It should create a 25 step menu e.g
Step 1 - Progress 30
Step 2 - Progress 0
Step 3 - Progress 0
Step 4 - Progress 50
...
...
...
...
Step 25 - Progress 0
http://test.whclpreview.com/login.aspx
username: joe
password: bloggs
This is the function -
private void GetStepData()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT * FROM step WHERE user_id='" + QID + "'ORDER BY step";
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
lit_progress.Text = "";
sqlDa.Fill(dt);
rowcount = dt.Rows.Count;
if (rowcount < 25)
{
diff = 25 - rowcount;
}
foreach (DataRow row in dt.Rows)
{
//assign the respective data of the row to two arrays - progress and step
progress_array[dt.Rows.Count] = Int32.Parse(row["progress"].ToString());
step_array[dt.Rows.Count] = Int32.Parse(row["step"].ToString());
//if the value of the step array matches that of the loop variable (i) then display the progress array value in the link
if (step_array[dt.Rows.Count] == i)
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + progress_array[dt.Rows.Count] + "</li>";
}
//if the value of step array does not match the value of the loop variable (i) then display a value of 0 for the link
else if (step_array[dt.Rows.Count] != i)
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + 0 + "</li>";
}
//some counter bit of text output, not important
Literal1.Text = "There are "+rowcount+" steps that have been filled in and "+diff.ToString()+ " steps still to be filled in";
i++;
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (System.Data.SqlClient.SqlException ex)
{
Label1.Text = error;
}
finally
{
countrows = dt.Rows.Count;
connection.Close();
}
Can someone please help as this is urgent.
Thank you!
bakra
Member
342 Points
59 Posts
Re: Assign a database field value to a menu item (step)
Apr 01, 2012 12:03 AM|LINK
The reason your code is only generating the number of steps equal to the number of rows you have in your database is because the for loop in your code (see below) that does the iterations for you will only execute the amount of times directly equivalent to the number of records returned by your query
foreach (DataRow row in dt.Rows) // if your table contains three records this loop will only execute three times
{
}
To create the remaining steps you will need to add an addition loop after your loop to create the additonal steps e.g.
rowcount = dt.Rows.Count;
for(int i=rowcount +1; i <= 25; i++) //this loop will starting from the number of records returned until it reaches 25
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + 0 + "</li>";
}
Hope this helps
Please mark as answer if this resolved your problem
corrado123
Member
58 Points
53 Posts
Re: Assign a database field value to a menu item (step)
Apr 01, 2012 12:47 PM|LINK
Hi bakra,
And many thanks for the reply.
When I added the code after the database loop it did generate the 25 steps but without a relevant progress value (all progress values in second loop were outputed as 0)
I modified your code and now it pulls a progress value from the database based on the first row and the last row and not the rows that are in between?
i.e for the folowing data -
The code is generating
for (int i = rowcount + 1; i <= 25; i++) //this loop will starting from the number of records returned until it reaches 25
{
// if the value of the step array matches that of the loop variable (i) then display the progress array value in the link
if (step_array[dt.Rows.Count] == i)
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + progress_array[dt.Rows.Count] + "</li>";
}
// if the value of step array does not match the value of the loop variable (i) then display a value of 0 for the link
else if (step_array[dt.Rows.Count] != i)
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + 0 + "</li>";
}
}
Would you have any ideas on how to modify the code to extract all the progress values and assign them to their respective step?
Much appreciated!
bakra
Member
342 Points
59 Posts
Re: Assign a database field value to a menu item (step)
Apr 01, 2012 02:20 PM|LINK
Hi corrado123,
Please replace the loops in your code with the following code:
int [] step_array = new int [26];
int [] progress_array = new int [26];
for(int i=0; i < =25; i++) //this loop will set all inital values of these arrays to zero
{
progress_array[i] = 0;
step_array[i] = 0;
}
foreach (DataRow row in dt.Rows)
{
//assign the respective data of the row to two arrays - progress and step
int stepNum = Int32.Parse(row["step"].ToString()); // contains step number stored in the database
progress_array[stepNum] = Int32.Parse(row["progress"].ToString());
step_array[stepNum] = Int32.Parse(row["step"].ToString());
}
for(int i=1; i < =25; i++) //this loop will execute 25 times
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + progress_array[i] + "</li>";
}
Hope this helps
Please "Mark as Answer" if this resolved your issue
mattGuimaUK
Member
2 Points
1 Post
Re: Assign a database field value to a menu item (step)
Apr 01, 2012 02:25 PM|LINK
Hi corrado,
I think what you need is a fresh approach here. I think you're making life harder for yourself.
I'd be a little worried that your logic is tied up to 25 steps since this would prove quite inflexible should they decide to change that requirement. I know that's a not priority for you, but you could approach this problem in two ways which would give the solution you're looking for, plus add that bit of flexibility there:
1)have a steps table on your database with an id and a StepNumber field of whatever is a relevant name for it. then just build the relationship on a separate table much like the one you have now by having a surrogate key, step id, user id and progress. On the client code query a join on the steps lookup table and the relationship table. It should always pull all available steps and the associated data for that user and you can populate all that in one loop. this way not only your code if vastly simplified but you also have the option of adding more steps or archiving steps by adding a column and filtering them out in a query in the future, etc.
2)If you for some reason cannot alter the database schema or prefer to constraint your solution to the backend code, then the simplest and cleanest way of tacking this would be to first set up your data structure then attempt to "render" it. I think one of the problems you have here is that you are mixing data processing with what you ultimately want to display on the screen.
Create a Step object containg Id, StepNumber, UserId and Progress properties. initialize a collection such as List<Step>. Now loop through the database records and create a Step object with each iteration. match the step number with the loop counter by checking for if(i == row["step"]) . if it matches then populate the object with the data from the database otherwise give it defaults (zero to progress and the set the user id to the one in question). Now create another method that takes the List<Step> as a parameter and call it RenderSteps or something of the sort. On that method create your array and do a foreach on each Step in the collection setting the html with values extracted from the Step object.
Incidentally, if you have any control over the architecture I would actually change the data layer approach to be more OOP and only return objects and then pass the List<Step> to the web form (I'm guessing this is web forms?) and let it loop through it and do the rendering. Also, if at all possible, I would consider fitting in Ling-to-Sql or EF which would bring massive improvements to your data layer.
Let me know if any of this makes sense or if nothing really helps I can try to help you figure something else out.
Regards,
Matt
corrado123
Member
58 Points
53 Posts
Re: Assign a database field value to a menu item (step)
Apr 01, 2012 04:09 PM|LINK
Hi matt,
I require a quicker solution.
I think the easiest way is to create 25 rows in the one table for the user then allow the user to update one of those 25 steps.
Thanks for the reply
bakra
Member
342 Points
59 Posts
Re: Assign a database field value to a menu item (step)
Apr 01, 2012 10:25 PM|LINK
Hi corrado123,
Please replace the loops in your code with the following code:
int [] step_array = new int [26];
int [] progress_array = new int [26];
for(int i=0; i < =25; i++) //this loop will set all inital values of these arrays to zero
{
progress_array[i] = 0;
step_array[i] = 0;
}
foreach (DataRow row in dt.Rows)
{
//assign the respective data of the row to two arrays - progress and step
int stepNum = Int32.Parse(row["step"].ToString()); // contains step number stored in the database
progress_array[stepNum] = Int32.Parse(row["progress"].ToString());
step_array[stepNum] = Int32.Parse(row["step"].ToString());
}
for(int i=1; i < =25; i++) //this loop will execute 25 times
{
lit_steps.Text += "<li><a href=steps.aspx?step=" + i + "&QID=" + QID + ">Step " + i + "</a> Progress - " + progress_array[i] + "</li>";
}
Hope this helps
Please "Mark as Answer" if this resolved your issue
</div>corrado123
Member
58 Points
53 Posts
Re: Assign a database field value to a menu item (step)
Apr 02, 2012 08:21 AM|LINK
Excellent Bakra, your code has solved the issue and a huge thank you!