PLEASE HELP
-
Hello Folks, I really need help to solve a real stupid situation I have a SQL Server database table named MAINT_START.Columns of this table are: PlateNumber char(11)--------> PK MaintEntryDate smalldatetime---> PK MaintReason char(20) I want to update this table's MaintReason column via a SQL String. My code is like this: /********************************************************************** string strUpdate="UPDATE MAINT_START SET MaintReason='" + cMaintReason.Text.TrimEnd() + "'," + " MaintCompany='" + cMaintCompany.Text.TrimEnd() + "'," + " MaintCompanyTel='" + cMaintCompanyTel.Text.TrimEnd() + "'," + " MaintCompanyAddress='" + cMaintCompanyAddress.Text.TrimEnd() + "'" + " WHERE PlateNumber='" + cPlateNumber.Text + "' AND" + " MaintEntryDate =Convert.ToDateTime(cMaintEntry.Text)"; SqlCommand aCommand=new SqlCommand(strUpdate,Myconnection); /************************************************************************ Dear friends is it really impossible to insert or update a smalldatetime column? Please Help..
-
Hello Folks, I really need help to solve a real stupid situation I have a SQL Server database table named MAINT_START.Columns of this table are: PlateNumber char(11)--------> PK MaintEntryDate smalldatetime---> PK MaintReason char(20) I want to update this table's MaintReason column via a SQL String. My code is like this: /********************************************************************** string strUpdate="UPDATE MAINT_START SET MaintReason='" + cMaintReason.Text.TrimEnd() + "'," + " MaintCompany='" + cMaintCompany.Text.TrimEnd() + "'," + " MaintCompanyTel='" + cMaintCompanyTel.Text.TrimEnd() + "'," + " MaintCompanyAddress='" + cMaintCompanyAddress.Text.TrimEnd() + "'" + " WHERE PlateNumber='" + cPlateNumber.Text + "' AND" + " MaintEntryDate =Convert.ToDateTime(cMaintEntry.Text)"; SqlCommand aCommand=new SqlCommand(strUpdate,Myconnection); /************************************************************************ Dear friends is it really impossible to insert or update a smalldatetime column? Please Help..
innocent73 wrote:
Dear friends is it really impossible to insert or update a smalldatetime column?
It is perfectly possible - But first you may want to address some deeper concerns. You should use parameterised queries for a number of reasons the main one is that it is more secure and less susceptable to a SQL Injection Attack (your code is highly suscetable to a SQL Injection Attack). Also it makes things easier to read. See: SQL Injection Attacks and Tips on How to Prevent Them[^]
innocent73 wrote:
" MaintEntryDate =Convert.ToDateTime(cMaintEntry.Text)";
SQL server knows nothing of
Convert.ToDateTime(cMaintEntry.Text)because that is C# code. If you continue in the way you have written your code so far, you just need to inject the text without conversion (but you have to assume that the text is in the correct format already). Alternatively you can pass a date in as a parameter (see the above linked article to see how) and secure your system at the same time.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
innocent73 wrote:
Dear friends is it really impossible to insert or update a smalldatetime column?
It is perfectly possible - But first you may want to address some deeper concerns. You should use parameterised queries for a number of reasons the main one is that it is more secure and less susceptable to a SQL Injection Attack (your code is highly suscetable to a SQL Injection Attack). Also it makes things easier to read. See: SQL Injection Attacks and Tips on How to Prevent Them[^]
innocent73 wrote:
" MaintEntryDate =Convert.ToDateTime(cMaintEntry.Text)";
SQL server knows nothing of
Convert.ToDateTime(cMaintEntry.Text)because that is C# code. If you continue in the way you have written your code so far, you just need to inject the text without conversion (but you have to assume that the text is in the correct format already). Alternatively you can pass a date in as a parameter (see the above linked article to see how) and secure your system at the same time.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
Firstly I would like to say thank you for you attention. I already tried to get success result via a stored procedure. And unfortunately the result was not ok.Here is my stored procedure: /**************************************************************** CREATE PROCEDURE [MaintStartUpdate] (@PlateNumber_1 [char], @MaintEntryDate_2 [smalldatetime], @MaintReason_5 [char](20), @MaintCompany_6 [char](20), @MaintCompanyTel_7 [char](11), @MaintCompanyAddress_8 [nvarchar](50)) AS UPDATE [CollLogistics].[dbo].[MAINT_START] SET [MaintReason] = @MaintReason_5, [MaintCompany] = @MaintCompany_6, [MaintCompanyTel] = @MaintCompanyTel_7, [MaintCompanyAddress] = @MaintCompanyAddress_8 WHERE PlateNumber = @PlateNumber_1 AND [MaintEntryDate]= @MaintEntryDate_2 GO /************************************************************** I really want to crash my computer right now. Because this is a very stupid and easy SQL update...And I still could not do anything!
-
Firstly I would like to say thank you for you attention. I already tried to get success result via a stored procedure. And unfortunately the result was not ok.Here is my stored procedure: /**************************************************************** CREATE PROCEDURE [MaintStartUpdate] (@PlateNumber_1 [char], @MaintEntryDate_2 [smalldatetime], @MaintReason_5 [char](20), @MaintCompany_6 [char](20), @MaintCompanyTel_7 [char](11), @MaintCompanyAddress_8 [nvarchar](50)) AS UPDATE [CollLogistics].[dbo].[MAINT_START] SET [MaintReason] = @MaintReason_5, [MaintCompany] = @MaintCompany_6, [MaintCompanyTel] = @MaintCompanyTel_7, [MaintCompanyAddress] = @MaintCompanyAddress_8 WHERE PlateNumber = @PlateNumber_1 AND [MaintEntryDate]= @MaintEntryDate_2 GO /************************************************************** I really want to crash my computer right now. Because this is a very stupid and easy SQL update...And I still could not do anything!
innocent73 wrote:
And unfortunately the result was not ok.
What does that mean? How was the result "not ok"? What should it do and what does it do? Some possible area to check:
innocent73 wrote:
(@PlateNumber_1 [char],
Be explicit - how many characters are in this parameter?
innocent73 wrote:
[char](20),
Are you sure a char(20) is what you want? If you supply a value like "hello" it will append 15 spaces to make up to the full size.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
innocent73 wrote:
And unfortunately the result was not ok.
What does that mean? How was the result "not ok"? What should it do and what does it do? Some possible area to check:
innocent73 wrote:
(@PlateNumber_1 [char],
Be explicit - how many characters are in this parameter?
innocent73 wrote:
[char](20),
Are you sure a char(20) is what you want? If you supply a value like "hello" it will append 15 spaces to make up to the full size.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
I am really sorry for my bad english.. The question is I think very simple? How can I send a smalldate parameter from c# to SQL server. That's all!
-
Hello Folks, I really need help to solve a real stupid situation I have a SQL Server database table named MAINT_START.Columns of this table are: PlateNumber char(11)--------> PK MaintEntryDate smalldatetime---> PK MaintReason char(20) I want to update this table's MaintReason column via a SQL String. My code is like this: /********************************************************************** string strUpdate="UPDATE MAINT_START SET MaintReason='" + cMaintReason.Text.TrimEnd() + "'," + " MaintCompany='" + cMaintCompany.Text.TrimEnd() + "'," + " MaintCompanyTel='" + cMaintCompanyTel.Text.TrimEnd() + "'," + " MaintCompanyAddress='" + cMaintCompanyAddress.Text.TrimEnd() + "'" + " WHERE PlateNumber='" + cPlateNumber.Text + "' AND" + " MaintEntryDate =Convert.ToDateTime(cMaintEntry.Text)"; SqlCommand aCommand=new SqlCommand(strUpdate,Myconnection); /************************************************************************ Dear friends is it really impossible to insert or update a smalldatetime column? Please Help..
I'm not really that experienced in SQL, but... We all know that queries are only text, so if you want to pass a date to SQL server, you should pass it as a text. And why not? I believe it would be something like: string strUpdate="UPDATE MAINT_START SET MaintReason='" + cMaintReason.Text.TrimEnd() + "'," + " MaintCompany='" + cMaintCompany.Text.TrimEnd() + "'," + " MaintCompanyTel='" + cMaintCompanyTel.Text.TrimEnd() + "'," + " MaintCompanyAddress='" + cMaintCompanyAddress.Text.TrimEnd() + "'" + " WHERE PlateNumber='" + cPlateNumber.Text + "' AND" + " MaintEntryDate =" DateTime.Parse(cMaintEntry.Text).ToString(); Did you notice the Parse-ToString way? Another way if you really want to pass it as a datetime -I really can't see why the persistence:)- perhaps you should consider using Typed datasets rather than SQLCommands?? Just a suggestion. I hope that was close enough;)
-
I am really sorry for my bad english.. The question is I think very simple? How can I send a smalldate parameter from c# to SQL server. That's all!
innocent73 wrote:
How can I send a smalldate parameter from c# to SQL server
DateTime theDate = new DateTime(2006,06,19);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM MyTable Where [date] = @someDate";
cmd.Parameters.Add("@someDate", theDate);
...
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
I'm not really that experienced in SQL, but... We all know that queries are only text, so if you want to pass a date to SQL server, you should pass it as a text. And why not? I believe it would be something like: string strUpdate="UPDATE MAINT_START SET MaintReason='" + cMaintReason.Text.TrimEnd() + "'," + " MaintCompany='" + cMaintCompany.Text.TrimEnd() + "'," + " MaintCompanyTel='" + cMaintCompanyTel.Text.TrimEnd() + "'," + " MaintCompanyAddress='" + cMaintCompanyAddress.Text.TrimEnd() + "'" + " WHERE PlateNumber='" + cPlateNumber.Text + "' AND" + " MaintEntryDate =" DateTime.Parse(cMaintEntry.Text).ToString(); Did you notice the Parse-ToString way? Another way if you really want to pass it as a datetime -I really can't see why the persistence:)- perhaps you should consider using Typed datasets rather than SQLCommands?? Just a suggestion. I hope that was close enough;)
Nader Elshehabi wrote:
We all know that queries are only text, so if you want to pass a date to SQL server, you should pass it as a text. And why not?
I'll tell you why not: Because you open up the application to explotation by attackers. When you start injecting bits of text into SQL Strings you give the attacker an opening that they can use to create a bigger hole so that they can gain access to your system. See the article I refered to in my initial reply: SQL Injection Attacks and Tips on How To Prevent Them[^]
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog