Solved

Could somebody help me with this

Posted on 2006-11-07
5
187 Views
Last Modified: 2010-04-16
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
Comment
Question by:siddhuoops
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:Sinclair
ID: 17892031
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
 

Author Comment

by:siddhuoops
ID: 17892552
//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
 
LVL 2

Accepted Solution

by:
Sinclair earned 500 total points
ID: 17893406
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
 

Author Comment

by:siddhuoops
ID: 17893457
Thats right, I don't need the tblCustScores at all. Let me try your idea and see what I come up with.
0
 

Author Comment

by:siddhuoops
ID: 17940705
That helped..thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now