|
Sascha,
It is still not working for me.
Norris
|
|
|
|
|
1) I just forgot to remove the line using System.Windows.Forms; - you don't need it.
2) Seems like you haven't yet adjusted the column names in the last query. The reason why you don't get an exception because of that and why nothing happens at all is:
3) I'm pretty sure the method isn't called anywhere. These lines:
gvNewResource.DataSource = QueryStaff();
gvNewResource.DataBind(); aren't supposed to be in QueryStaff(). At the moment it's a snake that bites its own tail - you "try" to call QueryStaff() from within itself but it's not called from anywhere "outside". The method that contained your original code is probably the place where you should move these two lines into (as I've already written in my second-last reply or so  ) or just into any method that is actually triggered by something (maybe a button-click).
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Okay I am confused. I'm suppose to put this method in my other code. I am in a loop now when I add this to my code.
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
}
}
|
|
|
|
|
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))
{
DataTable dataTable = new DataTable();
cmd.CommandText = "SELECT * FROM TempTable;";
dataAdapter.Fill(dataTable);
dataTable.BeginLoadData();
using (StreamReader reader = File.OpenText(@"c:\Users\pzd74f\Downloads\TestLabor2015.csv"))
{
string line;
if (reader.ReadLine() != null)
{
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();
dataAdapter.Update(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);
conn.Close();
return dataTable;
}
}
}
}
|
|
|
|
|
Alright, one step further 
You can remove this line: this.QueryStaff();
In which way does the Hours-Stuff not work as expected yet?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
It's not showing the hours. Just the name.
The table has the hours but the sum in not working.
|
|
|
|
|
Please re-enable the code for the temporary table so that we can rule out that there's something wrong with your replacement by a permanent table:
- two lines have to be un-commented
- in the two query-strings put a # in front of "TempTable" -> #TempTable
- delete or rename the permanent table "TempTable" in the database
Unrelated: You can remove the line conn.Close(); , it happens automatically because of the using-statement.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
I'm sorry I can't help you with ASP.NET, I have next to no experience with that. But. If your original code, wherever it was, was executed (even though it didn't do what it should), then this should work when you remove these two lines from QueryStaff() and insert them where your original code was.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
No problem with asp.net. I took this project over from a person who left to go to another project and division. Java/C++/Oracle programming is what I did in the past. So I am very new to C#.
|
|
|
|
|
I'm getting this error:
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
|
|
|
|
|
Yep, that's what I mentioned, what I forgot in the posted version of my code, because the code I tested was slightly different and didn't require explicit handling of this. You can fix this, right? 
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
So I will need to code an opening of the database and available connection since it closed?
|
|
|
|
|
If that is the case why didn't I need to have it when I did the select on the "where not exists" query?
|
|
|
|
|
One more thing My perm table is called TempTable maybe I should create the temp table with a different name?
|
|
|
|
|
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?
What do you mean by "it" here?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Between the opening brace and the following comment "// create temporary table in database" insert a conn.Open();
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Okay didn't get the error. However, I don't see the table in SQL Server?
|
|
|
|
|
I tried to query it and got an error?
|
|
|
|
|
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?
Sorry, I'm tired - now I think I understood what you meant: The DataAdapter on this line: dataAdapter.Fill(dataTable); opens the connection automatically if it isn't already, but cmd.ExecuteNonQuery(); doesn't, so after un-commenting that line it became neccessary to open the connection explicitly.
Quote: One more thing My perm table is called TempTable maybe I should create the temp table with a different name?
I think you're tired too Yes, you should have renamed or deleted the perm table. (I actually don't know if it would be a problem, I just told you to do that to avoid a potential error.)
Quote: Okay didn't get the error. However, I don't see the table in SQL Server?
It's a temporary table. It exists only during the lifetime of the connection and is only visible to that connection.
Quote: I tried to query it and got an error?
Always tell the exact error message when you're trying to get help
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Sorry again. You are right I am tired too. I think I am good for now. I can figure out the hours issue. You have been so helpful and I have learn a lot from you. Thank you for being so patience with me. What time zone are you in? Are you from France?
|
|
|
|
|
You're welcome! But if you can't figure out the hours issue, feel free to ask.
I'm from Germany, one of my ancestors came from France
Good luck and good night!
cheers, Sascha
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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)
{
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))
{
conn.Open();
cmd.CommandText = "CREATE TABLE #TempTable(Name nvarchar(100) NOT NULL, Hours decimal(6, 2) NOT NULL);";
cmd.ExecuteNonQuery();
DataTable dataTable = new DataTable();
cmd.CommandText = "SELECT * FROM #TempTable;";
dataAdapter.Fill(dataTable);
dataTable.BeginLoadData();
using ( StreamReader reader = new StreamReader(NewResourceFileUpload.FileContent))
{
string line;
if (reader.ReadLine() != null)
{
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();
dataAdapter.Update(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;
}
}
}
}
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
