C#

Norris Chappell wrote:

If that is the case why didn't I need to have it when I did the select on the "where not exists" query?

Quote:

If that is the case why didn't I need to have it when I did the select on the "where not exists" query?

Quote:

One more thing My perm table is called TempTable maybe I should create the temp table with a different name?

Quote:

Okay didn't get the error. However, I don't see the table in SQL Server?

Quote:

I tried to query it and got an error?

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 StreamReader(NewResourceFileUpload.FileContent))
                      
          
            //    using (StreamReader reader = File.OpenText(@"c:\Users\pzd74f\Downloads\FinalLabor2015.csv"))
                {
                    string line;
                    if (reader.ReadLine() != null) // skip first line (headers)
                    {
                        while ((line = reader.ReadLine()) != null)
                        {
                            string[] columns = line.Split(Colon, StringSplitOptions.None);

                            DataRow row = dataTable.NewRow();
                            row["Name"] = columns[nameColumnIndex];
                            row["Hours"] = Decimal.Parse(columns[hoursColumnIndex], NumberFormatInfo.InvariantInfo);
                            dataTable.Rows.Add(row);
                          
                        }
                        
                    }
                }
                dataTable.EndLoadData();
          
                // insert the records from the DataTable into the temporary table
                dataAdapter.Update(dataTable);
         
                // load the result of the "main purpose" query into the DataTable
                dataTable.Clear();
                cmd.CommandText = "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;";
                dataAdapter.Fill(dataTable);
               
               
                return dataTable;
            }
            
        }
    
    }
}

OleDbConnection cn = DataBase.CreaConnessione();
    OleDbDataAdapter ad = new OleDbDataAdapter();
    Operai o = new Operai();

    private void Form2_Load(object sender, EventArgs e)
    {



    }

    private void btnentrata_Click(object sender, EventArgs e)
    {
        txtentrata.Text = DateTime.Now.ToLongTimeString();
        OleDbCommand cmd = cn.CreateCommand();
        cmd.CommandText = "INSERT INTO operai (nome,cognome,entrata) VALUES ('" + this.txtnome.Text + "','" + this.txtcognome.Text + "','" + this.txtentrata.Text + "')";
        cmd.CommandType = CommandType.Text;
        cn.Open();

        cmd.ExecuteNonQuery();
        cn.Close();
    }

    private void btuscita_Click(object sender, EventArgs e)
    {
        txtuscita.Text = DateTime.Now.ToLongTimeString();
        OleDbCommand cmd = cn.CreateCommand();
        cmd.CommandText = "INSERT INTO operai (nome,cognome,uscita) VALUES ('" + this.txtnome.Text + "','" + this.txtcognome.Text + "','" + this.txtuscita.Text + "')";
        cmd.CommandType = CommandType.Text;
        cn.Open();

        cmd.ExecuteNonQuery();
        cn.Close();
    }

private int? _id;

private void btnentrata_Click(object sender, EventArgs e)
{
    using (OleDbConnection cn = DataBase.CreaConnessione())
    using (OleDbCommand cmd = cn.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO operai (cognome, nome, entrata) VALUES (?, ?, ?)";
        cmd.CommandType = CommandType.Text;

        // OleDbCommand doesn't use named parameters, so the names don't matter here; only the order.
        cmd.Parameters.AddWithValue("p0", txtcognome.Text);
        cmd.Parameters.AddWithValue("p1", txtnome.Text);
        cmd.Parameters.AddWithValue("p2", DateTime.Now);

        cn.Open();
        cmd.ExecuteNonQuery();
        
        cmd.CommandText = "SELECT @@IDENTITY";
        cmd.Parameters.Clear();
        _id = (int)cmd.ExecuteScalar();
    }
}

private void btuscita_Click(object sender, EventArgs e)
{
    if (_id == null)
    {
        throw new InvalidOperationException("Row not created!");
    }
    
    using (OleDbConnection cn = DataBase.CreaConnessione())
    using (OleDbCommand cmd = cn.CreateCommand())
    {
        cmd.CommandText = "UPDATE operai SET uscita = ? WHERE ID = ?";
        cmd.CommandType = CommandType.Text;

        // OleDbCommand doesn't use named parameters, so the names don't matter here; only the order.
        cmd.Parameters.AddWithValue("p0", DateTime.Now);
        cmd.Parameters.AddWithValue("p1", _id);

        cn.Open();
        cmd.ExecuteNonQuery();
    }
}