VB.net Windows Form Error System.Data.SqlClient.SqlException: 'Incorrect syntax near '#'.'

Question

I am not a programmer and won't claim to be one. I am trying to do a query and having a problem.

System.Data.SqlClient.SqlException: 'Incorrect syntax near '1'.'

    Public Sub SearchData()
        'Populates the Datagrid
        Dim id As String = Date1.Value
        Dim id2 As String = Date2.Value
        Dim id3 As String = namecbx.SelectedValue
        Dim sql1 As String = ""
        Dim SQLConnectionstring As String = My.Settings.OFIData
        sql1 = "SELECT TOP (100) PERCENT FullName, FunctionCode, DateTime FROM dbo.timeclockpunches WHERE (DateTime BETWEEN " & id & " AND " & id2 & ") AND (FullName =" & id3 & ")"
        '---Standard Code for database connection
        Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection(SQLConnectionstring)
        Dim comm As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql1, conn)
        Dim dataadapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(comm)
        Dim ds As DataSet = New DataSet()
        Dim RowIndex As Integer = 0
        '---open the connection and fill the dataset---
        conn.Open()
        '---fill the dataset---
        dataadapter.Fill(ds, "FullName") ' Error Happens Here
        '---close the connection---
        conn.Close()
        '---bind to the DataGridView control---
        vdgv.DataSource = ds
        dataadapter.Dispose()
        ds.Dispose()
        conn.Dispose()
        comm.Dispose()
        vdgv.DataMember = "FullName"
        If vdgv.RowCount >= 1 Then
            'Column Names = "Between Quotes" will disable as column header
            vdgv.Columns(0).HeaderCell.Value = "FullName"
            vdgv.Columns(1).HeaderCell.Value = "FunctionCode"
            vdgv.Columns(2).HeaderCell.Value = "DateTime"

            'Column Visible
            vdgv.Columns(0).Visible = True
            vdgv.Columns(1).Visible = True
            vdgv.Columns(2).Visible = True

            'Column Width
            vdgv.Columns(0).Width = 250
            vdgv.Columns(1).Width = 100
            vdgv.Columns(2).Width = 100

            'Hides the Horizontal Scrollbar
            vdgv.ScrollBars = ScrollBars.Vertical
            'Does not allow users to add rows
            vdgv.AllowUserToAddRows = False
        End If
    End Sub

Answer 1

Change:

sql1 = "SELECT TOP (100) PERCENT FullName, FunctionCode, DateTime FROM dbo.timeclockpunches WHERE (DateTime BETWEEN " & id & " AND " & id2 & ") AND (FullName =" & id3 & ")"

to:

sql1 = "SELECT TOP (100) PERCENT FullName, FunctionCode, DateTime FROM dbo.timeclockpunches WHERE (DateTime BETWEEN @start AND @end) AND (FullName = @name)"

Then after:

      Dim comm As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql1, conn)

Add:

comm.Parameters.Add("@start", SqlDbType.Date).Value = Date1.Value
comm.Parameters.Add("@end", SqlDbType.Date).Value = Date2.Value
comm.Parameters.Add("@name", SqlDbType.NVarChar, 40).Value = namecbx.SelectedValue

Building an SQL string by inlining values as you tried to do is difficult to get right. It is a lot easier to use a parameterised commands. That is, you use these things starting with @ and then you defined with SqlCommand.Parameters.Add.