October 16, 2010 at 11:57 pm
Hullo Friends, :w00t:
I need your help. Please help me. 😀
The company I am working for is still using SQL SERVER 2000 and C#NET2008 Window Application.
I have been asked to create a SQL String to retrieve data from TBLSALEORDERS using intMth and IntYear parameters based on ORDERDATE. I have not done this before using just Month and Year before.
Here are the coding.
string strCustID = this.txtCustID.text;
int intMth = Convert.Int32(this.txtMth.text);
int intYear = Convert.Int32(this.txtYear.Text);
String strSql = "";
strSql += "Select OrderID, convert(varchar(10),OrderDate,103) as [OrderDate],";
strSql += "convert(varchar(10), RequiredDate, 103) as [RequiredDate],";
strSql += "convert(varchar(10), ShippedDate, 103) as [ShippedDate], ";
strSql += "From TblSaleOrders ";
strSql += "where (CustomerID) = '" + strCustID + "'";
strSql += " And OrderDate.month = " + intMth ;
strSql += And OrderDate.Year = " + intYear ;
October 17, 2010 at 12:39 pm
Can't provide a whole lot of help right now, reading this on my BlackBerry in the car.
First thing I'll tell you is that you are doing this wrong. You shouldn't be creating and running SQL statements directly in the application. You should be using stored procedures called with appropriate parameters. I can help with the stored proc, but you'll need someone with more experience on the other end to help with how it is called and how you use the returned result set.
How you proceed is up to you.
October 17, 2010 at 4:47 pm
Hi Lynn,
Thank you for trying to help.
October 18, 2010 at 4:56 am
You are creating a big security hole in your application here. As Lynn mentioned you should *not* build up SQL in the application like this you should use a stored procedure.
Read this article on SQL injection, it explains in detail.
October 18, 2010 at 6:26 am
Developers may not want to use stored procedures, I get it, but you should at least use parameterized queries. This avoids T-SQL injection and will lead to plan reuse. This link provides a decent example of how to do this. You can benefit from much simpler code that also performs better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 18, 2010 at 6:43 am
Also - I can't help but notice that you are using what looks to be something like LinQ syntax instead of TransactSQL code. firing that code off as a "regular" query will fail the parser before even attempting to execute.
In particular - "OrderDate.Month". Unless you have a TABLE called Orderdate, and a column called Month, it won't know what you mean. The T-SQL code for that is to use an actual functon call e.g.
MONTH(orderdate)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 18, 2010 at 8:00 am
Okay, here is one way of writing a stored proc to accomplish your task. It assumes that the month and year are passed as separate parameters.
create procedure dbo.MonthlyOrdersForCustomer (
@iCustID int,
@iMonth int,
@iYear int -- as a 4 digit year
)
as
begin
declare @CustID int,
@BeginMonth datetime,
@EndMonth datetime;
set @CustID = @iCustID;
Set @BeginMonth = dateadd(mm, @iMonth - 1, dateadd(yy, @iYear - 1900, cast('19000101' as datetime)));
set @EndMonth = dateadd(mm, 1, @BeginMonth)
select
OrderID,
OrderDate,
RequiredDate,
ShippedDate
from
TblSalesOrders
where
CustomerID = @CustID
and Orderdate >= @BeginDate
and OrderDate < @EndDate;
end
You'll need to add any conversions to dates that you want, this is just to help get you started.
Any errors in the code are unintentional. I wrote it on paper and posting from my BlackBerry.
October 18, 2010 at 12:40 pm
I'm not clear on what exact problem you are trying to resolve.
Yes, you should use either a stored procedure or a parameterized SQL command. But all that was originally posted is that you have to write a query, and then you did exactly that. Is there a problem with the query? Does it not do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 19, 2010 at 1:45 am
Hi Matt Miller,
Regarding your quotation list below:
In particular - "OrderDate.Month". Unless you have a TABLE called Orderdate, and a column called Month, it won't know what you mean. The T-SQL code for that is to use an actual functon call e.g.
MONTH(orderdate)
------------------------------
The table does have a column called OrderDate with Datatype :DateTime : Length 8. There is no column called Month.
The Business Analyst requirements are on the Order Query FORM, the user input numuric Month and Year. So based on these 2 textbox, both are parameters in the normal SQL String to retrieve the data based on ORDERDATE column DATA.
October 19, 2010 at 1:56 am
Hullo Matt Miller,
Yaaa........Hoooooo..........
Thank you very much for sharing this with me:
Month(OrderDate). and I added this as well Year(OrderDate)
I tried it out to meet the Business Analyst Requirements and Specificaition and it works very well on my normal SQL String. The Window application has been implemented to the users.
You are awesome and wonderful in sharing your information with me. You are very generous and precised based on my Original Problem posting seeking for help. Your answer matches my posting.
I am so glad to meet you at this wonderful FORUM. Thank you very much for your help...........Yeee.....haaaaaa.....
Have a Good Day,.
Cheers,
Lennie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply