C# Excel Lookup

Gaz124
Gaz124 used Ask the Experts™
on
Hi

I currently have a web form built in Visual Web Developer with c# code behind. The user fills in the form and when they click submit the results are wrote to an Excel spreadsheet.

We are getting issues were users are submitting the form multiple times which is causing problems when analysing the spreadsheet data.

What I would like to do is have the form 'lookup' the spreadsheet and see if there has been a submission in the past 4weeks. If so it would then display that information to the user saying something like 'You cannot submit the form because an entry was made on dd/mm/yyy'

Could anyone tell me if this is possible? if not the excel lookup maybe a constraint on the submit button to allow each user to only submit once a month?

All ideas are welcome!

Thanks

Here is the code behind;

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Templates
{
    public partial class WebForm36 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Submit_Click(object sender, EventArgs e)
        {
            string[] username = Request.ServerVariables["LOGON_USER"].ToString().Split('\\');
            globalfunctions getAttribute = new globalfunctions();

            //Build comma seperated line
            string responseline = username[1].Remove(3);
            responseline = responseline + "," + getAttribute.getADattribute(username[1], "displayName");
            responseline = responseline + "," + completedBy.Text;
            responseline = responseline + "," + jobTitle.Text;
            responseline = responseline + "," + checkComp.Text;
            responseline = responseline + "," + DateTime.Now;

            //append to csv file
            StreamWriter sw = File.AppendText("e:\\results\\StockRotation4W.csv");
            sw.WriteLine(responseline);
            sw.Close();
            Response.Redirect("thanks.aspx");
        }
    }
}
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AndyAinscowFreelance programmer / Consultant

Commented:
Are you writing to an excel file as your question asks or a csv file as your code shows?

Author

Commented:
sorry, Yes its a .csv
AndyAinscowFreelance programmer / Consultant

Commented:
So is the issue the csv file being overwritten with different data that is then analysed, appended with extra data or that excel should not reload a csv file during a certain time period since the last load/analysis ?
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Author

Commented:
Issue is that users are submitting data to the .csv two or three times a month and should only be once.

I want to be able to look into the .csv, see if there has been a submission for that user (coloum name is Store_number) and if so stop the

 StreamWriter sw = File.AppendText("e:\\results\\StockRotation4W.csv");
            sw.WriteLine(responseline);
            sw.Close();
            Response.Redirect("thanks.aspx");

and Redirect to another page '"Duplicate.aspx"

Thanks
AndyAinscowFreelance programmer / Consultant

Commented:
OK.
When the user clicks the submit button.
Use StreamReader to read the complete csv file in line by line.
In a loop for each line check if the details have already been appended, store in a boolean variable
Close the StreamReader.

Now check the boolean and have something like:

if(AlreadyWritten)
{
 Response.Redirect("Duplicate.aspx");
}
else
{            //append to csv file
            StreamWriter sw = File.AppendText("e:\\results\\StockRotation4W.csv");
            sw.WriteLine(responseline);
            sw.Close();
            Response.Redirect("thanks.aspx");
}

Author

Commented:
"OK.
When the user clicks the submit button.
Use StreamReader to read the complete csv file in line by line.
In a loop for each line check if the details have already been appended, store in a boolean variable
Close the StreamReader"

This is the part i am unsure about could you please explain a little more or show some example code?


Thanks
AndyAinscowFreelance programmer / Consultant

Commented:
From the help files:

using System;
using System.IO;

class Test
{
    public static void Main()
    {
        try
        {
            // Create an instance of StreamReader to read from a file.
            // The using statement also closes the StreamReader.
            using (StreamReader sr = new StreamReader("TestFile.txt"))
            {
                String line;
                // Read and display lines from the file until the end of
                // the file is reached.
                while ((line = sr.ReadLine()) != null)
                {
                    Console.WriteLine(line);    <<-------------------- HERE
                }
            }
        }
        catch (Exception e)
        {
            // Let the user know what went wrong.
            Console.WriteLine("The file could not be read:");
            Console.WriteLine(e.Message);
        }
    }
}


at HERE you parse the line to extract the information to identify if it is a duplicate

Author

Commented:
I have already found that but I dont know how to check against the file, which im guessing is where you have place HERE.

Sorry it seems im not explaining too clear on this, I havent been using C# long.


AndyAinscowFreelance programmer / Consultant

Commented:
You have this for writing
        //Build comma seperated line
            string responseline = username[1].Remove(3);
            responseline = responseline + "," + getAttribute.getADattribute(username[1], "displayName");
            responseline = responseline + "," + completedBy.Text;
            responseline = responseline + "," + jobTitle.Text;
            responseline = responseline + "," + checkComp.Text;
            responseline = responseline + "," + DateTime.Now;


What you need to do is at 'HERE' take the line of comma separated values apart and check for a 'duplicate'

Author

Commented:
OK but how do i do that?
AndyAinscowFreelance programmer / Consultant

Commented:
Here for example (you have to know what is a duplicate):
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_CSharp/Q_26169514.html

Author

Commented:
Dont really understand the example, cant see how it relates because i need to check user input against exsisting data before insertion
AndyAinscowFreelance programmer / Consultant

Commented:
That splits a line into the values separated by commas - so you can compare the values in the csv file with the values the user is attempting to append (because i need to check user input against exsisting data before insertion) which is what you want to do.

Author

Commented:
How would i do that?
Freelance programmer / Consultant
Commented:
if(valueExisting == valueToAppend)
{
  //values match - duplicate
}
else
{
  //values different - not a duplicate
}


ps.
if( (a==b) && (c==d) ....)  to compare multiple values

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial