SqlException was unhandled... Line 1: Incorrect syntax near '2' ... VB.Net, MS SQL, VS 2005

This blows up on debug with the message:

SqlException was unhandled... Line 1: Incorrect syntax near '2'

The value "2", is from a Windows form control for a date selection and the "2" is the month.

Application code bombs and the stripped out SQL works fine in SQL Mgr.

I'm probably killing myself over a misplaced apostrophe...

--APPLICATION CODE
 
        '--Start-----------------------------------------------------------------------------------
        strSQL = "SELECT dbo.P.PI, dbo.P.PSts, dbo.Ro.RoSts, dbo.Rr.RrCoe, "
        strSQL += "dbo.C.CNaSh, dbo.P.PLn, dbo.P.PAdr2 "
        strSQL += "FROM dbo.P INNER JOIN  "
        strSQL += "dbo.Ro ON dbo.P.PI = dbo.Ro.RoI INNER JOIN  "
        strSQL += "dbo.Rr ON dbo.Ro.RoIdx = dbo.Rr.RrRoIdx INNER JOIN  "
        strSQL += "dbo.C ON dbo.P.PCl = dbo.C.CIdx "
        strSQL += "WHERE P.PSts = 6060 "
        strSQL += "AND Rr.RrCoe is not null "
        strSQL += "AND (RoSts = 3 OR RoSts = 4) "
        strSQL += "AND Rr.RrCoe Between ''" & strCalStartDate & "' AND '" & strCalEndDate '"
        strSQL += strWhereClause
        strSQL += "order by CNaSh, PI "
        '--END-------------------------------------------------------------------------------------
 
--SQL Mgr CODE:
 
SELECT dbo.P.PI, dbo.P.PSts, dbo.Ro.RoSts, dbo.Rr.RrCoe,   
dbo.C.CNaSh, dbo.P.PLn, dbo.P.PAdr2  
FROM dbo.P  INNER JOIN  
dbo.Ro  ON dbo.P.PI = dbo.Ro.RoI INNER JOIN  
dbo.Rr  ON dbo.Ro.RoIdx = dbo.Rr.RrRoIdx INNER JOIN  
dbo.C  ON dbo.P.PCl = dbo.C.CIdx  
WHERE P.PSts = 6060
AND Rr.RrCoe is not null 
AND (RoSts = 3 OR RoSts = 4)
AND Rr.RrCoe Between '4/1/07' and '4/1/08'
AND P.PCl = 45 
order by CNaSh, PI

Open in new window

.NET ProgrammingMicrosoft SQL Server 2005

Last Comment

colofornia

8/22/2022 - Mon

photowhiz

4/10/2008

That code is a SQL injection attack just waiting to happen. Use the SqlCommand class; it handles .Net data types like dates, and prevents SQL injection.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(VS.80).aspx

Éric Moreau

4/10/2008

you have an extra '. this line:
strSQL += "AND Rr.RrCoe Between ''" & strCalStartDate & "' AND '" & strCalEndDate '"

should read:
strSQL += "AND Rr.RrCoe Between '" & strCalStartDate & "' AND '" & strCalEndDate '"

colofornia

4/10/2008

ASKER

tx emoreau,

I pasted the should read line in and now the error is:

Unclosed quotation mark before the character string '4/10/2008 order by CNaSh, PI '.
Line 1: Incorrect syntax near '4/10/2008 order by CNaSh, PI '.

I'm not bright enough to know HOW, but I think I see that my VB.Net SQL mash is creating a string out of things I don't want in a string... like I need to end the string right after the date.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

ASKER CERTIFIED SOLUTION

Éric Moreau

4/10/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts