|
There are a ton of problem with this code.
The major disfunctional standouts are why are you grabbing a GridViewRow, casting it to a TextBox for each column in the row?? All of these FindControl calls are garbage. Don't do this. The GridViewRow already gives you the data in the row instead of ignoring the row entirely and going to the UI grid to find controls.
You're basically finding the same controls over and over again. You're not going down the rows in the grid. You're getting the same controls in the first row of the grid.
You also seem to be inserting a value for StaffTrackingID but you're doing something awfully fishy to set this value.
The SQL statement you have is garbage. You're using string concatenation to build an SQL statement. This opens you up to SQL Injection attacks and can result in the complete loss of your database. Google for "C# SQL Parameterized queries" to find out what you're supposed to be doing.
|
|
|
|
|
Okay I took your advise and change my code and Parameterized my queries. I am now able to see all of my rows in the SQL database. However, I got a question for you? I need to have the users supply the period instead of it being generated in the code. Is there a way to do that Inline?
|
|
|
|
|
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.
|
|
|
|
|
Richard,
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!
|
|
|
|
|
You can't do it directly via the .NET FTP command. What you will have to do is work around this using something like this:
[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.
|
|
|
|
|
No success yet. With the following code ChangePermission returns a false value every time. The file I'm adjusting permissions for is located in the root directory. I'm kind of learning as I go so there's a good chance I missed something obvious.
[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";
|
|
|
|
|