|
I have no idea since you didn't define exactly what a "period" is, how the user enters such a thing and how it's represented in the database.
|
|
|
|
|
The Period(yyyy-MM-01) that is when the spreadsheet was produced. So if the spreadsheet was this month it would be 2015-06-01 and so forth. Right now it doesn't matter which spreadsheet I run the period is the same.
|
|
|
|
|
Don't store dates as strings.
Imagine Bart Simpson writing that 100 times on the chalkboard 
You can't calculate with strings. Dates as strings are useful when they should be read by humans but not for storing them in the database. Use the most suitable date/time datatype that the database system offers.
Apart from that, it looks like it comes from the GridView, so the user should already be able to supply it?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Norris Chappell wrote: IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData) INSERT INTO StaffTrackingFTEData (...
When you execute the query the first time, if there are no rows in the table, then the first row will be inserted.
For every subsequent row, that IF NOT EXISTS(...) condition will be false, since there will be at least one row in the table. Therefore, your INSERT statement will not be executed.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
So the "IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData)" is not needed? The reason I put that in was to eliminate duplicates. What is they submitted several times. I don't want the same data in there several times.
modified 25-Jun-15 15:45pm.
|
|
|
|
|
It's not eliminating duplicates - it's just preventing you from having more than one row in the table.
If you want to prevent duplicates, then you'd need to add a WHERE clause to that test to check the columns which you don't want to duplicate.
IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData WHERE SomeColumn = @SomeParameter And ...)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
take away IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData) I get " Cannot insert the value NULL into column 'StaffTrackingID', table 'SP2010_EDCStaffing_AppDB.dbo.StaffTrackingFTEData'; column does not allow nulls. INSERT fails.
|
|
|
|
|
That just shows that you have an issue with the subquery that should provide the StaffTrackingID. The issue was previously hidden because the INSERT wasn't actually attempted.
On a different note: Adding @StaffTrackingID as output parameter doesn't serve a purpose.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
If I didn't declare that variable it was giving me an error that I needed to declare it.
|
|
|
|
|
I updated my sql query and it is giving me this error: (Must declare the scalar variable "@StaffTrackingID")
protected void SubmitButton_Click(object sender, EventArgs e)
{
const string Query = "IF NOT EXISTS (SELECT StaffTrackingID, Period FROM StaffTrackingFTEData where StaffTrackingID = @StaffTrackingID and Period = @Period) INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) VALUES ((Select StaffTracking.ID From StaffTracking where StaffTracking.CATWResourceName = @Name), @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);
using (SqlCommand command = new SqlCommand(Query, conn))
{
conn.Open();
command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
foreach (GridViewRow row in gvCATW.Rows)
{
command.Parameters.AddWithValue("@Name", ((TextBox)row.FindControl("txtName")).Text);
command.Parameters.AddWithValue("@EstimateHours", ((TextBox)row.FindControl("txtEstimateHours")).Text);
command.Parameters.AddWithValue("@EstimateFTE", ((TextBox)row.FindControl("txtEstimateFTE")).Text);
command.Parameters.AddWithValue("@ActualHours", ((TextBox)row.FindControl("txtHours")).Text);
command.Parameters.AddWithValue("@ActualFTE", ((Label)row.FindControl("Label1")).Text);
command.Parameters.AddWithValue("@Comment", ((TextBox)row.FindControl("txtComment")).Text);
command.Parameters.AddWithValue("@CommentBy", ((TextBox)row.FindControl("txtCommentBy")).Text);
command.Parameters.AddWithValue("@Period", ((TextBox)row.FindControl("txtPeriod")).Text);
command.ExecuteNonQuery();
command.Parameters.Clear();
}
}
}
|
|
|
|
|
That's because you're not passing a parameter called @StaffTrackingID.
Try changing your query to:
const string Query = @"DECLARE @StaffTrackingID int;
SELECT @StaffTrackingID = ID FROM StaffTracking WHERE CATWResourceName = @Name;
If @@ROWCOUNT = 0 RAISERROR('No staff tracking record found for resource ''%s''.', 16, 1, @Name);
IF NOT EXISTS (SELECT 1 FROM StaffTrackingFTEData WHERE StaffTrackingID = @StaffTrackingID And Period = @Period)
INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period])
VALUES (@StaffTrackingID, @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)
";
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That worked but if no record is found for someone can I just skip it and continue? Your revised sql does exactly what I wanted to do except it gives an error and the users wouldn't know what to do with it. The problem is that we have some employee that are suppliers and others direct and vise versa. this is what I am getting.
No staff tracking record found for resource 'ALISON GREEN'.
Cannot insert the value NULL into column 'StaffTrackingID', table 'SP2010_EDCStaffing_AppDB.dbo.StaffTrackingFTEData'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The Field in StaffTracking to determine if a person is a direct or Supplier is PersonnelResourceType. I changed the above code to include in the where clause SELECT @StaffTrackingID = ID FROM StaffTracking WHERE CATWResourceName = @Name and PersonnelResourceType = 'Supplier'; but still getting that message.
modified 29-Jun-15 10:51am.
|
|
|
|
|
const string Query = @"DECLARE @StaffTrackingID int;
SELECT @StaffTrackingID = ID FROM StaffTracking WHERE CATWResourceName = @Name;
If @@ROWCOUNT != 0 And NOT EXISTS (SELECT 1 FROM StaffTrackingFTEData WHERE StaffTrackingID = @StaffTrackingID And Period = @Period)
INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period])
VALUES (@StaffTrackingID, @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)
";
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks that worked. Really appreciated it.
|
|
|
|
|
Don't Clear and re-Add the Parameters for each row; just set the Values.
|
|
|
|
|
Thanks. Just set the values beforehand?
|
|
|
|
|
No - create the SqlParameters just once, before the foreach-loop and capture them in variables. Then inside the foreach-loop assign new values to these instead of recreating new SqlParameters for each loop-iteration.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Can you provide a snippet of what you are talking about?
So this is not correct?
<pre lang="c#"> protected void SubmitButton_Click(object sender, EventArgs e)
{
const string Query = "INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) VALUES ((Select StaffTracking.ID From StaffTracking where StaffTracking.CATWResourceName = @Name), @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);
using (SqlCommand command = new SqlCommand(Query, conn))
{
conn.Open();
command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
foreach (GridViewRow row in gvCATW.Rows)
{
command.Parameters.AddWithValue("@Name", ((TextBox)row.FindControl("txtName")).Text);
.....
.....
.....
|
|
|
|
|
|
Hi, I'm having trouble with the following code which takes a file on my local hard drive and writes it to a Linux server via FTP. It works, however the permissions of the file after it has been written to the server is 000 and I would like permission set to 775. Any ideas? The source file on my local hard drive is first created with the StreamWriter object.
string fileUri = "ftp://" + serverAddressTextBox.Text + "/" + masterDirectoryFileName;
DeleteFileOnServer(fileUri);
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(fileUri);
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential(ftpUserNameTextBox.Text, ftpPasswordTextBox.Text);
StreamReader sourceStream = new StreamReader(directoryPath + "\\" + masterDirectoryFileName);
byte[] fileContents = Encoding.UTF8.GetBytes(sourceStream.ReadToEnd());
sourceStream.Close();
request.ContentLength = fileContents.Length;
Stream requestStream = request.GetRequestStream();
requestStream.Write(fileContents, 0, fileContents.Length);
requestStream.Close();
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
richTextBox2.Text += "Upload File Complete, status {0}" + response.StatusDescription;
response.Close();
Thanks for any insight you can provide!
|
|
|
|
|
[DllImport("wininet.dll")]
private extern static IntPtr InternetConnect(IntPtr internet, string host, short port, string username, string password, int service, int flags, IntPtr context);
[DllImport("wininet.dll")]
private extern static bool FtpCommand(IntPtr connect, bool expectResponse, int flags, string command, IntPtr context, out IntPtr ftpCommand);
[DllImport("wininet.dll")]
private extern static bool IntPtr InternetCloseHandle(IntPtr connect);
public static bool ChangePermission(string uri, string folder, string userName, string password)
{
IntPtr context = IntPtr.Zero;
IntPtr ftpCommand = IntPtr.Zero;
IntPtr open = InternetOpen("Portal", 1, null, null, 0x10000000);
IntPtr connected = InternetConnect(open, uri, 21, userName, password, 1, 0x08000000, context);
if (!FtpCommand(connected, false, 0x00000001, "CWD " + folder, context, out ftpCommand)) return false;
if (!FtpCommand(connected, false, 0x00000001, "SITE CHMOD 775 theFile.txt", context, out ftpCommand)) return false;
bool connectedclose = InternetCloseHandle(connected);
bool openclose = InternetCloseHandle(open);
return (connectedclose && openclose)
}Note that I've just typed this out into the editor here. There may be a minor syntax error or two, but this should give you the idea.
|
|
|
|
|
Awesome, Thanks! I'll give it a go and report back. It looks like I'll need to bring in the InternetOpen procedure from wininet.dll as well.
|
|
|
|
|
[DllImport("wininet.dll")]
private extern static IntPtr InternetConnect(IntPtr internet, string host, short port, string username, string password, int service, int flags, IntPtr context);
[DllImport("wininet.dll")]
private extern static bool FtpCommand(IntPtr connect, bool expectResponse, int flags, string command, IntPtr context, out IntPtr ftpCommand);
[DllImport("wininet.dll")]
private extern static bool InternetCloseHandle(IntPtr connect);
[DllImport("wininet.dll")]
static extern IntPtr InternetOpen(string lpszAgent, int dwAccessType, string lpszProxyName, string lpszProxyBypass, int dwFlags);
public static bool ChangePermission(string uri, string userName, string password, string fileName)
{
IntPtr context = IntPtr.Zero;
IntPtr ftpCommand = IntPtr.Zero;
IntPtr open = InternetOpen("Portal", 1, null, null, 0x10000000);
IntPtr connected = InternetConnect(open, uri, 21, userName, password, 1, 0x08000000, context);
if (!FtpCommand(connected, false, 0x00000001, "SITE CHMOD 775 " + fileName, context, out ftpCommand)) return false;
bool connectedclose = InternetCloseHandle(connected);
bool openclose = InternetCloseHandle(open);
return (connectedclose && openclose);
}
.
.
.
if (!ChangePermission("ftp://" + serverAddressTextBox.Text, ftpUserNameTextBox.Text, ftpPasswordTextBox.Text, masterDirectoryFileName))
richTextBox1.Text = "Permissions adjustment failed";
|
|
|
|
|
This project[^] may be of use. It has support for more FTP stuff than the .NET Framework.
I have used it successfully myself.
What do you get when you cross a joke with a rhetorical question?
The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.
Do questions with multiple question marks annoy you???
|
|
|
|
|
There is only problem which is about the input string was not in the correct format. I use a textbox1 which is for me to type a number in the string format as I store the string in a variable knn. Then I try to convert it to Int32 and store it in another variable wup. However, after I tried to run the program, the error of input string popped up and I really run out of idea on what to do next.
public void find() {
int a = 0;
int h = 0;
Int32 t = 1;
int f = 130;
int d = 230;
string knn;
Int32 wup = 0;
knn = textBox1.Text;
wup = Convert.ToInt32(knn);
int [,] yes=new int[10,10];
for(a=0 ;a<=7 ; a++) {
for (h = 0; h <= 7; h++)
{
if (a == 0)
{
if( t == wup)
{
System.Drawing.Graphics graphics = this.CreateGraphics();
System.Drawing.Rectangle square = new System.Drawing.Rectangle(f, d, 5, 5);
graphics.DrawRectangle(System.Drawing.Pens.Red, square);
textBox2.Text ="Index = "+ Convert.ToString(t);
textBox3.Text = "x = " + Convert.ToString(a);
textBox4.Text = "y = " + Convert.ToString(h);
}
f = f + 20;
}
if (a == 0 && h == 7)
{
f = 130;
d = d + 20;
}
if (a == 1)
{
if (t == wup)
{
System.Drawing.Graphics graphics = this.CreateGraphics();
System.Drawing.Rectangle square = new System.Drawing.Rectangle(f, d, 5, 5);
graphics.DrawRectangle(System.Drawing.Pens.Red, square);
textBox2.Text ="Index = "+ Convert.ToString(t);
textBox3.Text = "x = " + Convert.ToString(a);
textBox4.Text = "y = " + Convert.ToString(h);
}
f = f + 20;
}
if (a == 1 && h == 7)
{
f = 130;
d = d + 30;
}
if (a == 2)
{
if (t == wup)
{
System.Drawing.Graphics graphics = this.CreateGraphics();
System.Drawing.Rectangle square = new System.Drawing.Rectangle(f, d, 5, 5);
graphics.DrawRectangle(System.Drawing.Pens.Red, square);
textBox2.Text ="Index"+ Convert.ToString(t);
textBox3.Text = "x" + Convert.ToString(a);
textBox4.Text = "y" + Convert.ToString(h);
textBox4.Text = textBox4.Text + Environment.NewLine + "y= " + (Convert.ToString(h));
}
f = f + 20;
}
if (a == 2 && h == 7)
{
f = 130;
d = d + 30;
}
if (a == 3)
{
if (t == wup)
{
System.Drawing.Graphics graphics = this.CreateGraphics();
System.Drawing.Rectangle square = new System.Drawing.Rectangle(f, d, 5, 5);
graphics.DrawRectangle(System.Drawing.Pens.Red, square);
textBox2.Text = "Index" + Convert.ToString(t);
textBox3.Text = "x" + Convert.ToString(a);
textBox4.Text = "y" + Convert.ToString(h);
}
f = f + 20;
}
if (a == 3 && h == 7)
{
f = 130;
d = d + 30;
}
if (a == 4)
{
if (t == wup){
System.Drawing.Graphics graphics = this.CreateGraphics();
System.Drawing.Rectangle square = new System.Drawing.Rectangle(f, d, 5, 5);
graphics.DrawRectangle(System.Drawing.Pens.Red, square);
textBox2.Text ="Index = "+ Convert.ToString(t);
textBox3.Text = "x = " + Convert.ToString(a);
textBox4.Text = "y = " + Convert.ToString(h);
}
f = f + 20;
}
if (a == 4 && h == 7)
{
f = 130;
d = d + 30;
}
if (a == 5)
{
if (t == wup){
System.Drawing.Graphics graphics = this.CreateGraphics();
System.Drawing.Rectangle square = new System.Drawing.Rectangle(f, d, 5, 5);
graphics.DrawRectangle(System.Drawing.Pens.Red, square);
textBox2.Text ="Index"+ Convert.ToString(t);
textBox3.Text = "x" + Convert.ToString(a);
textBox4.Text = "y" + Convert.ToString(h);
}
f = f + 20;
}
yes[a, h] = t;
t++;
if (a == 5 && h == 7)
{
break;
}
}
}
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
}
private void textBox3_TextChanged(object sender, EventArgs e)
{
}
private void textBox5_TextChanged(object sender, EventArgs e)
{
}
}
}
modified 25-Jun-15 11:55am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
