How to compare a sql server table field with a csv field and display what is not in the sql table.

[Norris Chappell]

I need to replace: using (StreamReader reader = File.OpenText(@"c:\Users\pzd74f\Downloads\TestLabor2015.csv")) with something like this: protected void ImportButton_Click(object sender, EventArgs e) { if (this.FileUpload1.HasFile) { var extension = Path.GetExtension(FileUpload1.FileName); if (extension == ".csv") { StreamReader csvreader = new StreamReader(FileUpload1.FileContent); I need to have the user to be able to import the file instead of the csv be hardcoded.

[Sascha Lefevre]

Just pass FileUpload1.FileContent as an argument to QueryStaff :)

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

I tried to pass as an argument but getting errors: c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\CONTROLTEMPLATES\StaffingWebParts\VisualWebPart1\VisualWebPart1UserControl.ascx(13): error CS0117: 'ASP._controltemplates_staffingwebparts_visualwebpart1_visualwebpart1usercontrol_ascx' does not contain a definition for 'ImportButton_Click' I need protected void ImportButton_Click(object sender, EventArgs e) somewhere but not sure where? using ( StreamReader reader = new StreamReader(NewResourceFileUpload.FileContent))

using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Globalization;

namespace StaffingWebParts.VisualWebPart1
{
public partial class VisualWebPart1UserControl : UserControl
{

    protected void Page\_Load(object sender, EventArgs e)
    {

        if (!Page.IsPostBack)
        {
      //      this.QueryStaff();
            gvNewResource.DataSource = QueryStaff();
            gvNewResource.DataBind();
                       
        }
    }
   
    private static char\[\] Colon = new char\[\] { ',' };
    private DataTable QueryStaff()
    {
        const int nameColumnIndex = 1;
        const int hoursColumnIndex = 9;
             
            
            
        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings\["SQLStaffingConn"\].ConnectionString))
        using (var cmd = new SqlCommand("", conn))
        using (var dataAdapter = new SqlDataAdapter(cmd))
        using (var cmdBuilder = new SqlCommandBuilder(dataAdapter))
        {
            // create temporary table in database
           conn.Open();
           cmd.CommandText = "CREATE TABLE #TempTable(Name nvarchar(100) NOT NULL, Hours decimal(6, 2) NOT NULL);";
           cmd.ExecuteNonQuery();

            // create a DataTable and let the DataAdapter create appropriate columns for it
            DataTable dataTable = new DataTable();
            cmd.CommandText = "SELECT \* FROM #TempTable;";
            dataAdapter.Fill(dataTable);

            // read the CSV-records into the DataTable
            dataTable.BeginLoadData();
     
       using ( StreamReader reader = new StreamR

[Sascha Lefevre]

I'm sorry, I can't really help there, as I'm unfamiliar with ASP.NET. If I recall correctly, you took over this project from somebody else, right? I imagine there should be some code handling button-clicks in other parts of the application. Maybe you can take that as a kind of template how to do it. Else I would suggest you to open a new question in the ASP.NET forum so someone else can hopefully help you there.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

Okay thanks. I going to submit what I have to my Director. They will just need to name the csv what I have in the code. I never got the hours to work.

[Norris Chappell]

Sascha, The Director is happy with code but would like to see the hours. I looped thru the code with debug and see that the #temptable is showing the hours but are not summing then. Would this select statement be where I need to focus? SELECT Tmp.Name, SUM(Tmp.Hours) FROM TempTable AS Tmp WHERE NOT EXISTS (SELECT * FROM StaffTracking AS ST WHERE Tmp.Name = ST.ResourceName) GROUP BY Tmp.Name;"; I have tried several different scenarios but no results. Thanks, Norris

[Sascha Lefevre]

Hi Norris, there's no # in front of "TempTable" there. If it's the same in your code, that might be the problem. If it's not that I have no further ideas because it works for me. I hope you'll be able to fix it - good luck! /Sascha

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

Ok. Thank you for your help.

[Norris Chappell]

Okay got it figure out for the Hours. I needed to add to sum(Tmp.Hours) as Hours. :) ;)

[Sascha Lefevre]

Cheers! :-D

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson