Solved

Could somebody help me with this

Posted on 2006-11-07
5
186 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

12 Experts available now in Live!

Get 1:1 Help Now