Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Could somebody help me with this

Hello everyone,

                 I have a survey form in ASP 2.0 using C# language. There are four tables for this project that I have stored in the SQL database. There is a table called Area which has now 6 fields. So, each answer will have a score for at least one area. That means, I am calculating the final score for all 6 areas depending upon the selected answer. Right now, my program works and gives me the area with the highest value. Since I know now that I am dealing with 6 areas, I have defined my Submit button something like this.

public void surveySubmit(int score, int Name, int ROP, int FSI, int BTV, int cable, int Direct_Mail)
    {
        string con = "Data Source=sasql01;Initial Catalog=sandbox; Persist Security Info=True;User ID=sa_sql;Password=str@teg1K@m3r1ka";
        SqlConnection conn = new SqlConnection(con);
        string fetch = String.Format("Insert Into CustScores(Name, ROP, FSI, Cable, BTV, Direct_Mail, totalScore) values ({0},{1}, {2},{3}, {4}, {5}, {6})", Name, ROP, FSI, cable, BTV, Direct_Mail, score);
        SqlCommand cmd = new SqlCommand(fetch, conn);
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

    }

This is once the customer clicks on submit button, the CustScores table will be filled up with the scores for all six areas. And also I have defined my scores like this:
private string[] scores = new string[6]; meaning that I am going to get scores for only 6 areas.

Task: We are going to have an Admin page for our Media department where they can add questions, add answers and add more areas. Once, they add these things, they are all going to be in the respective tables. If the areas increases by 4 and lets say now there are 10 areas, do I have to modify my programming each time they add new areas? What is the best way to define the scores so that I can deal with as many areas I can?

Let me know if you didn't understand the question.

Thank You all in advance.
0
siddhuoops
Asked:
siddhuoops
  • 3
  • 2
1 Solution
 
SinclairCommented:
I think the best way would be to denormalize the score table, maybe something like this (sorry, I'm going to use regular SQL because I'm not very good with SQL server):

create table AREAS (
  areaName varchar2 primary key,
  description varchar2
);

create table CustScores (
  customerName varchar2 not null,
  areaName varchar2 not null references AREAS.areaName,
  score integer not null,
  primary key(customerName, areaName)
);

(note: in your actual code, you'll want to name your constraints, probably)

Now, you can query the list of all available areas like this:

select areaName from AREAS order by areaName;

You can use the results of this query to create your textboxes. Store the results in a List, not a flat array, and you can guarantee that you'll always have enough space. You can store the scores themselves in a Dictionary (i.e., a hashtable), like this:
IDictionary<string, int> scoreValues = new Dictionary<string, int>();
scoreValues["ROP"] = 42;

And you can query all scores for one particular customer like this:

select * from CustScores where customerName = ? order by areaName;

In order to insert scores for one customer, you'll probably want to do something like this:

insert Into CustScores(customerName, areaName, score) values (?, ?, ?)

One final note: it is a supremely bad idea to use string substitution to create SQL commands, because someone can make up a customer name like "' OR 1=1" and get full access to your database that way. It's better to use bind variables.


0
 
siddhuoopsAuthor Commented:
//This is done in clsDataAccess.cs page.

I have a table called Areas which has Area_id and AreaName. This is what I did so far;

public int MediaType()
    {
        string con = "Data Source=sasql01;Initial Catalog=sandbox; Persist Security Info=True;User ID=sa_sql;Password=str@teg1K@m3r1ka";
        SqlConnection conn = new SqlConnection(con);
        string fetch = "Select AreaName From Areas Order By AreaName";
        SqlCommand cmd = new SqlCommand(fetch, conn);
        SqlDataReader sdr = null;
        conn.Open();
        sdr = cmd.ExecuteReader();
        string MediaType = string.Empty;
        while (sdr.Read())
            MediaType = sdr[0].ToString();
        sdr.Close();
        conn.Close();

        return Convert.ToInt32(MediaType);

After this I have then defined an array

public int[] arrStores1
    {
        get { return new int[this.MediaType()]; }
        set { arrStores1 = value; }
    }


//This part was done in Default.aspx page.

private int storeValues(int[] arr, int pos, string[] val)
    {
        int adds = 0;
        for (int i = 0; i < val.Length; i++)
            arr[i] = Convert.ToInt32(val[i]);
        for (int i = 0; i < arr.Length; i++)
            adds += arr[i];
        return adds;
    }

I have a score table where I have the scores for all answers and that is different from the tblCustScores. The tblCustScores is filled up with the after the customer is done with the survey, i.e this table adds up all the values for the areas and puts them in the table tblCustScores. The Score table on the other hand has the AnswerID, AreaID, Cust_ID and all points for each answer per area is already stored in the Score table. I think you are confused with the tblCustScores and Score tables. The tblCustScores is blank but is filled once the customer is done with the survey. This is what I have to fill up the tblCustScores table.

public void surveySubmit(int score, int Radio, int ROP, int FSI, int BTV, int cable, int Direct_Mail)
    {
        string con = "Data Source=sasql01;Initial Catalog=sandbox; Persist Security Info=True;User ID=sa_sql;Password=str@teg1K@m3r1ka";
        SqlConnection conn = new SqlConnection(con);
        string fetch = String.Format("Insert Into CustScores(ROP, FSI, Cable, BTV, Radio,Direct_Mail, totalScore) values ({0},{1}, {2},{3}, {4}, {5}, {6})",ROP, FSI, cable, BTV, Radio, Direct_Mail, score);
        SqlCommand cmd = new SqlCommand(fetch, conn);
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

    }

There is my problem. Right now, this will allow me to store the values for only 6 areas. At some point if the areas are increased, then I will have to modify my code and so on and so on. Is there a way that I can fill up tblCustScores dynamically? Meaning, I can define as many areas to insert in the table I want. I hope this will make clear.


0
 
SinclairCommented:
Well, sort of. First of all, if you have the score table, you do not need tblCustScores at all. To retrieve the sum total score for a customer, you can do this:

select sum(score) from Score where custId = ? and surveyId = ? and areaId = ?

You can get a sum total for _all_ areas for a given customer using "group by":

select areaId, sum(score) as totalScore from Score where custId = ? and surveyId = ? group by areaId

Basically, you'd only need the tblCustScores table if you had millions of rows in the Score table, and you needed ultra-fast retrieval of the score totals.

If you want to keep track of the customer's progress through the survey, I'd create a table like this:

create table SURVEY_PROGRESS (
  progressId integer primary key,
  surveyId not null references SURVEYS.surveyId,
  customerId not null references CUSTOMERS.customerId,
  status varchar check (status in 'start', 'in_progress', 'finished'),
  -- more housekeeping data here: timestamp, etc.
);

When the customer begins the survey, you insert a row into SURVEY_PROGRESS, and when he's done, you set the "status" column to 'finished'.
0
 
siddhuoopsAuthor Commented:
Thats right, I don't need the tblCustScores at all. Let me try your idea and see what I come up with.
0
 
siddhuoopsAuthor Commented:
That helped..thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now