none
Syntax for file path in SQL Select RRS feed

  • Question

  • I need to define a connection command as follows:

    Dim filename as string

    filename = "C:\Folder\Subfolder\file.ext"  'In reality this is dynamically assigned

    Connection.Command = "SELECT * FROM Table1 WHERE FileNameWithPathField = " & filename

    -----------------------

    While reader is executed, it returns an error message "Incorrect Syntax Near 'C:'.

    Where am I wrong? I have tried replacing \ with / and with \\.

    I am using VB.Net on VS Express 2012 

    Thanks

    Shameer

    • Moved by Bob Beauchemin Tuesday, September 30, 2014 8:27 PM Moved to the client-side SqlClient forum
    Tuesday, September 30, 2014 10:25 AM

Answers

  • In T-SQL you have to set a string values into single quotes =>

    Connection.Command = "SELECT * FROM Table1 WHERE FileNameWithPathField = '" & filename & "'"

    But better use SQL Parameter to prevent issues / SQL injection


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by mdshameer Wednesday, October 1, 2014 8:22 AM
    Tuesday, September 30, 2014 10:42 AM
  • Connection.Command = "SELECT * FROM Table1 WHERE FileNameWithPathField = @filename"
    Connecttion.Command.Parameters.Add("@filename", SysDbType.NVarChar, 255).Value = filename

    You should never build query strings by concatenating user input. Nothing good comes out of that, but a lot of bad things. As you just experienced.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by mdshameer Wednesday, October 1, 2014 8:22 AM
    Tuesday, September 30, 2014 12:28 PM

All replies

  • In T-SQL you have to set a string values into single quotes =>

    Connection.Command = "SELECT * FROM Table1 WHERE FileNameWithPathField = '" & filename & "'"

    But better use SQL Parameter to prevent issues / SQL injection


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by mdshameer Wednesday, October 1, 2014 8:22 AM
    Tuesday, September 30, 2014 10:42 AM
  • Connection.Command = "SELECT * FROM Table1 WHERE FileNameWithPathField = @filename"
    Connecttion.Command.Parameters.Add("@filename", SysDbType.NVarChar, 255).Value = filename

    You should never build query strings by concatenating user input. Nothing good comes out of that, but a lot of bad things. As you just experienced.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by mdshameer Wednesday, October 1, 2014 8:22 AM
    Tuesday, September 30, 2014 12:28 PM
  • Yes, both these works.

    Thanks, I will learn parametrization

    Thanks

    Shameer

    Wednesday, October 1, 2014 8:21 AM