troubleshooting Question

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

Avatar of colofornia
colofornia asked on
.NET ProgrammingMicrosoft SQL Server 2005
6 Comments1 Solution3402 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Éric Moreau
Senior .Net Consultant
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros