Geeks With Blogs

News My Blog has been MOVED to https://mfreidge.wordpress.com
Michael Freidgeim's OLD Blog My Blog has been MOVED to https://mfreidge.wordpress.com

I had a MS Access 2002 VB code

Form.RecordSource = " Select * from dbo.[TempBarcodesList]  where SessionID= " & Quoted(hdnSessionID)

with a leading space before Select in SQL statement.

Profiler showed that the following SQL was send to the database 

 exec sp_executesql N'SELECT * FROM   Select * from dbo.[TempBarcodesList]  where SessionID= ''2006-02-22 12:07:06.327''  WHERE "SessionId" = @P1 AND "AutoID" = @P2', N'@P1 varchar(23),@P2 bigint', '2006-02-22 12:07:06.327', 22 

and it obviosly had sintax errors.

When the leading space  was remove access inserted brackets and generated SQL became correct:

exec sp_executesql N'SELECT * FROM ( Select * from dbo.[TempBarcodesList]  where SessionID= ''2006-02-22 11:49:48.043'' ) AS DRVD_TBL WHERE "SessionId" = @P1 AND "AutoID" = @P2', N'@P1 varchar(23),@P2 bigint', '2006-02-22 11:49:48.043', 12 

 

 

Posted on Wednesday, February 22, 2006 8:36 AM SQL Server | Back to top


Comments on this post: MS Access Project uses incorrect SQL if RecordSource starts with leading space.

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net